Beginner SQL Workbook

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 emails of all members who joined after June

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

Example 3b: Look up the names of all members who joined between July and July.

SELECT first_name, last_name FROM '/work/SunBurst/Member.csv' m WHERE join_month >= '2022-06-01' AND join_month <= '2022-07-31';

Example 4: Find all UC Urvine Crop Top (prod_id: 1) and Women's Biker Shorts (prod_id: 3) orders.

SELECT * FROM '/work/SunBurst/Order.csv' o WHERE prod_id IN (1, 3);

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;

Be aware that null values interacts with you comparison and word operators in a special way. So you would want to use aggregates whenever you work with NULL values (which we will cover later :D ). You can more information about how to handle null values here.

Bad Query: Return all the orders that are from non-members.

SELECT * FROM '/work/SunBurst/Order.csv' o WHERE discount_code = NULL

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

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

SELECT o.sold_date, p.name FROM '/work/SunBurst/Order.csv' o JOIN '/work/SunBurst/Product.csv' p ON o.prod_id = p.prod_id;

You can also use WHERE to join two tables using Boolean expressions!

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.

The query will first sort the orders by their sold_date. If the sold_date of two orders are the same, the query will look to the prod_id to try and break the tie!

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

order_idint64

1 - 1394

shipment_idint64

1 - 21

1393

1387

1384

1381

1382

1387

1383

1389

1385

1392

1386

1383

1384

1391

1388

1385

1389

1386

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 JOIN '/work/SunBurst/Product.csv' p ON o.prod_id = p.prod_id;

Example 11b: Find the total revenue we made in July

SELECT SUM(price) FROM '/work/SunBurst/Order.csv' o JOIN '/work/SunBurst/Product.csv' p ON o.prod_id = p.prod_id WHERE sold_date >= '2022-07-01' AND sold_date <= '2022-07-31';

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 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 JOIN '/work/SunBurst/Product.csv' p1 ON o1.prod_id = p1.prod_id WHERE 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.

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