[11/9/22] SQL Workshop w/ Data @ UCI x MAISS

SQL Basics

SELECT & FROM - The Foundational Words of SQL

Two essential words to every SQL query: SELECT and FROM. So a typical query looks like this:

-- Don't actually run this. This is just an very basic example of how SELECt and FROM are used. SELECT column1, column2, ... FROM table [name]

[name] is an optional (indicated by []) temporary name for the table.

Let's break it down:

Example 1: Find the names and prices of all of our products

SELECT name, price FROM "public"."Product" p;

Example 2: Return all the rows in the Discount table

SELECT * FROM "public"."Discount" d;

You can also specify more than one table in your FROM clause. But in order to do that, we will need to learn about the WHERE clause.

WHERE (AND, IN, OR, NOT, IS NULL)

Example 3: Look up the names of all members who joined in June or July

SELECT first_name, last_name FROM "public"."Member" m WHERE join_month >= '2022-06-01' AND join_month <= '2022-07-31';

Example 4: Find all the orders that have a discount applied to them.

SELECT * FROM "public"."Order" o WHERE discount_code IS NOT NULL;

Return all the order_id that contain the "July Promo" code and are only from members.

You can use WHERE besides data filtering. You can use the WHERE clause to join two tables using Boolean expressions.

Return the name of all products ordered and when they were sold.

SELECT o.sold_date, p.name FROM "public"."Order" o, "public"."Product" p WHERE o.prod_id = p.prod_id;

Our practice questions will not require you to do this but it's an important tool to keep in mind.

LIMIT, ORDER BY

LIMIT allows you to limit the number of rows displayed in your query.

ORDER BY allows you to order the query by specific column(s)

ORDER BY column1, ... ASC sorts the query results by colums1, ... in ascending order (smallest to largest)

ORDER BY column1, ... DESC sorts the query results by column1, ... in descending order (largest to smallest)

Example 5: Return the name of the first member who joined us!

SELECT first_name, last_name FROM "public"."Member" LIMIT 1;

Example 6: Sort the Order Table by prod_id and THEN sold_date in ascending order.

SELECT * FROM "public"."Order" ORDER BY prod_id, sold_date ASC;

Return the prod_id of the last 10 orders.

Aggregates (COUNT, DISTINCT, SUM, AVG, MIN, MAX)

An aggregate in SQL is a function that performs a calculation on multiple values and returns a single output. There are many different aggregates that perform a variety of calculations. Here are some of the basic, but very important, aggregates:

Example 7: How many orders from non-members do we have?

SELECT COUNT(*) FROM "public.Order" o WHERE member_email IS NULL;

Example 8: Find all the unique members who purchased a product on June 10th.

SELECT DISTINCT(member_email) FROM "public"."Order" o WHERE sold_date = '2022-06-10';

DISTINCT is a special aggregate because you can use it WITHIN another aggregate.

Example 9: Find the number of members who purchased AT LEAST one item in July

SELECT COUNT(DISTINCT(member_email)) FROM "public"."Order" o WHERE sold_date >= '2022-07-01' AND sold_date <= '2022-07-31';

Example 10: Find the total revenue we made in July

SELECT SUM(price) FROM "public"."Order" o, "public"."Product" p WHERE o.prod_id = p.prod_id AND sold_date >= '2022-07-01' AND sold_date <= '2022-07-31';

Example 11: Return the prices of our cheapest and most expensive product

SELECT MIN(price), MAX(price) FROM "public"."Product";

When the last time a member purchased an item from our store?

Subqueries - Creating Tables from Queries

Subqueries are essentially queries inside queries, temporary tables that we create using SQL queries. These can be very filtering tool.

Example 12: Find the names of all members who are have not purchased our UC Urvine Crop Top (aka Product ID 1)

SELECT first_name, last_name FROM "public"."Member" WHERE email NOT IN ( SELECT DISTINCT(member_email) FROM "public"."Order" WHERE prod_id = 1 AND member_email IS NOT NULL );

Example 13: What were the most expensive item(s) our members purchased from our store?

SELECT p.name FROM "public"."Product" p WHERE p.prod_id IN ( SELECT prod_id FROM "public"."Order" o WHERE o.member_email IS NOT NULL) AND price = ( SELECT MAX(price) FROM "public"."Order" o1, "public"."Product" p1 WHERE o1.prod_id = p1.prod_id AND o1.member_email IS NOT NULL );

Let's break down this query:

From the Products Table

Where the product was purchased from a member

And their price matches the price of the most expensive item purchased by a member

Give me their product name.

Name the first two members that purchased our Women's Biker Shorts (Product ID: 3).