Created
October 6, 2015 00:00
-
-
Save BrianDunlap89/41f046eb68169c3f161b to your computer and use it in GitHub Desktop.
SQL Homework - Brian Dunlap
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- PLEB MODE -- | |
| -- 1 | |
| -- SQL: SELECT COUNT(id) FROM users; | |
| -- A: 50 | |
| -- 2 | |
| -- SQL: SELECT title, price FROM items ORDER BY price DESC LIMIT 5; | |
| -- A: Small Cotton Gloves, Small Wooden Computer, Awesome Granite Pants, | |
| -- Sleek Wooden Hat, Ergonomic Steel Car | |
| -- 3 | |
| -- SQL: SELECT title, price FROM items WHERE category="Books" ORDER BY price ASC LIMIT 1; | |
| -- A: Ergonomic Granite Chair (category = "") | |
| -- SQL: SELECT title, price FROM items WHERE category LIKE "%Books%" ORDER BY price ASC LIMIT 1; | |
| -- A: Ergonomic Granite Chair (category LIKE "%%") | |
| -- 4 | |
| -- SQL: SELECT user_id FROM addresses WHERE street="6439 Zetta Hills" | |
| -- AND city="Willmouth" AND state="WY"; | |
| -- A: user_id = 40 | |
| -- SQL Part Deux: SELECT first_name, last_name FROM users WHERE id=40; | |
| -- A: Corrine Little | |
| -- 5 | |
| -- SQL: SELECT id FROM users WHERE first_name="Virginie" AND last_name="Mitchell"; | |
| -- A: Virginie Mitchell's id = 39 | |
| -- SQL: UPDATE addresses SET city="New York", state="NY", zip=12345 WHERE user_id=39; | |
| -- 6 | |
| -- SQL: SELECT SUM(quantity) FROM orders; | |
| -- A: 2125 | |
| -- 7 | |
| -- SQL: INSERT INTO users (first_name, last_name) VALUES ("Brian", "Dunlap"); | |
| -- SQL: INSERT INTO orders (user_id, quantity) VALUES (51, 50000); | |
| -- HARD MODE -- | |
| -- 1 | |
| -- SQL: SELECT SUM(price) FROM items; | |
| -- A: 467488 | |
| -- 2.1 | |
| -- SQL: SELECT items.title, orders.quantity FROM items INNER JOIN orders ON items.id=orders.item_id ORDER | |
| -- BY orders.quantity DESC; | |
| -- A: 39-way tie among orders with a quantity of 10. Top entry was Gorgeous Granite Car though. | |
| -- 2.2 | |
| -- SQL: SELECT items.title, items.price * orders.quantity FROM items INNER JOIN orders ON items.id=orders. | |
| -- item_id ORDER BY items.price * orders.quantity DESC LIMIT 20; | |
| -- A: Awesome Granite Pants - 97900 | |
| -- 3 | |
| -- SQL: SELECT user_id, items.price * orders.quantity FROM orders INNER JOIN items ON orders.item_id=items. | |
| -- id ORDER BY items.price * orders.quantity DESC; | |
| -- A: user_id = 2, user first and last name = "Missouri Carroll" (?) | |
| -- 4 | |
| -- SQL: SELECT category, items.price * orders.quantity FROM items INNER JOIN orders ON items.id=orders. | |
| -- item_id ORDER BY items.price * orders.quantity DESC LIMIT 5; | |
| -- A: Top 3 categories - Toys & Books (97900), Health & Grocery (91290), Games (90000) | |
| -- 5 | |
| -- SELECT SUM(items.price * orders.quantity) FROM items INNER JOIN orders ON items.id=orders.item_id WHERE | |
| -- category="Books"; | |
| -- A: 420566 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment