Oracle SQL - From Beginner to Pro

Analyze a schema creation script and its log to understand core Oracle commands, including drop user cascade, create user identified by, grant privileges, and commit, rollback, and save point.

Learn to create sequences and triggers in Oracle SQL, using a before insert trigger with a sequence to populate primary keys, and explore mutating triggers and best practices.

Explore data manipulation language in Oracle sql, covering inserts, updates, deletes, dml table expressions, and where clauses, with practical examples and remote and local schema references.

Explore multi-table queries by comparing old non-ansi and ANSI join syntax, mastering inner and outer joins. Learn using and on clauses, and practice left, right outer joins, and self-joins.

Explore safe use of subqueries in Oracle SQL, distinguishing uncorrelated and correlated forms and single-row versus multi-row queries. Examine performance implications, inline views, and the having clause.

Explore Oracle SQL set operators such as union all, union, intersect, and minus. Master translate and replace string functions and the case statement.

Explore advanced Oracle SQL queries, traversing hierarchies with connect by and level, using start with and lpad, then apply roll up and analytics like rank, dense_rank, cume_dist, and percent_rank.

Explore PL/SQL procedures by building update product suggested price, which uses a cursor over previous sales, computes the average, and updates the product's suggested price with robust exception handling.

Transform a simple price-suggestion procedure into a weighted PL/SQL function that factors order quantity, then call the function from the procedure to apply weighted discounts.

Explore pl/sql triggers for auditing changes to the suggested price of a product with a before update trigger, and survey Oracle packages as building blocks for modular code.

Explore oracle collections, including nested tables and associative arrays, and practice inserting, updating, and deleting nested elements with constructors and inline views, while noting performance considerations.

Explore large objects in Oracle, including clob, blob, and bfile, and compare in-database storage with external file storage, with guidance on management, performance, and common pitfalls.

Examine Oracle architecture and memory areas, including SGA and before/after image logs, to identify performance hotspots and bottlenecks; also cover views, materialized views, and synonyms.

Learn to boost Oracle SQL performance by using bound variables over dynamic SQL, leveraging indices and composite indices, and applying analytic functions and materialized views judiciously.

Review relational theory, normalization, er diagrams, ddl and dml, and queries to see how oracle sql models data and supports practical database design.