Oracle Database 12c Certified Professional - SQL Foundations

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.

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.

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 how the in clause filters results by a finite set of values in the where clause, using string and numeric examples.

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 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.

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

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.

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.

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.

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.

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.

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.