2022年SQLserver查询语句练习题 .pdf

《2022年SQLserver查询语句练习题 .pdf》由会员分享，可在线阅读，更多相关《2022年SQLserver查询语句练习题 .pdf（7页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQL server 查询语句练习题用 SQL 语句创建四个表：create database tongji go use tongji go create table student ( Sno varchar(20) not null primary key ,-学号Sname varchar(20) not null,-学生姓名Ssex varchar(20) not null, -学生性别Sbirthday datetime,-学生出生年月Class varchar(20)-学生所在班级) go create table teacher-老师( Tno varchar(20) not

2、null primary key ,-教工编号（主码）Tname varchar(20) not null,-教工姓名Tsex varchar(20) not null, -教工性别Tbirthday datetime,-教工出生年月Prof varchar(20),-职称Depart varchar(20) not null-教工所在部门) go create table Course-课程( Cno varchar(20) not null primary key ,-课程号Cname varchar(20) not null,-课程名称Tno varchar(20) not null r

3、eferences teacher(Tno), -教工编号（外码）) go create table Score-分数( Sno varchar(20) not null references student(Sno), -学号（外码）Cno varchar(20) not null references Course(Cno), -课程号（外码）primary key(Sno,Cno), Degree Decimal(4,1),-成绩) 表中数据如下：表（一） Student 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名

4、师精心整理 - - - - - - - 第 1 页，共 7 页 - - - - - - - - - Sno Sname Ssex Sbirthday class 108 曾华男1977-09-01 95033 105 匡明男1975-10-02 95031 107 王丽女1976-01-23 95033 101 李军男1976-02-20 95033 109 王芳女1975-02-10 95031 103 陆君男1974-06-03 95031 表（二） Course Cno Cname Tno 3-105 计算机导论825 3-245 操作系统804 6-166 数字电路856 9-888

5、高等数学831 表（三） Score Sno Cno Degree 103 3-245 86 105 3-245 75 109 3-245 68 103 3-105 92 105 3-105 88 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 7 页 - - - - - - - - - 109 3-105 76 101 3-105 64 107 3-105 91 108 3-105 78 101 6-166 85 107 6-166 79 108 6-166 81 表（

6、四） Teacher Tno Tname Tsex Tbirthday Prof Depart 804 李诚男1958-12-02 副教授计算机系856 张旭男1969-03-12 讲师电子工程系825 王萍女1972-05-05 助教计算机系831 刘冰女1977-08-14 助教电子工程系1 、 查询 Student表中的所有记录的Sname、 Ssex和 Class列。select Sname,Ssex,Class from student 2 、 查询教师所有的单位即不重复的Depart列。select distinct depart from teacher 3 、 查询 Stude

7、nt表的所有记录。select * from student 4 、 查询 Score表中成绩在60 到 80之间的所有记录。select * from score where degree between 60 and 80 5 、 查询 Score表中成绩为85 ，86或 88的记录。select * from score where degree in(85,86,88) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 7 页 - - - - - - - - - 6

8、 、 查询 Student表中 “ 95031 ” 班或性别为 “ 女” 的同学记录。select * from student where class=95031 or Ssex=女 7 、 以 Class降序查询Student表的所有记录。select * from student order by class desc 8 、 以 Cno升序、 Degree降序查询Score表的所有记录。select * from score order by cno,degree desc 9 、 查询 “ 95031 ” 班的学生人数。select count(*) from student whe

9、re class=95031 10 、 查询 Score表中的最高分的学生学号和课程号。（子查询或者排序）select sno,cno from score where degree =(select max(degree) from score) 11 、 查询每门课的平均成绩。select cno,avg(degree) from score group by cno 12 、查询 Score表中至少有5 名学生选修的并以3 开头的课程的平均分数。select avg(degree) from score where cno like3% group by cno having count

