学生表练习

1、查询所有的课程的名称以及对应的任课老师姓名

select course.cname,teacher.tname from course inner join teacher on course.teacher_id = teacher.tid;

2、查询学生表中男女生各有多少人

select gender as '性别',count(gender) as '个数'  from student group by gender;

3、查询物理成绩等于100的学生的姓名

select sname as '物理课程满分100' from student where sid in (select student_id from score where num = 100 and course_id in (select cid from course where cname = '物理'));

4、查询平均成绩大于八十分的同学的姓名和平均成绩

select sname as '学生姓名',new_num as '平均成绩'  from student inner join (select distinct student_id,avg(num) as new_num from score group by student_id) as B on student.sid = B.student_id;

5、查询所有学生的学号，姓名，选课数，总成绩

mysql> select sid as '学生学号',sname as '学生姓名',a.course_num as '学生选课数',a.num_num as '学生总成绩' from student left join (select student_id,count(course_id) course_num,sum(num) num_num from score group by student_id )as a on student.sid=a.student_id;

6、 查询姓李老师的个数

select count(tname) as '姓李老师的个数' from teacher where tname LIKE '李%';

7、 查询没有报李平老师课的学生姓名

 select sname as '未报刘老师课程的学生' from student where sid not in (select distinct student_id from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname='李平老师'))); 

8、 查询物理课程比生物课程高的学生的学号

select student_id as '学生生物id',num as'生物成绩',course_id  as'生物' from score where course_id in (select cid from course where cname = '生物') ;

9、 查询没有同时选修物理课程和体育课程的学生姓名

select sname as '学生名称' from student where sid in (select distinct student_id from score where course_id in  (select cid from course where cname='物理' or cname='体育') group by student_id having count(student_id) =1);

10、查询挂科超过两门(包括两门)的学生姓名和班级

select F.sname, caption from class inner join (select class_id,sname from student where sid in (select student_id from score where num < 60 group by student_id having count(num) >=2)) as F on F.class_id = class.cid;

11、查询选修了所有课程的学生姓名

mysql> SELECT student.sname FROM student WHERE sid IN ( SELECT student_id FROM score GROUP BY student_id HAVING COUNT( course_id ) = ( SELECT count( cid ) FROM course ) );

12、查询李平老师教的课程的所有成绩记录

select student_id as '学生姓名',num as '学生成绩' from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师'));

13、查询全部学生都选修了的课程号和课程名

select course_id as '课程名称',count(course_id) as '选修次数' from score group by course_id having course_id in (select cid from course);

14、查询每门课程被选修的次数

select course_id as '课程名称',count(course_id) as '选修次数' from score group by course_id having course_id in (select cid from course);

15、查询之选修了一门课程的学生姓名和学号

select student.sid as '学生学号',sname as '学生姓名' from student inner join (select student_id from score group by student_id having count(course_id) =1) as S on S.student_id = student.sid;

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

mysql> SELECT-> *-> FROM-> score-> ORDER BY-> num DESC;

17、查询平均成绩大于85的学生姓名和平均成绩

select sname as '学生姓名',new_num as '平均成绩'  from student inner join (select distinct student_id,avg(num) as new_num from score group by student_id) as B on student.sid = B.student_id;

18、查询生物成绩不及格的学生姓名和对应生物分数

select sname as '学生姓名',S.num as '生物分数' from student inner join (select student_id,num from score where num < 60 and course_id = (select cid from course where cname='生物')) as S on S.student_id = student.sid;

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

select student_id,avg(num) from score group by student_id having student_id in (select distinct student_id from score inner join (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师')) as C on C.cid = score.course_id) order by avg(num) desc limit 1;

单表多表查询练习题

练习1：SELECT语句的基本使用
1、 查询每个雇员的所有记录；

mysql> SELECT *from employee;

2、 查询前5个会员的所有记录；

SELECT *from employee LIMIT 0,5;

3、查询每个雇员的地址和电话；

SELECT addr,tel from employee;

4、 查询num为001的雇员地址和电话；

mysql> SELECT addr,tel FROM employee WHERE num=001;

5、查询表Employee表中女雇员的地址和电话，使用AS子句将结果列中各列的标题分别指定为地址、电话；

mysql> SELECT addr AS 地址,tel AS 电话 FROM employee WHERE sex="女";

6、 计算每个雇员的实际收入；

mysql> SELECT num,income-outcome from salay;

7、找出所有性王的雇员的部门号（部门号不能重复显示）；

mysql> SELECT  DISTINCT depno  from employee WHERE name LIKE"王%";

8.、找出所有收入在2000－3000元之间的雇员编号

SELECT num FROM salay WHERE income BETWEEN 2000 AND 3000;

练习2：子查询的使用（答案可以不唯一）
1、查找在财务部工作的雇员情况；

mysql> select *from employee WHERE depno=(SELECT depno FROM department WHERE depName="财务部");

2、 查找在财务部且年龄不低于研发部任一个雇员年龄的雇员的姓名；

mysql> SELECT name from employee WHERE depno=-> (SELECT depno from department WHERE depName="财务部") AND birth (SELECT birth FROM employee WHERE depno=(SELECT depno FROM department WHERE depName="研发部"));

3、查找比所有财务部雇员收入都高的雇员的姓名；

mysql> SELECT DISTINCT income,name from employee,salay,department WHERE employee.num=-> salay.num AND income>all-> (SELECT income FROM employee,salay,department WHERE employee.num=salay.num AND employee.depno=department.depno and depName="财务部");

练习3：连接查询的使用
1、 查找每个雇员的情况及薪水情况；

mysql> SELECT e.*, s.* FROM employee e-> INNER JOIN salay s-> ON e.Num=s.Num;

2、查找财务部收入在2200元以上的雇员姓名及其薪水详细情况；

SELECT name,salay.* FROM employee,salay,department WHERE employee.num=salay.num AND employee.depno=department.depno AND depName="财务部"AND income>2200;

练习4：数据汇总
1、求财务部雇员的平均实际收入；

mysql> SELECT avg(income-outcome) FROM employee,salay,department WHERE-> employee.num=salay.num AND employee.depno=department.depno and depName="财务部";

2、 求财务部雇员的总人数；

mysql> SELECT count(*) FROM employee,salay,department WHERE employee.num=salay.num AND employee.depno=department.depno and depName="财务部";

练习5：GROUP BY 、ORDER BY 子句的使用
1、 求各部门的雇员数（要求显示，部门号、部门名称和部门雇员数）；

mysql> SELECT e.depno AS 部门号,d.depname AS 部门名称,count(*)AS 部门雇员数 FROM employee e INNER JOIN department d on e.depno=d.depno  GROUP BY  d.depno ;

2、求部门的平均薪水大于2500的部门信息（要求显示，部门号、部门名称和平均工资）

mysql> SELECT e.depno AS 部门号,d.depname AS 部门名称,avg(income)AS 部门平均工资 FROM employee e,department d,salay s  GROUP BY  d.depno,e.num ;