/*
tb_dept:部门表：
DEPTNO:部门编号
DNAME:部门名称
LOC:部门地点*/
if object_id('tb_dept') is not null drop table tb_dept
go
create table tb_dept(DEPTID char(4),DEPTNAME varchar(10),LOC varchar(50))
go
insert into tb_dept
select 'D001','ACCOUNTING','NEW YORK' union all
select 'D002','RESEARCH','DALLAS' union all
select 'D003','SALES','CHICAGO' union all
select '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_employee
select 7369,'SMITH','CLERK',7902,'1980-12-17',800,null,'D002' union all
select 7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,'D003' union all
select 7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,'D003' union all
select 7566,'JONES','MANAGER',7839,'1981-4-2',3975,null,'D002' union all
select 7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,'D003' union all
select 7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,null,'D003' union all
select 7788,'SCOTT','ANALYST',7566,'1987-4-19',3000,null,'D002' union all
select 7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,'D003' union all
select 7876,'ADAMS','CLERK',7788,'1987-5-23',1100,null,'D002' union all
select 7900,'JAMES','CLERK',7698,'1981-12-3',950,null,'D003' union all
select 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