The Complete Oracle SQL Bootcamp

learn to write a basic select statement to retrieve data from Oracle database tables using the two mandatory clauses: select and from, with examples from departments.

Learn to write basic select statements in Oracle SQL, using select and from clauses to display specific columns or all columns from tables such as employees, departments, and regions.

Learn to use arithmetic operations in Oracle SQL select statements: plus, minus, multiply, and divide, apply precedence rules with parentheses, and understand null values in arithmetic expressions.

Practice arithmetic operations in a select statement to show last_name, salary, department_id, salary multiplied by 12, and commission (commission_pct multiplied by salary), noting null results when commission_pct is null.

Use column alias to rename a column heading in select statements, with optional as, and note aliases appear uppercase and require double quotes for spaces, special characters, case sensitivity.

Practice using column aliases in select statements to rename hire_date to startdate, last_name to name, and job_title to title (including job_id) in the jobs table, using the optional as keyword.

Explore the concatenation operator to join last names and first names, use literal character strings, and apply the distinct keyword to remove duplicates in Oracle SQL queries.

Use the describe command to display a table’s structure, listing region_id (number) and region_name (varchar2(25)) and their nullability. View the same structure in SQL Developer.

Master the concatenation operator and literal strings to create a full name, apply distinct to return unique job ids, and use describe to view table structures.

Learn to filter data in Oracle SQL by extending a basic select with a where clause, applying conditions on department, region, and dates, including case sensitivity and date formats.

Practice filtering data with SQL by selecting columns and rows for employee 176, country India, location 1000, and department marketing. Learn how string case sensitivity affects query results.

Learn to apply SQL comparison operators in where clauses, including =, >, <, >=, <=, between, in, and like, plus null checks with is null and is not null.

Practice using comparison operators to filter employees by salary, commission, and department, and write selects for last names and salaries using between and like.

Master logical operators in Oracle SQL by combining conditions with and, or, and not in where clauses, and learn operator precedence and parentheses through department and salary examples.

Practice using logical operators in the where clause to filter employees by hire date and salary, and display a full name by concatenating last and first names with an alias.

Sort data with the order by clause in Oracle SQL, specifying columns or expressions and applying multi level sorting by department, salary, and location in ascending or descending order.

Learn to sort query results with order by in Oracle SQL: filter employees by department 10, 40, and 90, then sort by department_id desc, salary asc, and employee_id desc.

Learn how the Oracle row limiting clause restricts results by a number of rows or a percentage, using order by, offset, and sample to select top records.

Learn to use the row limiting clause in a select statement to show department names for location 1700, order by department_id in ascending order, skipping three rows and returning four.

Explore substitution variables in Oracle SQL, enabling dynamic queries by prompting for input values, using single or double ampersands, and running scripted statements from files.

Learn the difference between single and double ampersand substitution variables in Oracle SQL. Understand that single & prompts for every occurrence, while double && prompts once and reuses the value.

Learn how to define substitution variables in Oracle SQL and use define, undefined, and verify commands to assign, remove values, and display or suppress variable substitution results.

Learn to use substitution variables in Oracle SQL select statements to prompt for department id and to order results by a chosen column, with live verification of outputs.

Explore Oracle SQL functions, including singular (single-value) and group functions, with overviews of character, numeric, date, convergent, and general functions for calculating, formatting dates and numbers, and converting data types.

Learn Oracle character functions for case conversion, including lower, upper, and initial capital, to format names and regions in select statements and handle case sensitivity.

Learn to use case conversion functions in a select statement to display last names in uppercase, show department number, and filter by names starting with a letter using substitution variables.

Explore Oracle SQL character manipulation functions, including concat, substring, length, left, right, trim, and replace, with nested examples showing how to build full names.

Practice character manipulation in Oracle SQL with length and substring, select department data by location 1700, and replace the first two characters of country name with stars using nested functions.

Explore numeric functions in Oracle SQL, including round, trunc, mod, ceil, and floor, with examples using arithmetic expressions and the dual table to compute results.

Practice numeric functions with Oracle select statements, including remainder, ceil, and floor, while computing a 10 percent salary increase, rounding to zero decimals, and aliasing the new salary.

Explore Oracle sql date functions such as current_date, current_timestamp, months_between, next_day, last_day, round, and trunc to calculate, adjust, and display dates.

Explore Oracle date functions to show yesterday, today, tomorrow using this date with plus or minus days, from dual, and add three months to hire_date for employees in department 30.

Learn how Oracle automatically and manually converts data types, using implicit and explicit conversions and conversion functions to turn characters into numbers or dates, with practical examples.

Learn to_char in Oracle to convert dates and numbers to text, using flexible format models to display day, month, year, time, and currency.

Explore how to use the to_char function in Oracle SQL to format dates and numbers, display employee last names and salaries, and format hire dates and currency in department records.

Learn how to use to_number and to_date to convert characters to numbers and dates, using explicit format models to avoid errors.

