Skip to content

Instantly share code, notes, and snippets.

@TonyNguyen87
Created March 21, 2016 20:35
Show Gist options
  • Save TonyNguyen87/b26c2818a207201384a4 to your computer and use it in GitHub Desktop.
Save TonyNguyen87/b26c2818a207201384a4 to your computer and use it in GitHub Desktop.
DATABASICS
Normal Mode - No Joins Required!
How many users are there?
SELECT SUM(id) FROM users;
What are the 5 most expensive items?
SELECT * FROM items ORDER BY price desc LIMIT 5;
What's the cheapest book? (Does that change for "category is exactly 'book'" versus "category contains 'book'"?)
SELECT * FROM items WHERE category LIKE "%Books%" ORDER BY price asc LIMIT 1
NOTE: There is a Books category.
Who lives at "6439 Zetta Hills, Willmouth, WY"? Do they have another address?
SELECT * FROM addresses WHERE street LIKE "%6439%";
43|Kyra|Kilback|demarcus.predovic@grimes.org
43|40|6439 Zetta Hills|Willmouth|WY|15029
44|40|54369 Wolff Forges|Lake Bryon|CA|31587
Correct Virginie Mitchell's address to " New York, NY 12345".
UPDATE addresses SET zip = 12345 WHERE user_id = 39 AND state LIKE "%NY";
How many total items did we sell?
SELECT SUM(quantiTy) FROM ORDERS;
Simulate buying an item by inserting a User for yourself and an Order for that User.
INSERT INTO users (first_name, last_name) VALUES ("TONY", "NGUYEN");
INSERT INTO addresses (user_id) VALUES (51);
INSERT INTO orders (user_id, item_id, quantity, created_at) VALUES (51, 1, 1, 2015-02-09);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment