sql作业答案

1．用SQL语句完成下列操作。

（1）求在2002年8月份，已售货物的销售总数。

SELECT 货物，SUM(数量) FROM 订购;

WHERE 订购日期 BETWEEN {^2002-08-01}AND{^2002-08-31};

GROUP BY 货物

。

SELECT 客户ID，姓名，城市 FROM 客户;

WHERE 客户ID IN ( SELECT 客户ID FROM 订购;

WHERE 订购日期 BETWEEN {^2001-01-01}AND{^2001-12-31}) （3）求每个货物ID及购买该货物数量最多的客户的ID。

SELECT 货物ID, 客户ID FROM 订购 DG;

WHERE 数量=(SELECT MAX(数量) FROM 订购;

WHERE 货物ID=DG.货物ID)

（4）求购买了货物的客户信息。

SELECT * FROM 客户 WHERE EXIST;

(SELECT * FROM 订购 WHERE 订购.客户ID=客户.客户ID) （5）在货物表中增加售出量字段（N/3/0），并汇总货物的售出总数。

ALTER TABLE 货物 ADD 售出量 N(3)

UPDATE 货物 SET 售出量=(SELECT SUM(数量) FROM 订购;

WHERE 订购.货物ID=货物.货物ID)

2．用SQL语句完成下列查询工作。

S（供应商），数据项有SNO，SNAME，STATUS，CITY

P（零件），数据项有PNO，PNAME，WEIGHT，COLOR

SP（零件供应记录），数据项有SNO，PNO，QTY，表示某供应商供应拥有某零件的数量为QTY

(1)找出所有S打头的供应商提供的零件清单（须含零件名称、数量）。

SELECT P.PNAME,SP.QTY FROM S,P,SP;

WHERE P.PNO=SP.PNO .AND. S.SNO=SP.SNO;

.AND. S.SNAME LIKE ′S%′

(2)列出所有供应商提供的零件种类数（须含供应商名、零件种类数量）。

SELECT S.SNAME,COUNT(SP.PNO) FROM S,SP;

WHERE S.SNO=SP.SNO GROUP BY SNO

(3)求北京的供应商的数目。

SELECT COUNT(S.SNO) FROM S WHERE S.CITY=′北京′

(4)求不供应P2商品的供应商名字。

SELECT S.SNAME FROM S,SP;

WHERE S.SNO=SP.SNO .AND. .NOT. EXISTS;

(SELECT * FROM SP WHERE PNO=′P2′ .AND. S.SNO=SP.SNO)

3．参照上题提供的表和数据，使用SQL语句完成下列任务。

(1)把所有供应零件重量超过17（含17）的供应量增加10%。

UPDATE SP SET QTY=QTY*1.1;

WHERE PNO IN (SELECT PNO FROM P,SP;

WHERE SP.PNO=P.PNO .AND. P.WEIGHT>=17)

(2)将零件供应记录中没有的零件从零件表中删除。

DELETE FROM P WHERE PNO .NOT. IN;

(SELECT DISTINCE PNO FROM SP)

4．用SQL语句完成下列操作。

S（供应商），数据项有SNO，SNAME，STATUS，CITY

P（零件），数据项有PNO，PNAME，WEIGHT，COLOR

J（工程项目），数据项有JNO，JNAME，CITY

SPJ（零件供应表）数据项有SNO，PNO，JNO，QTY。表示某供应商供应某零件给某工程

(1)求所有供应商的所在城市名，要求没有重复信息。

SELECT DISTINCT CITY FROM S

(2)按照零件名的升序和价格的降序（在零件名相同时）显示零件的信息

SELECT PNO,PNAME,COLOR,WEIGHT,PRICE FROM P;

ORDER BY PNAME, PRICE DESC

(3)求供应工程J1零件的单位号码SNO

SELECT SNO FROM SPJ WHERE JNO=′J1′

(4)求供应工程J1零件P1的供应单位号码

SELECT SNO FROM SPJ WHERE JNO=′J1′ .AND. PNO=′P1′

(5)求供应工程J1红色零件的单位号码

SELECT SNO FROM SPJ WHERE JNO= ′J1′ .AND. PNO IN;

(SELECT PNO FROM P WHERE COLOR=′红′)

(6)求没有使用天津单位生产的零件的工程号JNO

SELECT JNO FROM SPJ WHERE NOT EXISTS;

(SELECT * FROM S WHERE S.SNO=SPJ.SNO .AND. CITY=′天津′)

(7)把全部灰色零件的颜色改为黑色

UPDATE P SET COLOR=′黑′ WHERE COLOR=′灰′

(8)由S1供应给J1的零件P1今改为由S2供给，作必要的修改

UPDATE SPJ SET SNO= ′S2′;

WHERE SNO= ′S1′ .AND. JNO= ′J1′ .AND. PNO= ′P1′

(9)求在北京的所有供给商和工程的名字

SELECT SNAME FROM S WHERE CITY=′北京′ UNION;

SELECT JNAME FROM J WHERE CITY=′北京′

(10)删除全部使用蓝色零件的工程供应记录

DELETE FROM SPJ WHERE PNO IN;

(SELECT PNO FROM P WHERE COLOR=′蓝′)

5．用SQL语句完成下列操作（数据表基于例题）。

(1)求选修C1课程的学生学号和得分，结果按分数降序排列。

SELECT SNO,G FROM SC WHERE CNO= ′C1′ ORDER BY G DESC

(2)求年龄在20岁与22岁之间（包括20岁和22岁）的学生学号和年龄。

SELECT SNO,SA FROM S WHERE SAGE BETWEEN 20 AND 22

(3)求在下列各系的学生：计算机系、数学系。

SELECT * FROM S WHERE SDEP=′计算机系′ OR SDEP=′数学系′

(4)求缺少学习成绩的学生学号和课程号。

SELECT SNO,CNO FROM SC WHERE G IS NULL

(5)求选修C1课程且成绩为B以上的学生信息及成绩。

SELECT S.SNO,SNAME,SDEP,SA,G FROM S,SC;

WHERE S.SNO=SC.SNO .AND. O=′C1′.AND. (SC.G=′A′.OR. SC=′B′)

(6)求每一课程的间接先行课（即先行课的先行课）。

SELECT O,SECOND.PCNO FROM C FIRST, C SECOND;

WHERE FIRST.PCNO=O

(7)求没有选修C3课程的学生姓名。

SELECT SNAME FROM S WHERE .NOT. EXISTS;

(SELECT * FROM SC WHERE SNO=S.SNO AND CNO=′C3′)

(8)求计算机系的学生以及年龄小于18岁的学生。

SELECT * FROM S WHERE SDEP=′计算机系′ UNION;

SELECT * FROM S WHERE SAGE<18

(9)求课程号及选修该课程的学生人数。

SELECT CNO,COUNT(SNO) FROM SC GROUP BY CNO

(10)求选修课程超过3门的学生学号。

SELECT SNO FROM SC GROUP BY SNO HAVING COUNT(*)>3

6．（1）建立人事数据库，根据下表给出的表结构，使用SQL语言建立数据表职工，定义职工号为主索引，性别默认为“男，工资限制在500～2000”之间。