12、案例练习

-- 1、查询工资最低的员工信息：last_name,salary
select last_name,salary from employees where salary=(select min(salary) from employees);
-- 2、查询平均工资最低的部门信息
select * from departments where department_id = (select department_id  from employees group by department_id order by avg(salary) limit 1);
-- 3、查询平均工资最低的部门信息和该部门的平均工资
select d.*,avg.avg_salary
from departments d join (select department_id,avg(salary) as avg_salary  from employees group by department_id order by avg(salary) limit 1) avg on d.department_id = avg.department_id; 
-- 4、查询平均工资最高的job信息
select * from jobs where job_id = (select job_id from employees group by job_id order by avg(salary) desc limit 1);
-- 5、查询平均工资高于公司平均工资的部门有哪些？
select department_id from employees group by department_id having avg(salary)>(select avg(salary) from employees);
-- 6、查询出公司中所有manager的详细信息
select * from employees where employee_id in (select DISTINCT manager_id from employees);
-- 7、各个部门中最高工资中最低的那个部门的最低工资是多少
select min(salary) from employees where department_id = (select department_id from employees group by department_id ORDER BY max(salary) limit 1);
-- 8、查询平均工资最高的部门的manager的详细信息：
-- 方法一：
select last_name,s.department_id,email,salary from employees s join departments d on s.employee_id=d.manager_id where s.department_id = (select department_id from employees group by department_id order by avg(salary) desc limit 1);
-- 方法二：
select last_name,s.department_id,email,salary from employees s join departments d on s.employee_id=d.manager_id where s.department_id =(
select department_id from employees group by department_id having avg(salary) = (select max(avg_salary) from (select avg(salary) as avg_salary from employees group by department_id) avg));

-- 一、查询每个专业的学生人生
select majorid,count(*) from student group by majorid;
-- 二、查询参加考试的学生中，每个学生的平均分、最高分
select studentno,avg(score),max(score) from result group by studentno;
-- 三、查询姓张的每个学生的最低分大于60的学号、姓名
select s.studentno,studentname from student s join result r on s.studentno=r.studentno where studentname like '张%' group by r.studentno having min(score) > '60';
-- 四、查询每个专业生日在"1988-1-1"后的学生姓名、专业名称
select studentname,majorname from student s join major m on s.majorid=m.majorid where DATEDIFF(borndate,'1988-1-1')>0;
-- 五、查询每个专业的男生人数和女生人数分别是多少
-- 方法一
select majorid,sex,count(*) from student group by majorid,sex;
-- 方法二
select majorid 
(select count(*) from student where sex='男' and majorid=s.majorid) 男，
(select count(*) from student where sex='女' and majorid=s.majorid) 女，
from student s
group by majorid;
-- 六、查询专业和张翠山一样的学生的最低分
select min(score) from result r JOIN (select studentno from student where majorid in (select majorid from student where studentname='张翠山')) s on r.studentno = s.studentno;
-- 七、查询大于60分的学生的姓名、密码、专业名
select studentname,loginpwd,majorname from student s join major m on s.majorid=m.majorid where s.studentno in (select studentno from result where score>'60');
-- 八、按邮箱位数分组，查询每组的学生个数
select LENGTH(email),COUNT(*) from student group by LENGTH(email);
-- 九、查询学生名、专业名、分数
select studentname,majorname,score from student s join major m on s.majorid=m.majorid left join result r on s.studentno=r.studentno;
-- 十、查询哪个专业没有学生，分别用左连接和右连接实现
-- 左连接
select majorname,studentname from major m left join student s on m.majorid=s.majorid where s.studentname is null;
-- 右连接
select studentname,majorname from student s RIGHT join major m on m.majorid=s.majorid where s.studentname is null;
-- 十一、查询没有成绩的学生人数
select count(*) from student where studentno not in (select DISTINCT studentno from result);