《数据库系统概论》复习重点问答题3-数据查询

1问答题4—数据查询一、第3章课后习题P127-4．针对上题中建立的四个表试用SQL完成第二章习题5中的查询（1）求供应工程J1零件的供应商号码SNO答：SELECTSNOFROMSPJWHEREJNO=’J1’（2）求供应工程J1零件P1的供应商号码SNO答：SELECTSNOFROMSPJWHEREJNO=’J1’ANDPNO=’P1’（3）求供应工程J1零件为红色的供应商号码SNO答：SELECTSNOFROMSPJWHEREJNO=’J1’ANDPNOIN(SELECTPNOFROMPWHERECOLOR=’红’)或：SELECTSNOFROMSPJ,PWHEREJNO=’J1’ANDSPJ.PNO=P.PNOANDCOLOR=’红’（4）求没有使用天津供应商生产的红色零件的工程号JNO答：SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMSPJWHERESPJ.JNO=J.JNOANDSNOIN(SELECTSNOFROMSWHERECITY=’天津’)ANDPNOIN(SELECTPNOFROMPWHERECOLOR=’红’))或：SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMSPJ,S,PWHERESPJ.JNO=J.JNOANDSPJ.SNO=S.SNOANDSPJ.PNO=P.PNOANDS.CITY=’天津’ANDP.COLOR=’红’)（5）求至少用了供应商S1所供应的全部零件的工程号JNO答：SELECTDISTINCTJNOFROMSPJ,SPJZWHERENOTEXISTS(SELECT*FROMSPJSPJXWHERESNO=’S1’ANDNOTEXISTS(SELECT*FROMSPJSPJYWHERESPJY.PNO=SPJX.PNOANDSPJY.JON=SPJZ.JNO))2P127-5．针对习题3中的四个表，试用SQL完成以下各项操作（1）找出所有供应商的姓名和所在城市答：SELECTSNAME,CITYFROMS（2）找出所有供应商的姓名和所在城市答：SELECTPNAME,COLOR,WEIGHTFROMP（3）找出使用供应商S1所供应零件的工程号码答：SELECTJNOFROMSPJWHERESNO=’S1’（4）找出工程项目J2使用的各种零件的名称及其数量答：SELECTP.PNAME,SPJ.QTYFROMP,SPJWHEREP.PNO=SPJ.PNOANDSPJ.JNO=’J2’（5）找出上海厂商供应的所有零件号码答：SELECTDISTINCTPNOFROMSPJWHERESNOIN(SELECTSNOFROMSWHERECITY=’上海’)（6）找出使用上海产的零件的工程名称答：SELECTJNAMEFROMJ,SPJ,SWHEREJ.JNO=SPJ.JNOANDSPJ.SNO=S.SNOANDS.CITY=’上海’或：SELECTJNAMEFROMJWHEREJNOJNO(SELECTJNOFROMSPJ,SWHERESPJ.SNO=S.SNOANDS.CITY=’上海’)（7）找出没有使用天津产的零件的工程号码答：SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMSPJWHERESPJ.JNO=J.JNOANDSNOIN(SELECTSNOFROMSWHERECITY=’天津’））或：SELECTJNOFROMJWHERENOTEXISTS（SELECT*FROMSPJ,SWHERESPJ.JNO=J.JNOANDSPJ.SNO=S.SNOANDS.CITY=’天津’）（8）把全部红色零件的颜色改成蓝色答：UPDATEPSETCOLOR=’蓝’WHERECOLOR=’红’3（9）由S5供给J4的零件P6改为由S3供应，请作必要的修改答：UPDATESPJSETSNO=’S3’WHERESNO=’S5’ANDJNO=’S4’ANDPNO=’P6’（10）从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录答：DELETEFROMSPJWHERESNO=’S2’DELETEFROMSWHERESNO=’S2’（11）请将（S2，J6，P4，200）插入供应情况关系答：INSERTINTOSPJ(SNO,JNO,PNO,QTY)VALUES(S2,J6,P4,200)或：INSERTINTOSPJVALUES(S2,P4,J6,200)二、补充题1．设数据库中有基本表：教师(工号,姓名,性别,职称,工龄,基本工资,补贴），请用SQL语句写出工龄在20年以上（包括20年）、基本工资低于1500元的教师所有信息。答：SELECT*FROM教师WHERE工龄=20AND基本工资15002．在教学管理系统中含有两个关系：学生(学号,姓名,性别,出生日期,系名），成绩(学号,课程名,成绩)。若查询每个学生的选修课程数、总成绩、平均成绩，写出实现上述要求的SQL语句。答：SELECT学号,COUNT(课程名),SUM(成绩),AVG(成绩)FROM成绩GROUPBY学号3．图书出版管理数据库中有两个基本表：图书(书号,书名,作者编号,出版社,出版日期）,作者(作者,编号,作者名,年龄,地址)，试用SQL语句写出下列查询：检索年龄低于作者平均年龄的所有作者的作者名、书名和出版社。答：SELECT作者名,书名,出版社FROM图书,作者WHERE图书.作者编号=作者.作者编号AND年龄=(SELECTAVG(年龄)FROM作者)4．基于数据库中的成绩表：成绩(学号,成绩名,成绩)，用SQL语句写出：只选修一门课程的学生学号、课程名及成绩。答：SELECT*FROM成绩XWHERENOTEXISTS（SELECT*FROM成绩YWHEREY.学号=X.学号ANDY.课程名X.课程名）45．本题基于以下数据库模式：学生关系S(学号,姓名,性别)、课程关系C(课程号,课程名)、成绩关系SC(学号,课程号,分数)，请用SQL语言完成下列查询。（1）分别统计男生和女生的人数答：SELECT“男生人数为：”,COUNT(*)FROMSWHERE性别=‘男’SELECT“女生人数为：”,COUNT(*)FROMSWHERE性别=‘女’（2）检索选修课程号为“C1”且分数最高的学生学号和分数答：SELECT学号,MAX(分数)FROMSCWHERE课程号=‘C1’（3）检索选修课程名为“DB”的学生姓名和分数答：SELECT姓名,分数FROMS,SC,CWHERES.学号=SC.学号ANDSC.课称号=C.课称号ANDC.课成名=‘DB’6．本题基于这样的三个表即学生表S、课程表C和学生选课表SC，它们的结构如下：S(S#,SN,SEX,AGE,DEPT)C(C#,CN)SC(S#,C#,GRADE)，其中S#为学号，SN为姓名，SEX为性别；AGE为年龄，DEPT为系别，C#为课程号，CN为课程名，GRADE为成绩。请用SQL语句写出如下查询。（1）检索所有比“王华”年龄大的学生姓名、年龄和性别答：SELECTSN,AGE,SEXFROMSWHEREAGE(SELECTAGEFROMSWHERESN=’王华’)（2）检索选修了“C2”课程的学生中成绩最高的学生学号答：SELECTS#FROMSCWHEREC#=’C2’ANDGRADE=ALL(SELECTGRADEFROMSCWHEREC#=’C2’)（3）检索学生姓名及其所选修课程的课程号和成绩答：SELECTS.SN,SC.C#,GRADEFROMS,SCWHERES.S#=SC.S#（4）检索选修4门以上课程的学生总成绩（不含不及格课程），并要求按总成绩的降序排列答：SELECTS#,SUM(GRADE)FROMSCWHEREGRADE=60GROUPBYS#HAVINGCOUNT(*)=4ORDERBY2DESC7．本题基于以下定义：图书(总编号,分类号,书名,作者,出版单位,单价)，读者(借书证号,单位,姓名,5性别,职称,地址)，借阅(借书证号,总编号,借书日期)，写出下列各命令或命令组的功能。（1）SELECTMAX(单价)最高价,MIN(单价)最低价,AVG(单价)平均价FROM图书答：求出所有图书的最高价、最低价和平均价（2）SELECTCOUNT(*)FROM图书WHERE单价BETWEEN10AND20答：统计10至20元之间的图书数量（3）SELECT借书证号,COUNT(*)FROM借阅WHERE借书日期’98/01/01’GROUPBY借书证号HAVINGCOUNT(*)=3答：分组统计出97年底以前借阅不低于3本图书的借书证号及数量（4）SELECT单位,COUNT(*)FROM读者,借阅WHERE读者.借书证号=借阅.借书证号GROUPBY单位答：按单位分组统计出被借阅图书的数量（5）SELECT书名,COUNT(*)FROM图书,借阅WHERE图书.总编号=借阅.总编号GROUPBY书名ORDERBYCOUNT(*)答：求被借阅图书的名字和借阅数量，并按借阅数量升序排列8．有2个表：Student（学号char(8)，姓名varchar(8)，年龄int，专业varchar(2)，入学日期DateTime）Score（学号char(8)，课程名varchar(10)，成绩numeric(5,2)）写出下列每条语句或程序段的功能。（1）SELECTCOUNT(*)FROMStudentsWHEREleft(专业,3)=’计算机’答：统计计算机专业人数（2）UPDATEScoreSET成绩=@cWHERE学号=@aAND课程名=@b答：将学号为@a且课程名为@b学生的成绩更改为@c（3）DELETEFROMScoreWHERE学号=@aAND课程名=@b答：从Score表中删除学号为@a、课程名为@b值的学生成绩记录6（4）SELECT*FROMStudent,ScoreWHEREStudent.学号=Score.学号答：查看学生及成绩表（5）SELECTStudent.学号,姓名,专业,COUNT(*)AS门数FROMStudent,ScoreWHEREStudent.学号=Score.学号GROUPBYStudent.学号,姓名,专业答：显示学生的学号、姓名、专业和选课门数（6）SELECT学号,AVG(成绩)AS平均成绩FROMScoreGROUPBY学号答：显示每个学生的平均成绩9．已知关系模式：Customers(customerid,name,emailaddress,city,tel,company,birthday,type)客户(客户编号，姓名，邮箱地址，城市，电话，公司，出生日期，客户类型)Goods(goodsname,description,storage,supplier,status,price)货品(货品名称，描述，库存量，供应商，状态，价格)Orders(orderid,goodsname,customerid,quantity,ordersum,orderdate)订单(订单编号，货品名称，客户编号，订货数量，总金额，订货日期)请写出下列各命令或命令组的功能（1）SELECTcustomerid,name,emailaddressFROMCustomersWHEREemailaddressISNOTNULL答：查询留有邮箱地址客户的客户编号、客户姓名和客户邮箱地址（2）SELECTgoodsname,SUM(quantity)FROMOrdersGROUPBYgoodsnameHAVINGSUM(quantity)500答：查询订单中货品总量超过500（不含500）的货品名称和货品总量（3）SELECTgoodsname,priceFROMGoodsWHEREprice(SELECTAVG(price)FROMGoods)答：查询价格高于所有货品