Complete Oracle SQL Masterclass: Go from zero to Hero

Learn how to set up Oracle Express, connect to the system schema using SQL Developer or SQL Plus, and run basic queries like describe and select from employees.

Explore selection and projection with sql queries, viewing how to select all or specific columns from tables, and apply arithmetic operators with proper precedence to compute values.

Understand null values and their distinction from zero or empty, learn how null affects arithmetic, and discover column aliases for formatting, including using as for annual salary.

Master the concatenation operator and distinct keyword in Oracle SQL by concatenating character strings and numeric values, handling literal strings, and removing duplicates with practical examples.

Explore the where clause and comparison operators in Oracle SQL, learning how to filter rows by department and salary, compare strings, and apply case sensitivity with practical queries.

Explore how to use between and in operators in Oracle SQL to filter numeric ranges (salary), select by specific values, and search data like last names in the where clause.

Explore how the like operator and wildcards enable pattern matching in Oracle SQL, using underscore, percent, and escape sequences, and distinguish is null from is not null to find nulls.

Explore how to filter data with logical operators in Oracle SQL, using and, or, and not in where clauses, combining salary thresholds with pattern matching like 'S%'.

Learn to sort data with the order by clause in Oracle SQL, using ascending and descending orders, sorting by columns and aliases, and handling nulls.

Explore working with dates in Oracle SQL by using the date data type, date literals, and comparison operators to filter employees by hire dates, including between and like.

Explore SQL functions, including single-row functions and aggregate group functions. Understand how inputs map to outputs and how grouping shapes the results.

Explore how the Oracle dual table acts as a dummy single-column source. Use select one from dual to fetch a single value or date for programming.

Explore case manipulation in Oracle SQL using upper and lower functions, plus a third function. These functions affect only display, not stored data, in selects.

Explore character manipulation in Oracle SQL masterclass by using concat, substring, length, instr, and padding functions like LPAD and RPAD to format and align names.

Explore Oracle SQL number functions, including rounding, floor, trunc, and modulus, with practical examples using a sample number and the dual table to illustrate decimal control.

Learn how to convert dates to strings and strings to dates using Oracle conversion functions like to_char and to_date, with custom formats and handling time components.

Master date handling in Oracle SQL by adding days and hours, subtracting values. Explore months between, add months, next day, last day, truncate, and round for payroll and accounting.

Apply conditional logic in Oracle SQL using case and decode constructs to implement salary raises by job id, with default handling, and explore NVL, NVL2, NULLIF, and COALESCE.

Explore case and decode in Oracle SQL to handle null values, using if-then-else style logic in queries, with examples on salary, commission, and string length checks.

Learn how to nest single-row functions in Oracle SQL to concatenate strings, extract substrings, and uppercase first names, demonstrated on employee data.

Learn how group functions in Oracle SQL compute aggregates like average, max, min, standard deviation, and variance, handle nulls, and use group by.

Learn to group data using group by on department and job, apply aggregate functions like sum, average, min, max, and perform multi-column grouping with ordering.

Discover how to filter grouped data with the having clause in Oracle SQL, differentiate it from the where clause, and apply aggregate-driven conditions to groups.

Explore cross joins and Cartesian products in Oracle SQL, showing how combining rows from employees and departments yields the Cartesian product and how joins differ from single-table queries.

Explore natural join and inner join concepts in Oracle SQL, compare their behaviors, and learn to use on and using clauses, aliases, and common columns to join departments and locations.

Explore full outer joins by combining employees and departments. Learn how left and right joins reveal nulls for nonmatching records and how normalization supports clean data.

Explore joining multiple tables in Oracle SQL, convert cartesian products into meaningful results with proper join conditions, and use where clauses for adding multiple criteria across employees, departments, and locations.

Master self joins in Oracle SQL by pairing the employees table with itself, linking on manager_id to show each employee with their manager’s name.

Explore non-equijoins in Oracle SQL by joining on non-equality conditions, using salaries from the jobs table to filter employees by salary ranges and roles such as programmer.

Explore how sessions arise from connections and enable user interactions with the database. Learn that transactions are the unit of work behind inserts, updates, and deletes.

Explore the insert statement in Oracle SQL, manage transactions with commit and rollback, and insert data into departments by specifying columns and values.

Master the update statement in Oracle SQL by updating one or multiple columns with set and where, including salary hikes, while enforcing referential integrity and primary key constraints.

Learn to use delete statements to remove rows from tables with where clauses. Understand commit or roll back and referential integrity between parent and child tables.

Explore the deadlock concept in operating systems and databases, where two processes hold exclusive locks on resources and wait for each other, forming a circular wait and possible resolution.

Demonstrate deadlock in Oracle SQL by locking rows with select for update, exploring exclusive locks and circular waits between sessions, and showing resolution.

Explore data definition language and the create table command, contrasting ddl with dml and commit rollback, and practice creating tables and querying with select.

Explore how to modify tables with alter table by adding and dropping columns. Use describe commands to view the updated table structure.

Discover the Oracle data dictionary and metadata concepts, including dictionary tables and views, and learn how to query schema objects, tables, and access controls for database administration.

Master data management in Oracle SQL by using create table as to back up data, rename tables, and truncate tables, while comparing truncate with delete and exploring rollback implications.

Demonstrate dropping a table in Oracle SQL, permanently removing the table and its structure from a schema, with rollback limitations, production environment caution, and the need to obtain proper permissions.

Explore the most common Oracle data types, including variable length character data (char, varchar), unicode storage, numeric with precision and scale, and date types, plus the royalty pseudo column.

Gain understanding of constraints and the primary key, including why a primary key must be unique and not null, with real-world examples of unique identifiers in databases.

Explore Oracle data dictionary constraints using user_constraints and user_cons_columns; learn to join them to list constraint names, tables, columns, positions, and related indexes for a table.

Explore creating primary keys in Oracle using column and table level constraints, naming options, and composite primary keys with uniqueness and not null guarantees.

Learn how unique constraints enforce distinct values in a column and how composite unique constraints on multiple columns depend on column order, with Oracle SQL examples that allow NULL values.

Explore referential integrity with a foreign key from employees to departments, enforcing valid department references, and preventing orphan records, while using joins and handling nulls and delete options.

Enforce referential integrity in Oracle by creating a parent table and a child table with a foreign key, and understand cascade behaviors and constraint management.

Explore not-null constraints in Oracle SQL, including column-level and explicit naming, see how they enforce data entry and how to add constraints after table creation.

Learn to create and use check constraints in Oracle SQL, including table-level and column-level constraints, with explicit names, and to add, test, and view them in user_constraints.

Drop and disable constraints to see how unique constraints prevent duplicates in a test table. Enable constraints, verify status in the data dictionary, and recreate tables to confirm behavior.

Create and modify simple views in Oracle SQL to save complex queries, using create view or replace, with where filters and view hierarchies.

Explore simple and complex views in Oracle SQL, including single-table queries, natural joins, aggregate functions with column aliases, and read-only views that prevent DML on base tables.