Master how to_number and to_date work in Oracle by converting strings to numbers and dates using format models, and apply these skills in practical queries.

Explain nvl, nvl2, nullif, and coalesce in Oracle SQL, returning the first non-null value, converting nulls to defaults, and handling type mismatches with salary examples.

Explore NVL and NVL2 through practice queries that show department details and manager status, displaying 'has manager' or 'no manager' by manager_id, and handle commissions with NVL and to_char.

Learn to use case statement and decode function in Oracle SQL, including simple and search syntaxes, to output new salaries based on job and conditions.

Practice case and decode in Oracle SQL to map job_id values to grades, display employee last names, and alias the result as grade; use quotes for zero, with identical outputs.

Explore group functions in sql, including average, count, max, min, and sum, and learn how to apply them in select statements with the having clause and handling null values.

Explore how to use group functions with a select statement to compute max, min, sum, and average salaries for department 100, labeling columns and rounding to two decimals.

Explore how distinct counts non-null, unique job values in employees, and how nulls influence group functions like count and average when replacing nulls with zeros.

practice using count with distinct to count unique, non-null values in a column. apply this to department_id in employees, location_id in departments, and manager_id in employees.

Analyze how to use the group by clause to divide rows into groups, compute max salary per department, average salary by job id, and count employees by department and job.

Master the group by clause in Oracle SQL to aggregate data, counting employees by job ID and computing sums, minimums, maximums, and averages of salaries.

Learn to use the having clause after group by to filter grouped results by aggregate values, such as maximum or average salary per department greater than 10000, with order by.

Learn to use the having clause in Oracle SQL to filter grouped results, identify regions with more than five countries, and sum salaries by job for departments 50 and 80.

Learn how joins link multiple tables to retrieve related data using inner, natural, self, cross joins, and outer joins such as left and full, with department and location examples.

Learn to write an inner join using natural join syntax to link tables by a common column, with Oracle automatically using shared names like location_id to fetch departments and cities.

Practice natural joins and inner joins in Oracle SQL by linking departments, locations, and countries to display department name, city, street, and country.

Master inner join using the using clause to link tables by a common column, and contrast with natural join by specifying the join column.

Master inner joins using clause across three SQL practices that combine departments, locations, and countries, returning department name, city, street address, country name, school name, and related employee data.

Master inner joins in Oracle SQL using on and using clauses, join tables by matching or aliased columns, and resolve ambiguity with table aliases.

Join the employees, departments, and locations tables using on clauses to display each employee's last name, department id, department name, and city, filtered for London City.

Use a self join, an inner join of the employees table with itself, to display each employee and their manager by aliasing as e and m.

Master inner join and self join in Oracle SQL by joining the employees table to itself, selecting employee id and last name with the manager id and manager last name.

Explore non-equijoins by matching employee salaries to grade ranges using between, linking employees and job grades to reveal each grade based on salary intervals.

Understand outer join concepts: left, right, and full outer joins, and how they include unmatched data alongside inner join results, with nulls for nonmatching rows.

Explore outer joins in Oracle SQL by joining locations and countries on country_id, comparing inner, left, right, and full outer joins to retrieve city and country names, including unmatched rows.

Explore the cross (Cartesian) join in Oracle SQL by combining every employee with every department, showing how 107 employees and 27 departments yield 2889 rows and the cross join syntax.

Execute a cross join in Oracle SQL to combine countries and regions, producing every pairing and an output of 100 records, illustrating how cross joins generate large datasets.

Learn how to write subqueries and use an inner query to filter employees by salary or by the average salary, returning those who earn more than the subquery result.

A single row subquery returns one value from the inner query to the outer query, forming a scalar subquery; use operators like =, >, >= with Oracle.

Practice single-row subqueries in oracle sql by identifying Lee's hire date and listing employees hired after him. Show employees earning the average salary by joining departments and ordering by salary.

Explore how multi-row subqueries feed the outer query with in, any, and all operators, enabling cross-department salary comparisons and filtered results.

Learn to write multi-row subqueries to find employees in departments with any employee whose last name starts with g, and whose salary exceeds any salary in department 110.

Explore multi-column subqueries that return two columns, enabling pairwise comparisons of salary and department id to filter employees, with examples of matching and excluding results.

Learn to write a multi-column query to display the last name and department for employees whose job id and department match the employee named Lee, using a subquery.

Explore set operators in oracle sql—union, union all, intersect, and minus—and learn how compound queries combine two results with matching columns, equal precedence, and order by clause at the end.

Build compound queries using union and union all between two select statements, controlling duplicates and ordering by department id.

Explore union and union all set operators to combine multiple queries, display department and job IDs, and control duplicates in Oracle SQL practice.

Explore set operators intersect and minus in Oracle SQL, building a compound query with union, removing duplicates, and ensuring matching column counts and data types across components.

Explore using minus and intersect set operators in Oracle SQL to compare locations with and without departments, retrieving location, city, and address.