–1. 列出至少有两个员工的部门。
SELECT
A.DEPTNO
FROM EMP A
GROUP BY A.DEPTNO
HAVING COUNT(A.EMPNO)>=2;
–2. 查询工资大于或者等于2000 的员工信息
SELECT
A.EMPNO,
A.ENAME,
A.JOB,
A.MGR,
A.HIREDATE,
A.SAL,
A.COMM,
A.DEPTNO
FROM EMP A
WHERE A.SAL>=2000;
–3. 查询至少有两个员工的工资不低于2000的部门编号
SELECT
A.DEPTNO
FROM EMP A
WHERE A.SAL>=2000
GROUP BY A.DEPTNO
HAVING COUNT(A.EMPNO)>=2;
–4. 列出薪金比“SMITH”多的所有员工。
SELECT
B.ENAME
FROM EMP B
WHERE B.SAL>(SELECT
A.SAL
FROM EMP A
WHERE A.ENAME='SMITH'
);
–5. 查询工资 与 SCOTT 相同的员工信息
SELECT
B.EMPNO,
B.ENAME,
B.JOB,
B.MGR,
B.HIREDATE,
B.SAL,
B.COMM,
B.DEPTNO
FROM EMP B
WHERE B.SAL=(SELECT
A.SAL
FROM EMP A
WHERE A.ENAME='SCOTT'
);
–6. 查询工资 与 SCOTT 相同的员工信息,并且不返回SCOTT自己的信息
SELECT
B.EMPNO,
B.ENAME,
B.JOB,
B.MGR,
B.HIREDATE,
B.SAL,
B.COMM,
B.DEPTNO
FROM EMP B
WHERE B.SAL=(SELECT
A.SAL
FROM EMP A
WHERE A.ENAME='SCOTT'
)
and b.ename <>'SCOTT';
–7.列出所有员工的姓名及其直接上级的姓名。
SELECT
B.ENAME
,A.ENAME
FROM EMP A
INNER JOIN EMP B
ON A.EMPNO=B.MGR;
–8.列出受雇日期早于其直接上级的所有员工。
SELECT
B.ENAME
FROM EMP A
INNER JOIN EMP B
ON A.EMPNO=B.MGR
AND A.HIREDATE>B.HIREDATE ;
–9.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。
SELECT
A.DNAME
,B.EMPNO,
B.ENAME,
B.JOB,
B.MGR,
B.HIREDATE,
B.SAL,
B.COMM,
B.DEPTNO
FROM DEPT A
LEFT JOIN EMP B
ON A.DEPTNO=B.DEPTNO;
–10.列出所有“CLERK”(办事员)的姓名及其部门名称。
SELECT
A.ENAME
,B.DNAME
FROM EMP A
INNER JOIN DEPT B
ON A.DEPTNO=B.DEPTNO
WHERE A.JOB='CLERK';
–11.列出最低薪金大于1500的各种工作。
SELECT
A.JOB
FROM EMP A
GROUP BY A.JOB
HAVING MIN(A.SAL)>1500;
–12.列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。
SELECT
A.ENAME
FROM EMP A
WHERE A.DEPTNO=(SELECT
B.DEPTNO
FROM DEPT B
WHERE B.DNAME='SALES'
);
---------------------------------------
SELECT
A.ENAME
FROM EMP A
INNER JOIN DEPT B
ON A.DEPTNO=B.DEPTNO
AND B.DNAME='SALES' ;
------------------------------------- WITH AS 临时表
WITH ABC AS (SELECT
B.DEPTNO
FROM DEPT B
WHERE B.DNAME='SALES'
)
SELECT
A.ENAME
FROM EMP A
INNER JOIN ABC B
ON A.DEPTNO=B.DEPTNO ;
–13.列出薪金高于公司平均薪金的所有员工。
SELECT
B.ENAME
FROM EMP B
INNER JOIN(SELECT
AVG(A.SAL) AVG_SAL
FROM EMP A
)A1
ON B.SAL>A1.AVG_SAL ;
------------------------------
select
B.ENAME
from emp b
WHERE B.SAL>(SELECT
AVG(A.SAL)
FROM EMP A
);
–14.列出与“SCOTT”从事相同工作的所有员工。
SELECT
B.ENAME
FROM EMP B
WHERE B.JOB=(SELECT
A.JOB
FROM EMP A
WHERE A.ENAME='SCOTT'
);
–15.列出薪金等于部门30中员工的薪金的非30号部门的员工的姓名和薪金。
SELECT
A.ENAME
,A.SAL
FROM EMP A
INNER JOIN EMP B
ON A.SAL=B.SAL
AND A.DEPTNO<>B.DEPTNO
AND B.DEPTNO=30;
-----------------------
SELECT
A.SAL
,A.ENAME
FROM EMP A
WHERE EXISTS(SELECT
B.SAL
FROM EMP B
WHERE B.DEPTNO=30
AND A.SAL=B.SAL
)
AND A.DEPTNO<>30;
------------------------
SELECT
A.SAL
,A.ENAME
FROM EMP A
WHERE A.SAL IN(SELECT B.SAL FROM EMP B WHERE B.DEPTNO=30)
AND A.DEPTNO<>30;
–16.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
SELECT
A.ENAME
,A.SAL
FROM EMP A
WHERE A.SAL>(SELECT MAX(B.SAL) FROM EMP B WHERE B.DEPTNO=30);
–17.列出在每个部门工作的员工数量、平均工资和平均服务期限。
SELECT
A.DEPTNO
,COUNT(A.EMPNO)
,ROUND(AVG(A.SAL),2)
,ROUND(AVG(SYSDATE-A.HIREDATE)/365)
FROM EMP A
GROUP BY A.DEPTNO;
–18.列出所有员工的姓名、部门名称和工资。
SELECT
A.ENAME
,B.DNAME
,A.SAL
FROM EMP A
LEFT JOIN DEPT B
ON A.DEPTNO=B.DEPTNO;
–19.列出所有部门的详细信息和部门人数。
WITH ABC AS(SELECT
A.DEPTNO
, COUNT(A.EMPNO) COUNT_M
FROM EMP A
GROUP BY A.DEPTNO
)
SELECT
B.DEPTNO
,B.DNAME
,B.LOC
,NVL(C.COUNT_M ,0)
FROM DEPT B
LEFT JOIN ABC C
ON B.DEPTNO=C.DEPTNO ;
–20.列出各种工作的最低工资。
SELECT
A.JOB
,MIN(A.SAL)
FROM EMP A
GROUP BY A.JOB;
–21.列出各个部门的MANAGER(经理)的最低薪金。
SELECT
A.DEPTNO
,MIN(A.SAL)
FROM EMP A
WHERE A.JOB='MANAGER'
GROUP BY A.DEPTNO;
–22.列出所有员工的年工资,按年薪从低到高排序。
SELECT
A.ENAME
,(A.SAL+NVL(A.COMM,0))*12
FROM EMP A
ORDER BY (A.SAL+NVL(A.COMM,0))*12;
–23.查出emp表中薪水在3000以上(包括3000)的所有员工的员工号、姓名、薪水。
SELECT
A.EMPNO
,A.ENAME
,A.SAL
FROM EMP A
WHERE A.SAL>=3000;
–24.查询出所有薪水在’ALLEN’之上的所有人员信息。
SELECT
A.ENAME
FROM EMP A
WHERE A.SAL>(SELECT B.SAL FROM EMP B WHERE B.ENAME='ALLEN');
–25.查询出emp表中部门编号为20,薪水在2000以上(不包括2000)的所有员工,显示他们的员工号,姓名以及薪水,以如下列名显示:员工编号 员工名字 薪水
SELECT
A.EMPNO 员工号
,A.ENAME 姓名
,A.SAL 薪水
FROM EMP A
WHERE A.SAL>2000;
–26.查询出emp表中所有的工作种类(无重复)
SELECT
A.JOB
FROM EMP A
GROUP BY A.JOB;
–27.查询出所有奖金(comm)字段不为空的人员的所有信息。
SELECT
A.EMPNO,
A.ENAME,
A.JOB,
A.MGR,
A.HIREDATE,
A.SAL,
A.COMM,
A.DEPTNO
FROM EMP A
WHERE A.COMM IS NOT NULL;
–28.查询出薪水在800到2500之间(闭区间)所有员工的信息。(注:使用两种方式实现and以及between and)
SELECT
a.ename
FROM EMP A
WHERE A.SAL>=800
AND A.SAL<=2500;
------------------
SELECT
a.ename
FROM EMP A
WHERE A.SAL between 800AND 2500;
–29.查询出员工号为7521,7900,7782的所有员工的信息。(注:使用两种方式实现,or以及in)
select
A.ENAME
from EMP A
WHERE A.EMPNO IN(7521,7900,7782);
---------------------------------------
select
A.ENAME
from EMP A
WHERE A.EMPNO =7521
OR A.EMPNO =7900
OR A.EMPNO =7782;
–30.查询出名字中有“A”字符,并且薪水在1000以上(不包括1000)的所有员工信息。
SELECT
*
FROM EMP A
WHERE A.ENAME LIKE '%A%'
AND A.SAL>1000;
--31.查询出名字第三个字母是“M”的所有员工信息。
SELECT
A.ENAME
FROM EMP A
WHERE A.ENAME LIKE '__M%';
–32.将所有员工按薪水升序排序,薪水相同的按照入职时间降序排序。
SELECT
*
FROM EMP A
order by A.SAL ,A.HIREDATE DESC;
–33.将所有员工按照名字首字母升序排序,首字母相同的按照薪水降序排序。
SELECT
*
FROM EMP A
ORDER BY SUBSTR(A.ENAME,1,1),A.SAL DESC;
–34.查询出最早工作的那个人的名字、入职时间和薪水。
SELECT
B.ENAME
,B.HIREDATE
,B.SAL
FROM EMP B
WHERE B.HIREDATE=(SELECT
MIN(A.HIREDATE)
FROM EMP A
);
–35.显示所有员工的名字、薪水、奖金,如果没有奖金,暂时显示100.
SELECT
A.ENAME
,A.SAL
,NVL(A.COMM,100)
FROM EMP A;
–36.显示出薪水最高人的职位。
SELECT
A.JOB
FROM EMP A
WHERE A.SAL=(SELECT MAX(SAL) FROM EMP );
–37.查出emp表中所有部门的最高薪水和最低薪水,部门编号为10的部门不显示。
SELECT
A.DEPTNO
,MAX(A.SAL)
,MIN(A.SAL)
FROM EMP A
WHERE A.DEPTNO<>10
GROUP BY A.DEPTNO;
–38.删除10号部门薪水最高的员工(不需要 commit)。
DELETE FROM EMP A WHERE A.SAL=(SELECT MAX(B.SAL) FROM EMP B WHERE B.DEPTNO=10);
SELECT * FROM EMP
–39.将薪水最高的员工的薪水降30%。
UPDATE EMP A SET A.SAL=A.SAL*0.7 WHERE A.SAL=(SELECT MAX(B.SAL) FROM EMP B );
–40.查询员工姓名,工资和 工资级别(工资>=3000 为3级,工资>2000 为2级,工资<=2000 为1级)
SELECT
A.ENAME 姓名
,A.SAL 工资
,CASE WHEN A.SAL >=3000 THEN '3级'
WHEN A.SAL >2000 THEN '2级'
WHEN A.SAL <=2000 THEN '1级'
END 工资级别
FROM EMP A ;