数据库查询的练习

已知三张表：部门表、员工表、工资等级表

-- 部门表 [部门编号deptno， 部门名称dname, 部门地址loc]
create table dept(
deptno int PRIMARY KEY,dname VARCHAR(14),
loc VARCHAR(13) 
);
-- 添加数据
INSERT INTO dept VALUES (10,'accounting','NEW YORK');
INSERT INTO dept VALUES (20,'research','DALLAS');
INSERT INTO dept VALUES (30,'sales','CHICAGO');
INSERT INTO dept VALUES (40,'operations','BOSTON');

-- 员工表emp [员工编号empno, 员工姓名ename, 员工工作job, 员工直属领导编号mgr， 入职时间hiredate, 工资sal, 奖金comm, 部门编号deptno]
CREATE TABLE emp(
empno int PRIMARY KEY,
ename VARCHAR(10), 
job VARCHAR(9),
mgr int, 
hiredate DATE,
sal double,
comm double,
deptno int 
);
-- 添加数据
INSERT INTO emp VALUES(7369,'smith','clerk',7902,"1980-12-17",800,NULL,20);
INSERT INTO emp VALUES(7499,'allen','salesman',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'jones','manager',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'martin','salesman',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'blake','manager',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'clark','manager',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'scott','analyst',7566,'1987-07-03',3000,NULL,20);
INSERT INTO emp VALUES(7839,'king','president',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'turner','salesman',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'adams','clerk',7788,'1987-07-13',1100,NULL,20);
INSERT INTO emp VALUES(7900,'james','clerk',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'ford','analyst',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'miller','clerk',7782,'1981-01-23',1300,NULL,10);

-- 工资等级表salgrade 【等级 grade, 最低工资 lowsal, 最高工资 hisal】
CREATE TABLE salgrade(
grade int,
lowsal double, 
hisal double 
);
-- 添加数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

1.找出姓名以a、b、s开始的员工信息

 select * from emp where ename regexp '^[a,b,s]'; 

2.返回员工的详细信息并按姓名排序

 select * from emp order by ename;

3.返回员工的信息并按工作降序工资升序排列

select * from emp order by job desc, sal ;  

4.返回拥有员工的部门名、部门号

 select distinct  dname,dept.deptno from dept inner join emp on dept.deptno = emp.deptno; 

5.工资高于smith的员工信息

select * from emp where sal >(select sal from emp where ename = 'smith') ;

6.返回员工和所属经理的姓名

create view v_mgr as select distinct ename,empno from emp where empno in (select mgr from emp); 
select emp.ename,v_mgr.ename from emp inner join v_mgr on emp.mgr=v_mgr.empno; 

7.返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名

select v_mgr.ename as 经理,group_concat(emp.ename)as 下属 from emp inner join v_mgr on emp.mgr = v_mgr.empno and emp.hiredate < v_mgr.hiredate group by emp.mgr; 

8.返回员工姓名及其所在的部门名称

 select ename,dname from emp inner join dept on emp.deptno in(select deptno from dept)and emp.deptno = dept.deptno ; 

9.返回从事clerk工作的员工姓名和所在部门名称

select ename,dname from emp inner join dept on emp.deptno in(select deptno from dept)and emp.deptno = dept.deptno and emp.job = 'clerk'; 

10.返回部门号及其本部门的最低工资

 select deptno as 部门,min(sal) as 最低工资 from emp group by deptno; 

11.返回销售部(sales)所有员工的姓名

 select ename as 销售部 from dept inner join emp on dname='sales' and emp.deptno=dept.deptno ; 

12.返回与scott从事相同工作的员工

select ename from emp where job=(select job from emp where ename='scott')and enam
                                e!='scott'; 

13.返回员工的详细信息(包括部门名称及部门地址)

select emp.*,dname,loc from emp inner join dept on emp.deptno=dept.deptno; 

14.返回员工工作及其从事此工作的最低工资

select group_concat(ename),job as 工作名称,min(sal)as 最低工资 from emp group by job; 

15.返回工资处于第四级别的员工的姓名

select emp.ename from emp inner join salgrade on emp.sal between salgrade.lowsal and salgrade.hisal and salgrade.grade =4; 

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

 create view v_es as select emp.*,salgrade.grade from emp inner join salgrade on emp.sal between salgrade.lowsal and salgrade.hisal and salgrade.grade =2; 
 select v_es.*,loc from v_es inner join dept on v_es.deptno = dept.deptno; 

17.工资等级高于smith的员工信息

select v_es.*,loc from v_es inner join dept on v_es.deptno = dept.deptno  and v_es.grade>(select grade from v_es where ename = 'smith' )