Advanced SQL for Data Engineering

Explore how to move beyond basic select queries by using data definition language and data manipulation language. Learn to create and maintain databases with ddl and dml.

Create databases, schemas, and tables with the create statement, defining columns with types and constraints like not null and default. Build a tutorial employees table and inspect its structure.

Learn to use the alter statement to modify tutorial.employees: add and drop columns (e.g., age), and set a new default for department (reassigned).

Insert into statements populate tables by specifying a table, a column list, and corresponding values; verify with select star, and learn defaults and multi-row inserts.

Use the update statement to modify existing table data by setting column values and applying a where clause to target specific records, as shown with an employee update.

Master deleting data using delete from with conditions and in lists, and using truncate to remove all rows, then restore data with insert for the next lesson.

Explore the merge statement, which combines update, delete, and insert into one operation by matching on id, updating when matched and inserting when not, with table aliasing.

Learn to drop tables, schemas, or databases with the drop statement, and heed its danger as drops are often unrecoverable, demonstrated by dropping employees and cleaning the schema.

Explore dates and times in Postgres, including timestamp with time zone, date formats, and UTC storage; learn functions like current_date, current_timestamp, date_trunc, and age to compute intervals.

Explain the four major temporal data types in databases—date, time, timestamp, and interval—and how casting strings to these types works, with notes on SQLite and Snowflake.

Learn how time zones work in databases, using current_setting('time zone') and convert timestamps between UTC and zones like New York, Denver, and Los Angeles with Postgres.

Learn how to define and manipulate intervals in SQL, creating intervals from strings, adding days to timestamps, and using the age function to derive intervals by subtracting two temporal values.

Learn how the enum datatype enforces a defined set of valid values and a custom order. Explore serial keys and constraints that preserve data integrity for weekdays and value lists.

Learn to use the array data type in SQL to store a sequence of integers, enable compact tables, and perform array queries and unnest operations.

Explain how the range type stores values with inclusive start and exclusive end, and how to query and cast numeric, int, and timestamp ranges in SQL.

Store and query nested data using jsonb in Postgres. Learn to index jsonb fields, fetch nested values, and update or remove keys.

Master window functions in sql to compute running totals and running sums per customer using over and partition by, and to filter by max values with window techniques.

Explore cross joins, the cartesian product, by pairing every row from two tables—advisors and buildings—to generate all possible combinations.

Learn how a lateral join fetches each user's most recent order by joining users with orders, using a left join to include users without orders, ordered by order date.

Explore how cross join and lateral join unpivot an unnormalized table, rename columns, and prepare data for normalization in Postgres.

Use coalesce to return the first non-null value from multiple fields, as shown with salary defaulting to zero and a bonus column, and learn how input order affects the result.

Classify employees using the case statement in SQL, ordering conditions from salary tiers to handle else cases, with bonus-based refinements illustrating order sensitivity.

Learn how the concat function combines VarChar values across columns to form a full name, add department information with spaces and a hyphen, and use coalesce to produce clean results.

Create a recursive cte to generate a date table from January 1, 2023 to February 1, 2023, using union all and an iteration that adds one day.

Explore recursive ctes to traverse hierarchical data, using union all and concatenation to build levels from boss to staff in an org chart, with practical analytics applications.

Explore database normalization by transforming an unnormalized table into the first normal form through unpivoting and tabular restructuring, reducing update complexity and clarifying one-to-one data relationships.

Explore second normal form by removing redundancy through tying non-key values to a primary key, splitting data into registrations and classes, and paving the way toward third normal form.

Explore third normal form by splitting data into related tables, using primary and foreign keys to connect students, advisors, rooms, and departments, and review all four normalization forms.

Explore star schema design for analytics with a central fact table and surrounding dimension tables, and contrast it with snowflake schema where dimensions are normalized for scalable joins.

Learn how to create stored procedures and user-defined functions in a Postgres database, including defining input parameters, language selection, and simple insert operations.

Master using a user defined function to compute department salary averages with a select statement, returning numeric via plpgsql, while favoring stored procedures for table manipulations.

Resolve a function error, define and invoke a user defined function, and use select to compute the average salary for department 1, demonstrating a stored procedure and a udf.

Create temporary tables to analyze a subset of large data within your session, saving computational resources. Delete the table automatically when the session ends or the stored procedure completes.

Materialized views sit between a table and a view, storing query results on disk to speed heavy aggregations and joins, with updates requiring a refresh when underlying tables change.

Master sql transactions as a single unit of work that enforces atomicity, consistency, isolation, and durability. Begin, update, commit, rollback, and use save points to control changes in Postgres.

Learn pgsql control structures in a sql block, including for and while loops, with variable declarations, a running sum, and a temporary table inside a transaction.

Create a ddl schema to recreate the erd for books, authors, customers, and purchases, defining primary keys and foreign keys like book_id, author_id, and customer_id.

Master core SQL design concepts: choosing a primary key, selecting suitable data types, avoiding reserved keywords, applying not null constraints, and enforcing referential integrity with foreign keys.

Insert two author records for J.K. Rowling and George Orwell into the authors table, then update the birth date for author id one using an update statement.

Apply window functions to rank employees by birth date, handle null time zones with coalesce to UTC, and convert work times to each employee's local time zone.