数据库查询习题整理（一）

select * from student
select * from teacher
select * from course
select * from score

上面原有几个新建的表从中查询一些数据

--1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select Sname,Ssex,Class from student

--2、 查询教师所有的单位即不重复的Depart列。用distinct去重
select distinct depart  from teacher

--3、 查询Student表的所有记录。
select *from student

--4、 查询Score表中成绩在60到80之间的所有记录。用between也可以用大于小于来做
select *from score where degree between 60 and 80

--5、 查询Score表中成绩为85，86或88的记录。用in来取离散的值
select *from score where degree in (85,86,88)

--6、 查询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”班的学生人数。count(*)统计函数统计有几个值
select count (*)from student where class='95031'

--10、查询Score表中的最高分的学生学号和课程号。

第一种方法：按分数排序，取top 1也就是取最高的
select top 1 sno,cno from score order by degree desc

--第二种方法：先查询出最高分，然后查询最高分的学号课程号
select sno,cno from score where degree=
(select max(degree)from score)

--11、查询‘3-105’号课程的平均分。利用avg统计函数
select AVG(degree)from score where cno='3-105'

--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。先要选出cno以3开头的，然后再进行分组求平均值
select AVG(degree)from score where cno like '3%' group by cno having count(*)>=5 

--13、查询最低分大于70，最高分小于90的Sno列。
select sno from score group by sno having MAX(degree)<90  and MIN(degree)>70

--14、查询所有学生的Sname、Cno和Degree列。先将student表和score表join起来，然后查询需要的列
select sname,cno,degree from student
join score on student.sno= score.sno

--15、查询所有学生的Sno、Cname和Degree列。
select sno,cname,degree from score
join course on score.cno=course.cno

--16、查询所有学生的Sname、Cname和Degree列。第一步写三个表,第二步join起来，第三步筛选需要的列
select sname,cname,degree
from student
join score on score .sno=student.sno
join course on score .cno=course.cno

--17、查询“95033”班所选课程的平均分。先将score和student两个表join起来，然后按照class条件筛选，最后求平均值
select AVG(degree)from score
join student on student .sno=score .sno
where class='95033'

--也可以用分组做：
select cno,avg(degree)from score where sno in
(select student.sno
from score
join student on student .sno=score .sno
where class='95033') group by cno

--18、假设使用如下命令建立了一个grade表：
select * from grade
create table grade(low  int,upp  int,rank  varchar(1))
insert into grade values(90,100,'A')
insert into grade values(80,89,'B')
insert into grade values(70,79,'C')
insert into grade values(60,69,'D')
insert into grade values(0,59,'E')
--现查询所有同学的Sno、Cno和rank列。
select sno,cno,rank from score
join grade on degree between low and upp

--19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。//无关子查询

先将student表和score表join起来，查询选修3-105课程的109号学生的成绩，然后选出选修3-105学生的成绩大于此成绩的同学记录
select *from student where sno in(
select sno from score where cno='3-105' and degree>
(select degree from score where cno='3-105'and sno='109'))

--20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。//相关子查询

外层条件拿到里层进行比较，符合条件的查询返回

找出选修多门课程的同学的sno；里层查询找出每门课程的最高分，前天条件是里层的cno和外层的cno一样

select *from score a where sno in
(
  select sno from score group by sno having COUNT(*)>1
)
and degree not in
(
select MAX(degree) from score b where a.cno=b.cno group by cno
)

--21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

先查询学号为109课程号为3-105的学生的成绩，然后查询score中分数大于此学生成绩的记录
select *from score where  degree>
(select degree from score where cno='3-105'and sno='109')

--22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

用year函数来取出sbirthday中的年份

查询sno=108同学的出生年份，查询出生年份等于此同学的学生的记录
select sno,sname,sbirthday from student where year(sbirthday) =
(select year(sbirthday) from student where sno='108')

--23、查询“张旭“教师任课的学生成绩。三个表join起来，然后满足条件tname为张旭
select *from score
join course on course.cno=score.cno
join teacher on  course.tno=teacher.tno
 where teacher.tname='张旭'

--24、查询选修某课程的同学人数多于5人的教师姓名。将teacher和course连接起来，然后查询人数多于5人的cno
select tname from teacher
join course on teacher.tno=course.tno
where course.cno in(
select cno from score group by cno having COUNT(*)>5 )

--25、查询95033班和95031班全体学生的记录。
select *from student where class='95033'or class='95031'