107 lines
1.8 KiB
SQL
107 lines
1.8 KiB
SQL
-- 查询名字中有 k
|
|
-- 并薪水大于 2000 的员工姓名、薪水、以及年收入(12*(薪水+奖金))
|
|
SELECT
|
|
ENAME,
|
|
SAL,
|
|
COMM,
|
|
NVL(COMM, 0),
|
|
12*(SAL+NVL(COMM, 0)) YEAR_SAL
|
|
FROM
|
|
EMP
|
|
WHERE
|
|
ENAME LIKE '%K%'
|
|
AND SAL > 2000;
|
|
|
|
-- 查询那些部门的平均薪水大于2000
|
|
SELECT
|
|
*
|
|
FROM
|
|
(
|
|
SELECT
|
|
DEPTNO,
|
|
AVG(NVL(SAL, 0)) AVG_SAL
|
|
FROM
|
|
EMP
|
|
WHERE
|
|
DEPTNO IS NOT NULL
|
|
GROUP BY
|
|
DEPTNO
|
|
HAVING
|
|
AVG(NVL(SAL, 0)) > 2000
|
|
ORDER BY
|
|
DEPTNO
|
|
) A
|
|
JOIN DEPT D
|
|
ON A.DEPTNO=D.DEPTNO;
|
|
|
|
-- 那些人的薪水超过 10 部门的最高薪水
|
|
SELECT
|
|
ENAME 姓名,
|
|
SAL 薪水,
|
|
DEPTNO 部门
|
|
FROM
|
|
EMP E
|
|
WHERE
|
|
E.SAL > (
|
|
SELECT
|
|
MAX(SAL)
|
|
FROM
|
|
EMP
|
|
WHERE
|
|
EMP.DEPTNO=20
|
|
);
|
|
|
|
-- 查询各个部门的最低薪水
|
|
SELECT
|
|
DEPTNO 部门号,
|
|
LOW_SAL 最低薪水
|
|
FROM
|
|
(
|
|
SELECT
|
|
MIN(NVL(SAL, 0)) LOW_SAL
|
|
FROM
|
|
EMP
|
|
GROUP BY
|
|
DEPTNO
|
|
) L
|
|
JOIN EMP E
|
|
ON L.LOW_SAL=E.SAL;
|
|
|
|
SELECT
|
|
DEPTNO 部门号,
|
|
MIN(SAL) 最低薪水
|
|
FROM
|
|
EMP
|
|
GROUP BY
|
|
DEPTNO);
|
|
|
|
-- 查询各个部门的平均大于20部门的平均薪水
|
|
SELECT
|
|
DEPTNO,
|
|
AVG(NVL(SAL, 0))
|
|
FROM
|
|
EMP
|
|
WHERE
|
|
DEPTNO IS NOT NULL
|
|
GROUP BY
|
|
DEPTNO
|
|
HAVING
|
|
AVG(NVL(SAL, 0)) > (
|
|
SELECT
|
|
AVG(NVL(SAL, 0))
|
|
FROM
|
|
EMP
|
|
WHERE
|
|
DEPTNO=20
|
|
)
|
|
-- 查询员工姓名、薪水、部门号、和部门名称
|
|
SELECT
|
|
E.ENAME 姓名,
|
|
E.SAL 薪水,
|
|
E.ENAME 部门号,
|
|
E.DEPTNO 部门名称
|
|
FROM
|
|
EMP E,
|
|
DEPT D
|
|
WHERE
|
|
E.DEPTNO=D.DEPTNO; |