SQL语句强化练习题及答案.docx
- 文档编号:3583479
- 上传时间:2022-11-24
- 格式:DOCX
- 页数:13
- 大小:20.81KB
SQL语句强化练习题及答案.docx
《SQL语句强化练习题及答案.docx》由会员分享,可在线阅读,更多相关《SQL语句强化练习题及答案.docx(13页珍藏版)》请在冰豆网上搜索。
SQL语句强化练习题及答案
SQL语句强化练习题及答案
一、简单查询
1、列出全部学生的信息。
SELECT*FROM学生
2、列出软件专业全部学生的学号及姓名。
SELECT学号,姓名FROM学生WHERE专业="软件"
3、列出所有必修课的课号。
SELECTDISTINCT课号FROM必修课
4、求1号课成绩大于80分的学生的学号及成绩,并按成绩由高到低列出。
SELECT学号,成绩FROM选课WHERE课号="1"AND成绩>80ORDERBY成绩DESC
5、列出非软件专业学生的名单。
方法一:
SELECT姓名FROM学生WHERE专业<>"软件"
方法二:
SELECT姓名FROM学生WHERENOT专业="软件"
方法三:
SELECT姓名FROM学生WHERE专业!
="软件"
6、查询成绩在70~80分之间的学生选课得分情况
方法一:
SELECT*FROM选课WHERE成绩>=70AND成绩<=80
方法二:
SELECT*FROM选课WHERE成绩BETWEEN70AND80
不在此范围内的查询:
(注意写出和以下语句等价的语句)
SELECT*FROM选课WHERE成绩NOTBETWEEN70AND80
7、列出选修1号课或3号课的全体学生的学号和成绩。
方法一:
SELECT学号,成绩FROM选课WHERE课号="1"OR课号="3"
方法二:
SELECT学号,成绩FROM选课WHERE课号IN("1","3")
相反条件查询:
SELECT学号,成绩FROM选课WHERE课号NOTIN("1","3")
8、列出所有98级学生的学生成绩情况。
SELECT*FROM选课WHERE学号LIKE"98%"
SELECT*FROM选课WHERE学号LIKE"98____"
相反条件查询:
SELECT*FROM选课WHERE学号NOTLIKE"98%"
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课号="1"
2、列出选修1号课的学生的学号、姓名及成绩。
SELECT学生.学号,姓名,成绩FROM学生S,选课XWHERES.学号=X.学号AND课号="1"
3、求出总分大于150的学生的学号、姓名及总成绩。
SELECT学生.学号,姓名,SUM(成绩)AS总成绩FROM学生,选课;
WHERE学生.学号=选课.学号GROUPBY选课.学号HAVINGSUM(成绩)>150
(二)自连接查询
1、列出那些专业相同的学生相应的姓名及专业信息。
2、求至少选修1号课和2号课的学生的学号。
SELECTX.学号FROM选课X,选课YWHEREX.学号=Y.学号ANDX.课号="1"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)),根据雇员关系列出上一级经理及其所领导的职员清单。
(教案中的例题)
(三)超连接
1、列出选修1号课的学生姓名及成绩。
方法一:
(使用简单连接查询格式)
SELECT姓名,成绩FROM学生,选课WHERE学生.学号=选课.学号AND课号="1"
方法二:
(使用内部连接格式)
SELECT姓名,成绩FROM学生INNERJOIN选课ON学生.学号=选课.学号WHERE课号="1"
方法三:
内部连接的INNER短语可以省略。
(与方法二等价)
SELECT姓名,成绩FROM学生JOIN选课ON学生.学号=选课.学号WHERE课号="1"
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号课的最低成绩要高的学生的学号和成绩。
方法一:
SELECT学号,成绩FROM选课WHERE课号="2"AND成绩>;
(SELECTMIN(成绩)FROM选课WHERE课号="1")
方法二:
ANY等价于SOME,所以可将ANY换成SOME。
SELECT学号,成绩FROM选课WHERE课号="2"AND成绩>ANY;
(SELECT成绩FROM选课WHERE课号="1")
2、求选修2号课的学生中,成绩比选修1号课的任何学生的成绩都要高的那些学生的学号和成绩。
方法一:
SELECT学号,成绩FROM选课WHERE课号="2"AND成绩>;
(SELECTMAX(成绩)FROM选课WHERE课号="1")
方法二:
SELECT学号,成绩FROM选课WHERE课号="2"AND成绩>ALL;
(SELECT成绩FROM选课WHERE课号="1")
(三)内外层互相关嵌套(外层依赖于内层的查询结果,内层依赖于外层来进一步查询)
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
姓名
C
8
性别
C
2
年龄
N
3
0
入学年月
D
允许空值
CREATETABLE研究生FREE(学号C(6),姓名C(8),性别C
(2),年龄N(3,0),入学年月DNULL)
2、假设已经建立了XSK数据库,在XSK中建立“学生1”表,其结构如表所示
字段名
字段类型
字段长度
小数位数
特殊要求
学号
C
6
主索引
姓名
C
8
不能为空
性别
C
2
年龄
N
3
0
年龄大于10小于45
是否党员
L
入学年月
D
默认值为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”表,其结构如表所示:
字段名
字段类型
字段长度
小数位数
特殊要求
课号
C
4
主索引
课程名
C
10
不能为空
学分
N
2
CREATETABLE课程1(课号C(4)PRIMARYKEY,课程名C(10)NOTNULL,学分N
(2))
4、在XSK数据库建立“选课1”表,其结构如表所示:
字段名
字段类型
字段长度
小数位数
特殊要求
学号
C
6
外索引与学生表建立关系
课号
C
4
外索引与课程表建立关系
成绩
N
5
2
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开课学期="1"OR开课学期="2"
3、删除“课程1”表中对开课学期字段的合法值约束,设置默认值为1。
ALTERTABLE课程1ALTER开课学期DROPCHECK
ALTERTABLE课程1ALTER开课学期SETDEFAULT"1"
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供应商号="S3"
3、检索出和职工E1、E3都有联系的北京的供应商信息。
SELECT*FROM供应商WHERE地址="北京"AND供应商号IN;
(SELECT供应商号FROM订购单WHERE职工号="E1")AND供应商号IN;
(SELECT供应商号FROM订购单WHERE职工号="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 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.求
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 语句 强化 练习题 答案