SQL exercise 题目答案(1-13)

背景:

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.

数据库方案由四个表组成：
产品（制造商、型号、类型）
PC（代码、型号、速度、RAM、HD、CD、价格）
笔记本电脑（代码、型号、速度、RAM、HD、屏幕、价格）
打印机（代码、型号、颜色、类型、价格）
产品表包含制造商、型号和产品类型（“PC”、“笔记本电脑”或“打印机”）的数据。假设产品表中的型号对于所有制造商和产品类型都是唯一的。PC表中的每台个人计算机都由一个唯一的代码明确标识，并且还具有其型号（外键指产品表）、处理器速度（以MHz为单位）–速度字段、RAM容量（以MB为单位）–RAM、硬盘驱动器容量（以GB为单位）–HD、CD-ROM速度（例如，“4X”）–CD及其价格等特征。笔记本电脑桌与PC桌相似，除了CD-ROM速度之外，它还包含屏幕尺寸（英寸）-屏幕。对于打印机表中的每个打印机型号，指定其输出类型（“Y”表示颜色，“N”表示单色）——颜色字段、打印技术（“Laser”、“Jet”或“Matrix”）——类型和价格。(百度翻译)

1. Find the model number, speed and hard drive capacity for all the PCs with prices below $500.
Result set: model, speed, hd. 

SELECT model,speed, hd
FROM PC
WHERE price < 500

 

2. List all printer makers. Result set: maker.

SELECT DISTINCT maker FROM Product WHERE type = 'printer'

 

3. Find the model number, RAM and screen size of the laptops with prices over $1000.

SELECT model,ram,screen FROM Laptop WHERE price > 1000

 

4. Find all records from the Printer table containing data about color printers.

 SELECT * FROM Printer WHERE color = 'y'

 

5. Find the model number, speed and hard drive capacity of PCs cheaper than $600 having a 12x or a 24x CD drive. 

SELECT model,speed,hd FROM PC WHERE price < 600 AND (cd = '12x' OR cd = '24x')

 

6. For each maker producing laptops with a hard drive capacity of 10 Gb or higher, find the speed of such laptops. Result set: maker, speed. 从这题开始变难, 需要把几个表连起来看.

方法1: 不联结两个表

SELECT P.Maker,L.speed FROM Product AS P,Laptop AS L 
WHERE P.model = L.model
AND L.hd>=10
GROUP BY P.Maker,L.speed

 方法2: 联结两个表

SELECT P.Maker,L.speed FROM Product AS P left join Laptop AS L 
on P.model = L.model
where L.hd>=10
GROUP BY P.Maker,L.speed

我大小写一向非常随意,不喜勿喷哈.

 

7. Get the models and prices for all commercially available products (of any type) produced by maker B.找到厂商B生产的所有产品的model和price

SELECT PC.model, price
FROM PC INNER JOIN   
         Product  ON PC.model = Product.model
WHERE Product.maker = 'B'
UNION
SELECT Laptop.model, price 
FROM Laptop INNER JOIN   
         Product ON Laptop.model = Product.model
WHERE Product.maker = 'B'
union
SELECT Printer.model, price 
FROM Printer INNER JOIN  Product ON Printer.model = Product.model
WHERE Product.maker = 'B'

好理解但是麻烦:

SELECT model, price
FROM PC
WHERE model in (select p.model from product as P left join PC on p.model = PC.model where maker='B')
UNION
SELECT model, price
FROM Laptop
WHERE model in (select p.model from product as P left join Laptop as L on p.model = L.model where maker='B')
UNION
SELECT model, price
FROM Printer
WHERE model in (select p.model from product as P left join Printer as pr on p.model = pr.model where maker='B')

 

8. Find the makers producing PCs but not laptops. 生产PC但是不生产Laptop的厂商.

SELECT DISTINCT maker
FROM Product AS P
WHERE type = 'PC' AND
      NOT EXISTS (SELECT maker
                  FROM Product
                  WHERE type = 'laptop' AND
                  maker = P.maker
                 )

更好理解的:

SELECT distinct maker FROM product
where maker in (select maker from product where type = 'PC'
except
select maker from product where type = 'Laptop')

 

 9. Find the makers of PCs with a processor speed of 450 MHz or more. Result set: maker.

 外联结的时候,要注意这里找的是PC对应的maker,所以主表应该是PC.PC有而product可有可无.

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

 或者干脆用内联结,PC有且product也有.

Select distinct maker from product
inner join PC 
on product.model = PC.model
where PC.speed >=450

 对内外联结的理解使得可以做到一题多解.

 

10. Find the printer models having the highest price. Result set: model, price.  价格最高的打印机.输出型号和价格.

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

11. Find out the average speed of PCs.

Select avg(speed) from PC

 这个难度曲线真让人摸不着头脑.

 

12. Find out the average speed of the laptops priced over $1000. 

注意from后面跟的不能是select 的列,要是表,所以最后要加个字母a代表表a 

SELECT AVG(speed) FROM 
(select speed from laptop where price >1000)a

 

13. Find out the average speed of the PCs produced by maker A. 找到厂商A生产的PC的平均速度. 

同样,注意from后面跟的是表. 

Select avg(speed) from
(select speed from pc where model in (select model from product where maker = 'A'))b

15. Get hard drive capacities that are identical for two or more PCs. Result set: hd. 获取两台或多台PC相同的硬盘容量

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

16. Get pairs of PC models with identical speeds and the same RAM capacity. Each resulting pair should be displayed only once, i.e. (i, j) but not (j, i).
Result set: model with the bigger number, model with the smaller number, speed, and RAM.