1、3.用用SQL语句建立第二章习题语句建立第二章习题5中的四个表：中的四个表：供应商关系：S(SNO，SNAME，STATUS，CITY)零件关系：P(PNO，PNAME，COLOR，WEIGHT)工程项目关系：J(JNO,JNAME，CITY)供应情况关系：SPJ(SNO,PNO,JNO,QTY)1 定义的关系S有四个属性，分别是供应商号(SNO)、供应商名(SNAME)、状态(STATUS)和所在城市(CITY)，属性的类型都是字符型，长度分别是4、20、10和20个字符。主键是供应商编号SNO。在SQL中允许属性值为空值，当规定某一属性值不能为空值时，就要在定义该属性时写上保留字“NOTN
2、ULL”。本例中，规定供应商号和供应商名不能取空值。由于已规定供应商号为主码，所以对属性SNO的定义中的“NOTNULL”可以省略不写。 CREATETABLES(SNOCHAR(4)NOTNULL，SNAMECHAR(20)NOTNULL，STATUSCHAR(10)，CITYCHAR(20)，PRIMARYKEY（SNO）；）； 2CREATE TABLE P (PNO CHAR（4） NOT NULL， PNAME CHAR(20) NOT NULL， COLOR CHAR(8)， WEIGHT SMALLINT， PRIMARY KEY(PNO)；CREATE TABLE J (JNO
3、 CHAR(4) NOT NULL， JNAME CHAR(20)， CITY CHAR(20)， PRIMARY KEY(JNO)；CREATE TABLE SPJ (SNO CHAR(4) NOT NULL， PNO CHAR(4) NOT NULL， JNO CHAR(4) NOT NULL， QTY SMALLINT， PRIMARY KEY (SNO，PNO，JNO)， FOREIGN KEY (SNO) REFERENCES S(SNO), FOREIGN KEY (PNO) REFERENCES P(PNO), FOREIGN KEY (JNO) REFERENCES J(JNO
4、)；34.针对上题中建立的四个表试用SQL语言完成第二章习题5中的查询1)求供应工程J1零件的供应商号码SNO;2)求供应工程J1零件P1的供应商号码SNO;3)求供应工程J1零件为红色的供应商号SNO;4)求没有使用天津供应商生产的红色零件的工程号JNO;5)求至少用了供应商S1所供应的全部零件的工程号JNO41)求供应工程求供应工程J1零件的供应商号码零件的供应商号码SNO; SELECT DISTINCT SNO FROM SPJ WHERE JNO=SELECT DISTINCT SNO FROM SPJ WHERE JNO=J1J1; ; SELECT SELECT子句后面的子句后面
5、的DISTINCTDISTINCT表示要在结果中去掉重复的供应商编号表示要在结果中去掉重复的供应商编号SNOSNO。一个供应商可以为一个工程一个供应商可以为一个工程J1J1提供多种零件。提供多种零件。 2)求供应工程求供应工程J1零件零件P1的供应商号码的供应商号码SNO; SELECT SNO FROM SPJ SELECT SNO FROM SPJ WHERE JNO= WHERE JNO=J1J1 AND PNO= AND PNO=P1P1; ;3)求供应工程求供应工程J1零件为红色的供应商号零件为红色的供应商号SNO; SELECT DISTINCT SNO FROM SPJ SELE
6、CT DISTINCT SNO FROM SPJ WHERE JNO= WHERE JNO=J1J1 AND PNO IN AND PNO IN (SELECT PNO (SELECT PNO FROM P FROM P WHERE COLOR= WHERE COLOR=红红）; ;54)求没有使用天津供应商生产的红色零件的工程号求没有使用天津供应商生产的红色零件的工程号JNO; 常见错误：常见错误： SELECT JNO FROM J WHERE NOT EXISTS (SELECT * FROM S,SPJ,P WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO A
7、ND SPJ.PNO=P.PNO AND S.CITY=天津AND P.COLOR=红); 当从单个表中查询时当从单个表中查询时,目标目标列表达式用列表达式用*,若为多表必须用若为多表必须用表名表名.*正确写法SELECT JNO FROM JWHERE NOT EXISTS (SELECT S.*,SPJ.*,P.* FROM S,SPJ,P WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND S.CITY=天津 AND P.COLOR=红)64)求没有使用天津供应商生产的红色零件的工程求没有使用天津供应商生产的红色零件的工
8、程号号JNO; SELECT JNO FROM J WHERE JNO NOT IN (SELECT JNO FROM S,SPJ,P WHERE S.SNO=SPJ.SNO AND SPJ.PNO=P.PNO AND S.CITY=天津 AND P.COLOR=红); SELECT JNO FROM J WHERE NOT EXISTS (SELECT * FROM SPJ WHERE SPJ.JNO=J.JNO AND SPJ.SNO IN (SELECT SNO FROM S WHERE S.CITY=天津) AND SPJ.PNO IN (SELECT PNO FROM P WHERE
9、 P.COLOR=红)75)求至少用了供应商求至少用了供应商S1所供应的全部零件的工程号所供应的全部零件的工程号JNOSELECT DISTINCT JNOFROM SPJ SPJ1WHERE NOT EXISTS (SELECT * FROM SPJ SPJ2 WHERE SNO=S1 AND NOT EXISTS PNO= ALL (SELECT * FROM SPJ SPJ3 WHERE PNO=SPJ2.PNO AND JNO=SPJ1.JNO) )85)求至少用了供应商求至少用了供应商S1所供应的全部零件的工程号所供应的全部零件的工程号JNO 第一种理解：SELECTDISTINCT
10、JNOFROMSPJSPJXWHERENOTEXISTS(SELECT*FROMSPJSPJYWHERESPJY.SNO=S1ANDNOTEXISTS(SELECT*FROMSPJSPJZWHERESPJZ.JNO=SPJX.JNOANDSPJZ.PNO=SPJY.PNOANDSPJZ.SNO=SPJY.SNO);查询结果：第二种理解：SELECTDISTINCTJNOFROMSPJSPJXWHERENOTEXISTS(SELECT*FROMSPJSPJYWHERESPJY.SNO=S1ANDNOTEXISTS(SELECT*FROMSPJSPJZWHERESPJZ.JNO=SPJX.JNOA
11、NDSPJZ.PNO=SPJY.PNO); 查询结果：J4SPJZ.SNO=SPJZ.SNO=S1S195.针对习题针对习题3中的四个表试用中的四个表试用SQL语言完成以下各项操作语言完成以下各项操作1)找出所有供应商的姓名和所在城市2)找出所有零件的名称、颜色、重量3)找出使用供应商S1所供应零件的工程号码4)找出工程项目J2使用的各种零件的名称及其数量5)找出上海厂商供应的所有零件号码6)找出使用上海产的零件的工程名称7)找出没有使用天津产的零件的工程号码8)把全部红色零件的颜色改成蓝色9)有S5供给J4的零件P6改为由S3供应，请作必要的修改10)从供应商关系中删除S2的记录，并从供应情
12、况关系中删除相应的记录11)请将(S2,J6,P4,200)插入供应情况关系101)找出所有供应商的姓名和所在城市找出所有供应商的姓名和所在城市SELECTSNAME,CITYFROMS;2)找出所有零件的名称、颜色、重量找出所有零件的名称、颜色、重量SELECTPNAME,COLOR,WEIGHTFROMP;3)找出使用供应商找出使用供应商S1所供应零件的工程号码所供应零件的工程号码SELECTDISTINCTJNOFROMSPJWHERESNO=S1;114)找出工程项目找出工程项目J2使用的各种零件的名称及其数量使用的各种零件的名称及其数量SELECTPNAME,QTYFROMP,SPJ
13、WHEREP.PNO=SPJ.PNOANDSPJ.JNO=J2;125)找出上海厂商供应的所有零件号码找出上海厂商供应的所有零件号码SELECTDISTINCTPNOFROMS,SPJWHERES.SNO=SPJ.SNOANDS.CITY=上海;SELECTDISTINCTPNOFROMSPJWHERESNOIN(SELECTSNOFROMSWHERES.CITY=上海);6)找出使用上海产的零件的工程名称SELECTJNAMEFROMS,SPJ,JWHERES.SNO=SPJ.SNOANDJ.JNO=SPJ.JNOANDS.CITY=上海;137)找出没有使用天津产的零件的工程号码找出没有使
14、用天津产的零件的工程号码SELECTJNOFROMJWHEREJNONOTIN(SELECTJNOFROMSPJ,SWHERES.SNO=SPJ.SNOANDS.CITY=天津);SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMSPJWHEREJNO=J.JNOANDSNOIN(SELECTSNOFROMSWHERES.CITY=天津);SELECTJNOFROMJWHERENOTEXISTS(SELECTSPJ.*,S.*FROMSPJ,SWHEREJNO=J.JNOANDSNO=S.SNOANDS.CITY=天津;148)把全部红色零件的颜色改成蓝色把全部红
15、色零件的颜色改成蓝色UPDATEPSETCOLOR=蓝WHERECOLOR=红;9)由由S5供给供给J4的零件的零件P6改为由改为由S3供应，请作必要的修改供应，请作必要的修改UPDATESPJSETSNO=S3WHERESNO=S5ANDJNO=J4ANDPNO=P610)从供应商关系中删除从供应商关系中删除S2的记录，并从供应情况关系中删除的记录，并从供应情况关系中删除相应的记录相应的记录DELETEFROMSWHERESNO=S2;DELETEFROMSPJWHERESNO=S211)请将请将(S2,J6,P4,200)插入供应情况关系插入供应情况关系INSERTINTOSPJVALUE
16、S(S2,P4,J6,200)常见错误：INSERTINTOSPJVALUES(S2,J6,P4,200)15 11.请为三建工程项目建立一个供应情况的视图SANJIAN_SPJ，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。针对该视图完成下列查询：1)找出三建工程项目使用的各种零件代码及其数量。2)找出供应商S1的供应情况。16创建视图：CREATEVIEWSANJIAN_SPJASSELECTSNO,PNO,QTYFROMSPJ,JWHERESPJ.JNO=J.JNOANDJ.JNAME=三建;1)找出三建工程项目使用的各种零件代码及其数量。SELECTPNO,SUM(QTY)SELECTPNO,QTYFROMSANJIAN_SPJFROMSANJIAN_SPJ;GROUPBYPNO;2)找出供应商S1的供应情况。SELECT*FROMSANJIAN_SPJWHERESNO=S117数据库设计方法数据库设计方法1）基本设计法基本设计法分五步进行：分五步进行：a.创建用户视图创建用户视图b.汇总用户视图，得出全局数据视图，即概念模型。汇总用户视图，得出全局数据视图
