Last active
March 28, 2016 04:44
-
-
Save brennanholtzclaw/313aed2d99439df62b90 to your computer and use it in GitHub Desktop.
[WIP] Intermediate SQL
This file contains 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
* What does SERIAL mean? | |
It will automatically populate an incrementing integer for the id. | |
* Write queries for the following: | |
What's the total revenue for all items? | |
SELECT SUM(revenue) FROM items; | |
What's the average revenue for all items? | |
SELECT AVG(revenue) FROM items; | |
What's the minimum revenue for all items? | |
SELECT MIN(revenue) FROM items; | |
What's the maximum revenue for all items? | |
SELECT MAX(revenue) FROM items; | |
What the count for items with a name? | |
SELECT COUNT(name) FROM items; | |
* Now, write a query that returns a count for all rows without counting the id column (It's not common, but it's not necessary for a table to have an id column). The result should be 5. | |
SELECT COUNT(*) FROM items; | |
* Write queries for the following: | |
Return all main courses. Hint: What ActiveRecord method would you use to get this? | |
SELECT * FROM items WHERE course='main'; | |
Return only the names of the main courses. | |
SELECT name FROM items WHERE course='main'; | |
Return the min and max value for the main courses. | |
SELECT (MIN/MAX)(revenue) FROM items WHERE course='main'; | |
What's the total revenue for all main courses? | |
SELECT SUM(revenue) FROM items WHERE course='main'; | |
* Can you get it to display only the name for the item and the name for the season? | |
SELECT items.name, seasons.name FROM items INNER JOIN seasons ON items.season_id = seasons.id; | |
* Having two columns with the same name is confusing. Can you customize each heading using AS? | |
SELECT items.name AS Item, seasons.name AS Season FROM items INNER JOIN seasons ON items.season_id = seasons.id | |
* Write a query that pulls all the category names for arugula salad. Hint: Use multiple INNER JOINs and a WHERE clause. | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment