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 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.

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.

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.

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 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.