SQL and PostgreSQL: The Complete Developer's Guide

Apply a where clause to filter rows, using an example of cities with area greater than 4000, then learn how Postgres processes from, where, and select.

Explore how the where keyword filters query results by area, using operators such as equal, not equal, less than, and greater than, and preview between and not in.

Explore how to use between, in, and not in a where clause to filter cities by area and name, and combine conditions with and or for complex queries.

Apply a where clause to filter a phones table and print the name and price for phones with units sold greater than 5000, reinforcing this concept.

Explore a practical where clause by building a query to select name and price from phones where units_sold exceeds 5000, illustrating filtering in SQL.

Learn to use the where clause with lists to filter a phones table by Apple or Samsung, selecting name and manufacturer, and explore multiple valid query approaches.

Compare two SQL solutions to filter the phones table by manufacturer, using an in list of Apple and Samsung and a separate or condition, and verify results.

Learn how to perform calculations in where clauses, such as computing population density as population divided by area and filtering cities with density over 6000, while clarifying order of operations.

Compute total revenue by multiplying price by units_sold for each phone, alias it as total_revenue, then select name and total_revenue from phones where total_revenue is greater than 1 million.

Learn how to update records using update, set, and where to modify a city's population, illustrated with Tokyo, and verify changes with a select query.

Demonstrates deleting SQL rows with delete from a table where a condition targets single or multiple records, using the cities table and Tokyo examples.

Update the phones table by setting units_sold to 8543 for rows where name equals '8', then verify results with a select * from phones.

Practice deleting rows from the phones table where the manufacturer is Samsung, then select all rows from phones to verify the results.

Move beyond single-table exercises and design a multi-table database for a photo sharing application in Postgres.

Design a photo sharing app database with four tables—users, photos, comments, and likes—and map their relationships for clear resource ownership.

Explore one-to-many and many-to-one relationships by modeling how a user owns photos and how photos host comments, using practical diagrams and SQL concepts.

Explore one-to-one and many-to-many relationships with real-world examples, and learn how these relationships contrast with one-to-many and many-to-one patterns in the database.

Identify the primary key as a unique, unchanging identifier for each row. Use a foreign key, such as user_id in the photos table, to model one-to-many and many-to-one relationships.

Master foreign keys by linking comments, users, and photos through one-to-many relationships; learn foreign key placement on the many side, explain primary vs foreign keys, and naming conventions.

Learn how to create a users table with an auto-generated id using Postgres serial, enforce a primary key, and store usernames with varchar(50), then verify inserts.

Create the photos table with a serial id as primary key, a url varchar(200), and a user_id integer foreign key referencing users(id), illustrating data consistency and user photo queries.

Add multiple photos to the photos table with urls and user IDs, then query the association between photos and users with joins and foreign keys in Postgres.

Add a foreign key by creating a boat_id column in crewmembers to link to boats, then query crew members with boat_id = 1; environment uses autoincrement instead of Postgres serial.

This lecture adds a foreign key boat_id to crew_members referencing boats.id and demonstrates a query to fetch crew members with boat_id = 1, confirming the relationship.

Explore data consistency through foreign key constraints in Postgres, ensuring photos reference existing users or have null associations. Learn three insertion scenarios: valid reference, invalid reference, and null reference.

Explore how foreign keys enforce deletion rules, prevent dangling references, and apply on delete options such as cascade, set null, set default, or no action.

Demonstrate on delete cascade by dropping and recreating a photos table, adding a foreign key, and testing user deletion auto removes related photos.

Explore how to implement on delete set null for a foreign key, showing a photos table where deleting a user id sets related photos' user_id to null.

Add a comments table linked to photos and users with id, photo_id, user_id, and contents up to 240 characters, and craft queries for a user's comments and a photo's comments.

Explore writing complex sql queries using joins and aggregation to answer multi-table questions, such as the average comments per photo and listing all comments with authors for a given photo.

Learn how to join comments and users tables to display each comment's contents alongside its author's username, using a join on user_id.

Explore basic join concepts by combining comments with related photos using an inner join on photos.id equals comments.photo_id, and select contents and url.

Practice joining books and authors with a join statement to print each book title and its author name, using a temporary table called books with authors.

Write a select to join books and authors on author_id equals id, selecting title and author name. Explore basic join concepts and how column order affects output.

