MySQL實訓-4

create database mianshi4;
use mianshi4;
create table  boys(
    boy_id int not null,
    boy varchar(10),
    toy_id int);

create table toys(
    toy_id int primary key not null,
    toy varchar(10));
 
create table drink(
    名稱 varchar(5),
    價格 decimal(8,2),
    碳水化合物 decimal(8,2),
    顔色 varchar(20),
    加冰 varchar(10),
    卡路裡 int);
    
insert into boys values
    (1,"Tony",3),
    (2,"Andy",2),   
    (3,"Frank",1), 
    (4,"Only",2), 
    (4,"Only",3), 
    (5,"Terrance",4), 
    (5,"Terrance",6);
    
insert into toys values
    (1,"ToyA"),
    (2,"ToyB"),
    (3,"ToyC"),
    (4,"ToyD"),
    (5,"ToyE");
    
    
insert into drink values
    ("A",1,8.4,"Yellow","N",33),
    ("B",2.5,3.2,"Blue","N",12),
    ("C",3.5,8.8,"Orange","Y",35),
    ("D",2.5,5.4,"Green","Y",24),
    ("E",5.5,42.5,"Purple","Y",171);


#請用left join寫出查詢語句，找出每個男孩買了哪個玩具，并寫出輸出結果集
select * from boys left join toys on boys.toy_id=toys.toy_id order by boy_id;

#找出既買過“ToyB”也買過”ToyC”的男孩
 select boy from boys where toy_id in
(select Toy_id  from  toys where toy in("ToyB","ToyC")) group by boy_id having count(boy_id)>1;      

#找出既買過“ToyB”也買過”ToyC”的男孩
 select boy from boys where toy_id in
(select Toy_id  from  toys where toy in("ToyB","ToyC")) group by boy_id having count(boy_id)>1;

#列出加冰，且顔色為yellow，且卡路裡大于30的飲料名稱和價格
select 名稱,價格 from drink where 顔色="yellow" and 加冰="Y" and 卡路裡>30;

#列出碳水化合物小于4，或者加冰的飲料名稱和顔色
select 名稱,顔色 from drink where 碳水化合物<4 or 加冰="Y";

#所有卡路裡小于100的飲料各一杯，需要多少錢
select sum(價格) from drink where 卡路裡<100;