数据库实验(3页).doc

《数据库实验(3页).doc》由会员分享，可在线阅读，更多相关《数据库实验(3页).doc（3页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、-数据库实验-第 3 页 数据库实验学号：200829010218姓名：黄芳一、建表S表的建立：CREATE TABLE S ( Sno Char(2) PRIMARY KEY，Sname Char(6) UNIQUE, Status Char(2)，City Char(4)P表的建立：CREATE TABLE P(Pno Char(2) PRIMARY KEY ，Pname Char(6)，COLOR C(2)， WEIGHT INT)J表的建立：CREATE TABLE J(Jno Char(2) PRIMARY KEY ，JNAME Char(8)， CITY Char(4)SPJ表的建

2、立：CREATE TABLE SPJ(Sno Char(2)，Pno Char(2)，JNO Char(2)， QTY INT)二、查询（1）SELECT DISTINCT SNO FROM SPJ WHERE JNO=J1（2）SELECT DISTINCT SNO FROM SPJ WHERE JNO=J1 AND PNO=P1（3）SELECT SNO FROM SPJ,P WHERE JNO=J1 AND SPJ.PNO=P.PNO AND COLOR=红（4）SELECT DIST JNO FROM SPJ WHERE JNO NOT IN (SELE JNO FROM SPJ,P,

3、SWHERE S.CITY=天津 AND COLOR=红 AND S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO)。（5）A、查询S1供应商供应的零件号SELECT DIST PNO FROM SPJ WHERE SNO=S1 结果是（P1，P2）B、查询哪一个工程既使用P1零件又使用P2零件。SELECT JNO FROM SPJ WHERE PNO=P1AND JNO IN (SELECT JNO FROM SPJ WHERE PNO=P2) 三、针对习题3中的四个表试用SQL语言完成以下各项操作： （1）SELECT SNAME,CITYFROM S （2）SELECT

4、PNAME,COLOR,WEIGHT FROM P（3）SELECT DISTINCT JNO FROM SPJ WHERE SNO=S1（4）SELECT PNAME,QTY FROM SPJ,P WHERE P.PNO=SPJ.PNO AND SPJ.JNO=J2（5）SELECT PNO FROM SPJ,S WHERE S.SNO=SPJ.SNO AND CITY=上海（6）SELECT JNAME FROM SPJ,S,J（7）SELECT DISTINCT JNO FROM SPJ,S WHERE S.SNO=SPJ.SNO AND S.CITY天津（8）UPDATE P SET COLOR=蓝 WHERE COLOR=红（9）UPDATE SPJ SET SNO=S3 WHERE SNO=S5 AND JNO=J4 AND PNO=P6（10）A、DELETE FROM S WHERE SNO=S2 B、DELETE FROM SPJ WHERE SNO=S2（11）INSERT INTO SPJ VALUES（S2，J6，P4，200）