SQL案例

1.

select e.employee_name AS MANAGER,ROW_NUMBER()over(PARTITION by e.date order by e.salary DESC) AS RN FROM
(
(select DISTINCT b.manager_id,a.employee_name from Employee a inner join Employee b on a.employee_id = b.manager_id) c
left join
(select employee_id,salary,date from Salary) d on c.manager_id = d.employee_id
) e
WHERE RN = 1
2.
select d.employee_id,d.employee_name,d.SalaryByYear from (
(
select a.employee_id,a.employee_name from Employee a where a.employee_id not in
(select distinct manager_id from Employee)
) b
left join
(
select employee_id,SUBSTR(date,1,4) as year,sum(salary) as SalaryByYear from Salary GROUP by employee_id,SUBSTR(date,1,4)
) c
on b.employee_id = c.employee_id
)d order by d.SalaryByYear desc limit 1

3.

    select DISTINCT b.COMPANYCOD from
    (
    (select BILLNUMBERID,ORDERNO,ADJDIRECT,COMPANYCOD,count(*) as count from sheet1 WHERE ADJDIRECT = "调入方" GROUP BY BILLNUMBERID,ORDERNO,ADJDIRECT,COMPANYCOD) a
    INNER JOIN
    (select BILLNUMBERID,ORDERNO,ADJDIRECT,COMPANYCOD,count(*) as count from sheet1 WHERE ADJDIRECT = "调出方" GROUP BY BILLNUMBERID,ORDERNO,ADJDIRECT,COMPANYCOD) b
    on a.BILLNUMBERID = b.BILLNUMBERID and a.ORDERNO = b.ORDERNO and a.COMPANYCOD=b.COMPANYCOD and a.count = b.count
    )

4.

    select distinct b.COMPANYCOD from
    (
    (select BILLNUMBERID,ORDERNO,ADJDIRECT,COMPANYCOD,count(*) as count from sheet1 WHERE ADJDIRECT = "调入方" GROUP BY BILLNUMBERID,ORDERNO,ADJDIRECT,COMPANYCOD having count=1) a
    INNER JOIN
    (select BILLNUMBERID,ORDERNO,ADJDIRECT,COMPANYCOD from sheet1 WHERE ADJDIRECT = "调出方") b
    on a.BILLNUMBERID = b.BILLNUMBERID and a.ORDERNO = b.ORDERNO and a.COMPANYCOD=b.COMPANYCOD
    )

5.

    select a.BILLNUMBERID,sum(ADJUSTMONEY + AFTERTAXMONEY) AS 调整总金额 from
    (SELECT distinct BILLNUMBERID,ADJUSTWAYC from sheet1 where ADJUSTWAYC = "双向调整") a
    INNER JOIN sheet1 b on a.BILLNUMBERID = b.BILLNUMBERID
    GROUP BY a.BILLNUMBERID

6.删除工资大于所在部门平均工资的员工记录。
delete from emp where sal>(select avg(sal) from emp where deptno=emp1.deptno);

7.查询大于自己部门平均工资的员工姓名、工资、所在部门平均工资、高于部门平均工资的额度。
select ename,sal,avgsal,sal-avgsal from emp e,(select deptno,avg(sal) avgsal from emp group by deptno )d where e.deptno=d.deptno and e.sal>d.avgsal;

8.查询所有工作在NEW YORK和CHICAGO的员工姓名、员工编号，以及他们的经理姓名、经理编号。
select e.ename,e.empno,m.ename,m.empno from emp e,emp m,dept d where e.mgr=m.empno and e.deptno=d.deptno and d.loc in(‘NEW YORK’,‘CHICAGO’);

9.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名、职位，不包括10部门员工。
select ename,job from emp where (mgr,job)in(select job,mgr from emp where deptno=10) and deptno<>10;

10.列出一个雇员都没有的所有部门名称。
select dname from dept d where not exists(select 1 from emp where deptno=d.deptno);

11.列出至少有一个雇员的所有部门名称。
select dname from dept d where exists (select 1 from emp where deptno=d.deptno);

12查询入职日期在1982年1985年的员工姓名，入职日期。
select ename,hiredate from emp where extract(year from hiredate) between 1982 and 1985;

13、查询入职日期最早的前5名员工姓名。
答案： select ename,hiredate from emp e where (select count(empno) from emp where hiredate<e.hiredate)<5;