多表查询、ALLSOME查询、聚集函数查询,分组查询,分组过滤查询
Student表格:
Dept(院系)学院号-学院名-院长:
Course表格:
Teacher表格,教师号-教师姓名-部门号-工资:
SC表格,学号-课程号-分数,反应学生和课程的对应关系:
多表查询
(1)求选修了001课程的学生的学号和姓名
需要了解学生和课程的对应关系,因此需要查询SC表;但SC表无法返回姓名,因此1、在SC表返回选修了001课程学生的学号 2、在Student表中返回学生的姓名。
Select `S#`,Sname From Student Where `S#` in (Select `S#` From SC Where `C#` = 001);
(2)求既学过001课程又学过002课程的学生的学号
只需要返回学号,因此只查询SC表就够了;先找出选了001课程学生的学号,再看同样的学号是否也选修了002课程,如果是就选中。
Select `S#` From SC Where `C#`=002 AND `S#` in(Select `S#` From SC Where `C#`=001);
(3)列出上过ZhaoSan课程的学生姓名?
涉及到SC,Course,Teacher,Student四张表。将SC,Course,Teacher三张表做联合,设条件过滤,取出S#到Student中返回Sname
Select Sname From Student Where `S#` in (Select `S#` From SC, Teacher T, Course C Where T.Tname = 'ZhaoSan' AND C.`C#`=SC.`C#` AND C.`T#` = T.`T#`);
(4)找出工资最低的教师姓名
Select Tname From Teacher Where Salary<=All (Select Salary From Teacher );
ALL和SOME关键字
(1)找出001号课成绩不是最高的所有学生的学号
< 后接some,some表示最大值,>后接some,some表示最小值
Select `S#` From SC Where `C#`='001' AND Score < some(Select Score From SC Where `C#` =001);
(2)找出所有课程都不及格的学生姓名
ALL关键字和some相反
Select Sname From Student Where 60 > ALL (Select Score From SC Where `S#`=Student.`S#`);
聚集函数查询
求教师的工资总额?
Select Sum(Salary) From Teacher;
求计算机系教师的工资总额?
需要先去Dept表中查找CS的系号,然后去Teacher表中查找CS系的教师,求工资总和。
Select sum(Salary) From Teacher T, Dept Where T.`D#` = Dept.`D#` and Dept.Dname='CS' ;
求数据库课程的平均成绩?
需要用到Course表格和SC表格,Course表格返回DataBase课程的编号,SC表格找出对应编号的课程成绩
Select AVG(Score) From Course, SC Where Course.Cname='DataBase' and Course.`C#` = SC.`C#`;
分组查询
求每一个学生的平均成绩?
Select `S#`, AVG(Score) From SC Group by `S#`;
求每一门课程的平均成绩?
需要知道每门课程所有考生的成绩,因此需要Course和SC表格,Course表格返回课程号,SC表格返回
Select `C#`, AVG(Score) From SC Group by `C#`;
分组过滤查询
求不及格课程超过两门的同学的学号:
求有课程不及格同学的学号:
Select `S#` From SC Where Score < 60 Group by `S#` Having Count(*) > 2;