求一道数据库面试题解决方案

/*tb_dept:部门表：DEPTNO:部门编号DNAME:部门名称LOC:部门地点*/if object_id('tb_dept') is not null drop table tb_deptgocreate table tb_dept(DEPTID char(4),DEPTNAME varchar(10),LOC varchar(50))goinsert into tb_deptselect 'D001','ACCOUNTING','NEW YORK' union allselect 'D002','RESEARCH','DALLAS' union allselect 'D003','SALES','CHICAGO' union allselect 'D004','OPERATIONS','BOSTON' --EMPNO:雇员编号--ENAME:雇员姓名--JOB:工作类别--MGR:上级编号--HIREDATE:雇佣日期--SAL:月薪--COMM:每月津贴--DEPTNO:部门编号create table tb_employee(EMPID int,EMPNAME varchar(10),JOB char(10) ,MGRID int,INDATE smalldatetime,SAL int,COMM int,DEPTID char(4))insert into tb_employeeselect 7369,'SMITH','CLERK',7902,'1980-12-17',800,null,'D002' union allselect 7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,'D003' union allselect 7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,'D003' union allselect 7566,'JONES','MANAGER',7839,'1981-4-2',3975,null,'D002' union allselect 7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,'D003' union allselect 7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,null,'D003' union allselect 7788,'SCOTT','ANALYST',7566,'1987-4-19',3000,null,'D002' union allselect 7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,'D003' union allselect 7876,'ADAMS','CLERK',7788,'1987-5-23',1100,null,'D002' union allselect 7900,'JAMES','CLERK',7698,'1981-12-3',950,null,'D003' union allselect 7902,'FORD','ANALYST',7566,'1981-12-3',3000,null,'D001'--1、列出月薪比 "BLAKE" 少的所有雇员select * from tb_employee where SAL<(select SAL from tb_employee where EMPNAME='BLAKE')--2、列出至少有一个雇员的部门详细信息select * from tb_dept where DEPTID in (select DEPTID from tb_employee group by DEPTID having COUNT(deptid)>0)--3、列出所有雇员的姓名及其直接上级的姓名  select ta.EMPID,ta.EMPNAME,ta.MGRID,ISNULL(tb.EMPNAME,'沒有上級') as '上級領導' from tb_employee ta left join tb_employee tb on ta.MGRID=tb.EMPID--4、列出入职日期早于其直接上级的所有雇员select ta.EMPID,ta.EMPNAME,ta.INDATE '自己indate',tb.INDATE '領導indate' from tb_employee ta left join tb_employee tb on ta.MGRID=tb.EMPID where ta.INDATE<tb.INDATE--5、列出没有雇员的部门信息   select * from tb_dept where DEPTID not in (select DEPTID from tb_employee group by DEPTID having COUNT(deptid)>0)--6、列出所有“CLERK”（办事员）的姓名及其部门名称  select ta.EMPNAME,tb.DEPTNAME from tb_employee ta left join tb_dept tb on ta.DEPTID=tb.DEPTID where JOB='CLERK'--7、列出各种工作类别中月薪大于1500的最低薪金select JOB,min(SAL) from tb_employee where SAL>1500 group by JOB--8、列出月薪高于公司平均水平的所有雇员select * from tb_employee where SAL>(select AVG(sal) from tb_employee)--9、列出与“SCOTT”从事相同工作的所有雇员select * from tb_employee where JOB=(select JOB from tb_employee where EMPNAME='SCOTT')--10、列出某些雇员的姓名和薪金，条件是他们的月薪高于部门D003中所有雇员的薪金select * from tb_employee where SAL>(select MAX(SAL) from tb_employee where DEPTID='D003')--11、列出每个部门的信息以及该部门中雇员的数量   select ta.*,(select COUNT(DEPTID) from tb_employee tb where ta.DEPTID=tb.DEPTID ) 'EmpCount' from tb_dept ta --12、列出所有雇员的雇员名称、部门名称和月薪   select ta.EMPNAME,tb_dept.DEPTNAME,ta.SAL from tb_employee ta left join tb_dept on ta.DEPTID=tb_dept.DEPTID--13、列出各个部门的MANAGER（经理）的最低薪金--答:兩表看不出誰是MANAGER--14、列出所有雇员的年薪，并且按年薪排序（按12个月计算）select EMPID,EMPNAME,INDATE,SAL*12 as 'YearSal' from tb_employee order by YearSal DESC--15、列出薪金水平处于第四位的雇员select TOP 1 * from (select top 4 EMPID,EMPNAME,INDATE,SAL from tb_employee order by SAL DESC) ta order by TA.SAL asc