多测师肖老师_mysql之单表和多表题（9.2）

--
-- drop table dept;
-- drop table emp ;
#部门表
create table dept(
deptno int primary key,
dname varchar(14), -- 部门名称
loc varchar(13)-- 部门地址
)default charset=utf8 ;
insert into dept values (10,'会计部','纽约');
insert into dept values (20,'技术部','达拉斯');
insert into dept values (30,'销售部','芝加哥');
insert into dept values (40,'运营部','波士顿');

select * from dept
#员工表

-- drop table emp;

create table emp(
empno int primary key, -- 员工编号
ename varchar(10), -- 员工姓名
job varchar(9), -- 员工工作
mgr int, -- 员工直属领导编号
hiredate date, -- 入职时间
sal double, -- 工资
comm double, -- 奖金
deptno int, -- 所在部门
foreign key (deptno) references dept(deptno)) default charset=utf8; -- 关联dept表

-- alter table emp add foreign key (deptno) references dept(deptno);
insert into emp values(7369,'smith','职员',7566,"1980-12-17",800,null,20);
insert into emp values(7499,'allen','销售员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'ward','销售员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'jones','经理',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'martin','销售员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'blake','经理',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'clark','经理',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'scott','职员',7566,'1987-07-03',3000,2000,20);
insert into emp values(7839,'king','董事长',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'turners','销售员',7698,'1981-09-08',1500,50,30);
insert into emp values(7876,'adams','职员',7566,'1987-07-13',1100,null,20);
insert into emp values(7900,'james','职员',7698,'1981-12-03',1250,null,30);
insert into emp values(7902,'ford','销售员',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'miller','职员',7782,'1981-01-23',1300,null,10);

#工资等级表

create table salgrade(
grade int,-- 等级
losal double, -- 最低工资
hisal double ) default charset=utf8; -- 最高工资

insert into salgrade values (1,500,1000);
insert into salgrade values (2,1001,1500);
insert into salgrade values (3,1501,2000);
insert into salgrade values (4,2001,3000);
insert into salgrade values (5,3001,9999);

select * from dept ;
select * FROM emp ;
select * from salgrade ;

=================================================

 dept

 

 emp

 

 salgrade

 

 

多表查询答案

#1、返回拥有员工的部门名、部门号。

#2、工资水平多于smith的员工信息。

#3、返回员工和所属经理的姓名。

#4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。

#5、返回员工姓名及其所在的部门名称。

#6、返回从事clerk工作的员工姓名和所在部门名称。(CLERK销售)

#7、返回部门号及其本部门的最低工资。

#8、返回销售部(sales)所有员工的姓名。

#9、返回工资水平多于平均工资的员工。

#10、返回与SCOTT从事相同工作的员工。(职员)

#11、返回与30部门员工工资水平相同的员工姓名与工资。

 

方法一(考虑员工工资水平)：select ename ,sal from emp where sal in (select sal from emp where deptno=30) 

 方法二（考虑员工的平均水平）：

SELECT ename,sal from emp WHERE
sal>(sELECT losal from salgrade WHERE losal<(SELECT AVG(sal) from emp WHERE deptno=30)
and hisal>(SELECT AVG(sal) from emp WHERE deptno=30))
AND sal<(SELECT hisal from salgrade WHERE losal<(SELECT AVG(sal) from emp WHERE deptno=30)
and hisal>(SELECT AVG(sal) from emp WHERE deptno=30)
);

 

#12、返回工资高于30部门所有员工工资水平的员工信息。

#13、返回部门号、部门名、部门所在位置及其每个部门的工员总数。

#14、返回员工的姓名、所在部门名及其工资。

#15、返回员工的详细信息。(包括部门名)

#16、返回员工工作及其从事此工作的最低工资。

#17、计算出员工的年薪，并且以年薪排序。

#18、返回工资处于第四级别的员工的姓名。

方法一：

select ename from emp a ,salgrade b where a.sal>=b.losal and a.sal<=b.hisal and b.grade=4

方法二：

SELECT ename from emp WHERE
sal>(
SELECT losal from salgrade
WHERE grade=4
)
AND
sal<(
SELECT hisal from salgrade
WHERE grade=4
);

方法三：

select  c.ename  from  emp c,(select  losal,hisal from  salgrade where grade=4)s WHERE c.sal between s.losal  and  s.hisal

#19、返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资

方法一：

select ename ,loc,sal,hisal,losal from emp a,dept b salgrade c where
a.deptno=b.deptno and sal>=losal and sal<hisal and grade=2 ;

#20.工资等级多于smith的员工信息。

方法一：

SELECT * from emp where sal>(
SELECT hisal from salgrade WHERE
losal<(SELECT sal from emp WHERE ename='smith')
AND
hisal>(SELECT sal from emp WHERE ename='smith')
) ;

方法二：

select * from emp a ,salgrade b where a.sal>b.hisal and b.grade >(
select grade from emp s ,salgrade c where s.sal>c.losal and s.sal<=c.hisal and s.ename ="smith" )