SQL Essentials: The Beginner's Guide to SQL Language

Install PostgreSQL and create tables with data for lab exercises. Choose the Windows or Mac lecture for your computer, then download the zip file containing scripts from the resources tab.

Install PostgreSQL for Windows by downloading installer from PostgreSQL.org, running it with a password and locale, then use pgAdmin 4 to run a script containing create table and copy commands.

Install and configure PostgreSQL on macOS, including downloading the installer, setting locale and password, running the data script, and using pgAdmin4 to validate tables in the postgres database.

Explore pgAdmin, a client tool for submitting queries to PostgreSQL, and learn to execute, save, and manage multiple queries, view data output, and inspect table structures in a local browser.

Learn how to create tables with the create table command, define columns and primary and foreign keys, and understand common data types for numbers, text, and date and time.

Learn how primary keys enforce unique values and not null constraints, and how foreign keys enforce references, illustrating one-to-many, many-to-one, one-to-one, and many-to-many relationships using band and album examples.

Learn to write simple select queries to read data from a table, filter columns and rows, and apply expressions with column aliases and upper-case transformations.

Learn to filter rows with a where clause and logical operators, use limit to preview results, and control operator order with parentheses for precise queries.

Learn to retrieve information about the band Led Zeppelin from the band and album tables using select queries, limit rows, where filters, and query history checks.

Explore SQL operators: IN and NOT IN for matching a column to a value list or subquery, and BETWEEN and NOT BETWEEN for inclusive ranges.

Explore subqueries: use where band_id in (subquery) and derive tables in from clause. Alias requirements vary; these patterns enable more complex queries and set up future joins.

Combine the album and band queries into a single query using a subquery with IN, and improve readability with indentation to explain why IN handles multiple rows better than =.

Learn to use sql comments to describe code and selectively enable or disable conditions with one-line and block comments, including where clauses and substring-based checks.

Explore null values as unknowns across numeric and character data, learn to test with is null and coalesce, and understand how null affects arithmetic and concatenation.

Explore how NULL values impact SQL comparisons and not equal queries. Use A IS NULL and A IS NOT NULL, plus NOT (A = 1), to include all rows correctly.

Work through exercise 3 solutions, selecting from band where name equals 'Icarus', then use year IS NULL with AND; avoid year = NULL, which returns no rows.

Explore how null values affect in and not in with subqueries, examples from LIST1 and BAND tables, and learn to avoid empty results by adding where column is not null.

Explore data aggregation with count, group by, and where to compute row counts and group results by band_id and year using the album table.

Explore how where and having filter data before and after aggregation, using count(*) with group by to identify bands by album counts.

Learn to use count(*) and count(column) to tally rows and non-null values, then sum n_albums, and see how nulls affect grouping and aggregation.

Master aggregation functions beyond count, including min, max, sum, avg, and count distinct, and use group by with column positions to simplify queries and reveal data quality insights.

Learn to count distinct values with count(distinct ...), select distinct, and group by to list unique years or band_id and year. Use a subquery and count(*) for multi-column distincts.

Sort query results with an order by clause, use descending or ascending order, and nulls first or last, reflecting the sequence: select, from, where, group by, having, and order by.

Demonstrate exercise 4 solutions using aggregation and group by name with count(*) and having, then identify the band with the most albums via subqueries and alternatives.

Explore how inner joins combine data from album and band tables, using aliases to pull columns from both sides, and observe how many-to-one relationships shape query results.

By selecting Led Zeppelin from the band table and performing an inner join to the album table to view combined band and album data.

Learn to join three or more tables using inner joins on band_id and person_id, alias tables, and format queries for readability to map musicians with band, band_person, and person.

Analyze duplicate and missing rows in band and album joins to understand data integrity, and verify primary and foreign key constraints with test queries.

Explore left outer joins to retain rows with no matches, using NULLs for missing band data, and see how where vs on clause affects results.

Explains SQL join syntax variations, comparing from with inner join and on to from with comma separated tables and where, and shows that left join equals left outer join.

Explore right outer join concepts by contrasting them with left outer joins, identifying the primary album table, and showing how swapping tables rewrites right as left outer joins.

Explore solving sql exercises by counting Led Zeppelin albums per year using a calendar_year lookup. Apply left outer joins, group by year, and count(album_id) to show zero years.

Explore full outer join by combining albums and bands, returning rows from both tables, filling unmatched rows with nulls, regardless of row order.

Explore self joins to relate rows within the same table, using a music instrument hierarchy to show parent, grandparent, and child relationships with left outer joins.

Learn to query a music instruments hierarchy in a single table using self-joins across levels, left joins, and counts of non-null ids to identify the bottom of the hierarchy.

Explore cross joins, or cartesian joins, which pair every row from the first table with every row from the second when no join condition is specified.

Explore set operators, including union all; union all includes duplicates while union removes duplicates. Learn to align column counts and data types, and that order by applies after the union.

Learn how except all (also called minus) subtracts rows from one query and how duplicates are retained or removed. Explore null values, not in, and coalesce effects on results.

Explore the intersect and intersect all operators to find common rows across queries, handle duplicates, and manage null values with cautious subqueries and coalesce considerations.

Explore solving exercise 8 by querying albums named 'Now' and 'The collection', intersecting two select lists to identify bands that released both, then verify band names from the band table.

Overview of functions and operators in SQL, including logical and comparison operators, the like operator, mathematical operators, and concatenation ||; learn the most frequently used SQL functions.

Master useful numeric functions and operators in SQL, including ABS, ROUND, TRUNC, FLOOR, CEILING, CAST, and NULLIF, with integer division nuances and NULL handling.

Master the like operator to match a template against character values using % and _ wildcards. Escape characters and craft patterns that locate starts, ends, or contains substrings with examples.

Learn to use substring, position, and character_length to manipulate strings in SQL. Discover trimming, lower and upper case, and splitting names by spaces with practical debugging tips.

Learn date and time handling in SQL by converting strings to dates with TO_DATE, formatting with TO_CHAR, using current date and timestamp, and extracting year, month, day, and time.

Learn to query names containing magic with like and position, handle case with lower or upper, and analyze first letters and names using group by and count.

Master the insert command to add new rows with insert values, insert into with or without a column list, insert-select, and loading data from files.

Use update to modify existing rows, not add new ones, with syntax: update table set column = value where condition; you can also update from another table via a join.

Use the delete command to remove rows from a table, with or without a where clause; verify the rows with a select count using the same where condition before deleting.

Explore exercise 10 solutions, including creating a table using create table as select, verifying row counts, and updating, inserting, and deleting rows in the band and album tables.

Identify discrepancies between two tables by comparing per band album counts, handling null values with full outer joins and except, and using subqueries and aggregation.

Learn to eliminate discrepancies between two tables by using generic update, delete, and insert queries, updating band data from the album table, and validating changes via comparison queries.