据库习题答案.ppt

数据库管理系统 二、设有一个SPJ数据库，包括S、P、J、SPJ四个关系模式 S(SNO，SNAME，STATUS，CITY)： P(PNO，PNAME，COLOR，WEIGHT)： J(JNO，JNAME，CITY)； SPJ(SNO，PNO，JNO，QTY)； 供应商表S由供应商代码(SNO)、供应商姓名(SNAME)、供应商所在城市(CITY)组成； 零件表P由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量（WEIGHT）组成： 工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项J所在城，(CITY)组成： 供应情况表SPJ供应南代码(SNO)、零件代码(PNO)、工程顶目代码（JNO），供应数量(QTY)组成，表示某供应商供应某种零件给某工程项目的数量为QTY。 1、用SQL语句建立这四个表。 CREATE TABLE S (sno CHAR(4) NOT NULL UNIQUE, sname CHAR(20) , status INT, city CHAR(10)); CREATE TABLE p (pno CHAR(4) NOT NULL UNIQUE, pname CHAR(20) , color CHAR(4), weight INT); CREATE TABLE j (jno CHAR(4) NOT NULL UNIQUE, jname CHAR(20), city CHAR(20)); CREATE TABLE spj (sno CHAR(4) NOT NULL, pno CHAR(4) NOT NULL, jno CHAR(4) NOT NULL, qty INT); CREATE CLUSTER INDEX SPJ_INDEX ON SPJ(sno,pno,jno)； 2、针对以上四个表用SQL语句完成以下操作 找出所有供应商的姓名和所在城市。 SELECT sNAME,city FROM s; 找出所有零件的名称、颜色、重量。 SELECT pname,color,weight FROM p; 找出使用供应商S1所供应零件的工程号码。 SELETE DISTINCT jno FROM spj WHERE sno=‘S1’; 找出工程项目J2使用的各种零件的名称及其数量qty SELECT p.pname,Sum(qty）Totqty FROM p,spj WHERE p.pno=spj.pno AND spj.jno=‘J2’ Group BY PNAME; 找出上海厂商供应的所有零件号码。 SELECT pno FROM spj WHERE sno IN (SELECT sno FROM s WHERE city=‘上海’); 找出使用上海产的零件的工程名称。 SELECT DISTINCT(jname) FROM j WHERE jno IN (SELECT jno FROM spj WHERE sno IN (SELECT sno FROM s WHERE city=”上海”)); 找出没有使用天津产的零件的工程号码。 把全部红色零件的颜色改成蓝色。 UPDATE p SET color=‘蓝’ WHERE color =‘红’; 由S5供给J4的零件P6改为由S1供应，请做必要的修改 UPDATE spj SET sno=”S1” WHERE sno=”S5” AND jno=”J4” AND pno=”P6”; 从供应商关系中删除s2的记录，并从供应情况关系中删除相应的记录。 DELETE FROM s WHERE sno=”S2”; DELETE FROM spj WHERE sno=”s2”; 请将(S2，J6，P4，200)插入供应情况关系。 INSERT INTO spj VALUES(“S2”，”J6”，”P4”，200); 统计各个供应商供应的零件总数。 SELECT SUM (qty) FROM spj GROUP BY sno; 查询总供应量在600以上的供应商的姓名、供应商编号、总供应数量。 SELECT s.sname,spj.sno,SUM(spj.qty) FROM spj,s WHERE s.sno=spj.sno GROUP BY sno HAVING SUM(spj.qty)60