Oracle 12 Cloud: Advanced SQL/PLSQL Hands-On

Explore the Oracle developer VM, including downloading the app development VM from Oracle, installing Oracle toolbox, importing and running the VM, and configuring login credentials for hands-on SQL/PLSQL exercises.

Open Oracle SQL Developer to install, launch, and connect to your database, using the Java-based SQL client to run queries and work with Oracle SQL and PL/SQL.

Create a new database connection by configuring host, port, service name, and credentials; test and save, then open the SQL worksheet to query the schema's tables and views.

Explore subqueries and inline views in Oracle 12 cloud, using nested queries and inline views in from and where clauses to identify the top 3 highest earning employees.

Master using single-value subqueries in Oracle SQL to filter results by a scalar value, including finding the highest or lowest salaries with the max and min functions.

Learn how to use multiple-value subqueries in Oracle SQL/PLSQL, returning department IDs for a location and filtering employees with in or not in operators.

Explore how correlated subqueries drive row-by-row comparisons, such as finding employees whose salaries exceed their department averages, via practical Oracle 12 cloud exercises.

Learn how multicolumn subqueries return multiple columns, such as mean salary and job_id per job, and use them to compare each employee’s salary and job_id against those values.

Explore using inline views in SQL: embed a subquery in the from clause to create a temporary virtual table, alias it, and join with other tables to fetch top salaries.

Understand how a B-tree index on table columns accelerates queries by avoiding full table scans. See how index traversal uses leaf blocks to locate rows efficiently.

Create and optimize indexes on table columns in Oracle 12 cloud, including single and composite indexes, and examine execution plans to understand performance and data retrieval.

Oracle data dictionary: learn how it stores information about tables, indexes, columns, sequences, and triggers, and how to use all_, user_, dba_, and dynamic performance views.

Explore how to query Oracle data dictionary views to discover information about tables, views, triggers, and indexes in your schema, using uppercase names and partial searches for object names.

Explore querying data dictionary views and dynamic performance views to discover tables, columns, and indexes, plus current database sessions and instances.

Explore database transactions as units of work in Oracle, detailing how dml operations are grouped, isolated, and committed or rolled back, with locks governing concurrent access and ddl effects.

Watch how database transactions in action enforce isolation across sessions, showing updates become visible after commit while other sessions see old values until the transaction is committed; rollback reverts changes.

Learn how Oracle locks rows during concurrent updates, observe how one session holds a lock on an employee row while another waits, and explore how commit or rollback frees locks.

Discover how sequences generate unique numbers for key values, set a start value and increment, and utilize nextval to insert sequential IDs into tables.

Create and manage Oracle sequences in Oracle Cloud, configure caching and max values, and retrieve next or current values to power inserts into tables such as the school supplies table.

Explore identity columns in oracle 12 cloud and how they behave like built-in table sequences that automatically generate values on insert.

Explore how Oracle table triggers automatically execute insert, update, or delete operations to enforce business rules, security, and referential integrity across tables, with DDL triggers noted.

Create and test triggers on table 1 and table 2 to duplicate data after insert, and implement an audit trigger tracking insert, update, or delete events.

Explore PL/SQL procedures, functions, and packages within the Oracle database. Learn to create reusable logic using the procedure structure with declaration, begin, and exception sections.

Discover Oracle Cloud PL/SQL functions and how they compare to procedures, including creating functions with arguments and return values, stored in the database, and invoked from SQL statements.

Explore how PL/SQL packages group related procedures, functions, types, and variables into a single unit. Learn how the spec exposes the interface while the body implements the code.

Create and test PL/SQL procedures and functions in Oracle 12 Cloud by using input parameters, declare variables, perform select into, enable dbms_output, and compare procedure versus function behavior.

Create synonyms to provide alternate names for schema objects such as tables, views, sequences, and procedures, enabling cross-schema access and simplified SQL with create or replace and drop syntax.

Create a simple view to store a query result, then query it to filter employees by job_id using a view defined as select from employee table where job_id = ...

Create and replace complex views by joining views with tables, enabling data presentation without rewriting queries. Restrict columns and embed conditions to simplify access and enhance security.

Partitioning a table divides it into named partitions with a partitioning key that routes inserts, updates, and deletes to the correct partition, e.g., monthly partitions by date.

Learn how table partitioning boosts performance via the optimizer's partition pruning that scans only relevant partitions, and explore partition-level administration benefits like targeted backups and maintenance, with possible index-influenced pruning.

Explore the main table partitioning types in Oracle 12 Cloud, including list, range, hash, and interval partitions, with examples like regional and monthly data grouping.

Create and query a list-partitioned table by mapping the sales_state column to explicit partitions like California, New York, Central, and other, demonstrating partition maintenance and selective querying.

Split the default list partition into two new partitions with alter table, then insert and verify records route to the correct state-based partition, such as Nevada.

Build a range-partitioned table by partitioning on the sales_date column and setting an upper bound for each partition. Maintain partitions in advance to keep inserts mapped and avoid errors.

Learn to implement hash partitioning on a sales number column, create four partitions, and auto distribute rows for performance gains; analyze the table and view partition distribution.