10、(*)=5 select avg(degree) from score group by cno having count(*)=5 and cno like3% 13 、查询分数大于70 ，小于 90的 Sno列。select sno from score where degree70 and degree(select degree from score where sno=109 and cno=3-105) select * from score where cno=3-105 and degree(select max(degree) from score where sno=109

11、) 20 、查询 score中选学多门课程的同学中分数为非最高分成绩的记录。select * from score a where sno in (select sno from score group by sno having count(*)1) and degree (select degree from score where sno=109 and cno=3-105) 22 、查询和学号为108的同学同年出生的所有学生的Sno 、Sname和 Sbirthday列。select * from student where YEAR(sbirthday) = (select YEAR

12、(sbirthday) from student where sno=108) 23 、查询 “ 张旭 “ 教师任课的学生成绩。select * from score where cno in(select cno from course where tno in(select tno from teacher where tname=张旭 ) 24 、查询选修某课程的同学人数多于5 人的教师姓名。select tname from teacher where tno in(select tno from course where cno in(select cno from score gr

13、oup by cno having count(*)=5) 25 、查询 95033班和 95031班全体学生的记录。select * from student where class in(95033,95031) 26 、查询存在有85 分以上成绩的课程Cno.select distinct cno from score where degree85 27 、查询出 “ 计算机系 “ 教师所教课程的成绩表。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 7 页 - -

14、 - - - - - - - select * from score where cno in(select cno from course where tno in(select tno from teacher where depart=计算机系 ) 28 、查询 “ 计算机系 ” 与“ 电子工程系 “ 不同职称的教师的Tname和 Prof。select tname,prof from teacher where depart=计算机系 and prof not in(select prof from teacher where depart=电子工程系)union select tna

15、me,prof from teacher where depart=电子工程系 and prof not in(select prof from teacher where depart=计算机系 ) select tname,prof from teacher a where prof not in(select prof from teacher b where b.depart != a.depart) 29 、查询选修编号为“3- 105 “课程且成绩至少高于选修编号为“3- 245 ”的同学的Cno 、Sno和 Degree,并按 Degree从高到低次序排序。select * fr

16、om score where cno=3-105 and degreeany(select degree from score where cno=3-245) 30 、查询选修编号为“3- 105 ”且成绩高于选修编号为“3-245 ”课程的同学的Cno 、Sno和Degree.select * from score where cno=3-105 and degreeany(select degree from score where cno=3-245) 31 、 查询所有教师和同学的name、sex和 birthday.select sname,ssex,sbirthday from

17、student union select tname,tsex,tbirthday from teacher 32 、查询所有 “ 女” 教师和 “ 女” 同学的 name、sex和 birthday.select sname,ssex,sbirthday from student where ssex=女 union select tname,tsex,tbirthday from teacher where tsex=女 33 、 查询成绩比该课程平均成绩低的同学的成绩表。select * from score a where degree1 37 、查询 Student表中不姓 “ 王”

18、 的同学记录。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 7 页 - - - - - - - - - select * from student where sname not like 王 % 38 、查询 Student表中每个学生的姓名和年龄。select sname,YEAR(getdate()-YEAR(sbirthday) from student 39 、查询 Student表中最大和最小的Sbirthday日期值。select max(sbirthda

19、y),min(sbirthday) from student 40 、以班号和年龄从大到小的顺序查询Student表中的全部记录。select * from student order by class desc,sbirthday asc 41 、查询 “ 男” 教师及其所上的课程。elect cname from course where tno in(select tno from teacher where tsex=男) select tname,cname from course,teacher where course.tno = teacher.tno and teacher.

20、tsex=男 42 、查询最高分同学的Sno 、Cno和 Degree列。select * from score where degree = (select max(degree) from score) select top 1 * from score order by degree desc 43 、查询和 “ 李军 ” 同性别的所有同学的Sname.select sname from student where ssex=(select ssex from student where sname=李军 ) 44 、查询和 “ 李军 ” 同性别并同班的同学Sname.select sn

21、ame from student where ssex=(select ssex from student where sname=李军 ) and class = (select class from student where sname=李军 ) 45 、查询所有选修“ 计算机导论” 课程的 “ 男” 同学的成绩表。select * from score where sno in(select sno from student where ssex=男) and cno in(select cno from course where cname=计算机导论 ) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 7 页 - - - - - - - - -