Oracle Database 12c Certified Professional - SQL Foundations

Shows how Ted Codd sparked the relational paradigm, moves from flat file history to tables with rows and columns, and links data via common columns to form an rdbms.

Explore the Oracle 12c RDBMS architecture, platform support, and storage and memory components, including data files, table spaces, caches, background processes, and features like RAC, Data Guard, and partitioning.

Explore structured query language for relational databases, covering data retrieval, modification, database object management, user privileges, and transaction control, with ansi iso standards and cross-platform tools like sequel developer.

Learn to install and configure sequel developer, connect to an Oracle database with administrative and linked up connections, test the connection, and run scripts to create tables and load data.

Set up Oracle APEX, a free cloud database, to run course exercises by uploading Apex only and linked up scripts in order.

Explore the basic SQL syntax rules in Oracle, including case insensitivity, whitespace handling, and the use of semicolon and slash as statement terminators, with examples.

Learn how to project data from a table using the select statement, including single-column, multi-column, and star projections, and understand the from clause and column order impact.

Learn to view a table's column structure and data types using describe, compare it with select star, and explore related tables in the linked up dataset.

Explore column aliases to format sql output, rename headers, and improve readability using the as keyword or plain alias syntax. Use quotes to preserve whitespace and case in column aliases.

Explore arithmetic operators in sql using the dual (pseudo) table to compute expressions via select, and show the difference between signup date and start date from employees, with string literals.

Use the distinct keyword to reveal unique values, querying addresses for states and employees for project IDs. Learn how null values appear in distinct results.

Learn how to use the concatenation operator to join first name and last name into a single output column, including spaces and string literals, and apply aliases for clearer results.

Explore the concept of selectivity in SQL by using the where clause to restrict row counts, illustrated with gender-based filtering on the employees table and demonstrating projection and condition syntax.

Explore selectivity and conditions of equality in the where clause, including arithmetic expressions, date comparisons, and null checks using is null for null project ids.

Explore conditions of non-equality in SQL, using not equal to and standard operators in where clauses, including string and date comparisons, and multi-column conditions.

Learn to use the between operator for range conditions in a where clause, with inclusive bounds that apply to numeric values, dates, and string values.

Explore how the in clause filters results by a finite set of values in the where clause, using string and numeric examples.

Learn pattern matching in sql with the like clause, using percent and underscore wildcards to filter selects. Observe case sensitivity and follow practical examples on employees and address data.

Explore boolean conditions to combine multiple where criteria using and, or, and not. Use like for pattern matching and between and not between to refine results.

Sort query results with the order by clause, using ascending or descending order, and sort by date of birth, start date, or aliases, while noting the required clause order.

Learn to sort data with the order by clause, using primary, secondary, and tertiary sorts across multiple columns, including positional sorting by column position and optional ascending or descending order.

Explore ampersand substitution, a runtime variable substitution that prompts for values to filter and sort SQL statements, enabling interactive where clauses, order by, and debugging scenarios.

Discover how single-row functions perform one-to-one data transformations in SQL, using examples like upper and square root, and contrast row-level with group functions.

Explore case conversion functions in Oracle SQL, including upper, lower, and knit cap, and how they transform display without altering stored data. These are functions applied to names and phrases.

Explore basic string manipulation functions in SQL, including length, lpad, rpad, trimming, left trim, right trim, and concat, with practical examples from addresses, websites, and names to format data.

Explore advanced string manipulation functions like replace, substring, and in string to transform and extract text in SQL, using start and end positions and negative indexing.

Explore Oracle date functions for arithmetic, learn to use current date and timestamp, differentiate date and timestamp with local time zone, and apply months_between and add_months to compute intervals.

Explore arithmetic functions in Oracle, including rounding, truncating, and the modulus operation, applied to numbers and dates with practical examples from dual and employee tables.

Learn how to convert date values to characters using to_char and format masks, including day, month, year, and time representations and default date displays.

Convert character strings to dates using the TO_DATE function and format masks, enabling date arithmetic and the months_between function on legacy data.

Learn numeric conversion in Oracle SQL by using to_number, avoiding string-based arithmetic, and handling format masks to convert money and date values into accurate numbers.

Explore handling null values in Oracle SQL with conditional expression functions such as NVL, NVL2, and DECODE, including replacing nulls with zeros or descriptive nicknames.

Master group functions for aggregate data transformation. Use count, min, max, sum, and average to summarize data groups and handle nulls and where clauses.

Learn how to use the group by clause to aggregate data, compute averages like login counts by gender, and handle multi-column groupings with project id, noting pitfalls and order by.

Learn how to exclude grouped rows with the having clause in SQL, filtering by project id and gender to show only average login counts greater than 1100 or 1300.

Combine the sql clauses in their required order: select, from, where, group by, having, and order by, to build syntactically correct queries.

Compare implicit and explicit join notation in sql, using common columns like employee ID to link employees and addresses, and explain how where vs join on govern the join.

Explore implicit cartesian joins, the cartesian product or cross join, and why they yield many rows and poor performance. Learn join syntax using a where clause on common columns.

Apply implicit equal joins to relate employees and addresses through the employee ID column, avoiding cartesian products; use table aliases to simplify multi-table queries while balancing readability and standards.

Explore implicit and left outer joins in SQL, ensuring included nulls and unassigned employees, by comparing inner versus outer joins and using explicit notation.

Explore explicit cross joins, also known as cartesian joins, which pair every row from two tables and yield a product-sized result, and learn why such joins are rarely used.

Explore ambiguous explicit joins with natural join to link employees and their email addresses by a common column, and see how named columns prevent unintended cartesian products.

Explore explicit column joins with the join using clause to link employees and messages on employee_id. Leverage this syntax to free the where clause and enable sorting with order by.

Master fully specified explicit joins with the join on clause, using equivalence notation and aliases, and compare them to implicit joins that rely on the where clause.

Learn non-equal joins and self-joins in Oracle SQL foundations, including how non-equal conditions differ from equality joins, and how a table can join to itself.

Master multi-table joins from two to many tables, using bridge tables to link employees to awards. Apply n minus one join conditions and explore explicit, implicit, and natural join methods.

Learn how scalar subqueries return a single value to compare with outer queries, using examples with web sites hit counts, averages, and the max hit count to avoid errors.

Uncover advanced scalar subqueries in having and select clauses to compare project id averages of login counts with overall averages, and to display login averages beside names.

Explore multi-row subqueries in SQL, handling subqueries that return multiple rows, using greater than any and greater than all, with having and group by project id and login count.

Explore correlated subqueries, distinguish them from non-correlated ones, and learn how outer and inner queries interact with exists, including performance considerations and the option to rewrite as joins.

Explore multi-column subqueries and inline views in Oracle SQL, returning multiple columns and rows, using in and join techniques to compare project-level login counts to averages.

Demonstrate nested subqueries by evaluating innermost queries first and comparing blog and website hit counts, noting nesting can reach 255 levels in where, having, from, or select clauses.

Explore set operators in SQL, including intersect, minus, union, and union all, to combine queries, retrieve common values, subtract results, and understand performance with large data sets.

learn to create data with the insert statement, a core dml operation, using positional notation, specifying column order, handling nulls, and performing multi-row inserts.

Learn to modify existing data using the update statement with single and multi-column changes, targeted by where clauses, and avoid unconditional updates.

Remove data from tables using delete with conditions, pattern matching, and boolean filters; compare to truncate, which unconditionally clears all rows instantly.

Master transaction control statements in SQL foundations, including commit, rollback, and save points, to maintain data concurrency. Understand how insert, update, and delete become visible only after commit.