Job-Ready SQL in an Afternoon

Learn how to create and drop databases, switch connections, and create and drop schemas, including using the public schema and introspecting schemas for organized table groups.

Create tables in database schemas by defining columns, data types, and primary keys; switch schemas, drop with cascade, and implement foreign keys referencing user info and event info tables.

Alter tables to add, drop, or modify columns in the user info table, such as adding a region column with a char(20) type and later resizing to bigint.

Create enumerated data types to model ordered concepts like the day of week, then use them in an attendance table; separate statements with semicolons for sequential execution.

Read data from the album table with select *, from album, and learn how quotation marks around table names affect queries, plus selecting specific columns and applying limit to results.

create a table from a query result using 'create table as select', with column types inferred from the results and optional if not exists to avoid clashes.

Learn to use aliases for column and table names, apply the as keyword, and order results with order by, including ascending and descending orders and column position references.

Filter the track table with a where clause to find songs by composer such as AC DC, using operators =, <=, >=, <, and > on milliseconds.

Explore joining conditional filters to refine SQL queries by combining and/or logic, using operators like =, >=, <=, like, and parentheses, and applying to track data with composer, milliseconds, and bytes.

Negate filter conditions in SQL with not equals, not like, not between, and not in, and learn proper order by and limit usage.

Learn how to filter for null values in SQL using is null and is not null, applying these checks to a track table and composing accurate where clauses.

Learn to cast data types to convert integers to real numbers for decimal results, using cast as data type and the :: data type syntax on values or columns.

Trim and standardize strings in SQL with ltrim, rtrim, and trim to remove unwanted characters, apply upper or lower case, and cast between text and numeric types.

Measure string length, bucket name lengths with case statements, and locate positions with position, using ilike for case-insensitive searches in composer data.

Learn to concatenate strings to build meaningful columns like song name plus composer, format with spaces, and cast numbers; also extract substrings using position and substring for data processing.

Explore string replacements in sql by using the replace function to format timestamps, convert timestamp to text, and standardize date formats across databases.

Learn to use time intervals in SQL to compute date differences, add durations to timestamps, and filter data by last week or seven days.

Explore SQL aggregation functions on the track table, calculating average, maximum, minimum, and total milliseconds, counting rows, handling nulls, and converting milliseconds to minutes.

Apply group by to compute aggregations per composer and album, counting tracks and averaging lengths. Use where and having to filter grouped results and order by counts.

Learn how to use unions to vertically stack data from split tables, creating a single queryable result across multiple track tables, and manage and clean up with drop table commands.

Learn how to use subqueries to nest results, group by invoice id, and count items, enabling multi-step aggregations and clearer query references.

Learn how common table expressions (CTEs) simplify large queries, boost readability and performance, and reuse results to find top five albums with the most songs and more than one genre.

Learn to use window functions and aliases to compute running totals, counts, and averages per album while preserving all track rows.

Use window functions to compute row numbers and ranks within partitions by unit price, showing how duplicates affect ranking with rank and dense_rank for consistent ordering.

Learn how to access data in other rows with window functions, using lead and lag to compute next invoice dates and time differences, including partitioning by customer.

Learn to split data into equally sized buckets using the ntile window function, partitioned by customer and ordered by invoice date, and assign a bucket number for analysis.