数据库语句练习及答案

1、实验二-、年龄小于20的为豆蔻，20-30之间为弱冠，30-40之间为儿立，40-50之间为不惑，50-60知命， 60-70，为花甲，其他为古稀 select *,case when age<=20 then '豆蔻' when age>20 and age<30 then '弱冠' when age>30 and age<40 then '儿立' when age>40 and age<50 then '不惑' when age>50 and age<60 then 

2、9;知命' when age>60 and age<70 then '花甲' else '古稀'end class_agefrom student-查找以_开头的学生的姓名 select *from student where sname like '_%'-以%开头的学生的姓名， select *from student where sname like '%'-查找第一个字母是m或者n第二个字母为ykmb的学生的姓名， select *from student where sname like 'm

3、nykmb%'-查找不是以m或者n开头且第二个字母为a到z之间任何一个字母的学生的姓名及具体息， select *from student where sname like 'mma-z%'-查找不是以a到f字母为首字母的学生的信息。 select *from student where sname like 'a-f%'-3、练习各种连接的操作，诸如 join 、left jion、right join、full join以及crossjoin的应用对A、B两个表进行连接。select *from A join B on A.Field_k=B.Fie

4、ld_kselect *from A left join B on A.Field_k=B.Field_kselect *from A right join B on A.Field_k=B.Field_kselect *from A full join B on A.Field_k=B.Field_kselect *from A cross join B-讲所有计算机学院的男同学信息显示出来并单独生成一个独立的表,表名字为jsjman。select *into jsjmanfrom studentwhere sex='男' and dept='计算机学院'实验

5、二6-【例4.1】 查询员工表中所有员工的姓名和联系电话，可以写为：select 姓名,电话 from Employees-【例4.2】 查询员工表中的所有记录，程序为：select *from Employees-【例4.3】 查询进货表中的所有的生产厂商，去掉重复值，程序为：select distinct 生产厂商 from Goods-【例4.4】 查询进货表中商品名称、单价和数量的前4条记录，程序为：select top 4 商品名称,零售价,数量 from Goods-【例4.5】 使用列的别名，查询员工表中所有记录的员工编号（别名为number），姓名（别名为name）和电话（别名

6、为telephone），select 编号 number,姓名 name, 电话 telephone from Employees-【例4.6】 查询各件商品的进货总金额，可以写为：select 商品名称,进货价*数量 from Goods-【例4.7】 在Employees表中查询姓名为王峰的员工的联系电话，程序为：select 姓名,电话 from Employees where 姓名='王峰'-【例4.8】 查询笔记本电脑的进货信息，程序为：select *from Goods where 商品名称='笔记本电脑'-【例4.9】 查询在2005年1月1日以

7、前销售的商品信息，可以写为：select 商品编号,数量,售出时间 from Sell where 售出时间<'2005-1-1'-【例4.10】 查询进货总金额小于10000元的商品名称，可以写为：select 商品名称 from Goods where 进货价*数量<10000-【例4.11】 查询2005年1月1日以前进货且进货价大于1000元的商品，可以写为：select 商品名称 from Goods where 进货时间<'2005-1-1' and 进货价>1000-【例4.12】 查询“李”姓员工的基本信息，可以写为：se

8、lect *from Employees where 姓名 like '李%'-【例4.13】 查询零售价格在2000到3000元之间的所有商品，可以写为：select *from Goods where 零售价>=2000 and 零售价<=3000-【例4.14】 查询打印机、摄像机的进货价格，程序为：select 商品名称,进货价 from Goods where 商品名称='打印机' or 商品名称='摄像机'-【例4.15】 查询电话不为空的员工信息，可以写为：select *from Employees where 电话

