MySQL数据库练习三

1.查询Score表中成绩在60到80之间的所有记录

SELECT * from SCORE
where degree>60 and degree<80;

2.查询 score 表中成绩为85，86或88的记录

select * from SCORE
where degree in (85,86,88);

3.以 cno 升序、degree降序查询 score 表的所有记录

select * from SCORE
order by cno asc,degree desc;

4.查询“95031”班的学生人数。

select count(*)
from STUDENT
where class=95031;

5.查询Score表中的最高分的学生学号和课程号

select sno,cno
from SCORE
where degree >= all(
select degree from score)

6.查询‘3-105’号课程的平均分。

select avg(degree) from SCORE
where cno='3-105';

7.查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

select cno,avg(degree)
from SCORE
where cno like '3%'
GROUP BY cno
having count(sno)>=5;

8.查询最低分大于70，最高分小于90的Sno列。

select sno
from SCORE
GROUP BY sno
having min(degree)>70 and max(degree)<90;

9.查询所有学生的Sname、Cno和Degree列。

select sname,cno,degree
from student left join score on STUDENT.sno=score.sno;

10.c查询所有学生的Sno、Cname和Degree列。

select STUDENT.sno,cname,degree
from STUDENT left join score on STUDENT.sno=score.SNO
left join course on course.cno=score.cno;

11.查询所有学生的Sname、Cname和Degree列。

select STUDENT.sno,cname,degree
from STUDENT left join score on STUDENT.sno=score.SNO
left join course on course.cno=score.cno;

12.查询“95033”班所选课程的平均分。

select avg(degree)
from SCORE
where sno in (
select sno
from STUDENT
where class=95033);

13.查询所有同学的Sno、Cno和rank列

select sno,cno,rank
from SCORE left join grade on degree BETWEEN low and upp;

14.查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

select * from score 
where cno = '3-105' and degree >(
select degree from score 
where sno = 109 and cno = '3-105');

15.查询score中选学一门以上课程的同学中分数为非最高分成绩的记录

-- 小于无论什么课程的最大值（最大值只有一个）
select * from SCORE
where sno in (
select sno 
from SCORE
where degree != (select max(degree) from score)
GROUP BY sno HAVING count(cno) >1)

–针对每个学生最大分数

select *
from score a
-- 将sno分组内连接求每组不等于最大值的，针对于学生
join (select sno,max(degree) max_degree from score GROUP BY sno) b
on a.sno=b.sno and a.DEGREE!=b.max_degree;

– 针对于每科课程最大分数

SELECT * 
from score a
join (select cno,max(degree) as max_degree from score GROUP BY cno) b
on a.cno=b.cno and a.degree != b.max_degree;

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

SELECT sno,sname,sbirthday
from STUDENT
where YEAR(sbirthday)=(
select YEAR(sbirthday)
from STUDENT
where sno=108) and sno!=108;

17.查询“张旭“教师任课的学生成绩。

select * from SCORE
where cno in (
select cno from course
where tno in (select tno from teacher
where tname='张旭'));

18.查询选修某课程的同学人数多于5人的教师姓名。

select tname from teacher 
where tno in(select tno from course
where cno in (
select cno from score 
GROUP BY cno
HAVING count(sno)>5));

19.查询95033班和95031班全体学生的记录。

select * from STUDENT
where class in ('95033','95031');

20.查询存在有85分以上成绩的课程Cno。

select cno from SCORE
where cno in (select cno from SCORE where degree>85)
GROUP BY cno;

21.查询出“计算机系“教师所教课程的成绩表。

select * from SCORE
where cno in(
select cno from COURSE
where tno in (
select tno from teacher 
where depart='计算机系'));

22.查询所有教师和同学的name、sex和birthday。

select tname name,tsex sex,tbirthday birthday
from TEACHER
UNION //集合函数，将教师和学生拼接起来
select sname name,ssex sex,sbirthday birthday
from student

23.查询所有“女”教师和“女”同学的name、sex和birthday。

--只要11对应，无论什么属性
select tname name,tsex sex,tbirthday birthday
from teacher where tsex = '女'
UNION
select sname name,ssex sex,sbirthday birthday
from student where ssex = '女';

24.查询成绩比该课程平均成绩低的同学的成绩表。

SELECT * from SCORE
where degree<(
select avg(degree) from SCORE)

25.查询所有任课教师的Tname和Depart。

-- 有可能老师没任课
select tname,depart
from TEACHER
where tno in(
select distinct(tno) from COURSE);

26.查询至少有2名男生的班号。

select class from STUDENT
where ssex = '男'
GROUP BY class 
having count(*)>1;

27.查询Student表中不姓“王”的同学记录。

select * from STUDENT
where sname not like '王%';

28.查询Student表中每个学生的姓名和年龄。

select sname,YEAR(NOW())-YEAR(sbirthday) age
from STUDENT