数据库SQL练习

数据库模式由四个关系组成，这四个关系的模式是：
Product(maker, model, type)
PC(model, speed, ram, hd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)
其中关系Product给出了各种产品的制造厂商maker，型号model，类型type（台式机，笔记本电脑或者打印机），这里简单假设所有产品的型号都惟一，而不管它是由哪个制造商生产的；

关系PC对于不同型号给出了如下属性——处理器速度speed（单位GHz）、RAM容量ram（单位GB）、硬盘容量hd（单位TB）；
关系Laptop（笔记本电脑）除了显示器（screen）尺寸外，其他各属性与PC一致，

关系Printer对于每种型号，有如下属性：是否彩色color（如果是，值为true）、处理类型type(激光、喷墨)、价格price。
用SQL完成完成下列操作

1) 找出所有打印机制造厂商；

mysql> select maker
    -> from Product
    -> where type = 'printer';

2) 找出速度在1.2以上的PC的制造商；

mysql> select distinct maker
    -> from Product
    -> join PC on PC.model = Product.model
    -> where PC.speed > 1.2;

3) 查询只卖笔记本电脑不卖PC的厂商；

mysql> select distinct maker
    -> from Product P
    -> join Laptop L on P.model = L.model
    -> where maker not in (
    ->    select maker
    ->    from Product
    ->    join PC on Product.model = PC.model
    ->  );

4) 找出售卖产品既包括笔记本、台式机、打印机的厂商；

mysql> select distinct maker
    -> from Product
    -> where
    -> maker in (
    ->   select maker
    ->   from PC,Product
    ->   where PC.model = Product.model
    -> ) and
    -> maker in (
    ->   select maker
    ->   from Laptop,Product
    ->   where Laptop.model = Product.model
    -> ) and
    -> maker in (
    ->   select maker
    ->   from Printer,Product
    ->   where Printer.model = Product.model
    -> ) ;

5) 找出售卖产品涵盖市面所有打印机的厂商；

mysql> select distinct maker
    -> from Product
    -> join Printer on Product.model = Printer.model
    -> group by maker
    -> having count(Product.model) = (
    ->     select count(model)
    ->     from Printer
    -> );

6) 找出专售（只卖此款并且型号最全）彩色喷墨打印机的厂商；

1.找出不生产其他（非彩色喷墨）打印机的厂家
2.厂家group by分组，通过having查询出种类（彩色喷墨）齐全的厂家

mysql> select distinct maker
    -> from (
    ->     select distinct maker,Product.model as model
    ->     from Product
    ->     join Printer on Product.model = Printer.model
    ->     where Printer.color = 'true' and Printer.type = 'inkjet'
    -> ) T1
    -> where maker not in(
    ->     select distinct maker
    ->     from Product
    ->     join Printer on Product.model = Printer.model
    ->     where Printer.color != 'true' or Printer.type != 'inkjet'
    -> )
    -> group by maker
    -> having count(model) = (
    ->     select count(model)
    ->     from printer
    ->     where color = 'true' and type = 'inkjet'
    -> );

7) 找出比任何一台PC机都慢的笔记本电脑；

mysql> select distinct *
    -> from Laptop
    -> where Laptop.speed < all(
    ->     select speed
    ->     from PC
    -> );

8)  找出最低价格的彩色打印机的制造商；

mysql> select distinct maker
    -> from product
    -> where model in(
    ->     select model
    ->     from printer
    ->     where price=(
    ->         select min(price)
    ->         from printer
    ->         where color='true' and type='inkjet'
    ->     )
    -> );

9) 查询价格在6000以上的笔记本电脑的平均价格；

需要利用子查询，直接用AVG(price)得出的是分组后，每组的平均值

mysql> select AVG(price)
    -> from(
    ->     select price
    ->     from Laptop
    ->     group by price
    ->     having price > 6000
    -> ) T;

10) 找出至少生产3种不同型号的PC的制造商及其最高销售价格；

mysql> select maker,max(price)
    -> from Product
    -> join PC on PC.model = Product.model
    -> group by maker
    -> having count(*)>=3;

11) 向数据库中插入如下信息：制造商C生产的型号为1100的笔记本电脑，速度为2.2，RAM为16，硬盘大小2，显示器12.5,售价为8499；

insert into Product values('C','1100','laptop');
insert into Laptop values('1100',2.2,16,2,12.5,8499);
 

12) 厂商A收购了厂商B，将所有B生产的产品改为由A生产。

update Product
set maker = 'B'
where maker = 'A';