Oracle SQL from Zero to Hero

Prepare your environment by selecting a Windows-based or Linux-based virtual machine in Oracle Virtual Box with 19c or 23AI, then import the appliance and configure a shared folder.

Prepare the course environment by configuring Windows and Linux VMs, downloading demo.zip with SQL scripts, and setting up shared folders, time zones, and rearm procedures for Oracle SQL practice.

Explore Oracle sql developer as a cross-platform integrated development environment that connects to Oracle databases, executes sql and pl/sql, models data with the data modeler, and import/export and migrate data.

Learn to start Oracle SQL Developer, create and test HR, SOE, and System connections, navigate objects, run statements or scripts, and export or save scripts while personalizing the code editor.

Describe relational database management systems (RDBMS) and their features, including Oracle database; understand structured data, SQL, and table relationships, and compare two-tier, three-tier, and n-tier architectures.

Explore relational database management systems, compare OLTP, DSS/OLAP, and hybrid apps, and examine warehouse versus OLTP workloads, ETL, NoSQL, and 2-tier, 3-tier, and N-tier architectures.

Learn data models and ER diagrams (ERDs), distinguish logical versus physical ERDs, and identify entities, attributes, primary keys, and foreign keys in one-to-many relationships.

Explore how to model many-to-many relationships using a junction table, implement ERDs from logical to physical designs, and reverse engineer using Oracle SQL Developer Data Modeler.

Explore Oracle SQL by describing the SQL language, its five command groups, and common interfaces; examine HR and SOE schemas and their tables, and review Oracle SQL documentation.

Explore how the select statement retrieves data from tables or views, selecting columns, filtering rows, joining tables, and performing basic grouping and statistics, with aliases, concatenation, arithmetic expressions, and comments.

Learn to write and execute SQL statements with proper syntax, handle arithmetic expressions and operator precedence, manage null values, use aliases, and perform concatenation to create full names.

Learn literals and the Q operator in Oracle SQL, with concatenation and currency formatting. Inspect table structures using describe and SQL Developer, and remove duplicates with distinct or unique.

Master the wear clause, comparison and logical operators, and precedence rules in oracle sql to restrict data with precise, case-sensitive queries.

Master Oracle SQL sorting with order by, apply row-limiting clauses for top-n results, control nulls, and sort by multiple columns, aliases, and expressions.

Learn how substitution variables make SQL queries dynamic by prompting for values and using define and accept commands. Apply variables in where clauses, column names, and expressions.

Master single row functions in Oracle SQL, including character, number, date, conversion, and general functions, with nesting and use of upper, lower, init cap, concat, substr, length, trim, and mod.

Explore how Oracle stores dates as numbers and displays ddmonrr, and compare rr and yy. Use sysdate, perform date arithmetic, and apply monthsBetween and trunc.

Discover how Oracle SQL handles implicit and explicit data type conversion, using toNumber, toCar, and toDate, including formatting dates with toCar and common format model elements.

Master time and date display with format elements (doubleH24, doubleH12, AM/PM, MI, SS) and modifiers (FM, SP, TH), and use toCar, toDate, and toNumber for data conversion.

Explore converting character data to date in Oracle SQL by implicit conversion using the nlsDateFormat and by using date literals, alongside the toDate function for full control.

Learn to handle null values in Oracle SQL using NVL, NVL2, NULLIF, and COALESCE to avoid null results in arithmetic, and to convert data types with to_char.

Master conditional expressions in SQL by using case and decode functions, compare their behavior per the ANSI SQL standard, and apply them to salary and tax examples.

Explore Oracle sql group functions, including avg, count, max, min, sum, and listagg, and learn how distinct and nvl affect calculations across grouped data.

Apply group functions with the group by clause on single and multiple columns, using having to filter groups and nesting group functions for department salary summaries.