Complete Oracle 12c SQL Certification Course

Explore database normalization and how to refine data for an Oracle database by applying the first, second, and third normal forms using a school database example.

Explore first normal form (1NF) by converting non-atomic attributes into atomic values, structuring relations with separate tables for courses and content, and using address normalization as an example.

Learn second normal form (2NF) once your data is in first normal form, and ensure every non key attribute is fully functionally dependent on the key column.

Explore the second normal form by splitting a single table into two: a student table and a project table, achieving fully functionally dependent data via student id and project id.

Revisit the data model to understand relationships and how third normal form shapes keys, highlighting employee_id as a key and email_id as a unique key.

learn how third normal form eliminates transitive dependency by normalizing data into separate tables, such as students and zip codes, to avoid cascading updates.

Trace the origin of SQL from IBM's invention to a standard language used across database vendors, and learn how Oracle SQL transfers to Teradata, MySQL, and DB2.

Explore Oracle products, including the flagship Oracle database and Oracle Enterprise Manager, and learn how Oracle Enterprise Manager monitors databases, servers, listeners, space, and applications to send alerts.

Explore oracle cloud concepts, comparing renting scalable servers to owning hardware, and how browser-based applications use oracle cloud services in competition with AWS.

Learn the tools to connect to an Oracle database, including SQL*Plus standalone client and SQL Developer, and distinguish between using a client vs a full database for remote connections.

Master the Oracle select statement by reading data from tables, understanding the syntax to use select * or all to fetch all columns, or listing specific columns from a table.

Execute your first select statement by querying the hr.employee table using owner.table syntax, returning all columns or specific fields while understanding user ownership and explicit owner names.

Learn the select syntax rules, including keyword case insensitivity, precise condition casing, and the requirement to terminate statements with a semicolon or slash.

Explore projection and selection in Oracle SQL: project specific columns with select and restrict rows with where clauses; use quotes for characters and note case sensitivity, numbers unquoted.

Mix projection and selection by querying specific columns, such as first_name, salary, and department_id, from employees where department_id = 90, showing how to combine column choice with a where clause.

Improve query readability in Oracle 12c by formatting SQL across multiple lines with indentation, ensuring clarity for both small and large queries, and recognizing end-of-statement options like semicolon or slash.

Describe how to inspect a table’s structure in Oracle 12c using the describe command. Learn to view column names, data types, and nullability for tables such as employees and jobs.

Explore Oracle SQL operators, including automatic operators, comparison operators, and the like operator, to simplify working with data inside the database.

Learn how to use SQL arithmetic operators to add, subtract, multiply, and divide salaries in select queries, demonstrate salary hikes, and compare before-and-after results without persisting changes.

Explore SQL comparison operators in where clauses, including equal to, greater than, greater than or equal to, less than, less than or equal to, and not equal, with salary examples.

Master the like operator for character columns in sql, using underscore and percent wildcards to match specific positions, such as names starting with a or the second character being a.

Use the is null and is not null operators to query employees with null or non-null commissions, and apply not and not like for patterns such as second character matches.

Learn the between operator in Oracle SQL, using salary ranges to select employees between 1000 and 3000 or 5000 and 10000, and compare with boolean and automatic operators.

Master the in operator in oracle 12c sql to filter employees by departments 10, 20, 30, 40, 50, and use not with in and between to find nonmatching rows.

Explore boolean operators in SQL: using and to require all conditions be true, or to return records when any condition is true, with true/false evaluation in where clauses.

Combine first name and last name using the concatenation operator (||) in Oracle 12c SQL, and insert a space between them to produce properly formatted full names.

Learn the president's rule for Oracle SQL operator precedence, from brackets to division, multiplication, addition, concatenation, comparison, null checks, between, not equal, not, and, or.

Illustrate how operator precedence changes results in a salary-based expression by solving left-to-right to get 1,997 and using the precedence rule to get 19,997.

Explore how operator precedence and brackets affect a salary calculation in Oracle SQL, comparing results with and without the precedence rule using a 1000 salary.

Rename oracle query output columns with column aliases. Place the alias after the column name in double quotes to produce readable titles like "50 percent hike" or "original salary".

Learn how to use table aliases in Oracle joins to disambiguate columns across multiple tables such as employee and department, enabling references like emv.last_name and emv.salary.

Sort query results with the order by clause, using ascending or descending order. Sort by single or multiple columns, including column numbers, such as salary and department.

Learn how to use the order by clause to sort data by department and then by first name in reverse order, and sort salaries within departments.

Explore ampersand substitution in Oracle SQL to substitute where conditions at runtime, using ampersand variables to generate dynamic queries for names or departments.

