数据库语句练习及答案.docx
《数据库语句练习及答案.docx》由会员分享,可在线阅读,更多相关《数据库语句练习及答案.docx(23页珍藏版)》请在冰豆网上搜索。
数据库语句练习及答案
实验二
--1、年龄小于20的为豆蔻,20-30之间为弱冠,30-40之间为儿立,40-50之间为不惑,50-60知命,60-70,为花甲,其他为古稀
select*,
case
whenage<=20then'豆蔻'
whenage>20andage<30then'弱冠'
whenage>30andage<40then'儿立'
whenage>40andage<50then'不惑'
whenage>50andage<60then'知命'
whenage>60andage<70then'花甲'
else'古稀'
endclass_age
fromstudent
--查找以_开头的学生的姓名
select*fromstudent
wheresnamelike'[_]%'
--以%开头的学生的姓名,
select*fromstudent
wheresnamelike'[%]%'
--查找第一个字母是m或者n第二个字母为ykmb的学生的姓名,
select*fromstudent
wheresnamelike'[mn][ykmb]%'
--查找不是以m或者n开头且第二个字母为a到z之间任何一个字母的学生的姓名及具体息,
select*fromstudent
wheresnamelike'[^mm][a-z]%'
--查找不是以a到f字母为首字母的学生的信息。
select*fromstudent
wheresnamelike'[^a-f]%'
--3、练习各种连接的操作,诸如join、leftjion、rightjoin、fulljoin以及crossjoin的应用对A、B两个表进行连接。
select*fromAjoinBonA.Field_k=B.Field_k
select*fromAleftjoinBonA.Field_k=B.Field_k
select*fromArightjoinBonA.Field_k=B.Field_k
select*fromAfulljoinBonA.Field_k=B.Field_k
select*fromAcrossjoinB
--讲所有计算机学院的男同学信息显示出来并单独生成一个独立的表,表名字为jsjman。
select*intojsjman
fromstudent
wheresex='男'anddept='计算机学院'
实验二6
--【例4.1】查询员工表中所有员工的姓名和联系电话,可以写为:
select姓名,电话fromEmployees
--【例4.2】查询员工表中的所有记录,程序为:
select*fromEmployees
--【例4.3】查询进货表中的所有的生产厂商,去掉重复值,程序为:
selectdistinct生产厂商fromGoods
--【例4.4】查询进货表中商品名称、单价和数量的前4条记录,程序为:
selecttop4商品名称,零售价,数量fromGoods
--【例4.5】使用列的别名,查询员工表中所有记录的员工编号(别名为number),姓名(别名为name)和电话(别名为telephone),
select编号number,姓名name,电话telephonefromEmployees
--【例4.6】查询各件商品的进货总金额,可以写为:
select商品名称,进货价*数量fromGoods
--【例4.7】在Employees表中查询姓名为王峰的员工的联系电话,程序为:
select姓名,电话fromEmployeeswhere姓名='王峰'
--【例4.8】查询笔记本电脑的进货信息,程序为:
select*fromGoodswhere商品名称='笔记本电脑'
--【例4.9】查询在2005年1月1日以前销售的商品信息,可以写为:
select商品编号,数量,售出时间fromSellwhere售出时间<'2005-1-1'
--【例4.10】查询进货总金额小于10000元的商品名称,可以写为:
select商品名称fromGoodswhere进货价*数量<10000
--【例4.11】查询2005年1月1日以前进货且进货价大于1000元的商品,可以写为:
select商品名称fromGoodswhere进货时间<'2005-1-1'and进货价>1000
--【例4.12】查询“李”姓员工的基本信息,可以写为:
select*fromEmployeeswhere姓名like'李%'
--【例4.13】查询零售价格在2000到3000元之间的所有商品,可以写为:
select*fromGoodswhere零售价>=2000and零售价<=3000
--【例4.14】查询打印机、摄像机的进货价格,程序为:
select商品名称,进货价fromGoodswhere商品名称='打印机'or商品名称='摄像机'
--【例4.15】查询电话不为空的员工信息,可以写为:
select*fromEmployeeswhere电话isnotnull
--【例4.16】查询商品的进货价格并按从大到小排序,程序为:
select商品名称,进货价fromGoodsorderby进货价desc
--【例4.17】按照商品进货数量的升序排序,在同一数量内,将按照进货价的降序排列,程序为:
select商品名称,进货价,数量fromGoodsorderby数量,进货价desc
--【例4.18】将goods表中联想公司的产品生创建一个新表lxgoods,具体为:
select*intolxgoodsfromGoodswhere生产厂商='联想公司'
--【例4.19】查询财务部的员工人数,可以写为:
selectcount(部门)人数fromEmployeeswhere部门='财务部'
--【例4.20】查询商品编号为2的商品的销售数量,可以写为:
selectsum(数量)fromSellwhere商品编号='2'
--【例4.21】统计各部门的人数,可以写为:
select部门,count(部门)人数fromEmployeesgroupby部门
--【例4.22】对员工表按性别统计各部门人数,可以写为:
select性别,部门,count(部门)人数fromEmployeesgroupby性别,部门
--【例4.23】使用WITHCUBE,可以写为:
select性别,部门,count(部门)人数fromEmployeesgroupby性别,部门withcube
--【例4.24】使用WITHROLLUP,可以写为:
select性别,部门,count(部门)人数fromEmployeesgroupby性别,部门withrollup
--【例4.25】统计各部门的男性人数,可以写为:
select性别,部门,count(部门)人数fromEmployeeswhere性别='1'groupby性别,部门
--【例4.26】统计销售总数量,请写出相关的语句,程序执行结果为:
select售货员工编号,商品编号,数量fromSellcomputesum(数量)
--【例4.27】分别统计各员工的销售总数,可以写为:
select售货员工编号,商品编号,数量fromSellorderby售货员工编号
computesum(数量)by售货员工编号
--【例4.31】使用SQLServer联接形式,程序为:
SELECT销售编号,商品名称,sell.数量as销售数量FROMgoods,sell
WHEREgoods.商品编号*=sell.商品编号
实验三
--一、能进行列的别名,使用三种方法分别演示,并将自己的输入的语法保存成txt文件
selectclass学号fromstudent
selectclassas学号fromstudent
selectclass'学号'fromstudent
--二、查找计算机学院、教育系、化工学院 所有姓刘的且名字是两个字的学生所有信息
select*fromstudent
where(dept='计算机学院'ordept='教育系'ordept='化工学院')andsnamelike'刘_'
--三、查找计算机学院中所有不姓刘的学生的信息,查找选择所有选择001号课程学生的信息及成绩大于90分学生的信息
select*fromstudentwheredept='计算机学院'andsnamelike'^刘%'
select*fromstudent,scoreswherestudent.sn=scores.snandcn='001'andgrade>90
select*fromstudent,scoreswherestudent.sn=scores.snandsnamelike'[^刘_]'andcn='001'andgrade>90
--练习,交叉并笛卡尔成绩sql语句使用方法,通过a,b实现。
select*fromAcrossjoinB
select*fromaintersectselect*fromb
--四、查找以“生”开头,且最后一个为“学”的课程的详细情况
select*fromstudent,scores,course
wherestudent.sn=scores.snand=andcnamelike'[生]%[学]'
--五、查找选001号课程的平均成绩、最大成绩、最小成绩,选课的人数
selectavg(grade)平均成绩fromscoreswherecn='001'
selectmax(grade)最大成绩fromscoreswherecn='001'
selectmin(grade)最小成绩fromscoreswherecn='001'
selectcount(cn)选课的人数fromscoreswherecn='001'
--六、分别查找选择了某一门课程的学生的人数、分别统计各个系的总人数,分别显示各系男女生总人数,男生人数大于3个的系
selectcname,count(sn)fromcourse,scoreswhere=groupbycname
selectdept,count(sn)'人数'fromstudentgroupbydept
selectdept,sex,count(sn)fromstudentgroupbydept,sexorderbydept
selectdept,sex,count(sn)fromstudentwheresex='男'groupbydept,sexhavingcount(sn)>3
--七、统计计机学院选择了三门以上课程的学生的学号
selectstudent.sn,dept,count(cn)fromstudent,scores
wherestudent.sn=scores.snanddept='计算机学院'
groupbystudent.sn,depthavingcount(cn)>3
selectscores.sn,count(cn),student.dept
fromscores,studentwherestudent.dept='计算机学院'andscores.sn=student.sn
groupbyscores.sn,student.depthavingcount(cn)>3
--八、如何通过提供的student、scores、course三个表生成如下表ok,使用into语句
selectstudent.sn,sname,,gradeintookfromstudent,scores,course
wherescores.sn=student.snand=
--九、在上面新生成的表ok上增加course表中的可课程名字,
altertablesyy1
addcnamenvarchar(255)
insertintosyy1(student.sn,sname,,grade,cname)
selectstudent.sn,sname,,grade,ame
fromstudent,scores,course
wherestudent.sn=scores.snand=
--十、查询各系及学生数,最后求出共有多少系和多少学生?
selectdept,count(sn)fromstudentgroupbydeptcomputecount(dept),sum(count(sn))
--统计各门课程的平均分,要求显示课程名字与代号,具体如下:
select,cname,avg(grade)fromcourse,scoreswhere=
groupby,cnameorderbycn
--十一、groupby命令复杂应用。
--1、统计各个年龄段的人数,具体如下:
selectclass_age=
case
whenage<=20then'豆蔻'
whenage>20andage<30then'弱冠'
whenage>30andage<40then'儿立'
whenage>40andage<50then'不惑'
whenage>50andage<60then'知命'
whenage>60andage<70then'花甲'
else'古稀'
end,count(sn)
fromstudent
groupby
case
whenage<=20then'豆蔻'
whenage>20andage<30then'弱冠'
whenage>30andage<40then'儿立'
whenage>40andage<50then'不惑'
whenage>50andage<60then'知命'
whenage>60andage<70then'花甲'
else'古稀'
end
--2、对表countrysex统计各个国家男女的人数,及总人数,结果如下图,
selectcountry,
sum(casewhensex='男'thenpopulatEnd)'男性人口',
sum(casewhensex='女'thenpopulatEnd)'女性人口'
fromcountrysexgroupbycountry
selectcountry,
sum(casewhensex='男'thenpopulatEnd)'男性人口',
sum(casewhensex='女'thenpopulatEnd)'女性人口',
sum(casewhensex='男'thenpopulatend)+sum(casewhensex='女'thenpopulatend)'总人口'
fromcountrysexgroupbycountry
--4对country表中进行统计实现如下:
select
casecountry
when'中国'then'亚洲'
when'印度'then'亚洲'
when'日本'then'亚洲'
when'美国'then'北美洲'
when'加拿大'then'北美洲'
when'墨西哥'then'北美洲'
else'欧洲'end洲名,sum(population)人数
fromcountry
groupby
casecountry
when'中国'then'亚洲'
when'印度'then'亚洲'
when'日本'then'亚洲'
when'美国'then'北美洲'
when'加拿大'then'北美洲'
when'墨西哥'THEN'北美洲'
else'欧洲'end
实验四
--一、查找所有比3141201号学生成绩高的学生成绩
selectx.*fromscoresx,scoresy
wherey.sn='3141201'andx.grade>y.grade
orderbygrade
select*fromscoreswheresn='3141201'
select*fromscoreswheregrade>all
(selectgradefromscoreswheresn='3141201'
)orderbygrade
select*fromscores
wheregrade>any(selectgradefromscoreswheresn='3141201')orderbygrade
--二、分别查找选择了某一门课程的平均分
selectcn,avg(grade)fromscoresgroupbycn
--三、统计计算机学院选择了三门以上课程的学生的学号
selectscores.sn,count(scores.sn)fromscores,student
wherescores.sn=student.snanddept='计算机学院'
groupbyscores.snhavingcount(scores.sn)>3
--四、对学生的成绩scores进行等级分类,60以下不及格,90以上良好等
select*,等级=
case
whengrade<60then'不及格'
whengrade>60andgrade<70then'及格'
whengrade>70andgrade<80then'中等'
whengrade>80andgrade<90then'良好'
else'优秀'
end
fromscores
--五、计算学生某门课程与该门课程的平均分之差、与自己所有课程的平均分之差、
--查找某个同学的某一门课程小于该门课程的平均成绩的学生的信息
--计算学生某门课程与该门课程的平均分之差
selectx.sn,,x.grade,avg(y.grade)'pingjun',abs(x.grade-avg(y.grade))'与平均分之差'
fromscoresx,scoresy
where=
groupbyx.sn,,x.grade
--与自己所有课程的平均分之差
selectx.sn,,x.grade,avg(y.grade),abs(x.grade-avg(y.grade))
fromscoresx,scoresy
wherex.sn=y.sn
groupbyx.sn,,x.grade
--查找某个同学的某一门课程小于该门课程的平均成绩的学生的信息
selectx.sn,,x.grade,avg(y.grade)'课程平均分'
fromscoresx,scoresy
where=
groupbyx.sn,,x.gradehavingx.grade--六、统计每个系的男女人数,及每个系的总人数、各系合计人数,还有各个系总的男女生人数
selectdept,sex,count(sn)fromstudent
groupbydept,sexwithcube
书店
--七、查询定购书量大于等于8本的客户名称和书名
selectclient_name,book_name,sum(book_number)as'书籍总数'fromclients,book,orderform
whereorderform.client_id=clients.client_idandbook.book_id=orderform.book_id
groupbyclient_name,book_namehavingsum(book_number)>=8
--八、统计检索不同客户定购的各种书的总量和所有书的总量、
selectclient_name,book_name,sum(book_number)as'书籍总数'fromclients,book,orderform
whereorderform.client_id=clients.client_idandbook.book_id=orderform.book_id
groupbyclient_name,book_namewithrollup
selectclient_name,book_name,book_number书籍总数frombook,clients,orderform
wherebook.book_id=orderform.book_idandclients.client_id=orderform.client_id
orderbyclient_name
computesum(book_number)
selectclient_name,book_name,book_number书籍总数frombook,clients,orderform
wherebook.book_id=orderform.book_idandclients.client_id=orderform.client_id
orderbyclient_name
computesum(book_number)byclient_name
实验五
--一、查找女生人数大于10个的学院
selectdept,sex,count(sn)fromstude