历年数据库查询.docx

资源描述

历年数据库查询.docx

《历年数据库查询.docx》由会员分享，可在线阅读，更多相关《历年数据库查询.docx（27页珍藏版）》请在冰豆网上搜索。

历年数据库查询.docx

历年数据库查询

第一题：

（注：

查询答案不唯一，尽供参考）

设某商业集团中有若干公司，其人事数据库中有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））

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=’联华公司’）

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；

DELETEFROMActs

WHEREMovieYear＜1940

（6）所有同名电影各自的电影名和制作年份；

SELECTM1．Title，M1．Year，M2．Year

FROMMovieASM1，MovieASM2

WHEREM1．Title＝M2．TitleANDM1．Year＜M2．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

FROMEMPA,WORKSB

WHEREA.E#=B.E#ANDSALARY>（SELECTAVG（SALARY）

FROMWORKSC

WHEREC.C#=B.C#）

5）检索工资总额最小的公司的编号和名称。

SELECTC.C#,C,CNAME

FORMWORKSB,COMPC

WHEREB.C#=C.C#

GROUPBYC.C#

HAVINGSUM（SALARY）<=ALL（SELECTSUM（SALARY）

FROMWORKS

GROUPBYC#）

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基本表中，删除联华公司的所有职工元组。

DELETEFROMWORKS

WHEREC#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，SC

WHERES.SNO=SC.SNO

ANDSC.CNOIN

（SELECTC.CNO

FROMC，CASCX

WHEREC.PCNO＝CX.CNO

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

SELCETSNO,SN

FROMS

WHERESNOIN

SELECTSNO

FROMSC

WHERECNOIN

（SELECTCNO

FROMC

WHERECN＝‘DB'））

（5）选修课程“DB”的学生姓名SN。

答案一

SELECTSN

FROMS

WHERESNOIN

SELECTSNO

FROMSC

WHERECNOIN

（SELECTCNO

FROMC

WHERECN＝‘DB'））；

答案二

SELECTSN

FROMS，SC，C

WHERES.SNO＝SC.SNO

ANDSC.CNO＝C.CNO

ANDC.CN＝‘DB'；

（6）所有学生都选修的课程名CN。

SELECTCN

FROMC

WHERENOTEXISTS

（SELECT*

FROMS

WHERENOTEXISTS

（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老师所授课程中一门课程的学生学号与姓名。

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。

答案一

CREATEVIEWRLSAS

SELECTSNO，SN

FROMS，SP，P

WHERES.SNO＝SP.SNO

ANDSP.PNO＝P.PNO

ANDS.CITY＝‘London'

ANDP.COLOR＝‘Red'；

答案二

CREATEVIEWRLSAS

SELECTSNO，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’）

（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.PN

FROMP,PPX

WHEREP.COLOR＝PX.COLORANDPX.PN＝‘TV'；

（3）查询销售商品“TV”的商店名SN。

SELECTS.SN

FROMS，SP，P

WHERES.SNO＝SP.SNOANDP.PNO＝SP.PNO

ANDP.PN=‘TV’

第一题：

学生——课程数据库的数据示例如下图所示，写出下列各题的SQL语句。

不要求写出执行后的结果。

Student

学号

Sno

姓名

Sname

性别

Ssex

年龄

Sage

所在系

Sdept

200215121

李勇

男

200215122

刘晨

女

200215123

王敏

女

200215125

张立

男

CourseSC

课程号

Cno

课程名

Cname

先行课

Cpno

学分

Ccredit

数据库

数学

信息系统

操作系统

数据结构

数据处理

PASCAL语言

学号

Sno

课程号

Cno

成绩

Grade

200215121

200215122

1.画出PowerDesigner中上述学生——课程数据库的物理数据模型。

2.用SQL语句建立一个“课程”表Course，须在语句中写出主键和外键。

CREATETABLECourse

（CnoCHAR（4）PRIMAR

展开阅读全文