brianrip/intermediate_SQL.md

## intermediate_SQL.md

      
    Raw
  

              intermediate_SQL.md
            
          
    Total revenue for all items?


SELECT SUM(revenue) FROM items;

Average revenue for all items?


SELECT AVG(revenue) FROM items;

####Minimum revenue for all items?

SELECT MIN(revenue) FROM items;

Maximum revenue for all items?


SELECT MAX(revenue) FROM items;

Count for items with a name?


SELECT COUNT(name) FROM items;

Count all rows from the items table?


SELECT COUNT(*) FROM items;

Return all main courses?


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 MAX(revenue), MIN(revenue) FROM items WHERE course='main';

What's the total revenue for all main courses?


SELECT SUM(revenue) FROM items WHERE course='main';

Inner Joins


SELECT items.name AS item_name, seasons.name AS season_name FROM items INNER JOIN seasons ON items.season_id = seasons.id;


SELECT items.name AS item_name, categories.name AS category_name FROM items INNER JOIN items_categories on items.id = items_categories.item_id INNER JOIN categories on items_categories.category_id= categories.id WHERE items.name = 'arugula salad';


Outer Joins


SELECT * FROM items WHERE revenue < (SELECT AVG(revenue) FROM items);


SELECT SUM(i.revenue) FROM items i INNER JOIN items_categories ic ON i.id = ic.item_id INNER JOIN categories c ON ic.category_id = c.id WHERE c.name='dinner';


SELECT c.name, SUM(i.revenue) FROM items i INNER JOIN items_categories ic ON i.id = ic.item_id INNER JOIN categories c ON ic.category_id = c.id GROUP BY c.name;


What is an INNER JOIN?


Returns a table from two tables where the rows have matching values

What is a LEFT OUTER JOIN?


Returns a table with all rows from the table on the left hand side of the query, and the matching values on the right table

What is a RIGHT OUTER JOIN?


Returns a table with all rows from the table on the right hand side of the query, and the matching values on the left table