Oracle Database: SQL

Examine implicit and explicit data type conversion in Oracle SQL. Oracle automatically converts input to native data type (implicit), while explicit conversion uses conversion functions, often via a character intermediary.

Describe how to_char converts dates to formatted text and how to_date converts strings to dates, using single-quoted format models, with rr, fm, and apex modifiers and calendar elements.

Learn nesting functions in Oracle SQL, where a function inside another executes from deepest level to outer, exemplified by substr, upper, concat, and date and salary formatting.

Explore oracle sql general functions nvl, nvl2, nullif, and coalesce for null handling. See examples for calculating annual compensation, selecting first non-null values, and decoding conditional logic.

Explore conditional expressions in Oracle SQL by comparing case expressions and the decode function, with salary and tax calculations using role-based multipliers and text-based calculation examples.

Explore how group functions report aggregate data in Oracle, computing average, sum, min, max, count, and distinct, while handling nulls with nvl across numeric, date, and character data.

Group rows using the group by clause to create department and job ID groups and calculate aggregates such as average and max salary, using the having clause to restrict groups.

Learn how nested grouping functions work by applying average and max to salary, then practice using group by, where, having, and order by clauses to refine results.

Master the main Oracle SQL joins, including natural, using, on, outer and cross joins, plus self-joins, with aliasing and join conditions for performance.

Explore subqueries in Oracle SQL, including inner and outer queries, single and multi row subqueries, and operators like any, all, exists, in, and having, with examples and null handling.

Master set operators in Oracle SQL, including union and union all, to combine multiple queries into compound results. Learn rules for column matching, data types, and final order by.

Explore set operators in oracle sql: intersect returns common rows, minus excludes rows from the first query, and union with matching select statements using type conversions for dummy columns.

Explore data manipulation with dml in Oracle: insert, update, delete, and truncate; manage transactions with commit and rollback; use constraints, data types, and subqueries for inserts.

Learn how to use Oracle SQL insert statements, including implicit and explicit inserts, with department table examples, column lists, and handling constraints and null values.

Master the Oracle update statement using the update clause, set, and where to safely modify data such as department IDs, with subqueries for multiple columns and cross-table updates.

Explore delete and truncate statements in Oracle SQL, focusing on the where clause and conditions, and compare rollback versus auto-commit, to choose fast structure-only removal when appropriate.

Explore how database transactions control data changes with commit, rollback, and savepoint to maintain data consistency in DML operations and read consistency across sessions.

Master the for update clause in a select statement to lock rows with a row-level lock, control timing with no wait, and apply update clauses across tables in Oracle SQL.

Learn Oracle DDL to create and manage tables, including constraints (primary key, foreign key, not null, unique, check), and related objects like views, sequences, indexes, and synonyms.

Master Oracle views, from simple to complex, built on one or more tables to tailor data access, with check options and read-only DML rules.

Learn how to create and manage Oracle sequences to generate unique numbers for primary keys, control start, increment, min/max values, caching, cycling, and how rollback or crashes affect gaps.