database hw 1 practice

gistfile1.txt

1. 找出 drinker 名稱為 alex 的人以及他常去的酒吧  

SELECT drinker, bar FROM `frequents` WHERE drinker = 'alex'

2. 列出 每個 drinker 的名稱 及 有幾種喜歡喝的酒  

SELECT drinker, COUNT(*) FROM `likes` INNER JOIN `drinkers` ON likes.drinker = drinkers.name GROUP BY drinker

3. 列出 所有賣的酒的平均價錢 < 4 的 bar name 及平均價錢 

SELECT * FROM (SELECT bar, AVG(price) AS avg_price FROM `sells` GROUP BY bar) AS TMP WHERE TMP.avg_price < 4

4. 列出 drinker 的名稱為 alex 喜歡喝的酒名稱 及酒的製造商

SELECT drinker, name, manf FROM beers INNER JOIN (SELECT * FROM `likes` WHERE drinker = 'alex') AS TMP ON beers.name = TMP.beer

5. 列出 有賣 >= 3 人喜歡的酒的 bar name 及 酒的名稱  

SELECT sells.bar, sells.beer FROM sells INNER JOIN (SELECT * FROM (SELECT beer, COUNT(*) AS count_drinker FROM `likes` GROUP BY beer) AS STAT WHERE STAT.count_drinker >= 3) AS TOPHOT ON sells.beer = TOPHOT.beer

6. 列出 drinker 常去的 bar 裡面 有賣他喜歡的酒之 drinker 名稱、酒的名稱、bar 的名稱

SELECT * FROM (SELECT B.drinker, A.beer, A.bar FROM `sells` AS A JOIN `frequents` AS B ON A.bar = B.bar) AS C WHERE EXISTS (SELECT * FROM `likes` WHERE beer = C.beer AND drinker = C.drinker) 

7. 新加一個 table (須與其他 table 有 relation) 並 insert data

CREATE TABLE friends(name varchar(50), friend varchar(50));

INSERT INTO friends(name, friend) VALUES 
('john', 'alex'),
('john', 'sean'),
('john', 'tory'),
('babs', 'betty');

8. 下一個原 table 與新創的 table 相關的 query 並敘述之

列出 drinker 名為 john 在常去的 bar 中，可以見到哪些好友

SELECT friend FROM (SELECT friend, bar FROM `frequents` INNER JOIN (SELECT * FROM `friends` WHERE name = 'john') AS qFriend ON drinker = qFriend.friend) AS wList WHERE EXISTS (SELECT * FROM `frequents` WHERE drinker = 'john' AND bar = wList.bar)