[11/18/22] MUSA SQL Workshop w/ Data @ UCI

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 '/work/SunBurst/Product.csv' p;

Example 2: Return all the rows in the Discount table

SELECT * FROM '/work/SunBurst/Discount.csv' 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 3a: Look up the names of all members who joined after June

SELECT * FROM '/work/SunBurst/Member.csv' m WHERE join_month >= "2022-07-01";

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

SELECT * FROM '/work/SunBurst/Order.csv' 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 ordered products and when they were sold.

SELECT o.sold_date, p.name FROM '/work/SunBurst/Order.csv' o, '/work/SunBurst/Product.csv' 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 6: Find the name and price of the two most expensive products we have in our store.

SELECT name, price FROM '/work/SunBurst/Product.csv' ORDER BY price DESC LIMIT 2;

Example 7: Return all the orders sorted by sold_date and then prod_id.

SELECT * FROM '/work/SunBurst/Order.csv' ORDER BY sold_date, prod_id;

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 8: How many orders from non-members do we have?

SELECT COUNT(*) FROM '/work/SunBurst/Order.csv' o WHERE member_email IS NULL;

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

SELECT DISTINCT(member_email) FROM '/work/SunBurst/Order.csv' o WHERE sold_date = '2022-06-10';

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

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

SELECT COUNT(DISTINCT(member_email)) FROM '/work/SunBurst/Order.csv' o WHERE sold_date >= '2022-07-01' AND sold_date <= '2022-07-31';

Example 11a: Find the total revenue

SELECT SUM(price) FROM '/work/SunBurst/Order.csv' o, '/work/SunBurst/Product.csv' p WHERE o.prod_id = p.prod_id;

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

SELECT MIN(price), MAX(price) FROM '/work/SunBurst/Product.csv';

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 13: 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 '/work/SunBurst/Member.csv' WHERE email NOT IN ( SELECT DISTINCT(member_email) FROM '/work/SunBurst/Order.csv' WHERE prod_id = 1 AND member_email IS NOT NULL );

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

SELECT p.name FROM '/work/SunBurst/Product.csv' p WHERE p.prod_id IN ( SELECT prod_id FROM '/work/SunBurst/Order.csv' o WHERE o.member_email IS NOT NULL) AND price = ( SELECT MAX(price) FROM '/work/SunBurst/Order.csv' o1, '/work/SunBurst/Product.csv' p1 WHERE o1.prod_id = p1.prod_id AND o1.member_email IS NOT NULL );

Let's break down this query:

From the Product Table

Where the product was purchased from a member

And their price equals the price of the most expensive item purchased by a member (which implies that we found the product!)

Give me their product name.