Explore how the order of from and join clauses affects results, how to disambiguate identical column names, and how aliases with the as keyword simplify SQL joins.

Learn how from and join order affects results, why a join can drop photos with null user ids, and how to preserve all photos when linking photos to users.

Explore how a join merges rows from photos and users, showing that a photo with a null user ID has no match and is dropped.

Explore four kinds of joins—inner, left outer, right outer, and full. Learn how they merge tables, select matching rows, and fill nulls for unmatched data, with inner join as default.

Master left outer joins to include all photos, even without a user, and explore right outer and full joins to show all users and unmatched data.

Learn when the order of from and join matters for left and right outer joins, and when it doesn't for inner or outer joins, with photos and users examples.

Write a query that returns every book title with its author name, ensuring all authors appear—even those without books—by using a join that includes all authors.

Compare left outer join and right outer join to display every author's name and their book title, including authors without a book.

Join comments and photos with an inner join on photo ids, then use where to print the url and contents when the comment user id equals the photo user id.

Learn to perform a three-way join across comments, photos, and users to reveal the photo author's username, using users.id = comments.user_id and users.id = photos.user_id with an inner join.

Perform a three-way join across reviews, books, and authors to display each book title with its author and the review rating, filtered when the author and reviewer match.

This exercise solution demonstrates building a multi-table inner join across reviews, books, and authors to select title, name, and rating, returning only complete rows.

Explore grouping and aggregation in SQL and PostgreSQL by using group by to condense many rows into fewer results and applying aggregate functions to obtain one value.

Learn how the group by keyword condenses rows by user id. Understand how the temporary grouped result is formed and how aggregate functions enable selecting non grouped columns.

Explore aggregate functions that reduce many values to a single result, including max, min, avg, count, and sum, and use them with group by.

Combine aggregates and grouping by using group by with aggregate functions like max and count on the comments table, grouping by user id to reveal per-user totals.

Discover how count handles nulls and why count(*) counts all rows, then apply grouping by user_id to compare per-user totals despite null values.

Group by photo ID and apply aggregate functions to count comments per photo with count(*). Learn how buckets show which photos have comments and how many.

Write a SQL query using PostgreSQL that prints an author's id and their book count, using only the books table. Avoid touching the authors table.

Walk through grouping the books table by author_id, then select author_id and count rows per group with count(*).

Join the books and authors tables to count how many books each author has authored, using the author's name and grouping by author name.

Explore a solution that prints the author's name by joining books and authors, grouping by the author's name, selecting the name, and counting rows.

Learn to filter groups with having after group by, compare with where, and count comments per photo to keep groups with more than two comments.

Learn to write a SQL query that counts comments per photo with photo ID < 3, using where, group by, and having with count(*) to filter groups.

Apply initial filters in SQL to identify comments on the first two photos. Use group by and having count(*) in PostgreSQL to find users with more than two comments.

Group phones by manufacturer, compute total revenue as price times units sold, and use having to print each manufacturer and its total revenue when revenue exceeds 2 million.

Apply a quick solution to SQL grouping and filtering by manufacturer, using group by, having, and sum(price * units_sold) to compute and filter revenue above 2 million.

Explore a PostgreSQL-based e-commerce dataset with users, products, and orders, illustrating a many-to-many relationship via a join table and practical joins, group by, and selecting queries.

Explore a group by exercise to count paid and unpaid orders in the orders table, applying group by and count to interpret the e-commerce data set in SQL and PostgreSQL.

Learn how to group by the paid column in the orders table and count rows per group using count(*), producing true and false buckets, and understand grouped or aggregated columns.

Practice joining users and orders to display each user’s first name, last name, and payment status. Start from either table, merge matching rows by user id, and select three columns.

Start with all users, join orders on orders.user_id = users.id, and select first_name, last_name, and paid status to see who paid.

Sort records using the order by clause in sql, sorting products by price from lowest to highest, with optional descending and explicit ascending options.

Explore two variations of sorting in sql by using order by on name strings and on price, with secondary weight sorting for equal prices, in ascending or descending order.

Master how to use offset and limit to paginate results and skip records. Apply order by price to get the least or most expensive items.

Apply sort, offset, and limit to the phones table to show the second and third most expensive phones by price.

