数据库1-4章习题.pptVIP

数据库习题课 第一章~第四章 第一章（15题） 第一章（16题） 第二章（10题） (1) 第二章（11题） 第三章（第4题） (1) Select SNO from SPJ where JNO=‘J1’; (2) Select SNO from SPJ where JNO=‘J1’ and PNO=‘P1’ (3) Select SNO from SPJ,P where SPJ.PNO=P.PNO and Color =‘红’ Select JNO from J where Jno not in (Select JNO from SPJ ,P,S where SPJ.PNO=P.PNO and SPJ.SNO=S.SNO and city=‘Tianjin’and Color=‘红‘) 第三章（第4题） (5) Select JNO from SPJ SPJ1 where not exists ( Select * from SPJ SPJ2 where sno=‘s1’ and not exists ( Select * from SPJ SPJ3 where SPJ3.JNO=SPJ1.JNO AND SPJ3.PNO=SPJ2.PNO) 第三章（第6题） (1) Select * from Customers where city=‘shanghai’; (2) Select Gname,Stocks,Price,Price*0.75 Discount from Goods order by Price (3) Select GoodId,Gname,Price from Goods where Gname like ‘%computer%’ (4) Select Stocks,Gname,Price from Goods where Stocks between 100 and 500 第三章（第6题） (5) Select Cname,Gname,Price,Quantity,Ordersum from Customers,Goods,Orders where Customers. CustomeID=Orders. CustomeID and Goods. GoodID=Orders. GoodID and Date between ‘2007-7-1’ and ‘2007-12-31’ and Ordersum30000 (6) Select CustomeID, GoodID, OrderID from Orders where Quantity200 Group by Date (7) Select * from Orders where CustomeID in (Select CustomeID from Customers where City=‘beijing’ ) 第三章（第6题） (8) Select * from Orders where CustomeID not in (Select CustomeID from Customers where City=‘tianjing’ ) Select Customers. CustomeID,Cname,City,OrderID,GoodID, Quantity,Ordersum,Date from Orders ,Customers