class_time.sql

USE specialty_coffee_db;

SELECT c.name AS Name, origin_country AS Origin
FROM coffee c;

SELECT c.name AS cafe, COUNT(*) AS items
FROM menu m
         JOIN cafe c ON m.cafe_id = c.cafe_id
GROUP BY c.name;


SELECT *
FROM menu m;

SELECT *
FROM cafe c;

SELECT c.name, co.name, price, brew_method
FROM menu m
         JOIN cafe c ON m.cafe_id = c.cafe_id
         JOIN coffee co ON m.coffee_id = co.coffee_id;

SELECT c.name
FROM menu m
         JOIN cafe c ON m.cafe_id = c.cafe_id;

SELECT c.name AS Cafeteria, COUNT(*) AS 'Number of coffees in the menu'
FROM menu m
         JOIN cafe c ON m.cafe_id = c.cafe_id
GROUP BY c.name;

SELECT *
FROM coffee;

SELECT process, COUNT(*) AS 'how many coffee'
FROM coffee
GROUP BY process;


SELECT brew_method, AVG(price) AS avg_price
FROM menu
GROUP BY brew_method;

SELECT brew_method, COUNT(*) AS 'number of items', SUM(price) AS total_revenue, AVG(price) AS avg_price
FROM menu
GROUP BY brew_method;

SELECT *
FROM menu;


SELECT MAX(price) AS max_price, MIN(price) AS min_price
FROM menu;

SELECT process, COUNT(*) AS 'how many coffee'
FROM coffee
WHERE roast_level = 'Light'
GROUP BY process
HAVING COUNT(*) > 1;

SELECT brew_method, AVG(price) AS avg_price
FROM menu
GROUP BY brew_method
HAVING AVG(price) > 5
ORDER BY avg_price DESC;


SELECT *
FROM coffee
WHERE origin_country = 'Ethiopia';

SELECT *
FROM coffee
WHERE origin_country
          LIKE '%a';


SELECT *
FROM coffee
WHERE origin_country
          LIKE 'E_uador';


SELECT DISTINCT roast_level
FROM coffee;

# the name the origin the brewing type and the price of the top 3 most expensive coffee in the menu of all cafes

SELECT c.name, c.origin_country, m.brew_method, m.price
FROM menu m
         JOIN coffee c ON m.coffee_id = c.coffee_id
ORDER BY price DESC LIMIT 3;




SELECT  * FROM menu m JOIN cafe c  ON m.cafe_id = c.cafe_id;

SELECT  * FROM cafe c JOIN menu m  ON c.cafe_id = m.cafe_id;

SELECT  * FROM cafe c LEFT JOIN menu m  ON c.cafe_id = m.cafe_id;


SELECT * from roaster;

SELECT * from roaster WHERE country = 'UK' OR city = 'Barcelona';

SELECT * from roaster WHERE country = 'Spain' AND city = 'Barcelona';