![[OLD] Data @ UCI](https://deepnote.imgix.net/avatars/1cfa9773-d773-408a-a8e3-9ab8c577f787)
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:
[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
Example 2: Return all the rows in the Discount table
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
Example 5: Find all the orders that have a discount applied to them.
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.
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.
Example 7: Return all the orders sorted by sold_date and then 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?
Example 9: Find all the unique members who purchased a product on June 10th.
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
Example 11a: Find the total revenue
Example 12: Return the prices of our cheapest and most expensive 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 13: Find the names of all members who are have not purchased our UC Urvine Crop Top (aka Product ID 1)
Example 14: What were the most expensive item(s) our members purchased from our store?
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).