【Oracle】Scott账号sql语句练习

       对于熟悉Oracle或者接触过Oracle的人，scott这个用户大家一定相当的熟悉。该账号在安装好Oracle的时候就有的一个初始账号，scott账号的密码是tiger,tiger是scott大神的一个宠物猫的名字。这里面有经典的三张表，EMP(员工雇佣表)、DEPT(部门表)、SALGRADE(薪资等级表)供我们练习使用，里面的数据都是精心设置的，符合大部分情况下的一个SQL查询，尤其入门Oracle或者学习Oracle的同学都建议在该账号上进行SQL练习。

接下来的练习题就是针对这三张表进行练习的。这里分为2个部分的查询， 普通查询和 函数练习。

普通查询

列出至少有一名员工的所有部门

SELECT
	dname AS 部门名称,
	loc AS 位置
FROM
	dept 
WHERE
	deptno IN ( SELECT DISTINCT DEPTNO FROM emp )

列出薪金比“SMITH”多的所有员工

SELECT
	* 
FROM
	emp 
WHERE
	sal > ( SELECT sal FROM emp WHERE ename = 'SMITH' )

列出所有员工的姓名及其上级的姓名

SELECT
	e1.ENAME AS 员工姓名,
	e2.ENAME AS 上级名称 
FROM
	emp e1
	LEFT JOIN emp e2 ON e1.MGR = e2.EMPNO

列出受雇日期早于其直接上级的所有员工

SELECT
	e1.ENAME AS 员工名称,
	e1.HIREDATE AS 员工入职日期,
	e2.ENAME AS 上级名称,
	e2.HIREDATE AS 上级入职日期 
FROM
	emp e1
	LEFT JOIN emp e2 ON e1.MGR = e2.EMPNO 
WHERE
	e1.HIREDATE < e2.HIREDATE

列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

SELECT
	d.DNAME AS 部门名称,
	e.ENAME AS 员工名称,
	e.JOB AS 岗位,
	e.SAL AS 薪资 
FROM
	DEPT d
	LEFT JOIN emp e ON d.DEPTNO = e.DEPTNO

列出所有“CLERK”（办事员）的姓名及其部门名称

SELECT
	e.ENAME AS 员工名称,
	d.DNAME AS 部门名称 
FROM
	emp e
	LEFT JOIN DEPT d ON d.DEPTNO = e.DEPTNO 
WHERE
	e.job = 'CLERK'

列出最低薪金大于1500的各种工作

SELECT DISTINCT
	job 
FROM
	emp 
WHERE
	sal < 1500

列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的员工编号

SELECT
	e.ename 
FROM
	emp e,
	dept d 
WHERE
	e.deptno = d.deptno 
	AND d.dname = 'SALES'

列出薪金高于公司的平均薪金的所有员工

SELECT
	ename 
FROM
	emp 
WHERE
	sal > ( SELECT avg( sal ) FROM emp )

列出与“SCOTT”从事相同工作的所有员工

SELECT
	ename 
FROM
	emp 
WHERE
	job = ( SELECT job FROM emp WHERE ename = 'SCOTT' )

函数练习

找出各月倒数第3天受雇的所有员工

SELECT
	* 
FROM
	emp 
WHERE
	hiredate = last_day( hiredate ) -2

找出早于12年前受雇的员工

SELECT
	* 
FROM
	emp 
WHERE
	months_between( SYSDATE, hiredate ) > 144

以首字母大写的方式显示所有员工的姓名

SELECT
	INITCAP( ename ) 
FROM
	emp

显示正好为5个字符的员工的姓名

SELECT
	ename 
FROM
	emp 
WHERE
	length( ename ) =5

显示不带有“R”的员工姓名

SELECT
	ename 
FROM
	emp 
WHERE
	instr( ename, 'R', 1, 1 ) =0 #‘R’在ename字段中 从第1个位置开始，第一次出现的索引

显示所有员工姓名的前三个字符

SELECT
	ename,
	SUBSTR( ename, 1, 3 ) 
FROM
	emp

显示所有员工的姓名,用“a”替换所有的“A”

SELECT
	ename,
	REPLACE ( ename, 'A', 'a' ) 
FROM
	emp

显示满10年服务年限的员工的姓名和受雇日期

SELECT
	ename,
	hiredate 
FROM
	emp 
WHERE
	months_between( SYSDATE, hiredate ) >= 120

显示员工的详细资料,按姓名排序

SELECT
	* 
FROM
	emp 
ORDER BY
	ename

显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面

SELECT
	ename,
	HIREDATE 
FROM
	emp 
ORDER BY
	HIREDATE

显示所有员工的姓名、工作和薪金，按照工作的降序排序，若工作相同按薪金排序

SELECT
	ename AS 姓名,
	job AS 工作,
	sal AS 薪金 
FROM
	emp 
ORDER BY
	job DESC,
	sal DESC

显示所有员工的姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同，把最早年份的排前面

SELECT
	ename,
	to_char( hiredate, 'yyyy' ) AS year,
	to_char( hiredate, 'mm' ) AS month 
FROM
	emp 
ORDER BY
	month,
	year

显示在一个月为30天的情况所有员工的日薪金，忽略余数

SELECT
	ename,
	trunc( sal / 30, 0 ) 
FROM
	emp

找出在(任何年份)的2月受聘的所有员工

SELECT
	* 
FROM
	emp 
WHERE
	to_char( hiredate, 'mm' ) = '02';

对于每个员工显示其加入公司的天数

SELECT
	ename,
	( SYSDATE - hiredate ) || '天' 
FROM
	emp;