Oracle SQL Mastery: From Novice to Certified Database Pro

Compare Oracle database installation options: virtual machine, local installation, and Oracle Apex web version; recommend virtual machine with SQL Developer, Oracle Apex as fallback.

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.

Set up a pre-configured Oracle database VM on VMware workstation, import the image, accept the license, start the VM, and access pre-installed tools, passwords, and database details.

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

Install Oracle VirtualBox on Windows and import a pre-built Oracle VM image (.ova) to run an Oracle database. Resolve dependencies, including the MS Visual C++ redistributable and Python bindings.

Import a pre-built ovf image into Oracle VirtualBox to create a virtual machine, then adjust memory and CPUs and explore full screen, display resolution, and shutdown options.

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.

Explore Oracle sample schemas, including HR, SH, OE, and IX, and learn how each theme models employee data, sales history, e-commerce orders, and information exchange as practical building blocks.

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.

Learn to retrieve data with the select statement in Oracle SQL, fetching all or specific columns from the employees table and filtering results with a where clause.

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.

Explore Oracle data types across character, numeric, date and time, and large object categories, and learn how char, varchar2, nchar, nvarchar2, number, date, timestamp, blob, clob, and nclob influence storage.

Learn to create your first Oracle table with the create table syntax, define columns and data types, and apply primary key and table-level constraints.

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.

Insert a single record into an employees table with insert into, listing columns such as employee id, name, email, hire date, and salary, using to_date for date formatting.

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.

Learn to insert records into the book collection table using insert all or two separate insert statements in SQL Developer, and verify the results.

Learn how to update existing records in Oracle Database using the update command, setting new salary and commission_pct values with where conditions and multi-column updates.

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.

Learn how to delete a single record with the delete from table where clause, using a practical example of removing an employee by id 300 and verifying the deletion.

Execute a delete query by removing all employees with id greater than 300, preview with select star from employees, then apply delete from employees using a where clause.

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.

Master DQL with select queries, extracting unique values, handling nulls, and using column aliases for readable reports; format outputs and apply literal strings and code operators for robust Oracle SQL.

Learn how distinct and unique operators return unique values from tables, including multi-column combinations and handling nulls, with practical Oracle SQL examples on department IDs and salaries.

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.

Learn to perform calculations in select statements using Oracle arithmetic operators—addition, subtraction, multiplication, and division—create calculated columns with aliases and derive hourly rates from salaries.

Explore null in Oracle as the indicator of absent or unknown values, not equal to any value or another null, and using is null or is not null for checks.

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.

Explore literal character strings and string literals in Oracle SQL, using single quotes, concatenation with columns, aliases, escaping single quotes by doubling, and when to use double quotes for identifiers.

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.

Summarizes the key DQL concepts: select queries, distinct values, null handling with NVL and COALESCE, and using column aliases and formatted output for readable results.

Explore how the where clause filters data in select queries, using in, like, handling null data, and constructing complex conditions with logical operators and operator precedence.

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.

Discover Oracle operators—arithmetic, comparison, and logical—and how addition, subtraction, and other operators power queries for filtering, calculations, and combining conditions.

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.

Learn how to use the IN and NOT IN operators in Oracle SQL to filter employees by department IDs, retrieving or excluding 60 and 80.

Explore how to use the like and not like operators in Oracle SQL to perform pattern matching with wildcards, including percentage and underscore, for prefix, suffix, and substring searches.

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 to work with multiple conditions in Oracle SQL by using the logical operators and, or, and not to retrieve data that satisfies combined criteria.

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.

Use the logical NOT operator in Oracle SQL to negate a condition, such as excluding department 60 or salary less than equal to 5000, and combine conditions with and or.

Combine and, or, and not to build complex SQL filters, using parentheses to order evaluation for department and job ID criteria in practical queries.

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 the core SQL operators—arithmetic, comparison, and logical—along with between, in, like, and null handling, and learn their precedence in queries.

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.

Define and enforce data integrity in Oracle by using primary keys, which uniquely identify each row, cannot be null, and are automatically indexed for faster queries.

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

Oracle table naming conventions emphasize descriptive, meaningful names in plural form. Combine words with underscores, avoid abbreviations and special characters, and keep names concise.

Describe tables in Oracle using the describe command to view a table's structure, including column names, data types, sizes, and nullability, or use the shorthand desc.

Learn to create a shopping fair table and an organizer table, define primary key and foreign key constraints, and establish relationships between the tables in Oracle SQL.

Explore identity columns in oracle database 12c, learning how generated by default and generated always options auto-create unique values via a system sequence, with rules for inserting explicit values.

Explore sequence options in Oracle Database that control how identity values are generated, including generated by default on null, start with, and increment by.

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.

Use alter table modify to change an existing column’s data type, size, and constraints, such as converting organizer_id to float and adjusting not null rules across columns.

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.

Learn to drop columns in an existing table with alter table drop column, using shopping fair and columns like organizer phone, organizer phone one, and organizer mail.

