目录

准备

需求

代码

准备

-- 创建test1数据库
create database test1;
-- 选择使用test1数据库
use test1;
-- 创建部门表
create table dept(deptno int primary key,-- 部门编号dname varchar(14) ,-- 部门名称loc varchar(13) -- 部门地址
) ;insert into dept values(10,'accounting','new york'),(20,'research','dallas'),(30,'sales','chicago'),(40,'operations','boston');
-- 创建员工表					
create table emp(empno int primary key, -- '员工编号',ename varchar(10), -- '员工名',job varchar(9), -- '员工工作',mgr int, -- '员工直属领导编号',hiredate date, -- '入职时间',sal double, -- '工资',comm double, -- '奖金',deptno int -- '对应dept表的外键'
);
-- 添加部门和员工之间的外键关系
alter table emp add constraint foreign key emp(deptno) references dept (deptno);insert into emp values (7369,'smith','clerk',7902,'19801217',800,null,20),
(7499,'allen','salesman',7698,'19810220',1600,300,30),
(7521,'ward','salesman',7698,'19810222',1250,500,30),
(7566,'JONES','MANAGER',7839,'19810402',2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'19810928',1250,1400,30),
(7698,'BLAKE','MANAGER',7839,'19810501',2850,NULL,30),
(7782,'CLARK','MANAGER',7839,'19810609',2450,NULL,10),
(7788,'SCOTT','ANALYST',7566,'19870419',3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,'19811117',5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,'19810908',1500,0,30),
(7876,'ADAMS','CLERK',7788,'19870523',1100,NULL,20),
(7900,'JAMES','CLERK',7698,'19811203',950,NULL,30),
(7902,'FORD','ANALYST',7566,'19811203',3000,NULL,20),
(7934,'MILLER','CLERK',7782,'19820123',1300,NULL,10);create table salgrade(grand int ,-- '等级',losal double, -- '最低工资',hisal double -- '最高工资'
);
insert into salgrade values (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);

需求

1、返回拥有员工的部门名、部门号。
2、工资水平多于smith的员工信息。
3、返回员工和所属经理的姓名。
4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。
5、返回员工姓名及其所在的部门名称。

6、返回从事clerk工作的员工姓名和所在部门名称。
7、返回部门号及其本部门的最低工资。
8、返回销售部(sales)所有员工的姓名。

9、返回工资水平多于平均工资的员工。
10、返回与scott从事相同工作的员工。

11、返回工资高于30部门所有员工工资水平的员工信息。
12、返回员工工作及其从事此工作的最低工资。
13、计算出员工的年薪,并且以年薪排序。
14、返回工资处于第四级别的员工的姓名。

15、返回工资为二等级的职员名字、部门所在地

代码

-- 1、返回拥有员工的部门名、部门号。
select  distinct  d.dname ,d.deptno  from dept d ,emp e where d.deptno =e.deptno ;
select  distinct  d.dname ,d.deptno  from dept d  right outer join emp e on d.deptno =e.deptno ;-- 2、工资水平多于smith的员工信息。
select * from emp where sal >(select sal from emp where ename='smith');
-- 3、返回员工和所属经理的姓名。
select  e.ename ,e2.ename  from emp e join emp e2 on e.mgr =e2.empno ;select  e.ename ,e2.ename  from emp e, emp e2 where e.mgr =e2.empno ;-- 4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。select  e.ename,e.hiredate  ,e2.ename,e2.hiredate  from emp e join emp e2 on e.mgr =e2.empno and e.hiredate (select avg(sal)  from emp e2 );
-- 10、返回与scott从事相同工作的员工。
select * from emp e where e.job =(select e2.job  from emp e2 where e2.ename='scott' ) and e.ename !='scott';-- 11、返回工资高于30部门所有员工工资水平的员工信息。
select * from emp e where sal>all(select sal from emp e2 where e2.deptno=30);-- 12、返回员工工作及其从事此工作的最低工资。
select e.job ,min(sal) from emp e group by e.job ;-- 13、计算出员工的年薪,并且以年薪排序。
select sal*12+ifnull(comm,0) as nian_sal  from emp e order by sal*12+ifnull(comm,0)  ;-- 14、返回工资处于第四级别的员工的姓名。
select e.ename,e.sal  from emp e where e.sal between (select s.losal from salgrade s where s.grand=4) and (select s.hisal  from salgrade s where s.grand=4) ;-- 15、返回工资为二等级的职员名字、部门所在地select e.ename ,d.dname ,e.sal  from emp e ,dept d ,salgrade s where 
s.grand =2 
and (e.sal>s.losal and e.sal s.losal and e.sal