135 lines
2.0 KiB
SQL
135 lines
2.0 KiB
SQL
/*
|
|
Author: Fromsko
|
|
DATE: 2024.09.10 20:30
|
|
*/
|
|
|
|
-- 查询两个表
|
|
SELECT
|
|
*
|
|
FROM
|
|
EMP_WHC;
|
|
|
|
SELECT
|
|
*
|
|
FROM
|
|
DEPT_WHC;
|
|
|
|
-- 工资大于张无忌的
|
|
SELECT
|
|
ENAME,
|
|
SALARY
|
|
FROM
|
|
EMP_WHC
|
|
WHERE
|
|
SALARY > ANY(
|
|
SELECT
|
|
SALARY
|
|
FROM
|
|
EMP_WHC
|
|
WHERE
|
|
ENAME='张无忌'
|
|
);
|
|
|
|
SELECT
|
|
ENAME,
|
|
DEPTNO
|
|
FROM
|
|
EMP_WHC
|
|
WHERE
|
|
DEPTNO IN (
|
|
SELECT
|
|
DEPTNO
|
|
FROM
|
|
EMP_WHC
|
|
WHERE
|
|
ENAME='张无忌'
|
|
)
|
|
AND ENAME<>'张无忌';
|
|
|
|
SELECT
|
|
ENAME,
|
|
E.DEPTNO,
|
|
A.COU,
|
|
A.AVG_SAL
|
|
FROM
|
|
EMP_WHC E,
|
|
(
|
|
SELECT
|
|
DEPTNO,
|
|
COUNT(*) COU,
|
|
AVG(NVL(SALARY, 0)) AVG_SAL
|
|
FROM
|
|
EMP_WHC
|
|
WHERE
|
|
DEPTNO IS NOT NULL
|
|
GROUP BY
|
|
DEPTNO
|
|
) A
|
|
WHERE
|
|
E.DEPTNO = A.DEPTNO;
|
|
|
|
--
|
|
SELECT
|
|
DEPTNO,
|
|
AVG(NVL(SALARY, 0))
|
|
FROM
|
|
EMP_WHC
|
|
WHERE
|
|
DEPTNO IS NOT NULL
|
|
GROUP BY
|
|
DEPTNO
|
|
HAVING
|
|
AVG(NVL(SALARY, 0)) > (
|
|
SELECT
|
|
AVG(NVL(SALARY, 0))
|
|
FROM
|
|
EMP_WHC
|
|
WHERE
|
|
DEPTNO=20
|
|
);
|
|
|
|
--列出员工的名字薪水和部门还有职位 要求这些员工所在
|
|
--部门的人数比10部门的人数多
|
|
SELECT
|
|
ENAME,
|
|
SALARY,
|
|
DEPTNO,
|
|
JOB
|
|
FROM
|
|
EMP_WHC
|
|
WHERE
|
|
DEPTNO IN (
|
|
SELECT
|
|
DEPTNO
|
|
FROM
|
|
EMP_WHC
|
|
WHERE
|
|
DEPTNO IS NOT NULL
|
|
GROUP BY
|
|
DEPTNO
|
|
HAVING
|
|
COUNT(*) > (
|
|
SELECT
|
|
COUNT(*)
|
|
FROM
|
|
EMP_WHC
|
|
WHERE
|
|
DEPTNO =10
|
|
)
|
|
);
|
|
|
|
--那些员工的薪水比本部门的平均薪水低?
|
|
SELECT
|
|
ENAME,
|
|
SALARY
|
|
FROM
|
|
EMP_WHC E
|
|
WHERE
|
|
SALARY <(
|
|
SELECT
|
|
AVG(SALARY)
|
|
FROM
|
|
EMP_WHC
|
|
WHERE
|
|
DEPTNO = E.DEPTNO
|
|
) |