My answers to sqlcourse2.com exercises

sqlcourse2exercises.txt

-- Select 

SELECT * FROM items_ordered WHERE customerid = 10449;

SELECT * FROM items_ordered WHERE item = 'tent';

SELECT customerid, order_date, item FROM items_ordered WHERE item LIKE '%S';

SELECT DISTINCT item FROM items_ordered;

-- 3: Aggregate functions

SELECT MAX(price) FROM items_ordered;

SELECT AVG(price) FROM items_ordered WHERE order_date LIKE '%DEC%';

SELECT Count(*) FROM items_ordered;

SELECT MIN(price) FROM items_ordered WHERE item LIKE '%tent%';

-- 4: group by 
SELECT count(state), state FROM customers GROUP BY state;

SELECT item, max(price), min(price) FROM items_ordered GROUP BY item;

SELECT customerid, count(customerid), sum(price) FROM items_ordered GROUP BY customerid;

-- 5: having
SELECT state, count(*) FROM customers GROUP BY state HAVING count(*) > 1;

SELECT item, max(price), min(price) FROM items_ordered GROUP BY price HAVING max(price) > 190.00;

SELECT customerid, count(customerid), sum(price) FROM items_ordered GROUP BY customerid HAVING count(*) > 1;

-- 6: order by
SELECT lastname, firstname, city FROM customers ORDER BY lastname ASC;

SELECT lastname, firstname, city FROM customers ORDER BY lastname DESC;

SELECT item, price FROM items_ordered WHERE price > 10.00 ORDER BY price ASC;

-- 7: where
SELECT customerid, order_date, item FROM items_ordered WHERE (item <> 'Snow Shoes') AND (item <> 'Ear Muffs');

SELECT item, price FROM items_ordered WHERE (item LIKE 'S%' OR item LIKE 'P%' OR item LIKE 'F%');

-- 8: set operations (in and between)
SELECT order_date, item, price FROM items_ordered WHERE price BETWEEN 10.00 AND 80.00;

SELECT firstname, city, state FROM customers WHERE state IN ('Arizona', 'Washington', 'Oklahoma', 'Colorado', 'Hawaii');

-- 8: math
SELECT item, SUM(price)/SUM(quantity) FROM items_ordered GROUP BY item;

-- 9: join
SELECT customers.customerid, customers.firstname, customers.lastname, items_ordered.order_date, items_ordered.item, items_ordered.price FROM items_ordered, customers WHERE items_ordered.customerid = customers.customerid;

SELECT customers.customerid, customers.firstname, customers.lastname, items_ordered.order_date, items_ordered.item, items_ordered.price, customers.state FROM items_ordered, customers WHERE items_ordered.customerid = customers.customerid ORDER BY customers.state DESC;