南京理工大学《数据库系统基础教程》试题和答案 - 百度文库

4ʹSQLʵ㣬ʹظ(8) 5ʹSQLļ㣺(8)

1) 200112ǩжĶšǩڡ۽

۽СС

2) 200112ǩ۽ĶšԱ۽(ע

вѯͼ)

3) 200112ԱԱš۽(עʹӣ

ûжԱ۽ӦΪ0)

ԱΪS0025ԱIJžԱż

product(maker,model,type) pc(model,speed,ram,hd,cd,price) laptop(model,speed,ram,hd,screen,price) printer(model, color,type,price)

p187 4.1.1

1. What PC models have a speed of at least 150? model(speed>=150(PC))

2. Which manufacturers make laptops with a hard disk of at least one gigabyte? maker(hd>=1(Laptop) //maker(Laptop Product)

Product)

Product) - maker(hd<1(Laptop) 3. Find the model number and price of all products(of any type) made by manufacturer B. model,price(maker=B(PC model,price(maker=B(Laptop printer.model,price(maker=B(Printer Product))

Product))

printer.model=product.model Product))

29

4. Find the model numbers of all color laser printers. model(type=laser and color(Printer))

5. Find those manufacturers that sell Laptops, but not PCs. maker(Laptop Product) - maker(PC Product)

6. Find those hard-disk sizes that occur in two or more PCs. pc1.hd(pc1(PC) pc1.model<>pc.model and pc1.hd=pc.hd PC)

7. Find those pairs of PC models that have both the same speed and RAM. pc1.model,pc.model(pc1(PC) pc1.model

8. Find those manufacturers of at least two different computers (PCs or laptops) with speeds of at least 133.

P2:=P1:= maker,model(speed>=133(PC) maker,model(speed>=133(Laptop) Answer:=P1.maker(P1 Product)

Product)

P1.model<>P2.model and P1.maker=P2.maker P2)

9. Find the manufacturer(s) of the computer (PC or laptop) with the highest available speed. P2:=P1:= model,speed(PC)ȡmodel,speed(Laptop) Answer:=maker(Product (model(P1) - P1.model(P1 P1.speed

10. Find the manufacturers of PCs with at least three different speeds. P3:=P2:=P1:= maker,speed(Product Answer:=P1.maker((P1 PC)

P1.speed

P2.speed

11. Find the manufacturers who sell exactly three different models of PC. P4:=P3:=P2:=P1:= maker,model(Product PC)

30

M1(maker,m1,m2,m3):=P1.maker,P1.model,P2.model,P3.model((P1 P2.model and P1.maker=P2.maker P2)

P1.model<

P2.model

Answer:=maker(M1) - M1.maker(M1

P253 5.1.3

M1.m3

a) Find the model number, speed, and hard-disk size for all PCs whose price is under $1600. select model,speed,hd from pc

where price<1600;

b) Do the same as (a), but rename the speed column megahertz and the hd column gigabytes. select model,speed as megahertz, hd as gigabytes from pc where price<1600; c) Find the manufacturers of printers. select maker from product where type=printer;

d) Find the model number, memory size, and screen size for laptops costing more than $2000. select model,ram,screen from laptop where price>=2000;

e) Find all the tuples in the Printer relation for color printers. select * from printer where color;

f) Find the model number, speed, and hard-disk size for those PCs that have either a 6x or 8x CD and a

31

price less than $2000. select model,speed,hd from pc

where price<2000 and (cd=6x or cd=8x);

p262 5.2.2

a) Give the manufacturer and speed of laptops with a hard disk of at least one gigabyte? Select laptop.model,maker,speed From laptop, product

Where laptop.model=product.model and hd>1;

b) Find the model number and price of all products (of any type) made by manufacturer B. (Select product.model,price,pc as type1 From product,pc

Where maker=B and product.model=pc.model ) Union

(Select product.model,price,laptop From product,laptop

Where maker=B and product.model=laptop.model ) Union

(Select product.model,price,printer From product,printer

Where maker=B and product.model=printer.model ) c) Find those manufacturers that sell Laptops, but not PCs. Select maker From product,laptop

Where product.model=laptop.model and maker not in (

32

ϵͷ779662525#qq.com(#滻Ϊ@)