Complete Oracle SQL Masterclass: Go from zero to Hero

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

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.

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

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.

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.

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.

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.

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.