Mark a column as unused to hide it from queries without dropping it, enabling testing and removal in Oracle SQL; unlike invisible, unused is not maintained and can be dropped.

Rename an existing table using the alter table rename command, as shown by renaming shopping fair to shopping fair info and reverting the change if needed.

Learn how to drop a table in Oracle SQL using drop table with options such as cascade constraints and purge, recoverability via recycle bin or flashback, and handling referenced objects.

Learn about Oracle read only tables that can be read but not modified, set via alter table, and used for archiving, security, performance, and data integration.

Learn how to truncate tables in Oracle using the truncate table command, permanently remove data while preserving table structure, and manage constraints and cascade effects on related tables.

Practice ddl statements in Oracle database by creating a Students table with student_id, first_name, last_name, and other columns; then add two columns and rename enrollment_status to status.

Discover data manipulation with DML in Oracle SQL, covering insert, update, and delete operations, their behavior, and how they enable you to work confidently as an Oracle SQL developer.

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.

Use insert all to insert multiple records into an Oracle table by listing columns and values, repeating for each row, ending with select star from dual, with date format conversions.

Learn to insert data from one table into multiple tables with Oracle's insert all, including conditional inserts and using source data to populate destination tables.

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.

Learn to use insert into select in Oracle SQL by inserting data from a select query into a table, with where clauses, joins, and subqueries.

Learn the Oracle update statement syntax, including update, set, and optional where clauses to update specific rows or the entire table.

Execute an update query to increase salaries by 10% for employees with age greater than 25, using a replicated table created as select star from employees.

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.

Perform bulk updates by using an update query with a where clause to modify multiple rows, such as increasing salaries by 10% for department 80, after verifying with a select.

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.

Differentiate drop, delete, and truncate: drop removes the table and data with no recovery, delete conditionally removes rows with rollback, and truncate clears data while preserving the table.

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.

Explore how joins in Oracle SQL connect data across multiple tables, covering what they are, how they work, and the types and usage to write both basic and advanced queries.

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.

Explore how equi joins work in Oracle SQL by comparing equality-based join conditions and the inner join keyword, showing that inner join and join produce the same results.

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.

Learn how the left outer join in Oracle returns all rows from the left table and matched rows from the right, with nulls for non matches.

Master full outer join in Oracle to return all rows from both tables, filling null values for non-matching records, with syntax using select, full outer join, and on.

Demonstrate non-equi joins in Oracle database by matching salary ranges between employees and jobs using between, greater than, and less than operators.

Master the self join in Oracle database by joining the employees table to itself, aliasing as e and m to fetch employees with their managers' names.

Master joining multiple tables in Oracle SQL by chaining joins in a single query, retrieving employee names, department names, and city using aliases and on conditions.

learn to join employees, job history, and departments to list employees who changed departments since hire date, returning the employee id, first name, last name, and current department name.

Explore the concept of functions in Oracle, learn about different function types, and apply them hands-on to see how to get these functions working.

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 scalar functions that operate on a single row and return a single result, including uppercase transformations. See their categorization into character, numeric, date, and conversion functions in Oracle SQL.

Explore Oracle character functions such as concat, lower, upper, initcap, lpad, rpad, ltrim, rtrim, trim, replace, substring, instring, and length, with syntax, usage, and practical examples.

Explore sql numeric functions such as abs, ceiling, floor, mod, power, round, and truncate with HR schema examples. See how they compute absolute values, ceilings, floors, remainders, powers, and rounding.

Explore Oracle sql date functions such as sysdate, add_months, last_day, next_day, months_between, and current_date. Learn how to apply these functions in queries, perform date arithmetic, and handle time zone differences.

Apply Oracle date functions and techniques to real use cases, combining add_months, trunc, next_day, months_between, floor, and mod to compute quarter dates, pay days, and employee ages.

Explore oracle's to_char and to_date conversion functions, mastering format models like YYYY, MM, DD, MON, and HH12 to convert dates and numbers to strings and vice versa.

Learn to handle null values in Oracle with coalesce, nullif, and nvl, returning the first non-null value, comparing expressions, and substituting missing data in queries.

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 group by in the HR schema of Oracle Database to count employees by department and by manager, cover null handling, and use count, sum, average, min, max aggregates.

Explore Oracle SQL grouping functions like sum, average, min, max, and count, group by department in the HR schema, and understand null handling in aggregates.

Group data in Oracle SQL by multiple columns to compute aggregates, such as total salary by department id and by department id with job id. Group by column order matters.

Discover how to restrict group results in Oracle with the having clause and group by, filtering departments by employee counts using aggregates and eligible columns.

Explore how to use where and having in Oracle SQL to filter data before and after grouping, with examples filtering salaries and department ids.

Master oracle sql by solving year-wise employee joins and department queries, including grouping, count and total salary calculations, with joins between employees and departments in the hr schema.

Learn how to sort data in Oracle using the order by clause, including multi-column sorts, default ascending order, and explicit descending order with practical salary examples.

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.

Master sorting in Oracle SQL by using order by with expressions, aliases, and null handling to control ascending or descending results, including nulls first or last.