Oracle SQL Mastery: From Novice to Certified Database Pro

Explore virtualization by installing and running a guest operating system inside a host, using Oracle VirtualBox or VMware to run Linux on Windows.

Install and set up VMware on Windows, including downloading VMware Workstation Player and the Oracle VM image. Prepare system requirements and complete login and license steps for Oracle access.

install Oracle VirtualBox on Mac and import a pre-built Oracle VM image to run a ready-to-use database environment.

Learn how to download and install Oracle SQL Developer on your local machine to connect to a VM-based Oracle database, with platform-specific setup guidance for Windows, Mac, and Linux.

Learn how to set up Oracle Apex, a web-based interface to run SQL commands, by requesting a free workspace, signing in, and using the SQL Workshop.

Learn to set up a connection to the HR schema in Oracle SQL Developer, test the connection, and explore tables, views, and other objects in HR DB.

Install the Oracle Apex sample HR data set, understand the OEHR prefix, and explore objects in SQL Workshop and Object Browser. Run queries and view data with SQL commands.

Explore database objects, including tables, views, indexes, stored procedures, triggers, and functions, and learn how each serves a specific purpose in structuring and querying data.

Explore CRUD operations in Oracle, including create, read, update, and delete, and learn to write basic Oracle queries for apps like blogs and ecommerce.

Explore the five Oracle statement types—DDL, DQL, DML, DCL, and TCL—and learn their purposes, examples, and how they manage data, access, and transactions.

Write a select query to list employees whose first name is John in Oracle SQL, using select star from employees and a where clause, ending with a semicolon.

Create a book collection table in Oracle SQL with book_id as primary key and fields for title, author's first name, last name, publisher, publication date, isbn, pages, genre, and availability.

Learn how to insert multiple records in Oracle database using the insert all syntax, with hard-coded values or a subquery from dual, applied to the employees table.

Master Oracle SQL update queries by increasing IT department salaries 10% (id 60) and setting commission percentages to zero for those above 15%, using SQL Developer or Notepad before execution.

Explore Oracle SQL basics: ddl, dql, dml, dcl, and tcl, learn data types from char to nchar, number, date, timestamp, lob, and master create, insert, select, update, and delete operations.

Learn how to create column aliases to customize result set headers using the as keyword, handle spaces with double quotes, and export user friendly reports.

Explore null values in the HR schema's employees table, including commission percentage, manager id, and phone number, and learn that null denotes absence of data in any data type.

Learn to produce readable SQL output using concatenation and the concat function, handle null values, format dates, and combine strings from dual and employees in SQL developer.

Explore date formatting in Oracle SQL by using the to_char function on hire_date from the employee table, applying custom formats like MON-DD-YYYY and the DL format with sysdate.

Discover how the quote operator creates string literals, handles escaping, and supports multiline strings in Oracle SQL; compare it with concatenation and explore code operator syntax with q.

Learn how to filter data in Oracle sql using the where clause, after select and from, by applying a condition such as salary > 5000 to return only matching employees.

Master Oracle SQL comparison operators, including equal to, not equal to, exclamation equal to, less than, greater than, and their equal variants, with practical filtering examples on department_id and salary.

Learn to query employees by salary range using the between operator in Oracle SQL. Use select from employees where salary between 5000 and 10000 to retrieve all within that range.

Master Oracle SQL filtering with NULL using IS NULL and IS NOT NULL to fetch or exclude missing values, illustrated by querying employees with null commission percentages.

Explore how the logical and operator in Oracle SQL combines conditions, like department_id = 60 and salary > 5000, and how to chain multiple and conditions for complex queries.

Discover how the OR logical operator in Oracle SQL returns results when at least one condition is true, such as department id 60 or salary greater than 5000.

Explore Oracle's rules of precedence to see how arithmetic, comparison, and logical operators are evaluated, and learn how parentheses clarify and sometimes change query results.

Master Oracle data definition language (DDL) to create, modify, and drop tables, views, indexes, and sequences; enforce constraints, define primary and foreign keys, and use alter statements for structural changes.

Create tables in Oracle using the create table command to define columns with data types and constraints, including primary keys and not null, plus table-level constraints like foreign keys.

Explore how foreign keys establish table relationships, enforce referential integrity, and prevent invalid or orphaned records, with real examples from employees and departments.

Explore the syntax and usage of the alter statement in Oracle, focusing on the HR schema, to modify tables and other objects with add, modify, rename, and drop column operations.

Learn to use alter table add to add one or multiple columns with data types and constraints, shown by adding organizer phone and organizer email to a shopping fairs table.

Rename a column using the alter table rename command, as shown in the shopping fair table by changing organizer email to organizer mail.

Learn how to use the alter table modify command to toggle column visibility between visible and invisible in Oracle SQL, and how to access hidden columns by explicitly selecting them.

Master Oracle SQL insert statements by understanding the syntax: insert into, table name, column list, and values, and learn how not null constraints, column order, and duplicates shape inserts.

Explore how insert first differs from insert all in Oracle SQL, including when clauses, else, and table bifurcation to control data distribution across high, mid, and low salary tables.

Update multiple columns in Oracle using the set and where clauses to modify emails and phones for specific records, and apply upper and lower functions for case-sensitive matches.

Explore how to delete specific records from a table using delete from table where clause, and recognize the permanent data removal when criteria match.

Delete all records from a table in Oracle database while preserving the table structure, using delete from table name, and know that this action cannot be undone without backups.

Engage in a hands-on challenge on dml statements by updating a customer’s phone, deleting Smiths, inserting John-based records into a VIP table, and updating phones for @example.com emails.

Learn how Oracle SQL joins combine data from multiple tables using a common column, with inner joins, aliases, and on clauses to relate employees and departments by department id.

Master outer joins in Oracle, including left, right, and full outer joins, to retrieve unmatched rows from multiple tables and fill gaps with null values.

Discover how Oracle SQL functions work, from scalar and grouping functions to practical uses like calculating salaries, manipulating data, and converting data types with input parameters.

Explore nesting multiple functions in Oracle SQL queries, using nvl, to_char, coalesce, and trunc to handle nulls, format dates, and derive defaults with practical examples.

Discover how to group data in a table using the group by clause and apply sorting in Oracle, ordering results in ascending or descending on a chosen field.

Group data by manufacturer using Oracle SQL's group by clause. Learn to apply aggregate functions—count, sum, avg, max, min—to produce insights like total toys per manufacturer.

Master the Oracle SQL group by clause to count toys per manufacturer, using select manufacturer, count(*) as total toys from toys, and group by manufacturer to reveal aggregated results.

Master sorting data by multiple columns in Oracle SQL using order by. Sort by department id ascending, then salary descending to organize employees by groups.