数据库SQL练习

10.23练习

数据库模式由四个关系组成，这四个关系的模式是：
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) 找出所有打印机制造厂商；
2) 找出速度在1.2以上的PC的制造商；
3) 查询只卖笔记本电脑不卖PC的厂商；
4) 找出售卖产品既包括笔记本、台式机、打印机的厂商；
5) 找出售卖产品涵盖市面所有打印机的厂商；
6) 找出专售（只卖此款并且型号最全）彩色喷墨打印机的厂商；
7) 找出比任何一台PC机都慢的笔记本电脑；
8)  找出最低价格的彩色打印机的制造商；
9) 查询价格在6000以上的笔记本电脑的平均价格；
10) 找出至少生产3种不同型号的PC的制造商及其最高销售价格；
11) 向数据库中插入如下信息：制造商C生产的型号为1100的笔记本电脑，速度为2.2，RAM为16，硬盘大小2，显示器12.5,售价为8499；
12) 厂商A收购了厂商B，将所有B生产的产品改为由A生产。

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

mysql> select distinct 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查询出种类（彩色喷墨）齐全的厂家

select distinct maker,count(*)
from product
where model in(
	select model
    from printer
    where color = 'true' and type = 'inkjet'
) and model not in(
	select model
    from printer
    where color = 'false' or type != 'inkjet'
)
group by maker
having count(*) >=all(
		select count(*)
		from product,printer
		where product.model = printer.model and printer.color = 'true' and printer.type = 'inkjet'
		group by maker
)
 

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

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

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

select distinct maker
from Product
where model in(
	select model
	from Printer p1
	where color = 'true' and price<=all(
		select price
		from Printer p2
		where color = 'true'
	)

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

select avg(price)
from Laptop
where price>6000

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';

---

11.21课堂练习 —— 全外连接

建表语句

create table project(ptype char(10),pvalue int, projectid int);
insert into project values('budget',100,101);
insert into project values('expend',90,101);
insert into project values('budget',200,102);
insert into project values('expend',190,102);
insert into project values('budget',300,103);
insert into project values('expend',20,104);

 查询语句

select t1.projectid,t1.pvalue as budget,t2.pvalue as expend
from project t1
left join project t2 on  t1.projectid = t2.projectid and t2.ptype = 'expend' 
where t1.ptype = 'budget'
union
select t2.projectid,t1.pvalue as budget,t2.pvalue as expend
from project t1
right join project t2 on  t1.projectid = t2.projectid and  t1.ptype = 'budget' 
where t2.ptype = 'expend'

大佬提供的更为简洁的做法

select pj1.projectid,pj2.pvalue as budget,pj3.pvalue as expend 
from project pj1
left join project pj2 on pj1.projectid = pj2.projectid and pj2.ptype = 'budget'
left join project pj3 on pj1.projectid = pj3.projectid and pj3.ptype = 'expend'