[WIP] Intermediate SQL

gistfile1.txt

* 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.