本次查询我们基于这几张表查询
***********查询练习**********
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select sname,ssex,class from student;
2、 查询教师所有的单位即不重复的Depart列。
select distinct depart from teacher;
3、 查询Student表的所有记录。
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);
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”班的学生人数。
select count(*) from student where class = '95031';
10、 查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
Select sno,cno,max(degree) from score group by cno;
11、 查询每门课的平均成绩。
Select cno,avg(degree) from score group by cno;
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
Select cno,avg(degree) from score where (select count(sno) from score )
至少有五名同学选修的课程：
Select cno, count(sno)>=5,avg(degree) from score where cno like’ 3%’ ;
13、查询分数大于70，小于90的Sno列。
Select sno from score where degree >70 and degree<90;
14、查询所有学生的Sname、Cno和Degree列。
Select sname,cno,degree from student,score where student.sno=score.sno;
15、查询所有学生的Sno、Cname和Degree列。
Select sno,cname,degree from score,course where course.cno=score.cno;
16、查询所有学生的Sname、Cname和Degree列。
Select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;
17、查询“95033”班学生的平均分。
Select class,avg(degree) from student,score where student.sno=score.sno class=’95033’;
18、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
Select * from student,score where student.sno=score.sno and cno=’3-105’ and degree>(select degree from score where sno=’109’ and cno=’3-105’);
19、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
先取出每科成绩的最高成绩
Select cno,max(degree) from score group by cno having count(*)>1;
select score.sno,score.cno,score.degree from score join (Select cno,max(degree) as big from score group by cno having count(*)>1)as a on score.cno = a.cno and score.degree <> a.big;
20、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录
Select * from score where cno='3-105' and degree>(select degree from score where sno='109' and cno='3-105');
21、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
Select * from student where sbirthday=(select sbiryhday from student where sno=’108’);
22、查询“张旭“教师任课的学生成绩（姓名）。
先找出该老师的课程编号；
Select cno from course where tno=(select tno from teacher where tname=’张旭’);
汇总：
Select sname,degree from student,score where student.sno=score.sno and cno=(Select cno from course where tno=(select tno from teacher where tname='张旭'));
23、查询考计算机导论的学生成绩
先找到计算机导论的课程编号
select cno from course where cname=('计算机导论');
Select sno,degree from score where cno= (select cno from course where cname=('计算机导论'));
24、课程名(cname)称为计算机导论 的课程编号
Select cno from course where cname=(‘计算机导论’);
25、查询李诚老师教的课程名称
select tno from teacher where tname=(‘李诚’);
Select cname from course where tno=(select tno from teacher where tname=(‘李诚’));
26、教高等数学的老师是哪个系的
Select tno from course where cname=(‘高等数学’);
Select depart from teacher where tno=(Select tno from course where cname=(‘高等数学’));
27、查询选修某课程的同学人数多于5人的教师姓名。
select cno from score group by cno having count(sno)>5;
select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(sno)>5));
28、查询95033班和95031班全体学生的记录。
Select * from student where class in(95033,95031);
29、查询成绩表中存在有85分以上成绩的课程Cno.
Select cno from score where degree>85;
30、查询出“计算机系“教师所教课程的成绩表。
Select * from score where cno in ();
Select cno from course where tno in();
Select tno from teacher where depart=’计算机系’;
Select * from score where cno in (Select cno from course where tno in(Select tno from teacher where depart=’计算机系’));
31、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
根据3-105找信息并且成绩还有要求
Select min(degree) from score where cno='3-245'; Select * from score where cno=’3-105’ and degree>(Select min(degree) from score where cno='3-245');
32、查询所有教师和同学的name、sex和birthday.
select sname,ssex,sbirthday from student union select tname,tsex,tbirthday from teacher;
33、查询所有“女”教师和“女”同学的name、sex和birthday.
select sname,ssex,sbirthday from student where ssex='女' union select tname,tsex,tbirthday from teacher where tsex='女';
34、查询所有成绩 比3-105课程平均成绩低的同学的成绩表。
Select * from score where degree <(Select avg(degree) from score where cno='3-105');
35、查询所有任课教师的Tname和Depart.
Select tname,depart from teacher;
36、查询所有未讲课的教师的Tname和Depart.
Select tname,depart from teacher where tno in (select tno from course where cno not in(select cno from score));
37、查询至少有2名男生的班号。(根据性别班级分)
Select class,count(ssex=’男’) >=2 from student;
38、查询Student表中不姓“王”的同学记录。
Select * from student where sname not like '王%';
39、查询Student表中每个学生的姓名和年龄。
Select sname, (year(now())-year(sbirthday)) as old from student;
40、查询Student表中最大和最小的Sbirthday日期值。
select max(sbirthday),min(sbirthday) from student;
41、以班号和年龄从大到小的顺序查询Student表中的全部记录。
Select * ,(year(now())-year(sbirthday)) as old from student order by class desc,old desc;
42、查询“男”教师及其所上的课程。
Select tno from teacher where tsex= '男';
Select cno,cname from course where tno in (Select tno from teacher where tsex= '男');
43、查询最高分同学的Sno、Cno和Degree列。
Select * from score where degree=(select max(degree) from score);
44、查询和“李军”同性别的所有同学的Sname.
Select * from student where ssex=(select ssex from student where sname= '李军');
45、查询和“李军”同性别并同班的同学Sname.
Select sname from student where class=(select class from student where sname= '李军') and ssex=(select ssex from student where sname= '李军');
46、查询所有选修“计算机导论”课程的“男”同学的成绩表。
Select cno from course where cname= '计算机导论' Select sno from student where ssex= '男' Select * from score where cno in (Select cno from course where cname= '计算机导论') And sno in (Select sno from student where ssex= '男');
