设有一数据库,包括四个表:学生表(Student)、课程表(Course)、成绩表(Score)以及教师信息表(Teacher)。
1、查询Score表中成绩为85,86或88的记录
2、查询Student表中“95031”班或性别为“女”的同学记录
3、查询Score表中的最高分的学生学号和课程号。(子查询或者排序)
4、查询每门课的平均成绩
5、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数
6、查询教师的职称,需要去重
7、查询成绩在60到80之间的所有记录
8、查询班级及每个班级多少人
9、查询“95033”班学生的平均分
10、查询所有学生的成绩,要求展示学生姓名、课程名称、成绩
11、查询Student表中不姓“王”的同学记录
12、以班号和年龄从大到小的顺序查询Student表中的全部记录
13、查询至少有2名男生的班号
14、查询“男”教师及其所上的课程
15、查询所有学生的成绩,要求展示学生姓名、课程名称、成绩、任课老师
1: SELECT * FROM score WHERE Degree=85 OR Degree=86 OR degree=88;
2:SELECT *FROM student WHERE Class=‘95031’ OR Ssex=‘女’;
3:SELECT sno,cno FROM score WHERE (SELECT MAX(degree) FROM score);
4:SELECT cno,AVG(degree) FROM SCORE t GROUP BY cno;
5:SELECT cno,AVG(degree) FROM SCORE WHERE cno LIKE’3%’ GROUP BY cno HAVING COUNT(cno)>5;
6:SELECT DISTINCT prof FROM teacher;
7:SELECT degree FROM score WHERE degree >=60 AND degree <=80;
8:SELECT Class,COUNT(Sno) FROM student GROUP BY Class;
9:SELECT class, AVG(degree) FROM student JOIN score ON student.sno
=score.sno
;
11:SELECT *FROM student WHERE Sname NOT LIKE ‘王%’;
12、SELECT *FROM student ORDER BY sbirthday ASC,class DESC;
13、SELECT class,COUNT(ssex) FROM student WHERE ssex=‘男’ GROUP BY class HAVING COUNT(ssex) >= 2;
14:select tname,cname from teacher,course where teacher.tno = course.tno and tsex = ‘男’;
15:SELECT sname,cname,degree,tname
FROM score
JOIN course ON score.Cno
=course.Cno
JOIN student ON Score.Sno
=student.sno
JOIN teacher ON teacher.Tno
=course.Tno
;