SQL语句强化练习题及答案Word格式文档下载.docx
《SQL语句强化练习题及答案Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《SQL语句强化练习题及答案Word格式文档下载.docx（13页珍藏版）》请在冰豆网上搜索。
SELECT学号,成绩FROM选课WHERE课号IN（"
"
）
相反条件查询：
SELECT学号,成绩FROM选课WHERE课号NOTIN（"
8、列出所有98级学生的学生成绩情况。
SELECT*FROM选课WHERE学号LIKE"
98%"
98____"
SELECT*FROM选课WHERE学号NOTLIKE"
9、列出成绩为空值（或不为空值）的学生的学号和课号。
答案一：
SELECT学号,课号FROM选课WHERE成绩ISNULL
答案二：
SELECT学号,课号FROM选课WHERE成绩ISNOTNULL
10、求出所有学生的总成绩。
SELECTSUM（成绩）AS总成绩FROM选课
11、列出每个学生的平均成绩。
SELECT学号,AVG（成绩）AS平均成绩FROM选课GROUPBY学号
12、列出各科的平均成绩、最高成绩、最低成绩和选课人数。
SELECT课号,AVG（成绩）AS平均成绩,MAX（成绩）AS最高分,;
MIN（成绩）AS最低分,COUNT（学号）AS选课人数FROM选课GROUPBY课号
二、连接查询
（一）简单连接
1、列出选修1号课的学生姓名及成绩。
SELECT姓名,成绩FROM学生,选课WHERE学生.学号=选课.学号AND课号="
2、列出选修1号课的学生的学号、姓名及成绩。
SELECT学生.学号,姓名,成绩FROM学生S,选课XWHERES.学号=X.学号AND课号="
3、求出总分大于150的学生的学号、姓名及总成绩。
SELECT学生.学号,姓名,SUM（成绩）AS总成绩FROM学生,选课;
WHERE学生.学号=选课.学号GROUPBY选课.学号HAVINGSUM（成绩）>
150
（二）自连接查询
1、列出那些专业相同的学生相应的姓名及专业信息。
2、求至少选修1号课和2号课的学生的学号。
SELECTX.学号FROM选课X,选课YWHEREX.学号=Y.学号ANDX.课号="
ANDY.课号="
2"
币种1代码C
（2）、币种2代码C
（2）、买入价N（8,4）、卖出价N（8,4）
币种1C（4）、币种2C（4）、买入价N（8,4）、卖出价N（8,4）
外汇名称C（10）、外汇代码C（10）
要求：
将所有“外汇汇率”表中的数据插入rate表中并且顺序不变，由于“外汇汇率”中的币种1和币种2存放的是外币名称，而rate表中的币种1代码和币种2代码应该存放外币代码，所以插入时要做相应的改动，外币名称与外向代码的对应关系存储在“外汇代码”表中。
SELECTA.外币代码AS币种1代码,B.外币代码AS币种2代码,;
买入价,卖出价FROM外汇代码A,外汇汇率,外汇代码B;
WHEREA.外币名称=外汇汇率.币种1ANDB.外币名称=外汇汇率.币种2INTOTABLErate
4、假定有“雇员”表（雇员号C
（2），雇员姓名C（6），经理号C
（2）），根据雇员关系列出上一级经理及其所领导的职员清单。
（教案中的例题）
（三）超连接
（使用简单连接查询格式）
（使用内部连接格式）
SELECT姓名,成绩FROM学生INNERJOIN选课ON学生.学号=选课.学号WHERE课号="
内部连接的INNER短语可以省略。
（与方法二等价）
SELECT姓名,成绩FROM学生JOIN选课ON学生.学号=选课.学号WHERE课号="
2、查询订货管理数据库中数据的仓库号、城市、供应商名和地址信息。
使用简单连接格式。
SELECT仓库.仓库号,城市,供应商名,地址FROM供应商,订购单,职工,仓库;
WHERE供应商.供应商号=订购单.供应商号AND订购单.职工号=职工.职工号;
使用超连接的内部连接格式。
（注意连接条件的顺序）
SELECT仓库.仓库号,城市,供应商名,地址FROM供应商JOIN订购单JOIN职工JOIN仓库;
3、查询没有选修任何课程的学生姓名。
使用嵌套查询
SELECT姓名FROM学生WHERE学号NOTIN（SELECT学号FROM选课）
使用超连接的右连接。
使用超连接的左连接。
（注意表名顺序和方法二的不同）
三、嵌套查询
（一）普通嵌套与谓词EXISTS
1、列出选修汇编语言课的学生的学号。
SELECT学号FROM选课WHERE课号=（SELECT课号FROM课程WHERE课名="
汇编语言"
使用谓词EXISTS。
注意和方法一格式上的不同。
SELECT学号FROM选课WHEREEXIST（SELECT*FROM课程;
WHERE课名="
AND选课.课号=课程.课号）
2、求软件专业所有必修课的课程信息。
SELECT*FROM课程WHERE课号IN;
（SELECT课号FROM必修课WHERE必修专业="
SELECT*FROM课程WHEREEXIST（SELECT*FROM必修课WHERE必修专业="
;
AND课程.课号=必修课.课号）
（二）量词ANY、SOME、ALL
1、求选修2号课的学生中，成绩比选修1号课的最低成绩要高的学生的学号和成绩。
;
（SELECTMIN（成绩）FROM选课WHERE课号="
ANY等价于SOME，所以可将ANY换成SOME。
ANY;
（SELECT成绩FROM选课WHERE课号="
2、求选修2号课的学生中，成绩比选修1号课的任何学生的成绩都要高的那些学生的学号和成绩。
（SELECTMAX（成绩）FROM选课WHERE课号="
ALL;
（三）内外层互相关嵌套（外层依赖于内层的查询结果，内层依赖于外层来进一步查询）
1、列出每门课程中成绩最高的选课信息。
SELECT*FROM选课AWHERE成绩=（SELECTMAX（成绩）FROM选课BWHEREA.课号=B.课号）
2、列出每个学生中成绩低于本人平均成绩的选课信息。
SELECT*FROM选课AWHERE成绩<
（SELECTAVG（成绩）FROM选课BWHEREA.学号=B.学号）
3、列出表“”（其内容就是在订购单表的基础上增加一个总金额字段）中每个职工经手的具有最高总金额的订购单信息。
（教案中例题）
SELECTout.职工号,out.供应商号,out.订货单号,out.订货日期,out.总金额;
FROM订购单outWHERE总金额=（SELECTMAX（总金额）FROM订购单inner1;
Whereout.职工号=inner1.职工号）
四、操作功能
1、在课程表中插入新的元组（5，大学英语）。
INSERTINTO课程（课号,课名）VALUES（"
5"
大学英语"
2、给学生表中男生的记录加上删除标记。
DELETEFROM学生WHERE性别="
男"
3、将选课表中所有课程的成绩分别提高5分。
将课号为“4”的课程的成绩置为空值。
UPDATE选课SET成绩=成绩+5
UPDATE选课SET成绩=NULLWHERE课号="
4"
五、定义表结构
1、建立研究生表，该表不属于任何数据库，其结构如表所示：
字段名
字段类型
字段长度
小数位数
特殊要求
学号
C
6
姓名
8
性别
2
年龄
N
3
入学年月
D
允许空值
CREATETABLE研究生FREE（学号C（6）,姓名C（8）,性别C
（2）,年龄N（3,0）,入学年月DNULL）
2、假设已经建立了XSK数据库，在XSK中建立“学生1”表，其结构如表所示
主索引
不能为空
年龄大于10小于45
是否党员
L
默认值为1999年9月1日
OPENDATABASEXSK
CREATETABLE学生1（学号C（6）PRIMARYKEY,姓名C（8）NOTNULL,性别C
（2）,;
年龄N（3,0）CHECK年龄>
10AND年龄<
45ERROR"
年龄必须大于10小于45"
是否党员L,;
入学年月DDEFAULT{^1999-9-1}）
3、在XSK数据库建立“课程1”表，其结构如表所示：
课号
4
课程名
10
学分
CREATETABLE课程1（课号C（4）PRIMARYKEY,课程名C（10）NOTNULL,学分N
（2））
4、在XSK数据库建立“选课1”表，其结构如表所示：
外索引与学生表建立关系
外索引与课程表建立关系
成绩
5
CREATETABLE选课1（学号C（6）REFERENCE学生,课号C（4）REFERENCE课程,成绩N（5,2））
六、修改表结构
1、为“课程1”表添加一个开课学期字段，字段类型为数值型，长度为1。
ALTERTABLE课程1ADDCOLUMN开课学期N
（1）
2、修改开课学期字段为字符型，合法值为1或2。
ALTERTABLE课程1ALTERCOLUMN开课学期C
（1）CHECK开课学期="
OR开课学期="
3、删除“课程1”表中对开课学期字段的合法值约束，设置默认值为1。
ALTERTABLE课程1ALTER开课学期DROPCHECK
ALTERTABLE课程1ALTER开课学期SETDEFAULT"
4、删除“课程1”表中的开课学期字段。
修改课程名字段为“课名”字段。
ALTERTABLE课程1DROP开课学期RENAME课程名TO课名
5、在“学生1”表的“年龄”字段上建立候选索引。
ALTERTABLE学生1ADDUNIQUE年龄TAG年龄
6、在“学生1”表中添加一个出生日期字段，删除年龄字段。
ALTERTABLE学生1ADD出生日期D
ALTERTABLE学生1DROPUNIQUETAG年龄DROP年龄
七、补充练习
利用订货管理数据库中表数据，用SQL完成查询。
1、检索在北京的供应商的名称。
SELECT供应商名FROM供应商WHERE地址="
北京"
2、检索出向供应商S3发过订购单的职工的职工号和仓库号。
方法一（嵌套查询）：
SELECT职工号,仓库号FROM职工WHERE职工号IN;
（SELECT职工号FROM订购单WHERE供应商号="
S3"
方法二（连接查询）：
SELECT职工.职工号,仓库号FROM职工,订购单;
WHERE职工.职工号=订购单.职工号AND供应商号="
3、检索出和职工E1、E3都有联系的北京的供应商信息。
SELECT*FROM供应商WHERE地址="
AND供应商号IN;
（SELECT供应商号FROM订购单WHERE职工号="
E1"
）AND供应商号IN;
E3"
4、检索出向S4供应商发出订购单的仓库所在的城市。
SELECT城市FROM仓库,职工,订购单WHERE仓库.仓库号=职工.仓库号;
AND职工.职工号=订购单.职工号AND供应商号="
S4"
5、检索出由工资多于1230元的职工向北京的供应商发出的订购单号。
SELECT订货单号FROM职工,订购单,供应商WHERE职工.职工号=订购单.职工号;
AND订购单.供应商号=供应商.供应商号AND工资>
1230AND地址="
6、检索出所有仓库的平均面积。
SELECTAVG（面积）AS面积FROM仓库
7、检索出每个仓库中工资多于1220元的职工个数。
SELECT仓库号,COUNT（*）职工个数FROM职工WHERE工资>
1220GROUPBY仓库号
8、检索工资低于本仓库平均工资的职工信息。
SELECT*FROM职工AAWHERE工资<
（SELECTAVG（工资）FROM职工BBWHEREAA.仓库号=BB.仓库号）
CREATVIEWviewName
as
SELECTAVG（column1）FROMtable1
asc1,
SELECTAVG（column2）FROMtable2
asc2,
SELECTAVG（column3）FROMtable3
asc3
有三个表emp,dept,salgrade,表结构和表内容如下所示
emp→雇员信息表
EMPNO雇员编号NOTNULLsmallint
ENAME雇员姓名VARCHAR（10）
JOB工作岗位VARCHAR（9）
MGR该雇员经理人的编号smallint
HIREDATE入职时间DATE
SAL薪水Numeric（7,2）
COMM津贴Numeric（7,2）
DEPTNO雇员所在部门编号smallint
表内容：
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
-----------------------------------------------------------------------------------------
7369SMITHCLERK790217-12月-8080020
7499ALLENSALESMAN769820-2月-81160030030
7521WARDSALESMAN769822-2月-81125050030
7566JONESMANAGER783902-4月-81297520
7654MARTINSALESMAN769828-9月-811250140030
7698BLAKEMANAGER783901-5月-81285030
7782CLARKMANAGER783909-6月-81245010
7788SCOTTANALYST756619-4月-87300020
7839KINGPRESIDENT17-11月-81500010
7844TURNERSALESMAN769808-9月-811500030
7876ADAMSCLERK778823-5月-87110020
7900JAMESCLERK769803-12月-8195030
7902FORDANALYST756603-12月-81300020
7934MILLERCLERK778223-1月-82130010
dept→部门信息表
DEPTNO部门编号NOTNULLsmallint
DNAME部门名字VARCHAR（14）
LOC部门所在地VARCHAR（13）
DEPTNODNAMELOC
------------------------------------------
10ACCOUNTINGNEWYORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
Salgrade→薪水等级表
GRADE薪水等级smallint
LOSAL该等级的最低薪水值smallint
HISAL该等级的最高薪水值smallint
GRADELOSALHISAL
-------------------------
17001200
212011400
314012000
420013000
530019999
求
1、从emp这张表里把平均工资和部门编号列出来，并且过滤掉大写是A的名字，把剩下的数据按照部门编号进行分组，分组之后的平均薪水必须大于2000，按照部门编号的倒序排列.
selectavg（sal）,DEPTNOfromemp
whereenamenotlike'
%A%'
groupbydeptno
havingavg（sal）>
2000
orderbydeptnodesc
2、.请你选出雇员的名字，和雇员在部门的名字？
selectemp.ename,dept.dname
fromempjoindeptonemp.DEPTNO=dept.DEPTNO
3、.求每个人的薪水值包括他的名字
selectsal,enamefromemp
4.求每个人的薪水值，名字和他的薪水处于第几个级别（非等值连接）
（1）selectsalas薪水,enameas雇员姓名,Salgrade.grade
fromempjoinSalgradeonsalbetweenlosalandhisal
（2）selectsalas薪水,enameas雇员姓名,Salgrade.grade
fromempjoinSalgradeonsal>
losalandsal<
hisal
5.求出他的名字，求出他所在部门的名称，求出他的薪水等级
Selectename,dept.dname,Salgrade.grade
Fromempjoindeptonemp.DEPTNO=dept.DEPTNO
joinSalgradeonsalbetweenlosalandhisal
6.求谁挣的钱最多？
selectenamefromempwheresal=（selectmax（sal）fromemp）
7.求出来有哪些工资位于所有人平均工资之上
selectename,salfromempwheresal>
（selectavg（sal）fromemp）;
8.按照部门进行分组之后挣钱最多的那个人的名字，部门编号？
selectename,salfromemp
join（selectmax（sal）max_sal,deptnofromempgroupbydeptno）t
on（emp.sal=t.max_salandemp.deptno=t.deptno）;
****理解子查询的关键-->
把它当成一张表
9.求这个人的名字和他经理人的名字（自连接）
selecte1.ename,e2.enamefromempe1,empe2wheree1.mgr=e2.empno;
自连接：
为同一张表起不同的别名，然后当成两张表来用
selecte1.ename,e2.enamefromempe1joinempe2on（e1.mgr=e2.empno）;
10.求部门平均薪水等级？
selectdeptno,avg_sal,gradefrom
（selectdeptno,avg（sal）avg_salfromempgroupbydeptno）t
joinsalgradeson（t.avg_salbetweens.losalands.hisal）;
11.求
升级会员 