数据库原理与应用试题及答案

RE BID=@bid --(2 分) SELECT @bs=QTY FROM BOOK WHERE BID=@bid --(2 分) PRINT ‘库存数量’+STR(@s) END ELSE INSERT INTO BOOK ( BID , QTY ) VALUES ( @bid , @s ) --(2 分) PRINT ‘新书入库’ GO 4. (1) SELECT COLOR FROM P WHERE PNO= ‘ P1’(2 分) (2) SELECT PNO , QTY FROM SPJ WHERE JNO= ‘ J1’ AND SNO= ‘ S1’ ORDER BY PNO DESC (2 分) (3) SELECT JNO FROM P JOIN SPJ ON ( P.PNO=SPJ.PNO ) WHERE COLOR= ‘红’ AND SNO= ‘ S1’(2 分) (4) SELECT J.PNO FROM J JOIN SPJ ON ( J.PNO=SPJ.PNO ) JOIN S ON ( S.SNO=SPJ.SNO ) WHERE J.CITY=S.CITY (2 分) (5) SELECT COUNT ( JNO ) FROM J WHERE CITY= ‘杭州’(2 分) (6) SELECT SNO , SUM ( QTY ) FROM SPJ GROUP BY SNO (2 分) (7) SELECT JNO FROM SPJ GROUP BY JNO HAVING SUM ( QTY )>( SELECT SUM ( QTY ) FROM SPJ WHERE JNO= ‘ J1’)(2 分) (8) DELETE FROM J WHERE JNO IN( SELECT JNO FROM SPJ WHERE SNO= ‘ S1’)(2 分)