sql-ex网站的练习答案01

网址为：https://www.sql-ex.ru/ 做题需要注册

背景如下：
Short database description “Computer firm”:
The database scheme consists of four tables:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)
The Product table contains data on the maker, model number, and type of product (‘PC’, ‘Laptop’, or ‘Printer’). It is assumed that model numbers in the Product table are unique for all makers and product types. Each personal computer in the PC table is unambiguously identified by a unique code, and is additionally characterized by its model (foreign key referring to the Product table), processor speed (in MHz) – speed field, RAM capacity (in Mb) - ram, hard disk drive capacity (in Gb) – hd, CD-ROM speed (e.g, ‘4x’) - cd, and its price. The Laptop table is similar to the PC table, except that instead of the CD-ROM speed, it contains the screen size (in inches) – screen. For each printer model in the Printer table, its output type (‘y’ for color and ‘n’ for monochrome) – color field, printing technology (‘Laser’, ‘Jet’, or ‘Matrix’) – type, and price are specified.

数据如下
product
maker model type
A 1232 PC
A 1233 PC
A 1276 Printer
A 1298 Laptop
A 1401 Printer
A 1408 Printer
A 1752 Laptop
B 1121 PC
B 1750 Laptop
C 1321 Laptop
D 1288 Printer
D 1433 Printer
E 1260 PC
E 1434 Printer
E 2112 PC
E 2113 PC

PC
code model speed ram hd cd price
1 1232 500 64 5.0 12x 600.0000
10 1260 500 32 10.0 12x 350.0000
11 1233 900 128 40.0 40x 980.0000
12 1233 800 128 20.0 50x 970.0000
2 1121 750 128 14.0 40x 850.0000
3 1233 500 64 5.0 12x 600.0000
4 1121 600 128 14.0 40x 850.0000
5 1121 600 128 8.0 40x 850.0000
6 1233 750 128 20.0 50x 950.0000
7 1232 500 32 10.0 12x 400.0000
8 1232 450 64 8.0 24x 350.0000
9 1232 450 32 10.0 24x 350.0000

Laptop
code model speed ram hd price screen
1 1298 350 32 4.0 700.0000 11
2 1321 500 64 8.0 970.0000 12
3 1750 750 128 12.0 1200.0000 14
4 1298 600 64 10.0 1050.0000 15
5 1752 750 128 10.0 1150.0000 14
6 1298 450 64 10.0 950.0000 12

Printer
code model color type price
1 1276 n Laser 400.0000
2 1433 y Jet 270.0000
3 1434 y Jet 290.0000
4 1401 n Matrix 150.0000
5 1408 n Matrix 270.0000
6 1288 n Laser 400.0000

第七题：Exercise: 7 (Serge I: 2002-11-02)
Get the models and prices for all commercially available products (of any type) produced by maker B.

select Product.model,price from Product join PC on Product.model=PC.model where Product.maker='B' 
union select Product.model,price from Product join Laptop on Product.model=Laptop.model where Product.maker='B'
union select Product.model,price from Product join Printer on Product.model=Printer.model where Product.maker='B'

第八题：Exercise: 8 (Serge I: 2003-02-03)
Find the makers producing PCs but not laptops.
解法一：

select distinct maker from Product where type='PC' and maker not in (select maker from Product where type='Laptop')

解法二：

select distinct maker from Product where type='PC' except select distinct maker from Product where type='Laptop'

第九题：Exercise: 9 (Serge I: 2002-11-02)
Find the makers of PCs with a processor speed of 450 MHz or more. Result set: maker.

select distinct maker from Product join PC on Product.model=PC.model where speed>=450

第十题：Exercise: 10 (Serge I: 2002-09-23)
Find the printer models having the highest price. Result set: model, price.

这样写不行，不知道为啥，有知道的大佬麻烦告知一下

select model,price from Printer where price >= all(select price from Printer)

这样写行

select model,price from Printer where price >= all(select max(price) from Printer)

第十一题：Exercise: 11 (Serge I: 2002-11-02)
Find out the average speed of PCs.

select avg(speed) from PC

第十二题：Exercise: 12 (Serge I: 2002-11-02)
Find out the average speed of the laptops priced over $1000.

select avg(speed) from Laptop where price>1000

第十三题：Exercise: 13 (Serge I: 2002-11-02)
Find out the average speed of the PCs produced by maker A.

select avg(speed) from Product join PC on Product.model=PC.model where maker='A'

第十五题：Exercise: 15 (Serge I: 2003-02-03)
Get hard drive capacities that are identical for two or more PCs.
Result set: hd.

写的真垃圾

select distinct s.hd from PC as b,PC as s where b.hd=s.hd and(b.model!=s.model or b.speed!=s.speed or b.ram!=s.ram or b.cd!=s.cd or b.price!=s.price)

select distinct s.hd from PC as b,PC as s where b.hd=s.hd and b.code!=s.code

Select hd from pc 
group by hd
having count(model) >=2

第十七题：Exercise: 17 (Serge I: 2003-02-03)
Get the laptop models that have a speed smaller than the speed of any PC.
Result set: type, model, speed.

SELECT DISTINCT p.type, l.model, l.speed
FROM Product p right join Laptop l on p.model=l.model
WHERE l.speed < (SELECT MIN (speed) 
                 FROM PC
                 ) AND 
      p.type = 'laptop'

第十八题：Exercise: 18 (Serge I: 2003-02-03)
Find the makers of the cheapest color printers.
Result set: maker, price.

select maker,price from Product join Printer on Product.model=Printer.model where Printer.color='y' and price=(select min(price) from Printer where color='y')