Learn to select phone names by ordering by price descending, then apply limit 2 and offset 1 to retrieve the second and third most expensive phones in postgresql.

Learn how to use union to combine two queries that fetch the top four most expensive items and the top four by price-to-weight ratio, including union all to keep duplicates.

Explore how the union keyword combines two queries, using parentheses to clearly apply order by and limit to the union results, while ensuring matching columns and compatible data types.

Learn how intersect returns only rows common to two queries, compare its behavior with union and union all, and examine duplicates using four top-priced products and four top price-to-weight ratios.

Understand the except set operation: it returns left-hand rows not found in the right-hand query. The order of queries changes the result, while extra right-side rows do not.

Write two queries using union: list manufacturers with phones under $170, and list manufacturers with more than two phones using group by and having; avoid parentheses in this environment.

Demonstrates solving a SQL exercise by writing two queries and uniting them with union, then using group by and having to filter manufacturers with more than two phones under 170.

Learn how subqueries compare prices by using an inner query to find the toys department's max price, and an outer query to list higher-priced products.

Explore how subqueries can appear in select, from, join, and where clauses, and how their shapes—single value, single column, or multiple rows—drive query design.

Explore how subqueries shape the data returned by a query, focusing on single-value subqueries inside a select statement, like calculating the max price from products.

Practice querying the phones table by listing each phone's name and price, plus a third column showing price divided by the max price via a subquery.

select name, price, and price ratio by dividing each phone's price by the maximum price from a subquery, aliasing the third column as price ratio.

Discover how to place a subquery in a from clause, apply an alias, and ensure outer query compatibility using a price to weight ratio example.

Explore how subqueries in the from clause can return a single value or a set, using max price and aliasing to power outer queries.

Explore how to use a subquery in the from clause to compute the average orders per user in SQL, illustrated by counting per user and applying avg on the results.

Practice calculating the average price of phones by manufacturer using a group by clause, renaming the avg price column, and then finding the maximum value with a subquery.

Calculate the average price by manufacturer in phones, wrap the result as a subquery with alias prices, and extract the max of avg_price renamed as max average price.

Explore using a subquery inside a join clause with an inner join and an on clause to connect orders and users by common IDs.

Learn how to use subqueries inside where clauses and operator choices dictate single value versus list results. Use the in operator to filter product orders by price to weight ratios.

Learn how the where clause operator dictates the subquery result shape, selecting a single value or a single column, then apply this to find products priced above the average.

Practice writing a SQL query in PostgreSQL to print phone names and prices where price is greater than the Samsung S56 20 monte price fetched via a subquery.

Select the phone name and price from phones and filter where price is greater than the subquery price of the S56 20 Monte.

Explore using the not in operator with a single-column subquery to filter products by departments not containing any item priced under 100, returning names and departments.

Learn to use the greater than all operator with a subquery to find products with prices higher than all industrial department items, returning name, department, and price.

Demonstrates the some operator as an alias for any, showing how a left value compares to at least one value from a subquery using product prices in the industrial department.

Practice a quick subquery exercise to print the name of every phone with a price greater than any phone made by Samsung, comparing each phone to Samsung prices.

Learn to use a subquery with the greater than all operator to find phone names priced higher than every Samsung phone, by selecting names from phones where manufacture equals Samsung.

Explore correlated subqueries by solving how to return the most expensive product per department, using outer and inner queries, aliases, and max price calculations.

Explore correlated subqueries by counting orders per product without join or group by, using an outer query on products and a subquery on orders.

Explore using a subquery inside a select clause without a from, when it returns a single value, to compute max, min, or average prices and display multiple results.

Practice calculating the maximum, minimum, and average prices from the phones table using three subqueries to produce a single row with three columns.

Combine three subqueries in a single outer select to compute max price, min price, and average price from phones, aliasing results as max price, min price, and avg price.

Explore the distinct keyword in SQL, used after select to obtain unique values, compare it with group by, and note that counting with distinct over multiple columns is not supported.

Practice counting distinct values in SQL by querying the manufacturer column to print the number of unique phone manufacturers, such as Nokia, Apple, Samsung, and Motorola.

Explore how to use select count with a distinct subquery to count unique manufacturers from phones. Validate the result by printing the value and checking the solution.