历年数据库查询.docx
- 文档编号:6341143
- 上传时间:2023-01-05
- 格式:DOCX
- 页数:20
- 大小:24.57KB
历年数据库查询.docx
《历年数据库查询.docx》由会员分享,可在线阅读,更多相关《历年数据库查询.docx(20页珍藏版)》请在冰豆网上搜索。
历年数据库查询
共17第1页
第一题:
(注:
查询答案不唯一,尽供参考)
设某商业集团中有若干公司,其人事数据库中有3个基本表:
职工关系EMP(E#,ENAME,AGE,SEX,ECITY),其属性分别表示职工工号、姓名、年龄、性别和居住城市。
工作关系WORKS(E#,C#,SALARY),其属性分别表示职工工号、工作的公司编号和工资。
公司关系COMP(C#,CNAME,CITY,MGR_E#),其属性分别表示公司编号、公司名称、公司所在城市和公司经理的工号。
1)用CREATETABLE语句创建上述3个表,需指出主键和外键。
CREATETABLEEMP
(E#CHAR(4)NOTNULL,
ENAMECHAR(8)NOTNULL,
AGESMALLINT,
SEXCHAR
(1),
ECITYCHAR(20),
PRIMARYKEY(E#))
OR:
CREATETABLEEMP
(E#CHAR(4)PRIMARYKEY,
ENAMECHAR(8)NOTNULL,
AGESMALLINT,
SEXCHAR
(1),
ECITYCHAR(20))
共17第2页
CREATETABLECOMP
(C#CHAR(4)NOTNULL,
CNAMECHAR(20)NOTNULL,
CITYCHAR(20),
MGR_E#CHAR(4),
PRIMARYKEY(C#),
FOREIGNKEY(MGR_E#)REFERENCESEMP(E#))
CREATETABLEWORKS(E#CHAR(4)NOTNULL,C#CHAR(4)NOTNULL,SALARYSMALLINT,PRIMARYKEY(E#,C#),FOREIGNKEY(E#)REFERENCESEMP(E#),FOREIGNKEY(C#)REFERENCESCOMP(C#))
2)检索至少为两个公司工作的职工工号。
SELECTX.E#
FROMWORKSX,WORKSY
WHEREX.E#=Y.E#ANDX.C#!
=Y.C#
3)检索与其经理居住在同一城市的职工的工号和姓名。
SELECTA.E#,A.ENAME
FORMEMPA,WORKSB,COMPC,EMPD
WHEREA.E#=B.E#ANDB.C#=C.C#ANDC.MGR_E#=D.E#ANDA.ECITY=D.CITY
4)检索职工人数最多的公司的编号和名称。
SELECTC.C#,C,CNAME
FORMWORKSB,COMPC
WHEREB.C#=C.C#
GROUPBYC.C#
HAVINGCOUNT(*)>=ALL(SELECTCOUNT(*)
FROMWORKS
GROUPBYC#)
5)检索平均工资高于联华公司平均工资的公司的编号和名称。
SELECTC.C#,C.CNAME
FROMWORKSB,COMPC
WHEREB.C#=C.C#
GROUPBYC.C#
HAVINGAVG(SALARY)>(SELECTAVG(SALARY)
FROMWORKSB,COMPC
WHEREB.C#=C.C#ANDCNAME='联华公司')
共17第3页
6)为联华公司的职工加薪,月薪不超过3000元的职工加薪10%,超过3000元的职工
加薪8%。
UPDATEWORKSSETSALARY=SALARY*1.1WHERESALARY<=3000ANDC#IN(SELECTC#
FROMCOMP
WHERECNAME='联华公司')
UPDATEWORKSSETSALARY=SALARY*1.08WHERESALARY>3000ANDC#IN(SELECTC#
FROMCOMP
WHERECNAME='联华公司')
7)在EMP表和WORKS表中删除年龄大于60岁的职工的有关元组。
DELETEFROMWORKSWHEREE#IN(SELECTE#FROMEMPWHEREAGE>60)DELETEFROMEMPWHEREAGE>60
第二题:
设一个电影资料数据库有3个基本表:
电影表Movie、演员表Actor和电影主演表Acts。
Movie关系的属性包括电影名、制作年份、长度等;Actor关系的属性包括演员名、地址、性别、生日等;电影主演表Acts包括电影名、制作年份、演员姓名等。
用SQL实现如下操作:
(1)为1990-1999年制作的电影建立视图;
CREATEVIEWMyearAS
SELECT*
FROMMovie
WHEREYear>=1990ANDYear<=1999
(2)对视图进行如下查询:
长度超过120分钟的电影名称和演员姓名。
SELECTTitle,actorName
FROMMyear,Acts
WHEREActs.MovieTitle=Myear.TitleANDLength>120
(3)要求为Movie关系增加一个属性MovieDirector(电影导演)。
用SQL实现这种关系模式的改变。
ALTERTABLEMovie
ADDMovieDirectorCHAR(20)
(4)把电影“我的世界”的长度改为109分钟;
UPDATEMovieSETLength=109
WHERETitle=‘我的世界'
(5)删除1940年以前制作的所有电影记录以及电影主演记录。
DELETEFROMMovie
WHEREYear<1940;
共17第4页
DELETEFROMActs
WHEREMovieYear<1940
(6)所有同名电影各自的电影名和制作年份;
SELECTM1.Title,M1.Year,M2.Year
FROMMovieASM1,MovieASM2
WHEREM1.Title=M2.TitleANDM1.Year (7)向演员关系Actor中插入一个演员记录,具体的分量由自己指定。 INSERTINTOActor(Name,Gender)VALUES(‘秀兰·邓波儿',‘F') (8)统计1999年制作电影的平均长度和最短长度; SELECTAVG(Length),MIN(Length) FROMMovie WHEREYear=1999 第一题: 设某商业集团中有若干公司,其人事数据库中有3个基本表: 职工关系EMP(E#,ENAME,AGE,SEX,ECITY),其属性分别表示职工工号、姓名、年龄、性别和居住城市。 工作关系WORKS(E#,C#,SALARY),其属性分别表示职工工号、工作的公司编号和工资。 公司关系COMP(C#,CNAME,CITY,MGR_E#),其属性分别表示公司编号、公司名称、公司所在城市和公司经理的工号。 1)建立一个有关女职工信息的视图EMPWOMAN,属性包括(E#,ENAME,C#,CNAME, SALARY)。 CREATEVIEWEMPWOMAN ASSELECTA.E#,A.ENAME,C.C#,CNAME,SALARY FROMEMPA,WORKSB,COMPC WHEREA.E#=B.E#ANDB.C#=C.C#ANDSEX='F' 2)检索居住城市和公司所在城市相同的职工工号和姓名。 SELECTA.E#,A.ENAME FROMEMPA,WORKSB,COMPC WHEREA.E#=B.E#,ANDB.C#=C.C#,ANDA.ECITY=C.CITY 3)假设每个职工可在多个公司工作,检索每个职工的兼职公司数目和工资总数。 显示(E#,NUM,SUMSALARY),分别表示工号、公司数目和工资总数。 SELECTE#,COUNT(C#)ASSUM,SUM(SALARY)ASSUMSALARY FROMWORKS GROUPBYE# 4)检索工资高于其所在公司职工平均工资的所有职工的工号和姓名。 SELECTA.E#,A.ENAME 共17第5页 FROMEMPA,WORKSB WHEREA.E#=B.E#ANDSALARY>(SELECTAVG(SALARY) FROMWORKSCWHEREC.C#=B.C#) 5)检索工资总额最小的公司的编号和名称。 SELECTC.C#,C,CNAME FORMWORKSB,COMPC WHEREB.C#=C.C# GROUPBYC.C# HAVINGSUM(SALARY)<=ALL(SELECTSUM(SALARY) FROMWORKSGROUPBYC#) 6)WANG职工的居住地改为苏州市。 UPDATEEMPSETECITY='苏州市'WHEREENAME='WANG' 7)为联华公司的经理加薪8%。 UPDATEWORKSASETSALARY=SALARY*1.08 WHEREEXISTS(SELECT* FROMCOMPB WHEREB.C#=A.C#ANDB.CNAME='联华公司'ANDB.MGR_E#=A.E#) 8)在WORKS基本表中,删除联华公司的所有职工元组。 DELETEFROMWORKSWHEREC#IN(SELECTC# FROMCOMP WHERECNAME='联华公司') 第二题: 关系模式如下: 学生S(SNO,SN,SEX,AGE)课程C(CNO,CN,PCNO)PCNO为直接先行课号选课SC(SNO,CNO,G)G为课程考试成绩用SQL写出查询程序: (1)查询选修了所有以“MAT”为先行课的课程的学生姓名SN。 SELECTSN FROMS,SCWHERES.SNO=SC.SNOANDSC.CNOIN(SELECTC.CNOFROMC,CASCX WHEREC.PCNO=CX.CNO 共17第6页 ANDCX.CN=‘MAT'); (2)查询年龄(AGE)大于“张三”年龄的学生姓名和年龄。 SELECTS.SN,S.AGE FROMS,SASSX WHERES.AGE>SX.AGEANDSX.SN=‘张三'; (3)查询课程名和直接先行课的课程名以及间接先行课的课程名。 SELECTCN,CX.CN,CY.CN FROMC,CASCX,CASCY WHEREC.PCNO=CX.CNO ANDCX.PCNO=CY.CNO; (4)将选修课程“DB"的学生学号SNO,姓名SN建立视图SDB。 CREATEVIEWSDB AS SELCETSNO,SN FROMS WHERESNOIN SELECTSNO FROMSC WHERECNOIN (SELECTCNOFROMC WHERECN=‘DB')) (5)选修课程“DB”的学生姓名SN。 答案一 SELECTSN FROMS WHERESNOIN SELECTSNO FROMSC WHERECNOIN (SELECTCNO FROMC WHERECN=‘DB')); 答案二 SELECTSNFROMS,SC,C WHERES.SNO=SC.SNO ANDSC.CNO=C.CNO ANDC.CN=‘DB'; 共17第7页 (6)所有学生都选修的课程名CN。 SELECTCN FROMC WHERENOTEXISTS(SELECT* FROMSWHERENOTEXISTS(SELECT* FROMSC WHERESNO=S.SNOANDCNO=C.CNO));答案二 SELECTCN FROMC WHERECNOIN(SELECTCNO FROMSC GROUPBYCNO HAVINGCOUNT(*)=(SELECTCOUNT(*) FROMS));第一题: 对于教学数据库中的4个关系: 教师关系T(T#,TNAME,TITLE) 课程关系C(C#,CNAME,T#) 学生关系S(S#,SNAME,AGE,SEX) 选课关系SC(S#,C#,SCORE) 其中,T中T#为主键;C中C#为主键,T#为外键;S中S#为主键;SC中S#,C#为主键且同时为外键。 用SQL实现如下操作: (1)用语句创建上述四个基本表: T,C,S,SC,并正确表达其数据类型和主、外键约束。 CREATETABLET(T#CHAR(4)NOTNULL,TNAMECHAR(8)NOTNULL,TITLECHAR(10),PRIMARYKEY(T#)) CREATETABLEC(C#CHAR(4),CNAMECHAR(10)NOTNULL,T#CHAR(4),PRIMARYKEY(C#),FOREIGNKEY(T#)REFERENCET(T#)); CREATETABLES(S#CHAR(4)NOTNULL,SNAMECHAR(8)NOTNULL,AGESMALLINT,SEXCHAR (2),PRIMARYKEY(S#)); CREATETABLESC(S#CHAR(4),C#CHAR(4),SCORESMALLINT,PRIMARYKEY(S#,C#),FOREIGNKEY(S#)REFERENCES(S#),FOREIGNKEY(C#)REFERENCEC(C#)); (2)检索学习课程号为C2课程的学生学号与成绩。 (3分)SELECTS#,SCOREFROMSCWHEREC#='C2'; (3)检索至少选修了LIU老师所授课程中一门课程的学生学号与姓名。 共17第8页 SELECTS,S#,SNAMEFROMS,SC,C,TWHERES.S#=SC.S#ANDSC.C#=C.C.#ANDC.T#=T.T#ANDTNAME='LIU' (4)检索选修课程号为C2和C4的学生学号。 (5)求每一教师每门课程的学生选修人数(超过50人),要求显示教师工号、课程号和学生人数。 显示时,查询结果按人数升序排列,人数相同按工号升序、课程号降序排列。 SELECTT#,C.C#,COUNT(S#)FROMC,SCWHEREC.C#=SC.C#HAVINGCOUNT(*)>50ORDERBY3,T#,C.C#DESC; (6)检索平均成绩最高的学生学号。 SELECTS#FROMSCGROUPBYS#HAVINGAVG(SCORE)>=ALL(SELECTAVG(SCORE)FROMSCGROUPBYS#) (7)往基本表S中插入一个元组(S36,GU,20,M)。 INSERTINTOS(S#,SNAME,AGE,SEX)VALUES(‘S36',‘GU',20.‘M') (8)把C4课程中小于该课程平均成绩的成绩元组从基本表SC中删除。 DELETEFROMSCWHEREC#‘C4'ANDSCORE<(SELECTAVG(SCORE)FROMSCWHEREC#=‘C4') (9)当C4课程的成绩低于该门课程平均成绩时,提高5%。 UPDATESCSETSCORE=SCORE*1.05WHEREC#=‘C4'ANDSCORE<(SELECTAVG(SCORE)FROMSCWHEREC#='C4') (10)基于基本表S、SC、C,建立关于S#、SNAME、CNAME和SCORE的视图。 CREATEVIEWSTUDENT_SCORE(S#,SNAME,CNAME,SCORE)ASSELECTS.S#,SNAME,CNAME,SCOREFROMS,SC,CWHERES.S#=SC.S#ANDSC.C#=C.C# 第二题: 关系模式如下: 商品P(PNO,PN,COLOR,PRICE)商店S(SNO,SN,CITY)销售SP(PNO,SNO,QTY) 用SQL实现如下操作: (1)查询销售所有商品的商店名SN。 SELECTSNFROMS WHERESNOIN (SELECTSNO FROMSP GROUPBYSNO HAVINGCOUNT(*)= (SELECTCOUNT(*)FROMP));答案二 SELECTSNFROMS WHERENOTEXISTS (SELECT*FROMP WHERENOTEXISTS(SELECT*FROMSP WHERESNO=S.SNOANDPNO=P.PNO)); (2)将在London销售红色商品的商店号SNO,商店名SN建立视图RLS。 共17第9页 答案一 CREATEVIEWRLSASSELECTSNO,SN FROMS,SP,PWHERES.SNO=SP.SNOANDSP.PNO=P.PNOANDS.CITY=‘London'ANDP.COLOR=‘Red';答案二 CREATEVIEWRLSASSELECTSNO,SN FROMS WHERECITY=‘London'ANDSNOIN (SELECTSNO FROMSP WHEREPNOIN(SELECTPNO FROMP WHERECOLOR=‘Red')); 第一题: 对于教学数据库中的4个关系: 教师关系 课程关系学生关系选课关系 T(T#,TNAME,TITLE)C(C#,CNAME,T#) S(S#,SNAME,AGE,SEX)SC(S#,C#,SCORE) 其中,T中T#为主键;C中C#为主键,T#为外键;S中S#为主键;SC中S#,C#为主键且同时为外键。 用SQL实现如下操作: (1)在基本表S中增加一个地址(ADDRESS)列。 ALTERTBALESADDADDRESSVARCHAR(30); (2)为基本表SC中的(S#,C#)建立唯一索引。 CREATEUNIQUEINDEXSC_INDEXONSC(S#ASC,C#DESC); (3)检索学习课程号为C2课程的学生学号与姓名。 SELECTS,S#,SNAMEFROMS,SCWHERES.S#=SC.S#ANDC#='C2'; (4)在基本表SC中检索男同学选修的课程号 SELECTDISTINCTC#FROMS,SCWHERES.S#=SC.S#ANDSEX='M' (5)检索选修课程号为C2或C4的学生学号。 (6)检索不学C2课程的学生姓名与年龄。 SELECTSNAME,AGEFROMSWHERS#NOTIN(SELECTS#FROMSCWHEREC#='C2') 共17第10页 (7)统计每门课程的学生选修人数。 SELECTC.C#,COUNT(S#)FROMC,SCWHEREC.C#=SC.C#GROUPBYC.C# (8)在基本表S和SC中检索至少不学C2和C4两门课程的学生学号。 SELECTS#FORMSWHERES#NOTIN(SELECTS#FROMSCWHEREC#IN(‘C2','C4')) (9)往基本表SC中插入一个选课元组(S5,C8),此处成绩值为空值。 INSERTINTOSC(S#,C#)VALUES(‘S5',‘C8') (10)把课程名为MATHS的成绩从基本表SC中删除。 DELETEFROMSCWHEREC#IN(SELECTC#FROMCWHERECNAME=‘MATHS') (11)把女同学的成绩提高10%。 UPDATESCSETSCORE=SCORE*1.1WHERES#IN(SELECTS#FROMSWHERESEX='F') (12)定义一个有关男学生的视图。 CREEATEVIEWS_MALEASSELECTS#,SNAME,AGEFROMSWHERESEX=‘M'第二题: 关系模式如下: 商品P(PNO,PN,COLOR,PRICE)商店S(SNO,SN,CITY)销售SP(PNO,SNO,QTY)用SQL实现如下操作: (1)所有商店都销售的商品的商品号PNO。 SELECTPNOFROMSP GROUPBYPNO HAVINGCOUNT(*)=(SELECTCOUNT(*)FROMS); (2)查询与商品“TV”颜色相同的商品名PN。 SELECTP.PNFROMP,PPX WHEREP.COLOR=PX.COLORANDPX.PN=‘TV'; (3)查询销售商品“TV”的商店名SN。 SELECTS.SN FROMS,SP,P WHERES.SNO=SP.SNOANDP.PNO=SP.PNOANDP.PN=‘TV' SQL语句。 不要求写出 第一题: 学生——课程数据库的数据示例如下图所示,写出下列各题的 共17第11页 执行后的结果。 Student 学号 姓名 性别 年龄 所在系 Sno Sname Ssex Sage Sdept 200215121 李勇 男 20 CS 200215122 刘晨 女 19 CS 200215123 王敏 女 18 MA 200215125 张立 男 19 IS CourseSC
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 历年 数据库 查询
![提示](https://static.bdocx.com/images/bang_tan.gif)