Learn ampersand substitution in Oracle SQL to filter employees by department numbers and substitute columns, tables, and where conditions for dynamic queries.

Discover double ampersand substitution in Oracle SQL, which stores a substituted value in the session to reuse across a query and beyond, reducing repeated prompts in large queries.

Use the dual table as a dummy, temporary store for calculations and expressions in Oracle. Run queries from dual to obtain the system date or perform arithmetic.

Explore Oracle's built-in single row functions, including numeric, character, and date functions, to process data without coding, with examples like rounding, case conversion, and date additions.

Explore Oracle character functions, including length, concatenation, substring, ltrim, rtrim, lower, upper, lpad, rpad, and replace, with practical examples on the dual and employees tables.

Explore built-in numeric functions in the complete Oracle 12c SQL course, including remainder, power, round, truncate, sine, and sqrt, with examples and rounding rules (0–4 stay, 5–9 add one).

Explore date functions in Oracle 12c SQL, including add months, last date, months between, next day, and rounding versus truncating rules for month and year.

Explain how group functions operate on multiple rows to return one value, with examples like average, count, min, max, and sum, and contrast with single-row functions.

Learn how to compute the average salary across all employees using the average function, and see the resulting value for all employees.

Count the total records in a table using count(*) and count(column) to tally a specific field, such as last_name. Use distinct to count departments and identify duplicates in employee table.

Master min and max functions on numeric and date columns, alias results with spaces using quotes, and identify earliest and latest dates such as oldest and newest employees.

Compute the total salary paid to all employees using the sum function and explore Oracle SQL group functions.

Learn to use the group by clause in oracle sql to group data by department_id and feed it into group functions to compute maximum salary and employee counts.

Use the having clause to refine grouped results by applying conditions after a group by; it must accompany group by and filters departments with employee counts greater than 10.

Discover subqueries, or nested queries, and learn how they can be placed in select, from, where, or having within the complete Oracle 12c SQL course.

Learn to use subqueries in the where clause to identify employees in a department by querying the departments table for the IT id and filtering the employees table.

Explore using subqueries in the select part and where clause, including counting departments and employees, aliases, and execution order with the dual table.

Explore subqueries in Oracle 12c by running nested queries to compare salaries with the average, retrieve last names, and use in clauses, distinct departments, and debug techniques from bottom up.

Master SQL joins to query related data across tables by leveraging normalization, such as employees, departments, locations, and regions. Use joins for efficient data retrieval in Oracle.

Discover how to join two or more tables using inner, left, right, and full outer joins, based on primary and foreign keys and join conditions in from or where clauses.

Discover the joins article on the support site Genesis dot com, use the search box to locate it, and navigate all types of joins from the left-hand menu.

Learn how to join two or more tables using key-based conditions, and compare traditional and Oracle join syntaxes while covering inner, outer, and cross joins.

Demonstrate inner join behavior by matching key columns across two tables and returning only the resulting rows, then explain natural join when the key names match.

Explore how left outer join and right outer join determine which rows appear in the output based on the left or right table and the join condition, including null handling.

Explore full outer join behavior by returning all records from both sides, including nonmatching keys and matching rows, illustrating how full outer join outputs include all relevant data.

Explore the cartesian (cross) join by multiplying each row of the left table with each row of the right table, yielding left rows times right rows.

Explore inner join, traditional join, and natural join to connect books and members, using on, using, and natural join syntax to project member names and book categories.

Explore and practice left outer join concepts, identifying left and right tables, using on or using clauses, and returning all left-table rows with nulls for unmatched right-table data.

Explore right outer join behavior by joining members to books, ensuring every book appears in the result and including matching member data when available, with nulls for non-matching borrowers.

Learn how a full outer join returns all matching and non-matching data, with nulls for missing sides, by comparing it to a right join and examining table counts.

Explains cross join (cartesian join), demonstrates the traditional comma syntax, and shows how the cartesian product yields 45 rows from 5 members and 9 books.

Compare traditional and left/right outer joins, showing how to write left outer join in the where clause with the plus symbol. Practice with imaginary tables to understand join outputs.

Explore the union operator in Oracle SQL, which combines rows from two queries, sorts the results, and removes duplicates to yield unique region names.

Explore the union all operator, which combines results from two queries without sorting and removing duplicates, returning all matching rows from corresponding columns.

Explore the intersect operator in Oracle SQL 12c, returning only rows present in both query results, removing duplicates after sorting.

Master the minus operator in Oracle 12c sql by subtracting the second result set from the first, with sorting, duplicate removal, and practice on your own tables.