多表询练习题15道

首先给出三个主表，分别表示员工表、部门表、等级表

create table emp1(
       id int auto_increment  comment 'ID' primary key,
       name varchar(50) not null comment '姓名',
       age int comment '年龄',
       job varchar(20) comment '职位',
       salary int comment '薪资',
       entrydate date comment '入职时间',
       managerid int comment '直属领导ID',
       dept_id int comment '部门ID'
    )comment'员工表';
alter table emp1 add constraint fk_emp1_dept_id foreign key (dept_id) references dept(id);

create table dept (
    id int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment'部门名称'
    )
    comment'部门表';

create table salgrade(
    grade int,
    losal int, -- 最低薪资
    hisal int  -- 最高薪资
)comment '薪资等级表';

接下来是题目以及对应sql语句

-- 1.查询员工的姓名，年龄，职位，部门信息（隐式内连接）
select e.name,e.age,e.job,d.name from emp1 e,dept d where dept_id=d.id;
-- 2.查询年龄小于30岁的员工的姓名，年龄，职位，部门信息（显示内连接）
select e.name,e.age,e.job,d.name from emp1 e inner join dept d on e.dept_id = d.id where e.age<30;
-- 3.查询拥有员工的部门ID，部门名称(内连接）
select distinct d.id,d.name from emp1 e,dept d where e.dept_id=d.id;
-- 4.查询所有年龄大于40岁的员工，及其所归属的部门，没有分配部门也要表示(左外连接）
select e.*,d.name from emp1 e left join dept d on e.dept_id = d.id where age>40;
-- 5.查询所有员工的工资等级
select e.*,s.grade,s.losal,s.hisal from emp1 e,salgrade s where e.salary between  s.losal and s.hisal;
-- 6.查询‘研发部’所有人员的信息及其工资等级
select e.* ,s.grade from emp1 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 emp1 e,dept d where e.dept_id=d.id and d.name='研发部';
-- 8.查询比灭绝工资高的员工信息
select *from emp1 where salary >(select salary from emp1 where name='灭绝');
-- 9.查询比平均薪资高的员工信息
select *from emp1 where salary>(select avg(salary)from emp1);
-- 10.查询低于本部门平均工资的员工信息
select *,(select avg(e1.salary) from emp1 e1 where e1.dept_id=e2.dept_id) '平均'from emp1 e2 where e2.salary<(select avg(e1.salary) from emp1 e1 where e1.dept_id=e2.dept_id);
-- 11.查询所有的部门信息，并统计部门的员工人数
select d.id,d.name ,(select count(*)from emp1 e where e.dept_id=d.id) '人数'from dept d;
-- 12。查询所有学生的选课情况，展示出学生名称，学号，课程名称
select s.name ,s.no,c.name from students s,student_course sc,course c where s.id=sc.studentid and c.id=sc.courseid;