数据库语句练习及答案.docx
《数据库语句练习及答案.docx》由会员分享，可在线阅读，更多相关《数据库语句练习及答案.docx（29页珍藏版）》请在冰点文库上搜索。
数据库语句练习及答案
实验二
--１、年龄小于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.商品编号
实验三
--一、能进行列的别名，使用三种方法分别演示，并将自己的输入的语法保存成ｔｘｔ文件
selectclass学号fromstudent
selectclassas学号fromstudent
selectclass'学号'fromstudent
--二、查找计算机学院、教育系、化工学院 所有姓刘的且名字是两个字的学生所有信息
select*fromstudent
where（dept='计算机学院'ordept='教育系'ordept='化工学院'）andsnamelike'刘_'
--三、查找计算机学院中所有不姓刘的学生的信息，查找选择所有选择001号课程学生的信息及成绩大于９０分学生的信息
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'[生]%[学]'
--五、查找选００１号课程的平均成绩、最大成绩、最小成绩，选课的人数
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
升级会员 