一、说明
第一次使用Oracle,想做一些练习，熟悉一些oracle。
表：使用的是scott用户，默认的表
具体表讲解，可以参考该文档：https://www.cnblogs.com/xjcheng1/p/7220159.html
二、基础练习
第一、查询工资在0-1000,1000-2000,2000-3000,3000以上各个工资范围的员工数。
SELECT
SUM(CASE WHEN sal>0 AND sal<1000 THEN 1 ELSE 0 END) AS "0<sal<1000",
SUM(CASE WHEN sal>1000 AND sal<2000 THEN 1 ELSE 0 END) AS "1000<sal<2000",
SUM(CASE WHEN sal>2000 AND sal<3000 THEN 1 ELSE 0 END) AS "2000<sal<3000",
SUM(CASE WHEN sal>3000 THEN 1 ELSE 0 END) AS "sal>3000"
FROM
emp
第二、要求查询出: 部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员的姓名。
SELECT
d.dname as "部门名称",
s.count as "部门的员工数",
s.avgsal as "部门的平均工资",
e.ename as "最低收入雇员的姓名"
FROM
emp e ,
dept d,
(
SELECT
deptno,COUNT(1) count,AVG(sal) avgsal,MIN(sal) minsal
FROM
emp
GROUP BY
deptno
) s
WHERE
e.deptno = d.deptno
AND
e.deptno = s.deptno
AND
s.minsal = e.sal
第三、查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职。
SELECT
COUNT(1),
job
FROM
emp
WHERE
sal > (SELECT AVG(SAL) FROM emp)
GROUP BY
job
第四、列出所有员工的姓名及其直接上级的姓名。
SELECT
e1.ename,
e2.ename AS manage
FROM
emp e1 LEFT JOIN emp e2
ON
e1.mgr = e2.empno
第五、返回比本部门平均工资高的员工的empno，ename，deptno，sal，以及平均工资。
SELECT
t1.empno,
t1.ename,
t1.deptno,
t1.sal,
t2.avgsal
FROM
emp t1 , (SELECT deptno,AVG(sal) avgsal FROM emp t2 GROUP BY t2.deptno) t2
WHERE
t1.deptno = t2.deptno
AND
t1.sal > t2.avgsal
第六、查询与7369或者7499号具有相同job和deptno的其他员工的empno,ename,job和empno。
SELECT
empno,
ename,
job,
deptno
FROM
emp
WHERE
(job,deptno)
IN
(
SELECT
job,
deptno
FROM
emp
WHERE
empno
IN
(7369,7499)
)
第七、查询员工工资2到5名的员工信息。
SELECT
*
FROM
(
SELECT
rownum r,empno,ename,sal
FROM
(
SELECT
empno,ename,sal
FROM
emp
ORDER BY
sal
DESC
)
) t
WHERE
t.r>=2 and t.r<=5
第八、列出薪金比smith多的所有员工信息。
SELECT
*
FROM
emp
WHERE
sal >
(
SELECT
sal
FROM
emp
WHERE
ename = 'SMITH'
)
第九、把hiredate列看做是员工的生日,求本月过生日的员工。
SELECT
*
FROM
emp
WHERE
EXTRACT(MONTH FROM hiredate)
=
EXTRACT(MONTH FROM sysdate);
第十、查询出1981各个月入职的员工数。
SELECT
COUNT(*),
to_char(hiredate,'yyyy-MM')
FROM
emp
WHERE
to_char(hiredate,'yyyy') = '1981'
GROUP BY
to_char(hiredate,'yyyy-MM')
ORDER BY
to_char(hiredate,'yyyy-MM')
第十一、查询和scott相同部门的员工姓名ename和雇用日期hiredate。
SELECT
ename,
hiredate
FROM
emp
WHERE
deptno
=
(
SELECT
deptno
FROM
emp
WHERE
ename = 'SCOTT'
)
第十二、查询在部门的loc为NEW YORK的部门工作的员工的员工姓名ename，部门名称dname和岗位名称job。
SELECT
e.ename,
d.dname,
e.job
From
emp e , dept d
WHERE
e.deptno = d.deptno
AND
dname =
(
SELECT
dname
FROM
dept
WHERE
loc = 'NEW YORK'
)
第十三、查询上司是king的员工姓名(ename)和工资(sal)。
SELECT
ename,
sal
FROM
emp
WHERE
mgr =
(
SELECT
empno
FROM
emp
WHERE
ename = 'KING'
)
第十四、查询与姓名中包含字母U的员工在相同部门的员工信息。
SELECT
*
FROM
emp
WHERE
deptno
IN
(
SELECT
deptno
FROM
emp
WHERE
ename
LIKE
'%U%'
)
第十五、查询所有雇员姓名和部门名称(使用left join，inner join, right join)。
SELECT
e.ename,
d.dname
FROM
emp e
INNER JOIN
dept d
ON
e.deptno = d.deptno
注意：这里的INNER JOIN 可以直接替换成 LEFT JOIN 和 RIGHT JOIN
第十六、显示每个员工的员工姓名、部门名称、职务、工资、和工资等级信息(使用left join，inner join, right join)。
SELECT
e.ename,
d.dname,
e.job,
e.sal,
s.grade
FROM
emp e
INNER JOIN
dept d
ON
e.deptno = d.deptno
INNER JOIN
salgrade s
ON
e.sal
BETWEEN
s.losal
AND
s.hisal
注意：这里的INNER JOIN 可以直接替换成 LEFT JOIN 和 RIGHT JOIN
第十七、取得每个部门最高薪水的人员名称。
SELECT
e.ename,
r.maxsal,
r.deptno
FROM
emp e ,
(
SELECT
MAX(sal) maxsal,
deptno
FROM
emp
GROUP BY
deptno
) r
WHERE
e.deptno = r.deptno
AND
e.sal = r.maxsal
ORDER BY
deptno
第十八、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称。
SELECT
*
FROM
(
SELECT
e1.empno,
e1.deptno,
e1.ename,
e2.ename AS manage,
e2.hiredate managedate,
e1.hiredate staffdate
FROM
emp e1 LEFT JOIN emp e2
ON
e1.mgr = e2.empno
) z
WHERE
z.staffdate < z.managedate
ORDER BY
z.empno
第十九、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数。
SELECT
s.ename,
s.dname,
k.deptno,
k.count
FROM
(
SELECT
e.ename,
d.dname,
e.deptno deptno
FROM
emp e , dept d
WHERE
e.deptno = d.deptno
AND
job = 'CLERK'
) s,
(
SELECT
COUNT(*) count,
deptno
FROM
emp
GROUP BY
deptno
) k
WHERE
s.deptno = k.deptno
第二十、找出早于12年前受雇的员工. 并且按受雇年份倒序排序。
SELECT
e.*,
TO_CHAR(hiredate,'yyyy') 受雇年份,
ROUND(MONTHS_BETWEEN(SYSDATE,hiredate)/12,2) 受雇年限
FROM
emp e
WHERE
MONTHS_BETWEEN(SYSDATE,hiredate)/12>12
ORDER BY
受雇年份
DESC
第二十一、列出从事同一种工作但属于不同部门的员工的一种组合。
SELECT
DISTINCT
e.empno,
e.ename,
e.job,
e.deptno
FROM
emp e,emp k
WHERE
e.deptno != k.deptno
AND
e.job = k.job
ORDER BY
job,deptno
三、重点说明
1.oracle数据库，sql语句不能使用limit关键字。
2.rownum非常的特殊，使用的时候需要特别注意。详细可参考文档：https://www.cnblogs.com/szlbm/p/5806070.html
3.所有包含中文的字体，需要加单引号。比如：job = 'CLERK' ，ename = 'KING'
4.函数的使用。
to_char() 函数功能，就是将数值型或者日期型转化为字符型。具体参考文档：https://www.cnblogs.com/aipan/p/7941917.html
extract()函数----用于截取年、月、日、时、分、秒。具体参考文档：https://www.cnblogs.com/xqzt/p/4477239.html