Product数据库
基与产品的数据库模式，完成下列查询：
Product(maker,model,type);
PC(model,speed,ram,hd,price);
Laptop(model,speed,ram,hd,screen,price);
Printer(model,color,type,price);
要求每题的答案应当至少使用一个子查询，并且要求使用两种不同的方法写出每个查询（例如使用不同的运算符：exists,in,all,any等）。
建表
创建、使用数据库
create database product;
use product;
建表
create table product
(maker char(20),
model char(10),
type char(20)
);
插入数据
insert into product
values('A','01','PC'),
('A','01','Laptop'),
('B','02','Laptop'),
('B','02','Printer'),
('B','03','Printer'),
('B','04','PC'),
('C','01','PC'),
('C','01','Laptop'),
('C','01','Printer'),
('C','02','Printer'),
('C','03','Laptop'),
('C','04','PC'),
('C','04','Printer');
建表
create table PC
(model char(10),
speed double,
ram int,
hd int,
price double
);
插入数据
insert into PC
values('01',2.1,512,80,3000),
('01',2.1,1024,160,3500),
('01',2.2,1024,160,3600),
('01',3.2,2048,200,3700),
('02',2.2,512,200,3800),
('02',3.3,2048,320,5000),
('02',3.2,2048,330,6000),
('03',2.3,1024,250,4800),
('03',3.2,1024,250,5100),
('04',2.2,1024,250,3900),
('04',3.3,1024,80,3800),
('04',3.3,1024,250,5500);
建表
create table Laptop
(model char(10),
speed double,
ram int,
hd int,
price double
);
插入
insert into Laptop
values('01',2.1,512,80,2000),
('01',2.1,1024,160,2500),
('01',2.2,1024,160,2600),
('01',3.2,2048,200,2700),
('02',2.2,512,200,2800),
('02',3.3,2048,320,4000),
('02',3.2,2048,330,5000),
('03',2.3,1024,250,3800),
('03',3.2,1024,250,4100),
('04',2.2,1024,250,2900),
('04',3.3,1024,80,2800),
('04',3.3,1024,250,4500);
建表
create table Printer
(model char(10),
color boolean,
type char(10),
price double
);
插入数据
insert into printer
values('01',true,'激光',1000),
('02',false,'激光',1000),
('03',true,'喷墨',3000),
('04',false,'喷墨',2000);
1）找出速度在3.0以下的PC制造商。
①
select distinct(maker)
from product
where model in
(select model
from PC
where speed<3.0);
②
select distinct(maker)
from product
where exists
(select *
from PC,product
where PC.model=product.model and speed<3.0);
2）找出价格最高的打印机的相关信息。
①
select * from Printer
where price>=all
(select price from Printer);
②
select * from Printer
where price=
(select max(price) from Printer);
3）找出速度比任何一台PC都慢的笔记本电脑。
①
select * from Laptop
where speed <all
(select speed from PC);
②
select * from Laptop
where speed <
(select min(speed) from PC);
4）找出价格最高的产品（PC，笔记本电脑或者打印机）的型号。
union的介绍https://www.runoob.com/sql/sql-union.html
①
select model from
(select model,price from pc union
select model,price from printer union
select model,price from laptop) a
where a.price in(select max(price) from
(select price from pc union
select price from printer union
select price from laptop)b);
②
select model from
(select model,price from pc union
select model,price from laptop union
select model,price from printer)b
where b.price>=all(
select price from pc union
select price from laptop union
select price from printer);
5）找出价格最低的彩色打印机的制造商。
①
select distinct(maker) from Product,Printer
where Printer.price<=all
(select price from printer
where color=1 )and Product.model=Printer.model;
②
select distinct(maker) from Product,Printer
where Printer.price=
(select min(price) from printer
where color=1 )and Product.model=Printer.model;
6）找出RAM容量最小而PC中速度最快者的制造商。
①
select distinct(maker) from product,pc
where pc.model=product.model and
ram =(select min(ram) from pc) and
speed =(select max(speed) from pc where ram =(select min(ram) from pc));
碎碎念
我个人水平一般，也没学视图什么的，感觉4、6题还是蛮难的，网上的题型很多，这两题的答案也是借鉴了不少大佬的解决办法。