数据库语句练习及答案

实验二-、年龄小于20的为豆蔻，20-30之间为弱冠，30-40之间为儿立，40-50之间为不惑，50-60知命， 60-70，为花甲，其他为古稀 select *,case when age20 and age30 and age40 and age50 and age60 and age70 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 mnykmb%-查找不是以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.Field_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=计算机学院实验二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）和电话（别名为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日以前销售的商品信息，可以写为：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 进货时间1000-【例4.12】 查询“李”姓员工的基本信息，可以写为：select *from Employees where 姓名 like 李%-【例4.13】 查询零售价格在2000到3000元之间的所有商品，可以写为：select *from Goods where 零售价=2000 and 零售价90select *from student,scores where student.sn=scores.sn and sname like刘_ and cn=001 and grade90-练习，交叉并笛卡尔成绩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 where cn=001select max(grade)最大成绩 from scores where cn=001select min(grade)最小成绩 from scores where cn=001select count(cn)选课的人数 from scores where cn=001-六、分别查找选择了某一门课程的学生的人数、分别统计各个系的总人数，分别显示各系男女生总人数，男生人数大于3个的系select cname,count(sn) from course,scores where = group by 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 student,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-八、如何通过提供的 student、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,scores,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 class_age= case when age20 and age30 and age40 and age50 and age60 and age70 then 花甲 else 古稀 end ,count(sn) from studentgroup bycase when age20 and age30 and age40 and age50 and age60 and agey.gradeorder by gradeselect *from scores where sn=3141201select *from scores where gradeall(select grade from scores where sn=3141201)order by gradeselect *from scoreswhere gradeany(select grade from scores where sn=3141201)order by grade-二、分别查找选择了某一门课程的平均分 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 *, 等级= case when grade60 and grade70 and grade80 and grade90 then 良好 else 优秀endfrom scores-五、 计算学生某门课程与该门课程的平均分之差、与自己所有课程的平均分之差、-查找某个同学的某一门课程小于该门课程的平均成绩的学生的信息-计算学生某门课程与该门课程的平均分之差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-查找某个同学的某一门课程小于该门课程的平均成绩的学生的信息select x.sn,,x.grade,avg(y.grade)课程平均分from scores x,scores ywhere =group by x.sn,,x.grade having x.grade=8-八、统计检索不同客户定购的各种书的总量和所有书的总量、select client_name,book_name,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 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 order 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) 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 grade90-三、查找所有与3141206号学生选同一门课程同学的学号。select sn from scoreswhere cn=any(select cn from scores where sn=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=student.sn and gradeall(select grade from scores,studentwhere scores.sn=student.sn and dept=计算机学院)select *from studentwhere student.sn in(select scores.sn from scores where scores.gradeall(select scores.grade from scores,student where student.sn=scores.sn and student.dept=计算机学院)-六、对于给定的数据表中的，分别求下列的除法运算，即RS，R1S1，R2S2，scc，stud_coursecourse的结果。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(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 from 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,filename=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包含个日志文件,分别为：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.mdf,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,size=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,size=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=80mb,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%),(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，stud10.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=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=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,filegrowth=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,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表，包括字段为 学号 , 姓名 , 专业名, 性别 , 出生年月 , 总学分 , 备注，要求：总学分在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 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 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的实体完整性约束；学生进行选课时候，学生与课程必须存在，方能进行选课，即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 char(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_renames,stualter table stu drop column sexalter table stu add class char(5)alter table stu alter column class varchar(20)-将综合数据库zonghe中student中的计算机学院学生的数据通过语句插入到刚刚建立好的stu表中；-由于表stude中是department,stu表中是dept,所以修改exec sp_renamestude.department,dept-由于stude表的数据类型与stu表的数据类型不一样，所以修改数据类型alter table stu alter column sn floatalter table stu alter column sname nvarchar(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 grade90 and dept=计算机学院and sex=男 and stude.sn=scores.sn-删除成绩为81分的同学的信息；delete from scores where grade=81-将zonghe数据库修改为test，删除数据库xxx，删除表xs。-由于这里的zonghe 数据库名字叫st，所以将 st改为testalter database st modify name=test drop database ok drop table xs 实验七-1、建立能查找所有计算机学院学生信息的视图，包括学生的学号，选课信息等（主要是联合查询）create view vi1as select student.sn,sname