9、is not null-【例4.16】 查询商品的进货价格并按从大到小排序，程序为：select 商品名称,进货价 from Goods order by 进货价 desc-【例4.17】 按照商品进货数量的升序排序，在同一数量内，将按照进货价的降序排列，程序为： select 商品名称,进货价,数量 from Goods order by 数量,进货价 desc-【例4.18】将goods表中联想公司的产品生创建一个新表 lxgoods，具体为：select *into lxgoods from Goods where 生产厂商='联想公司'-【例4.19】 查询财务部的员工

10、人数，可以写为：select count(部门) 人数 from Employees where 部门='财务部'-【例4.20】 查询商品编号为2的商品的销售数量，可以写为：select sum(数量) from Sell where 商品编号='2'-【例4.21】 统计各部门的人数，可以写为：select 部门,count(部门) 人数 from Employees group by 部门-【例4.22】 对员工表按性别统计各部门人数，可以写为：select 性别,部门,count(部门) 人数 from Employees group by 性别,部门-

11、【例4.23】 使用WITH CUBE，可以写为：select 性别,部门,count(部门) 人数 from Employees group by 性别,部门 with cube -【例4.24】 使用WITH ROLLUP，可以写为：select 性别,部门,count(部门) 人数 from Employees group by 性别,部门 with rollup-【例4.25】 统计各部门的男性人数，可以写为：select 性别,部门,count(部门) 人数 from Employees where 性别='1' group by 性别,部门-【例4.26】 统计销售

12、总数量，请写出相关的语句 ，程序执行结果为：select 售货员工编号,商品编号,数量 from Sell compute sum(数量)-【例4.27】 分别统计各员工的销售总数，可以写为：select 售货员工编号,商品编号,数量 from Sell order by 售货员工编号compute sum(数量) by 售货员工编号-【例4.31】 使用SQL Server联接形式，程序为：SELECT 销售编号,商品名称,sell.数量 as 销售数量 FROM goods,sell WHERE goods.商品编号*=sell.商品编号实验三-一、能进行列的别名，使用三种方法分别演示，并

13、将自己的输入的语法保存成文件select class 学号 from studentselect class as 学号 from studentselect class '学号' from student-二、查找计算机学院、教育系、化工学院所有姓刘的且名字是两个字的学生所有信息select *from studentwhere (dept='计算机学院' or dept='教育系' or dept='化工学院') and sname like '刘_' -三、查找计算机学院中所有不姓刘的学生的信息，查找选择所有

14、选择001号课程学生的信息及 成绩大于分学生的信息select *from student where dept='计算机学院' and sname like '刘%'select *from student,scores where student.sn=scores.sn and cn='001' and grade>90select *from student,scores where student.sn=scores.sn and sname like'刘_' and cn='001' and gr

15、ade>90-练习，交叉并笛卡尔成绩sql语句使用方法，通过 a，b实现。select *from A cross join Bselect * from a intersect select * from b-四、查找以“生”开头，且最后一个为“学”的课程的详细情况select *from student,scores,coursewhere student.sn=scores.sn and = and cname like '生%学'-五、查找选号课程的平均成绩、最大成绩、最小成绩，选课的人数select avg(grade)平均成绩 from scores wher

16、e cn='001'select max(grade)最大成绩 from scores where cn='001'select min(grade)最小成绩 from scores where cn='001'select count(cn)选课的人数 from scores where cn='001'-六、分别查找选择了某一门课程的学生的人数、分别统计各个系的总人数，分别显示各系男女生总人数，男生人数大于3个的系select cname,count(sn) from course,scores where = group b

17、y cnameselect dept,count(sn)'人数' from student group by deptselect dept,sex,count(sn) from student group by dept,sex order by deptselect dept,sex,count(sn) from student where sex='男' group by dept,sex having count(sn)>3-七、统计计机学院选择了三门以上课程的学生的学号select student.sn,dept,count(cn) from s

18、tudent,scores where student.sn=scores.sn and dept='计算机学院'group by student.sn,dept having count(cn)>3select scores.sn,count(cn),student.dept from scores,student where student.dept='计算机学院' and scores.sn=student.sn group by scores.sn,student.dept having count(cn)>3-八、如何通过提供的 stude

