多表查询-练习

-- 查询员工姓名，年龄，职位，部门信息（隐式内连接）
select e.name,e.age,e.job,d.name from emp as e,dept as d where e.dept_id=d.id;

-- 查询年龄小于30岁的员工姓名，年龄，职位，部门信息（显示内连接）
select e.name,e.age,e.job,d.name from emp as e join dept as d on d.id=e.dept_id where e.age<21;

-- 查询拥有员工的部门id，部门名称(重复的进行去重)
select distinct d.id,d.name from emp join dept as d on emp.dept_id = d.id;

-- 查询所有年龄大于20岁的员工，及其归属的部门名称；如果员工没有分配部门，也要展示出来
select e.*,d.name from emp as e left join dept as d on e.dept_id = d.id where e.age>20;

-- 查询所有员工的工资等级
select emp.*,salgrade.grade from salgrade join emp on emp.salary between salgrade.losal and salgrade.hisal;

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

-- 查询研发部员工的平均工资
select avg(e.salary) from emp as e left join dept as d on d.id = e.dept_id where d.name='研发部';

-- 查询工资比‘555’高的员工信息
select * from emp where salary>(select emp.salary from emp where name='555');

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

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

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

-- 查询所有学生的选课情况展示出学生姓名，学号，课程名称
create table student(
id int comment 'ID',
name varchar(10) comment '姓名',
no varchar(10) comment '学号'
) comment '学生表';
insert into student(id, name, no) values(1,'张三','2040'),
(2,'李四','2041'),
(3,'王五','2042'),
(4,'老六','2043'),
(5,'老八','2044');

create table course(
id int comment 'ID',
name varchar(10) comment '课程名称'
) comment '课程表';
insert into course(id, name) VALUES (01,'数学'),
(02,'英语'),
(03,'语文'),
(04,'物理'),
(05,'化学');
delete from course;
create table student_course(
id int primary key comment 'ID',
studentid int comment '学生ID',
courseid int comment '课程ID'
) comment '学生课程中间表';
insert into student_course(id, studentid, courseid) VALUES (001,1,01),
(002,2,02),
(003,3,03),
(004,4,04),
(005,5,05);
select s.name,s.no,c.name from student as s,student_course as sc,course as c where s.id=sc.studentid and c.id=sc.courseid;