sql作业答案

1．用SQL语句完成下列操作。订购客户客户ID订购日期货物ID数量10104109-01-2001101551031509-18-2001101521032910-28-2001101711031511-01-2001102961031511-02-2001101741010112-15-2001102881010112-22-2001101731033006-01-2002100141033006-18-2002102931032906-30-2002102921010107-08-2002100821033007-19-2002102831010108-13-2002100111031508-14-2002100831010108-18-200210091客户ID姓名城市10101黎明北京10299陈俊广州10315罗云广州10325韦林上海10329王菱深圳10330柳琴上海货物货物ID货物名单价1001睡袋568.001008充气垫898.001009帐篷768.001015罗盘26.001017军刀58.001028手套38.001029雪靴298.00（1）求在2002年8月份，已售货物的销售总数。SELECT货物，SUM(数量)FROM订购;WHERE订购日期BETWEEN{^2002-08-01}AND{^2002-08-31};GROUPBY货物。SELECT客户ID，姓名，城市FROM客户;WHERE客户IDIN(SELECT客户IDFROM订购;WHERE订购日期BETWEEN{^2001-01-01}AND{^2001-12-31})（3）求每个货物ID及购买该货物数量最多的客户的ID。SELECT货物ID,客户IDFROM订购DG;WHERE数量=(SELECTMAX(数量)FROM订购;WHERE货物ID=DG.货物ID)（4）求购买了货物的客户信息。SELECT*FROM客户WHEREEXIST;(SELECT*FROM订购WHERE订购.客户ID=客户.客户ID)（5）在货物表中增加售出量字段（N/3/0），并汇总货物的售出总数。ALTERTABLE货物ADD售出量N(3)UPDATE货物SET售出量=(SELECTSUM(数量)FROM订购;WHERE订购.货物ID=货物.货物ID)2．用SQL语句完成下列查询工作。S（供应商），数据项有SNO，SNAME，STATUS，CITYP（零件），数据项有PNO，PNAME，WEIGHT，COLORSP（零件供应记录），数据项有SNO，PNO，QTY，表示某供应商供应拥有某零件的数量为QTYS（供应商）SP（零件供应记录）SNOSNAMESTATUSCITYS1精益20天津S2盛行10北京S3东方红30北京SNOPNOQTYS1P1300S1P2200S1P3400S2P1300S2P2400S3P2200P（零件）PNOPNAMEWEIGHTCOLORP1螺母12红P2螺栓17灰P3螺丝刀14蓝P4螺丝刀14褐(1)找出所有S打头的供应商提供的零件清单（须含零件名称、数量）。SELECTP.PNAME,SP.QTYFROMS,P,SP;WHEREP.PNO=SP.PNO.AND.S.SNO=SP.SNO;.AND.S.SNAMELIKE′S%′(2)列出所有供应商提供的零件种类数（须含供应商名、零件种类数量）。SELECTS.SNAME,COUNT(SP.PNO)FROMS,SP;WHERES.SNO=SP.SNOGROUPBYSNO(3)求北京的供应商的数目。SELECTCOUNT(S.SNO)FROMSWHERES.CITY=′北京′(4)求不供应P2商品的供应商名字。SELECTS.SNAMEFROMS,SP;WHERES.SNO=SP.SNO.AND..NOT.EXISTS;(SELECT*FROMSPWHEREPNO=′P2′.AND.S.SNO=SP.SNO)3．参照上题提供的表和数据，使用SQL语句完成下列任务。(1)把所有供应零件重量超过17（含17）的供应量增加10%。UPDATESPSETQTY=QTY*1.1;WHEREPNOIN(SELECTPNOFROMP,SP;WHERESP.PNO=P.PNO.AND.P.WEIGHT=17)(2)将零件供应记录中没有的零件从零件表中删除。DELETEFROMPWHEREPNO.NOT.IN;(SELECTDISTINCEPNOFROMSP)4．用SQL语句完成下列操作。S（供应商），数据项有SNO，SNAME，STATUS，CITYP（零件），数据项有PNO，PNAME，WEIGHT，COLORJ（工程项目），数据项有JNO，JNAME，CITYSPJ（零件供应表）数据项有SNO，PNO，JNO，QTY。表示某供应商供应某零件给某工程项目的数量为QTYS（供应商）SPJ（联结记录）SNOSNAMESTATUSCITYS1精益20天津S2盛行10北京S3东方红30北京S4丰奉20天津S5菱庆30上海SNOPNOJNOQTYS1P1J1200S1P1J3100S1P1J4700S1P2J2100S2P3J1400S2P3J2200S2P3J4500S2P3J5400S2P5J1400S2P5J2100S3P1J1200S3P3J1200S4P5J1100S4P6J3300S4P6J4200S5P2J4100S5P3J1200S5P6J2200S5P6J4500J（工程项目）JNOJNAMECITYJ1三建北京J2一汽长春J3弹簧厂天津J44造船厂天津J5机车厂唐山J6无线电厂南京J7半导体厂上海P（零件）PNOPNAMECOLORWEIGHTPRICEP1螺母红120.05P2螺栓灰170.10P3螺丝刀蓝141.50P4螺丝刀褐141.20P5凸轮蓝405.00P6齿轮灰307.30(1)求所有供应商的所在城市名，要求没有重复信息。SELECTDISTINCTCITYFROMS(2)按照零件名的升序和价格的降序（在零件名相同时）显示零件的信息SELECTPNO,PNAME,COLOR,WEIGHT,PRICEFROMP;ORDERBYPNAME,PRICEDESC(3)求供应工程J1零件的单位号码SNOSELECTSNOFROMSPJWHEREJNO=′J1′(4)求供应工程J1零件P1的供应单位号码SELECTSNOFROMSPJWHEREJNO=′J1′.AND.PNO=′P1′(5)求供应工程J1红色零件的单位号码SELECTSNOFROMSPJWHEREJNO=′J1′.AND.PNOIN;(SELECTPNOFROMPWHERECOLOR=′红′)(6)求没有使用天津单位生产的零件的工程号JNOSELECTJNOFROMSPJWHERENOTEXISTS;(SELECT*FROMSWHERES.SNO=SPJ.SNO.AND.CITY=′天津′)(7)把全部灰色零件的颜色改为黑色UPDATEPSETCOLOR=′黑′WHERECOLOR=′灰′(8)由S1供应给J1的零件P1今改为由S2供给，作必要的修改UPDATESPJSETSNO=′S2′;WHERESNO=′S1′.AND.JNO=′J1′.AND.PNO=′P1′(9)求在北京的所有供给商和工程的名字SELECTSNAMEFROMSWHERECITY=′北京′UNION;SELECTJNAMEFROMJWHERECITY=′北京′(10)删除全部使用蓝色零件的工程供应记录DELETEFROMSPJWHEREPNOIN;(SELECTPNOFROMPWHERECOLOR=′蓝′)5．用SQL语句完成下列操作（数据表基于例题）。(1)求选修C1课程的学生学号和得分，结果按分数降序排列。SELECTSNO,GFROMSCWHERECNO=′C1′ORDERBYGDESC(2)求年龄在20岁与22岁之间（包括20岁和22岁）的学生学号和年龄。SELECTSNO,SAFROMSWHERESAGEBETWEEN20AND22(3)求在下列各系的学生：计算机系、数学系。SELECT*FROMSWHERESDEP=′计算机系′ORSDEP=′数学系′(4)求缺少学习成绩的学生学号和课程号。SELECTSNO,CNOFROMSCWHEREGISNULL(5)求选修C1课程且成绩为B以上的学生信息及成绩。SELECTS.SNO,SNAME,SDEP,SA,GFROMS,SC;WHERES.SNO=SC.SNO.AND.SC.CNO=′C1′.AND.(SC.G=′A′.OR.SC=′B′)(6)求每一课程的间接先行课（即先行课的先行课）。SELECTFIRST.CNO,SECOND.PCNOFROMCFIRST,CSECOND;WHEREFIRST.PCNO=SECOND.CNO(7)求没有选修C3课程的学生姓名。SELECTSNAMEFROMSWHERE.NOT.EXISTS;(SELECT*FROMSCWHERESNO=S.SNOANDCNO=′C3′)(8)求计算机系的学生以及年龄小于18岁的学生。SELECT*FROMSWHERESDEP=′计算机系′UNION;SELECT*FROMSWHERESAGE18(9)求课程号及选修该课程的学生人数。SELECTCNO,COUNT(SNO)FROMSCGROUPBYCNO(10)求选修课程超过3门的学生学号。SELECTSNOFROMSCGROUPBYSNOHAVINGCOUNT(*)36．（1）建立人事数据库，根据下表给出的表结构，使用SQL语言建立数据表职工，定义职工号为主索引，性别默认为“男，工资限制在500～2000”之间。职工表结构字段名类型宽度小数位数职工号C5部门号C2姓名C8性别C2职称C10出生日期D8婚否L1工资N77步骤一：创建数据库参考命令：CREATEDATABASE步骤二：打开数据库设计器。参考命令：MODIFYDATABASE步骤三：建立职工表。参考命令：CREATE职工（职工号C（5）PRIMARYKEY，部门号C（2），姓名C（2）DEFAULT；“男”，职称C（10），出生日期D，婚否L，工资N（8，2）CHECK工资=500AND工资=2000,ERROR；“工资应在500-2000之间”DEFAULT1000）（2）建立部门表（部门代号C（2），部门名称C（10），负责人C（8）），部门代码为主索引。参考命令：CREATETABLE部门（部门代号C（2）PRIMARYKEY，部门名称C（10），负责人；C（8））（3）建立工资表，结构如下,其中职工号为主索引。职工号C(5),基本工资N(8,2),津贴N(7,1),奖金N(3),会费N(5,2),公积金N(3)参考命令：CREATETABLE工资(职工号C(5)PRIMARYKEY,基本工资N(8,2),津贴N(7,1),;奖金N(3),会费N(5,2),公积金N(3))(4)删除职工表的婚否字段，增加年龄N（8，0），将姓名字段宽度改为6参考命令：ALTERTABLE职工ADD年龄N（8，0）ALTER姓名C(6)DROP婚否（5）职称增加默认值“教授”参考命令：ALTERTABLE职工ALTER职称SETDEFAULT“教授”（6）使用SQL的INSERT在各张表中输入一些数据。参考命令：法1：INSERTINTO部门VALUES(“22”,”网络教研室”,”李新”)法2：DIMENSIONA(3)A(1)=“12”A(2)=“基础教研室”A(3)=“王雨”INSERTINTO部门PROMARRAYA法3：部门代码=“21”部门名称=“软件教研室”负责人=“李玛丽”INSERTINTO部门PROMMEMVAR(7)将职工表添加的年龄字段，使用出生日期数据进行更新，工资表中的会费由基本工资的千分之五替换，注意观察表数据的变化。参考命令：UPDATE职工SET年龄=Y