19、nt、scores、course三个表生成如下表ok,使用into 语句select student.sn,sname,grade into ok from student,scores,coursewhere scores.sn=student.sn and =-九、在上面新生成的表ok上增加course表中的可课程名字，alter table syy1add cname nvarchar(255)insert into syy1(student.sn,sname,grade,cname)select student.sn,sname,grade,ame from student,score

20、s,coursewhere student.sn=scores.sn and =-十、查询各系及学生数，最后求出共有多少系和多少学生？select dept,count(sn) from student group by dept compute count(dept),sum(count(sn)-统计各门课程的平均分，要求显示课程名字与代号，具体如下：select ,cname,avg(grade) from course,scores where =group by ,cname order by cn-十一、groupby命令复杂应用。-1、统计各个年龄段的人数，具体如下： select

21、 class_age= case when age<=20 then '豆蔻' when age>20 and age<30 then '弱冠' when age>30 and age<40 then '儿立' when age>40 and age<50 then '不惑' when age>50 and age<60 then '知命' when age>60 and age<70 then '花甲' else '古稀&#

22、39; end ,count(sn) from studentgroup bycase when age<=20 then '豆蔻' when age>20 and age<30 then '弱冠' when age>30 and age<40 then '儿立' when age>40 and age<50 then '不惑' when age>50 and age<60 then '知命' when age>60 and age<70 then

23、'花甲' else '古稀'end-2、对表countrysex统计各个国家男女的人数，及总人数，结果如下图， select country, sum(case when sex='男' then populat End) '男性人口', sum(case when sex='女' then populat End) '女性人口'from countrysex group by country select country, sum(case when sex='男' then po

24、pulat End) '男性人口', sum(case when sex='女' then populat End) '女性人口', sum(case when sex = '男' then populat end) +sum( case when sex = '女' then populat end) '总人口'from countrysex group by country-4 对country表中进行统计实现如下：select case country when '中国' th

25、en '亚洲' when '印度' then '亚洲' when'日本' then '亚洲' when '美国' then '北美洲' when '加拿大' then '北美洲' when '墨西哥' then '北美洲' else'欧洲' end 洲名,sum(population) 人数from country group by case country when '中国' then

26、'亚洲' when '印度' then '亚洲' when '日本' then '亚洲' when '美国' then '北美洲' when '加拿大' then '北美洲' when '墨西哥' THEN '北美洲' else '欧洲' end实验四-一、 查找所有比3141201号学生成绩高的学生成绩select x.* from scores x,scores ywhere y.sn='31

27、41201' and x.grade>y.gradeorder by gradeselect *from scores where sn='3141201'select *from scores where grade>all(select grade from scores where sn='3141201')order by gradeselect *from scoreswhere grade>any(select grade from scores where sn='3141201')order by gra

28、de-二、分别查找选择了某一门课程的平均分 select cn,avg(grade) from scores group by cn -三、统计计算机学院选择了三门以上课程的学生的学号 select scores.sn,count(scores.sn) from scores,student where scores.sn=student.sn and dept='计算机学院' group by scores.sn having count(scores.sn)>3 -四、 对学生的成绩 scores进行等级分类，60以下不及格，90以上良好等 select *, 等级=

29、 case when grade<60 then '不及格' when grade>60 and grade<70 then '及格' when grade>70 and grade<80 then '中等' when grade>80 and grade<90 then '良好' else '优秀'endfrom scores-五、 计算学生某门课程与该门课程的平均分之差、与自己所有课程的平均分之差、-查找某个同学的某一门课程小于该门课程的平均成绩的学生的信息-计算学生某

30、门课程与该门课程的平均分之差select x.sn,x.grade,avg(y.grade)'pingjun',abs(x.grade-avg(y.grade)'与平均分之差'from scores x,scores ywhere =group by x.sn,x.grade-与自己所有课程的平均分之差select x.sn,x.grade,avg(y.grade),abs(x.grade-avg(y.grade)from scores x,scores ywhere x.sn=y.sngroup by x.sn,x.grade-查找某个同学的某一门课程小于该门

31、课程的平均成绩的学生的信息select x.sn,x.grade,avg(y.grade)'课程平均分'from scores x,scores ywhere =group by x.sn,x.grade having x.grade<avg(y.grade)-六、 统计每个系的男女人数，及每个系的总人数、各系合计人数，还有各个系总的男女生人数select dept,sex,count(sn) from studentgroup by dept,sex with cube 书店-七、查询定购书量大于等于8本的客户名称和书名select client_name,book_n

32、ame,sum(book_number) as '书籍总数 'from clients,book ,orderformwhere orderform.client_id=clients.client_id and book.book_id=orderform.book_id group by client_name,book_name having sum(book_number)>=8-八、统计检索不同客户定购的各种书的总量和所有书的总量、select client_name,book_name,sum(book_number) as '书籍总数 'fr

33、om clients,book ,orderformwhere orderform.client_id=clients.client_id and book.book_id=orderform.book_id group by client_name,book_name with rollupselect client_name,book_name,book_number 书籍总数 from book,clients,orderformwhere book.book_id=orderform.book_id and clients.client_id=orderform.client_id o

34、rder by client_name compute sum(book_number) select client_name,book_name,book_number 书籍总数 from book,clients,orderformwhere book.book_id=orderform.book_id and clients.client_id=orderform.client_id order by client_namecompute sum(book_number) by client_name实验五-一、查找女生人数大于10个的学院select dept,sex,count(sn

35、) from studentwhere sex='女'group by dept,sex having count(sn)>10-二、查找选择所有选择001号课程学生的信息及 成绩大于分学生的信息select *from scores,course,studentwhere = and scores.sn=student.sn and =001 and grade>90-三、查找所有与3141206号学生选同一门课程同学的学号。select sn from scoreswhere cn=any(select cn from scores where sn='

36、3141206')-四、查找与3141201号学生选课程有相同课程的学生的信息。?select student.sn,sname,cname from scores,course,studentwhere = and scores.sn=student.sn and =any(select from scores,coursewhere = and sn='3141201')-五、查找所有比计算机学院学生成绩高的学生信息select scores.sn,sname,sex,age,dept,class from scores,studentwhere scores.sn

37、=student.sn and grade>all(select grade from scores,studentwhere scores.sn=student.sn and dept='计算机学院')select *from studentwhere student.sn in(select scores.sn from scores where scores.grade>all(select scores.grade from scores,student where student.sn=scores.sn and student.dept='计算机

38、学院')-六、对于给定的数据表中的，分别求下列的除法运算，即R÷S，R1÷S1，R2÷S2，sc÷c，stud_course÷course的结果。select *from Rwhere not exists (select s.b,s.c from s where s.b not in (select R.c from R) and s.c not in (select R.c from R)select *from _R1where not exists(select _s1.c from _s1 where _s1.c not in

39、(select _R1.c from _R1)select * from _R2where not exists(select _S2.c,_S2.b from _s2 where _S2.c not in(select _R2.c from _R2) and _S2.b not in(select _R2.b from _R2) )select * from scwhere not exists(select ame,c.grade from c where ame not in(select ame from sc) and c.grade not in(select sc.grade f

40、rom sc) )-七、查找与3141201号学生所选课程完全相同的学生的信息。select sn from student s where not exists (select * from scores c where not in (select from scores c2 where s.sn='3141201') )实验六-一、创建一数据库xx，其中包含一个初始为10mb，增长为10mb，最大为100mb的数据库文件，与一个相应的日志文件，增长方式为10%文件。create database xxon primary(name='hello1',fi

41、lename='D:ok1.mdf',size=10mb,maxsize=100mb)log on(name='hello2',filename='D:ok2.ldf',size=10mb,maxsize=100mb,filegrowth=10%)-二、创建一个包含三个文件组的数据库文件 xxx，其中主文件组包含两个文件分别为stud1，stud2.test1文件组包含stud3，stud4.test2文件组包含stud5，stud6.test3文件组包含stud7，stud8.每个文件的增长方式为10%，起始大小为10mb，最大为80mb包含个

42、日志文件,分别为：rizhi1-rizhi8.ldf。create database xxxon primary(name='stud1',filename='D:stud1.mdf',size=10mb,maxsize=80mb,filegrowth=10%),(name='stud2',filename='D:stud2.mdf',size=10mb,maxsize=80mb,filegrowth=10%),filegroup test1(name='stud3',filename='D:stud3.m

43、df',size=10mb,maxsize=80mb,filegrowth=10%),(name='stud4',filename='D:stud4.mdf',size=10mb,maxsize=80mb,filegrowth=10%),filegroup test2(name='stud5',filename='D:stud5.mdf',size=10mb,maxsize=80mb,filegrowth=10%),(name='stud6',filename='D:stud6.mdf',s

44、ize=10mb,maxsize=80mb,filegrowth=10%),filegroup test3(name='stud7',filename='D:stud7.mdf',size=10mb,maxsize=80mb,filegrowth=10%),(name='stud8',filename='D:stud8.mdf',size=10mb,maxsize=80mb,filegrowth=10%)log on(name='rizhi1',filename='D:rizhi1.ldf',siz

45、e=10mb,maxsize=80mb,filegrowth=10%),(name='rizhi2',filename='D:rizhi2.1df',size=10mb,maxsize=80mb,filegrowth=10%),(name='rizhi3',filename='D:rizhi3.1df',size=10mb,maxsize=80mb,filegrowth=10%),(name='rizhi4',filename='D:rizhi4.1df',size=10mb,maxsize=80m

46、b,filegrowth=10%),(name='rizhi5',filename='D:rizhi5.1df',size=10mb,maxsize=80mb,filegrowth=10%),(name='rizhi6',filename='D:rizhi6.1df',size=10mb,maxsize=80mb,filegrowth=10%),(name='rizhi7',filename='D:rizhi7.1df',size=10mb,maxsize=80mb,filegrowth=10%),

47、(name='rizhi8',filename='D:rizhi8.1df',size=10mb,maxsize=80mb,filegrowth=10%)-三、能修改指定的数据库xxx文件名为ok，stud1文件名为test1，rizhi1改为log1，stud2 为test2，增加数据文件stud9,增加日志文件rizhi9，增加文件组test4 ，然后向test4中增加文件stud10，stud11，修改stud5，stud6的大小，即初始大小分别为30mb，最大为50mb增长为10mb，删除stud9，stud10，删除日至文件stud9.ldf，stud

48、10.ldf，删除test4文件组。-能修改指定的数据库xxx文件名为okalter database xxx modify name=ok-stud1文件名为test1alter database ok modify file( name='stud1', newname='test1')-rizhi1改为log1alter database ok modify file( name='zizhi1', newname='log1')-stud2 为test2alter database ok modify file( name

49、='stud2', newname='test2')-增加数据文件stud9,增加日志文件rizhi9alter database ok add file (name='stud9',filename='D:stud9.mdf',size=10mb,maxsize=80mb,filegrowth=10%)-增加日志文件rizhi9alter database ok add log file(name='rizhi9',filename='D:rizhi9.ldf',size=10mb,maxsize

50、=80mb,filegrowth=10%)-增加文件组test4 ，然后向test4中增加文件stud10，stud11，alter database okadd filegroup test4alter database ok add file(name='stud10',filename='D:stud10.mdf',size=10mb,maxsize=80mb,filegrowth=10%),(name='stud11',filename='D:stud11.mdf',size=10mb,maxsize=80mb,fileg

51、rowth=10%)to filegroup test4-修改stud5，stud6的大小，即初始大小分别为30mb，最大为50mb增长为10mb，删除stud9，stud10，alter database okmodify file(name='stud5',filename='D:stud5.mdf',size=30mb,maxsize=50mb,filegrowth=10mb)alter database okmodify file(name='stud6',filename='D:stud6.mdf',size=30mb,

52、maxsize=50mb,filegrowth=10mb)-删除stud9，stud10alter database okremove file stud9alter database okremove file stud10-删除日至文件stud9.ldf，stud10.ldf，alter database okremove file zizhi9alter database okremove file zizhi10删除test4文件组alter database okremove filegroup test4-四、能进在xxx数据库中创建 XS表，包括字段为 学号 , 姓名 , 专业名

53、, 性别 , 出生年月 , 总学分 , 备注，要求：总学分在2-10分之间，性别为男女，学号在0001-0099之间（用语句实现约束）。use okcreate table xs( sn int constraint ck_sn check(sn between 0001 and 0099), sname char(10), dept char(10), sex char(2)constraint ck_sex check(sex='男'or sex='女'), nianyue tinyint, score tinyint constraint ck_score

54、 check(score between 2 and 10 ), beizhu char(10)-五、在上述XS表中增加一列ok，属性为char，然后将其重命名为 addr'，修改其字段的属性为tiny int的，删除上述XS表 中的addr'，修改b表xs的名字为stud.-增加一列ok，属性为charuse okalter table xsadd ok char-然后将其重命名为 addr'，use okalter table xsexec sp_rename 'xs.ok','addr'-修改其字段的属性为tiny int的use

55、okalter table xsalter column addr tinyint-删除上述XS表 中的addr'，修改b表xs的名字为stud.use okalter table xsdrop column addr-修改b表xs的名字为stud.exec sp_rename 'xs','stud'-六、创建学生表s（sn，sname，sex，age，depte），课程表c（cn，cname），选课成绩表sc（sn，cn，grade，teacherno）。具体要求：保证s（sn）中sn的实体完整性约束；学生进行选课时候，学生与课程必须存在，方能进行选课

56、，即sc表中的sn必须为s表中的学生，而sc表中的cn必须为c表中的已经设立的课程；实现删除学生表s中的学生sn以及课程表中的cn课程时候，选课表sc中的相关记录必须同时自动将其删除（如果将s表中的sn或者c表中的cn删除后，在sc表中的相应数据为空如何实现？在没有删除sc表的情况下禁止删除s表中sn或者c表中的cn如何实现？）。create table s( sn int primary key, sname char(8), sex char(2), age smallint, dept char(10)create table c( cn int primary key, cname c

57、har(10)create table sc( sn int references s(sn), cn int references c(cn), grade int, teacherno varchar(10)-七、实现将表s修改为stu，删除sex列，增加class列，类型为char（5），将增加的class列的数据类型修改为varchar（20），将class列名修改为clas。exec sp_rename's','stu'alter table stu drop column sexalter table stu add class char(5)alt

58、er table stu alter column class varchar(20)-将综合数据库zonghe中student中的计算机学院学生的数据通过语句插入到刚刚建立好的stu表中；-由于表stude中是department,stu表中是dept,所以修改exec sp_rename'stude.department','dept'-由于stude表的数据类型与stu表的数据类型不一样，所以修改数据类型alter table stu alter column sn floatalter table stu alter column sname nvarc

59、har(MAX)alter table stu alter column age floatalter table stu alter column dept nvarchar(MAX)alter table stu alter column class nvarchar(MAX)insert into stu select sn,sname,age,dept,class from st.dbo.stude where dept='计算机学院'-将计算机学院所有男生的成绩小于80分的增加1分，大于90分的减少1分；select stude.sn,dept,grade+1from stude,scoreswhere grade<80 and dept='计算机学院'and sex='男' and stude.s