数据库 12 -- 多表查询（3）

// (1) 隐式内连接
select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id ;

// (2) 显式内连接
// select e.name , e.age , e.job , d.name from (select * from emp where age < 30) e join dept d on e.dept_id = e.id ;
 select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id = d.id where age < 30 ;

// (3) 隐式内连接
select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id ;

// (4) 左外连接
// select * from (select * from emp where age > 40) e left outer join ddept d on e.dept_id = d.id ;
select * from emp e left outer join ddept d on e.dept_id = d.id where age > 40 ;

// (5) 查询所有员工的薪资等级  隐式内连接
表 ： emp ，salgrade
连接条件 ：emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal ;
select e.* , s.grade from emp e , salgrade s where e.salary >= s.losal and e.salary <= s.hisal ;

// (6) 查询"研发部"所有员工的信息及工资等级
// select e.* , s.grade from (select * from emp where dept_id = (select d.id from dept d where name = '研发部')) e , salgrade s where e.salary >= s.losal and e.salary <= s.hisal ;
select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and (e.salary between s.losal and s.hisal) and d.name = '研发部'

// (7) 查询‘研发部’员工的平均工资
select avg(e.salary) from emp e , dept d where e.dept_id = d.id and d.name = '研发部' ;

// (8) 查询工资比‘灭绝’高的员工信息
select * from emp where salary > (select salary from emp where name = '灭绝') ;

// (9) 查询比平均薪资高的员工信息
select * from emp where salary > (select avg(salary) from emp) ;

// (10) 查询低于本部门平均工资的员工信息
select * from emp e1 where e1.salary < (select avg(e2.salary) from emp e2 where e2.dept_id = e1.dept_id) ;

// (11) 查询所有部门信息 ，并统计部门员工数量
select id , name ,  (select count(*) from emp where dept_id = id) '人数' from dept ;

// (12) 查询所有学生的选课情况 ，展示出姓名 、学号 、课程名称 内连接
select s.name , s.number , c.name from student s , student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id ;