Oracle SQL - A Complete Developer's Guide (2023)

Explore the relationship between a database and SQL, covering data feed, data extraction and reporting, and admin tasks like health checks, user management, and role-based access control.

Explore how a select statement retrieves data from an ERD by mapping entities to tables and attributes to columns, and understand one-to-many relationships and unique constraints in joins.

Explore Oracle versions from 7 through 18c, compare licensed editions with Oracle XE, and set up a free Livesql cloud account to run Table_Data_Scripts.sql and populate country data.

Install Oracle XE 11g by downloading the 11.2 version, unzip, run setup, and set a SYS password for the express edition. Connect as SYSDBA, create a user, and grant privileges.

Download, unzip, and launch Oracle SQL Developer; create a scott/tiger connection using tnsnames.ora details; explore SQL*Plus basics, including set linesize, pagesize, timing, spool, wrap, and login.sql customization.

Learn how to fetch data from tables in different schemas using schema prefixes, table and column aliases, and understand that the star (*) selects all columns.

Learn to filter data with the where clause and sort results using order by in oracle sql, defaulting to ascending, and specify sal for department 10 in asc or desc.

Learn to define, use, and undefine substitution variables in sql plus, using & and &&, and control prompt behavior and feedback with set verify on or off.

Learn how to use the 12c row limiting clauses to fetch top or bottom N rows from ordered data, using salary examples with offset, fetch next, percent, and with ties handling.

Connect to live SQL, use table aliases, and query Scott's schema to find employees in department ten, sorting by salary in descending order as code challenge 01.

Solve code challenge 01 by querying Scott's schema in live sql, returning employees from department 10 sorted by salary descending, using alias e and prefixed columns (e.ename, e.job).

Learn to load data from a file into two identically structured tables using a single script and a substitution variable in sql*plus, switching between table01 and table02.

Learn how to use a substitution variable to insert data into table01 and table02 from a single insert script, loading data into emp_demo01 and emp_demo02 with SQL*Plus.

Identify the top 30 percent of employees by salary from the emp table, using the 30 percent of 14 calculation to determine the five top earners.

Sort employees by salary in descending order and fetch the top 30 percent using fetch next 30 percent rows only from the EMP table.

Explore operands and operators in Oracle SQL, distinguishing unary and binary operators and using examples like addition, subtraction, and comparisons.

Explore Oracle SQL comparison operators including equals, not equals, greater than, less than, between, in, not in, is null, and is not null, with practical employee examples.

Explore how to perform wildcard searches in Oracle using the like operator, using underscore and percent symbols to pattern match and escape characters.

Explore the use of logical operators not, and, or in SQL to filter records, with examples using deptno and salary and a hands-on practice.

Apply arithmetic operators in Oracle SQL to compute total salary and commission on the emp table, using aliasing and the NVL function to handle nulls, and calculate commission percentages.

Master arithmetic operator precedence using the BODMAS rule and see how brackets, division, multiplication, addition, and subtraction shape Oracle SQL expressions with a practical example.

Learn how to concatenate strings in Oracle SQL using the pipe operator || to format a record as last name, comma, space, first name.

Explore Oracle SQL set operators—union, union all, intersect, and minus—with practical examples using tables A and B to show distinct results, duplicates, and common values.

Practice filtering employees by department 10 or 20 and salary over $1,500 using logical and comparison operators on the employee table in code challenge 04.

Filter employees in the emp table by department 10 or 20 and salaries above 1500, then simplify with dept_number in (10, 20) to fetch 11 records in Oracle SQL.

Identify employees whose name has a in the first or second position from the employee table, completing code challenge 05 and then proceed to the solution lecture.

Solve code challenge 05 by querying the emp table for names starting with 'a' or with 'a' in the second position, using 'a%' and '_a%' patterns in sql developer.

Explore Oracle built-in functions and learn to apply single-row, group, and analytical functions with numeric examples such as abs, floor, ceil, mod, power, sign, width_bucket, and round.

Learn to manipulate strings in Oracle SQL with concat, substr, upper, lower, initcap, lpad, rpad, ltrim, rtrim, translate, and more. Explore hands-on emp_str examples for formatting and cleansing data.

Master Oracle date functions, including sysdate, systimestamp, add_months, months_between, new_time, next_day, last_day, to_date, to_timestamp, extract, round, trunc, and to_char.

Explore Oracle sql conversion functions such as to_date, to_char, to_timestamp, and to_number, including explicit versus implicit conversions and their performance impact.

Learn how to nest functions in Oracle SQL by calling one function inside another, demonstrated with ltrim and rtrim to remove '*' and other symbols from a string.

Explore null handling in Oracle SQL with nvl, nvl2, coalesce, and nullif. Learn how these functions manage null values and return first non-null expressions.

Create a temporary data set using a with clause and union all, then trim special characters from the Ename column with Oracle built in trim and length checks.

Apply nested ltrim, trim, and rtrim to clean the ename column by removing special characters such as star, space, tilde, percentage sign, and dollar sign in Oracle SQL.

Explore group functions or aggregate functions such as max, min, avg, count, and sum, and learn their syntax with group by and having for department-wise salary totals.

Explore Oracle SQL pivot and unpivot, transforming rows to columns and back with practical employee and department examples.

Explore analytical functions in Oracle SQL, including row_number, rank, dense_rank, first, last, and listagg, with department-wise salary ranking and employee name aggregation.

Explore lead and lag analytic functions to compute previous and next values using offset and default values, illustrated with vehicle distance data and partition by vehicle.

Check the records from the emp table and count employees by location, map department numbers to locations, and use the Oracle pivot function to display rows as columns.

Demonstrates a solution to code challenge 07 by building a pivot query on the emp table to count employees by department and map department numbers to locations.

Compute customer wise account balances by querying the accounts table and the transaction table, treating credited as positive and debited as negative, to display per customer totals.

Solve code challenge 08 by calculating customer balances from the accounts and transaction tables, using decode to convert credits to positives and debits to negatives, grouped by customer.

Explore Oracle hierarchical queries, using connect by prior, level, and sys_connect_by_path to traverse root-to-leaf trees, identify leaves and cycles, and reveal paths with connect_by_root and connect_by_is_leaf.

Demonstrate hierarchical queries in Oracle SQL by simulating cycles and detecting loops with connect_by_is_cycle. Explain no cycle mode, cycle errors, and how to control traversal in an employee hierarchy.

Execute code challenge 09 in Oracle SQL by finding all employees working under employee Blake using the EMP table, and then move to the solution lecture.

Identify employees reporting to Blake by querying the emp table with a hierarchical query using connect by prior empno = mgr, starting with Blake's id 7698, to show Blake's subordinates.

Explore three query types—subquery, correlated subquery, and inline view—and learn how inner and outer queries interact, including when an inline view acts as a table.

Explore co-related subqueries with the exists and not exists clauses in Oracle SQL, using a join condition between inner and outer queries to find employees in New York.

Update and delete using correlated subqueries in Oracle SQL, as part of the Oracle certification; increase salaries for dept 20 employees by 10 percent.

Tackle code challenge 10 by applying a correlated subquery to add a $100 commission to employees in New York and Chicago, using the EMP table's comm column.

Demonstrates solving code challenge 10 in Oracle SQL by updating employee commissions for New York and Chicago using a correlated subquery on the emp and dept tables.

Learn how the sql case statement evaluates an expression against when-then conditions, uses an else clause, and aliases the result with a practical example on the employee table.

Explore the oracle decode function, its syntax, and mapping of expressions to outputs, illustrated with a deptno example and a comparison to the case statement.