SQL Fundamentals

Show every column and every row in the orders table

SELECT * FROM orders;

Show only the customer and amount columns

SELECT customer, amount FROM orders;

Show orders where the city is Chicago

SELECT * FROM orders WHERE city = 'Chicago'; (text needs single quotes)

Show orders with an amount greater than 100

SELECT * FROM orders WHERE amount > 100; (numbers don't need quotes)

Show orders NOT from Chicago

SELECT * FROM orders WHERE city != 'Chicago'; (<> also works)

Show customers whose name starts with J

SELECT * FROM orders WHERE customer LIKE 'J%'; ('%J' = ends with J, '%J%' = contains J)

Show Chicago orders that are also over 50

SELECT * FROM orders WHERE city = 'Chicago' AND amount > 50;

Show orders with an amount from 20 to 50, including both

SELECT * FROM orders WHERE amount BETWEEN 20 AND 50; (BETWEEN is inclusive)

Show orders from Chicago or Denver

SELECT * FROM orders WHERE city = 'Chicago' OR city = 'Denver'; (repeat the column name)

Shorter way to show orders from Chicago, Denver, or Austin

SELECT * FROM orders WHERE city IN ('Chicago', 'Denver', 'Austin');

Sort orders from biggest amount to smallest

SELECT * FROM orders ORDER BY amount DESC; (default is ascending)

Show only the 3 biggest orders

SELECT * FROM orders ORDER BY amount DESC LIMIT 3; (LIMIT goes last)

How many orders are in the table?

SELECT COUNT(*) FROM orders;

What's the total of all order amounts?

SELECT SUM(amount) FROM orders;

Find the single largest order amount

SELECT MAX(amount) FROM orders; (MIN = smallest, AVG = average)

Show each customer and their total spent

SELECT customer, SUM(amount) FROM orders GROUP BY customer;

Show customers whose total spent is over 100

SELECT customer, SUM(amount) FROM orders GROUP BY customer HAVING SUM(amount) > 100;

Difference between WHERE and HAVING

WHERE filters rows before grouping. HAVING filters groups after GROUP BY and can use SUM, COUNT, etc.

Add a new order: Sam bought a lamp for 40 in Denver

INSERT INTO orders (customer, product, amount, city) VALUES ('Sam', 'lamp', 40, 'Denver');

Change order #7's amount to 55

UPDATE orders SET amount = 55 WHERE id = 7; (no WHERE = every row changes!)

What does DELETE FROM orders; do with no WHERE?

Deletes every row. The empty table still exists.

Correct clause order in a query

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT (Some Funny Wizards Grow Huge Orange Lemons)