1、按照部门的编号，分组，求出每个部门的平均薪水？最高薪水？
实现：SQL>select deptno,avg(sal) from emp group by deptno;
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
20 2012.5
30 1562.85714
SQL> select deptno,max(sal) from empgroup by deptno;
DEPTNO MAX(SAL)
---------- ----------
10 5000
20 3000
30 2850
2、把题目1中平均薪水大于2000的组找出来。（这里要注意，where语句是对单条记录进行过滤的，你不能用where语句过滤分组之后的记录，因为你要明白，在实际执行中，它会首先执行where语句。执行where之后才能分组。Having对分组后的结果进行限制。）
实现：SQL>select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
20 2012.5
3、求出薪水值大于1200，按照部门编号进行分组，分组之后的平均薪水大于1500，最后按照组平均薪水的降序排列：
实现：SQL>select deptno, avg(sal) from emp where sal>1200 group by deptno having avg(
sal)>1500 order by avg(sal) desc;
DEPTNO AVG(SAL)
---------- ----------
20 2991.66667
10 2916.66667
30 1665
4、求出谁赚得钱最多。（可能你会用selectename, max(sal) from emp;乍一看，挺好啊。但是，你仔细想一想。Max函数只返回一个结果，ename怎么办呢？？所以，这种查询是不行的。）这时，我们考虑到用子查询，先求出最高的工资数，然后在根据工资数求结果。
SQL> select empno,ename,sal from empwhere sal=(select max(sal) from emp);
EMPNO ENAME SAL
---------- ---------- ----------
7788 SCOTT 5000
7839 KING 5000
子查询可以放在where 子句中，也可以放在from 子句中。
5、找出谁的工资在本部门中最高的？（你可能会想当然的写出如下语句：SQL>select empno,ename,sal,deptno from emp where sal=(select max(sal) from emp)group by deptno; 你仔细想想，where后面的sal=max。。只会返回一个值，怎么还能分组呢？你也可能会想当然的下出下面的语句：select empno,ename,sal,deptno from emp wheresal=(select max(sal) from emp group by deptno)把group放到子查询里面，但是，还是差不多的原因，where后面的等于一定返回一个值，而子查询这次会返回多个值。也是不行的；还有更聪明的同学，可能会想，我不要等于了，我改成in：selectempno,ename,sal,deptno from emp where sal in (select max(sal) from emp group bydeptno) 啊哈。。这下子出结果了：SQL> select empno,ename,sal,deptno from empwhere sal in (select max(sal) from e
mp group by deptno);
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7839 KING 5000 10
7788 SCOTT 5000 20
7698 BLAKE 2850 30
可是，我们继续分析。加入10这个部门有一个人的薪水是2850.。好了，这下2850也在子查询结果集里，肯定要把10这个部门的，这个人选出来。。可是明明他不是最高的嘛。。。这下问题出来了。。这样也是不行的。。。） 肿么办？看下面正确的思路：我们先查询出每个部门的最高薪水，这个其实很简单：SQL> select max(sal) ,deptno from emp group by deptno;
MAX(SAL) DEPTNO
---------- ----------
5000 10
5000 20
2850 30
现在，我们用emp表来链接它，这样就能保证薪水最高，和部门连接。
SQL> select empno,ename,sal,emp.deptnofrom emp,(select max(sal) max_sal,deptno
from emp group by deptno) t whereemp.sal=t.max_sal and emp.deptno=t.deptno;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7698 BLAKE 2850 30
7788 SCOTT 5000 20
7839 KING 5000 10
SQL> select empno,ename,sal,emp.deptnofrom emp join (select max(sal) max_sal,de
ptno from emp group by deptno) t on(emp.sal=t.max_sal and emp.deptno=t.deptno);
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7698 BLAKE 2850 30
7788 SCOTT 5000 20
7839 KING 5000 10
Ok，这下出来了。总结一下，对于分组求最高的问题，都要用这种子查询，加连接的形式。
6、求每个部门的平均薪水的等级？
同样得用到子查询，连接等级表。这里需要复习一下等级查询：
SQL> select grade,t.deptno from (selectavg(sal) avg_sal,deptno from emp group b
y deptno) t,salgrade s where (avg_sal betweens.losal and s.hisal);
GRADE DEPTNO
---------- ----------
4 10
4 20
3 30
7、求出每个人的经理人的名字（我们想，这张emp表中就记录了所有的信息，但是问题是咱们要查的都是名字，有的人是别人的经理人，但是他也有上级经理人。。内容比较混乱，查询方法比较简单）这就是传说中的自连接：
SQL> select e1.ename,e2.ename from empe1,emp e2 where e1.mgr=e2.empno;
ENAME ENAME
---------- ----------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
。。。。。。。。。。
已选择14行。
8、求出每个人的经理人的名字，但是没有经理人的人也给他写在左侧：
SQL> select e1.ename,e2.ename from empe1 left join emp e2 on( e1.mgr=e2.empno); ——这里left（outer） join就是传说中的左外连接。它会把左边这张表的不能和右表连接的数据列在左侧。
ENAME ENAME
---------- ----------
FORD JONES
SCOTT JONES
JAMES BLAKE
……………..
JONES KING
LEWIS FORD
SMITH FORD
TEST3
TEST2
KING
TOM
已选择18行。
9、求出每个雇员的名字，他所在的部门的名称。并且把所有的部门名称都写出来（即使一个雇员也没有）：
如果你用92年语法，用where条件语法：
SQL> select ename , dname from emp , dept where emp.deptno=dept.deptno;
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
………….
MARTIN SALES
JAMES SALES
TURNER SALES
WARD SALES
已选择18行。
很显然的问题是没有把，空部门的名称显示出来，这个不符合我们的要求。怎么办？啊哈。。。传说中的右外连接出场了！！！
SQL> select ename , dname from emp rightjoin dept on(emp.deptno=dept.deptno);
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
……………….
MARTIN SALES
JAMES SALES
TURNER SALES
WARD SALES
OPERATIONS
已选择19行。
啊哈，就是这个样子咯。。。还有一个 全外连接的东西哦。。就是full join 把左右都取出来。
10、求出雇员中哪些人是经理人（思考：只要在mgr列出现的empno都是经理人啊，所以嘛，我们完全可以用in关键字来处理）。。：
SQL> select ename from emp where empnoin (select distinct mgr from emp);
ENAME
----------
FORD
BLAKE
KING
JONES
SCOTT
CLARK
已选择6行。
11、不准用组函数，求出薪水的最高值，所对应的人。（思考：我们把两个表e1，e2中的sal作小于比较，那么左边这张表e1中，肯定有一个值是没法选出来的，因为它是最高值。这样我们选出了除了最高值以外的所有值。）
SQL>select empno,sal from emp where salnot in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal))
EMPNO SAL
---------- ----------
7788 5000
7839 5000
12、求出平均薪水最高的部门的部门编号。
第一眼看上去不难呀。。。自己试着写了一下。。其实还挺难的呜呜。。。
第一步，可能是要求出每个部门的平均薪水吧，这个没有异议：
SQL> select deptno, avg(sal) from empgroup by deptno;
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
20 2345.83333
30 1562.85714
这下，再看，最高的平均薪水不就出来啦嘛哈哈。。。
SQL> select max(avg_sal) from (selectdeptno, avg(sal) avg_sal from emp group by deptno)
MAX(AVG_SAL)
------------
2916.66667
有了最高的值，然后用一个where等值连接，通过查询第一步中的表，部门编号就出来了
SQL>Select deptno,avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno)
where
avg_sal =( select max(avg_sal) from (select deptno, avg(sal) avg_sal from emp group bydeptno));
13、求出平均薪水最高的部门的部门名称。
这个是根据上一个题的结果求出来的，如果直接给出这个题。估计一般童鞋们会思考一段时间才能解决。。。
SQL> select dname ,deptno fromdept
where
deptno= (Select deptno,avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno)
where
avg_sal =( select max(avg_sal) from (select deptno, avg(sal) avg_sal from emp group bydeptno))
);
DNAME DEPTNO
-------------- ----------
ACCOUNTING 10
注意，这里也可以用组函数的嵌套，也就是说，把两个组函数写在一起：select max(avg_sal) from (select deptno, avg(sal) avg_sal from emp group bydeptno)这句话可以这样写：select max(avg(sal)) from emp group by deptno)
如此一来，可以使你的程序稍微看少去简单明了。
14、求平均薪水的等级最低的部门的部门名称：
select dname from dept where deptno =(
select deptno from ( select grade ,t.deptno from salgrade s join(select deptno, avg(sal) avg_sal from emp group by deptno ) t on(t.avg_salbetween s.losal and s.hisal))where grade=(
select min(grade) from (
select grade ,t.deptno from salgrade s join(select deptno, avg(sal) avg_sal from emp group by deptno ) t on(t.avg_salbetween s.losal and s.hisal))))
啊哈。自己写出来了。。不过分析起来比较麻烦。哈哈。老师说一般的这么麻烦的不会在现实中用到，所以不深究了。。。
15、求比普通员工的最高薪水还要高的经理人的名称：
二话不说，先求出普通员工的最高薪水：SQL> select max(sal) from emp where empnonot in(select mgr from emp );
MAX(SAL)
----------
我擦来。。没出结果。。咋回事。。原来king这个行中没有mgr的数据。一旦有了空值，任何empno就会被认为在select mgr from emp 的结构集中。。。。肿么办？很简单。。加上 is not null：
SQL> select max(sal) from emp whereempno not in(select distinct mgr from emp wh
ere mgr is not null );
MAX(SAL)
----------
1620
比这个值还要高的经理人： SQL> select ename from emp
where sal >
(select max(sal) from emp where empno not in(select distinct mgr fromemp w
here mgr is not null ))
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD
已选择6行。
再仔细想下。。现在看是没有问题的，问题是我们还丢了一个条件。这个人必须是经理人。。（因为这个表中除了经理人就是普通员工，所以结果是对的。。。）好吧，下面我们来加上这个条件。。
SQL> select ename from emp
where empno in (select mgr from emp where mgr is not null) and
sal >
(select max(sal) from emp where empno not in(select distinct mgr fromemp w
here mgr is not null ))
ENAME
----------
FORD
BLAKE
KING
JONES
SCOTT
CLARK
已选择6行。
这样子就完美了。。。哈哈。。。
经典学生选课表
SQL> select * from s;
SNO SNAME
---------- --------------------
1 zhangsan
2 lisi
3 wangwu
4 huliu
5 dingqi
SQL> select * from c;
CNOCNAME CTEACHER
---------- ---------------------------------
1chinese liming
2english wanghui
3math chenzhong
4 huaxue yanghua
SQL> select * from sc;
SNO CNO SCORADE
---------- ---------- ----------
1 1 78
2 1 93
3 1 63
3 2 37
3 4 57
2 4 77
2 3 47
4 3 77
4 1 57
4 2 97
5 2 56
5 4 89
已选择12行
16、学生选课表中，找出没有选过“黎明”老师课程的所有学生姓名：
可能你会想，我可以先选出老师名不是黎明的课程编号。然后跟sc连接，求出没选这个编号的学生。列出如下语句：SQL>select s.sno,sname,c.cteacher from s,c,sc where c.cno=sc.cno and sc.sno=s.s
no and c.cteacher<> 'liming';
SNO SNAME CTEACHER
---------- ----------------------------------------
2 lisi chenzhong
2 lisi yanghua
3 wangwu yanghua
3 wangwu wanghui
4 huliu wanghui
4 huliu chenzhong
但是，我们这时候，会想，有学生即选了黎明老师的，也选了别人的（其中还有些学生就选了一门课，黎明老师的，那样的话上面的结果是对的）那上面会把那种学生照样选了出来。
我们要查的是，没有选过黎明老师课的学生，那我们可以找出选了黎明老师课的学生的学号啊。。
这样：select snofrom sc where cno = (select cno from c where cteacher='liming');
然后我们求出学号不在这里面的学生的姓名：下面才是正确结果哦。。。。。。。。
SQL> Select sname from s where sno notin (select sno from sc where cno = (selec
t cno from c where cteacher='liming'));
SNAME
--------------------
dingqi
17、列出两门以上（含两门）不及格的学生的姓名及平均成绩：
二话不说，先求出两门（含两门）不及格的学生的学生编号，以及不及格科目数：
select sno,count(*) cnt from sc where scorade <60 group by snohaving count(*) >=2
SNO CNT
---------- ----------
3 2
看来只有3号学生的不及格科目大于等于2.。。别忘了求出每个学生的平均成绩。。
SQL> select avg(scorade) avg_sc ,snofrom sc group by sno;
AVG_SC SNO
---------- ----------
78 1
72.3333333 2
52.3333333 3
77 4
72.5 5
这就是5名同学的平均成绩咯。。现在看来，我们要求上面这俩结果表的连接。。只要sno相同，就是我们要求的学生的结果，恩，最后看结果：
SQL> select t1.avg_sc,t2.sno,t2.cnt from
(select avg(scorade) avg_sc ,sno from sc group by sno) t1,
(select sno,count(*) cnt from scwhere scorade <60 group by sno having co
unt(*) >=2 ) t2
where t1.sno=t2.sno
AVG_SC SNO CNT
---------- ---------- ----------
52.3333333 3 2
平均成绩会有的。。学生号也会有的。。。还赠送了个不及格科目统计。。。。啊哈哈。。。。
18、列出即学过1号课程，又学过2号课程的学生编号：
SQL> select sno from sc where cno=1 andsno in(select sno from sc where cno=2);
SNO
----------
3
4
这个简单点，可以用两个相同的查询做等值连接sno。。。也可以像答案这样。比较好理解。。
19、40道经典练习题（需要的表的网址：）
--2. Show the last name, job, salary, and commission of those employees who earn commission. Sort the data by the salary in descending order.
SELECT last_name, job_id, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC--3. Show the employees that have no commission with a 10% raise in their salary (round off the salaries).
select 'the salary of '||last_name||'after a 10% raise is '||round(salary*(1+0.1),0) from employees where commission_pct is null;--4. Show the last names of all employees together with the number of years and the number of completed months that they have been employed.
SELECT last_name,
TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date) / 12) YEARS,
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, hire_date), 12)) MONTHS
FROM employees --需要注意，上面写法是标准答案，但是如果我用下面的写法，extract命令，得到的结果会小于正确结果，因为其算法不正确。
select last_name, extract(year from to_date(sysdate))-extract(year from to_date(hire_date)) as years,
mod(trunc(months_between(sysdate,hire_date),0),12) months from employees
--5. Show those employees that have a name starting with J, K, L, or M.
select last_name from employees where last_name like '%J%'
OR last_name like '%K%'
OR last_name like '%L%'
OR last_name like '%M%'
union
select last_name from employees where first_name like '%J%'
OR last_name like '%K%'
OR last_name like '%L%'
OR last_name like '%M%'上面属于非常笨的方法了，下面是用一个非常简单的办法：
SELECT last_name
FROM employees
WHERE SUBSTR(last_name, 1,1) IN ('J', 'K', 'L', 'M')--6. Show all employees, and indicate with “Yes” or “No” whether they receive a commission.
select last_name,salary ,nvl2(commission_pct,'YES','NO') COM
from employees上面是我做的，我觉得我的答案也很好啊，下面是标准答案：
SELECT last_name, salary,
decode(commission_pct, NULL, 'No', 'Yes') commission
FROM employees--7. Show the department names, locations, names, job titles, and salaries of employees who work in location 1800.
SELECT d.department_name, d.location_id,
e.last_name, e.job_id, e.salary
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.location_id = 1800--8. How many employees have a name that ends with an n? Create two possible solutions
select count(*) from employees where last_name like '%n'
select (select count(*) from employees) -(select count(*) from employees where last_name not like '%n') "COUNT(*)" from dual; SELECT COUNT(*)
FROM employees
WHERE SUBSTR(last_name, -1) = 'n'
/--9. Show the names and locations for all departments, and the number of employees working in each department.
--Make sure that departments without employees are included as well.select t.department_id,m.department_name,m.location_id,t.COUN "COUNT(E.EMPLOYEE_ID)" from
(select b.department_id department_id, count(a.employee_id) "COUN"
from employees a right join departments b on(a.department_id=b.department_id)
group by b.department_id) t , departments m
where m.department_id=t.department_id;上面是我写的，下面是答案
SELECT d.department_id, d.department_name,
d.location_id, COUNT(e.employee_id)
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id
GROUP BY d.department_id, d.department_name, d.location_id--10. Which jobs are found in departments 10 and 20?
select job_id,department_id from employees where department_id in(10,20)--11. Which jobs are found in the Administration and Executive departments,
--and how many employees do these jobs? Show the job with the highest frequency first.
SELECT e.job_id, count(e.job_id) FREQUENCY
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_name IN ('Administration', 'Executive')
GROUP BY e.job_id
ORDER BY FREQUENCY DESC
--12. Show all employees who were hired in the first half of the month (before the 16th of the month).
select last_name,hire_date from employees a,
(select employee_id ,extract(day from to_date(hire_date)) day from employees where extract(day from to_date(hire_date))<16) b
where a.employee_id=b.employee_id 上面我写的查询很麻烦了，答案很好，用了'DD' 日期格式：
SELECT last_name, hire_date
FROM employees
WHERE TO_CHAR(hire_date, 'DD') < 16--13. Show the names, salaries, and the number of dollars (in thousands) that all employees earn
SELECT last_name, salary, TRUNC(salary, -3)/1000 Thousands
FROM employees--14. Show all employees who have managers with a salary higher than $15,000.
--Show the following data: employee name, manager name, manager salary, and salary grade of the manager.
查询经理薪水高于15000的， 员工的姓名、经理姓名、经理薪水、经理的工资等级select t.employee,t.manager,t.salary,s.grade_level gra from
(select a.last_name employee,b.last_name manager ,b.salary salary from employees a,employees b
where a.manager_id=b.employee_id and b.salary>15000) t ,job_grades s
where (t.salary between s.lowest_sal and s.highest_sal); 我用了一个子查询，不过答案很简单，三个表直接连接。
SELECT e.last_name, m.last_name manager, m.salary,
j.grade_level
FROM employees e, employees m, job_grades j
WHERE e.manager_id = m.employee_id
AND m.salary BETWEEN j.lowest_sal AND j.highest_sal
AND m.salary > 15000--15. Show the department number, name, number of employees, and average salary of all departments,
--together with the names, salaries, and jobs of the employees working in each department
--查询所有部门的id，名字，员工数量，平均薪水，还有在各个部门工作的员工的信息。select d.department_id,
d.avg_sal,
d.emp_count,
c.last_name,
c.salary,
c.job_id
from employees c,
(select b.department_id department_id,
round(nvl(avg(salary), 0), 2) avg_sal,
count(a.employee_id) emp_count
from employees a
right join (select department_id from departments) b
on (a.department_id = b.department_id)
group by b.department_id) d
where c.department_id = d.department_id我按照上面这样写的，思路虽然清晰，但是不够简洁，因为多使用了一层子查询。其实把里面那个
(select b.department_id department_id,
round(nvl(avg(salary), 0), 2) avg_sal,
count(a.employee_id) emp_count
from employees a
right join (select department_id from departments) b
on (a.department_id = b.department_id)
group by b.department_id) d可以合并成对employees 和 departments两个表的一个查询就行。
--16. Show the department number and the lowest salary of the department with the highest average salary.
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id)--17. Show the department numbers, names, and locations of the departments where no sales representatives work.
SELECT *
FROM departments
WHERE department_id NOT IN(SELECT department_id
FROM employees
WHERE job_id = 'SA_REP'
AND department_id IS NOT NULL) --18. Show the department number, department name, and the number of employees working in each department that:
--a. Includes fewer than 3 employees:
--b. Has the highest number of employees
--c. Has the lowest number of employees:
a/select b.department_id,a.department_name ,b.count_employee from departments a ,
(select department_id,count(employee_id) count_employee from employees group by department_id) b
where a.department_id=b.department_id and b.count_employee < 3
答案写法：SELECT d.department_id, d.department_name, COUNT(*)
FROM departments d, employees e
WHERE d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(*) < 3 b/
select d.department_id,a.department_name ,d.count_employee from departments a ,
( select c.department_id,c.count_employee from
(select department_id,count(employee_id) count_employee from employees group by department_id) c
where c.count_employee =
(select max(b.count_employee) from (select department_id,count(employee_id) count_employee from employees group by department_id) b)
)d
where a.department_id=d.department_id
答案写法：SELECT d.department_id, d.department_name, COUNT(*)
FROM departments d, employees e
WHERE d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM employees
GROUP BY department_id) c/
select d.department_id,a.department_name ,d.count_employee from departments a ,
( select c.department_id,c.count_employee from
(select department_id,count(employee_id) count_employee from employees group by department_id) c
where c.count_employee =
(select min(b.count_employee) from (select department_id,count(employee_id) count_employee from employees group by department_id) b)
)d
where a.department_id=d.department_id
答案写法：SELECT d.department_id, d.department_name, COUNT(*)
FROM departments d, employees e
WHERE d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(*) = (SELECT MIN(COUNT(*))
FROM employees
GROUP BY department_id)
-- 19. Show the employee number, last name, salary, department number, and the average salary in their department for all employees.
select a.employee_id,a.last_name,a.salary,a.department_id, round(b.avg_salary,2) avg_sal from employees a,
(select avg(salary ) avg_salary ,department_id from employees group by department_id) b
where a.department_id=b.department_id答案写法：
SELECT e.employee_id, e.last_name,
e.department_id, AVG(s.salary)
FROM employees e, employees s
WHERE e.department_id = s.department_id
GROUP BY e.employee_id, e.last_name, e.department_id--20. Show all employees who were hired on the day of the week on which the highest number of employees were hired.
SELECT last_name, TO_CHAR(hire_date, 'DAY') day
FROM employees
WHERE TO_CHAR(hire_date, 'Day') =
(SELECT TO_CHAR(hire_date, 'Day')
FROM employees
GROUP BY TO_CHAR(hire_date, 'Day')
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM employees
GROUP BY TO_CHAR(hire_date, 'Day')))
--21. Create an anniversary overview based on the hire date of the employees. Sort the anniversaries in ascending order.
select last_name, to_char(hire_date, 'MM-DD') anniversary from employees
order by to_char(hire_date, 'MM-DD')SELECT last_name, TO_CHAR(hire_date, 'Month DD') BIRTHDAY
FROM employees
ORDER BY TO_CHAR(hire_date, 'DDD')--22. Find the job that was filled in the first half of 1990 and the same job that was filled during the same period in 1991.
select job_id from employees
where hire_date between to_date('1990-01-01','YYYY-MM-DD') and to_date('1990-06-30','YYYY-MM-DD')
and job_id in(
select job_id from employees
where hire_date between to_date('1991-01-01','YYYY-MM-DD') and to_date('1991-06-30','YYYY-MM-DD')
)
--23. Write a compound query to produce a list of employees showing raise percentages, employee IDs,
--and old salary and new salary increase. Employees in departments 10, 50, and 110 are
--given a 5% raise, employees in department 60 are given a 10% raise, employees in
--departments 20 and 80 are given a 15% raise, and employees in department 90 are not givena raise.
select
case
when department_id in(10,50,110) then '5% raise'
when department_id in(60) then '10% raise'
when department_id in(20,80) then '15% raise'
when department_id in(90) then 'no raise'
else 'others'
end "RAISE",
employee_id,salary,
case
when department_id in(10,50,110) then 0.05*salary
when department_id in(60) then 0.1*salary
when department_id in(20,80) then 0.15*salary
when department_id in(90) then 0
else salary
end "NEW_SALARY"
from employees;答案：
SELECT '05% raise' raise, employee_id, salary,
salary *.05 new_salary
FROM employees
WHERE department_id IN (10,50, 110)
UNION
SELECT '10% raise', employee_id, salary, salary * .10
FROM employees
WHERE department_id = 60
UNION
SELECT '15% raise', employee_id, salary, salary * .15
FROM employees
WHERE department_id IN (20, 80)
UNION
SELECT 'no raise', employee_id, salary, salary
FROM employees
WHERE department_id = 90;--25. a. Write queries to display the time zone offsets (TZ_OFFSET) for the following time zones.
-- b. Alter the session to set the TIME_ZONE parameter value to the time zone offset ofAustralia/Sydney.
-- c. Display the SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, andLOCALTIMESTAMP for this session.
-- Note: The output might be different based on the date when the command is executed.
-- d. Alter the session to set the TIME_ZONE parameter value to the time zone offset ofChile/Easter Island.
-- e. Display the SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, andLOCALTIMESTAMP for this session.
--f. Alter the session to set the NLS_DATE_FORMAT to DD-MON-YYYY.
SELECT TZ_OFFSET ('Australia/Sydney') from dual;
SELECT TZ_OFFSET ('Chile/EasterIsland') from dual;
ALTER SESSION SET TIME_ZONE = '+10:00';
SELECT SYSDATE,CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;
ALTER SESSION SET TIME_ZONE = '-06:00';
SELECT SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';--26. Write a query to display the last names, month of the date of join, and hire date of those
-- employees who have joined in the month of January, irrespective of the year of join.
select last_name,EXTRACT(month FROM hire_date) ,hire_date from employees where EXTRACT(month FROM hire_date) =1--27. Write a query to display the following for those departments whose department ID is greater than 80:
--The total salary for every job within a department 一个部门的每个工作的所有薪水
--The total salary 全部薪水总和
--The total salary for those cities in which the departments are located每个部门所在城市的薪水的总和
--The total salary for every job, irrespective of the department 每个工作的薪水总和，不考虑部门
--The total salary for every department irrespective of the city每个部门的薪水总和，不考虑城市
--The total salary of the cities in which the departments are located每个部门所在的城市的薪水的总和
--Total salary for the departments, irrespective of job titles and cities 每个部门的薪水总和，不考虑工资和城市
SELECT l.city,d.department_name, e.job_id, SUM(e.salary)
FROM locations l,employees e,departments d
WHERE d.location_id = l.location_id
AND e.department_id = d.department_id
AND e.department_id > 80
GROUP BY CUBE( l.city,d.department_name, e.job_id);
--28. Write a query to display the following groupings:
--Department ID, Job ID部门id，工作id
--Job ID, Manager ID 工作id，经理的id，
-- The query should calculate the maximum and minimum salaries for each of these groups.
此查询必须计算这些组的最大和最小工资。
select Department_id, Job_id,Manager_ID,max(salary),min(salary)
FROM employees
GROUP BY GROUPING SETS
((department_id,job_id), (job_id,manager_id))
--31. Write a query to delete the oldest JOB_HISTORY row of an employee by looking up theJOB_HISTORY table
--for the MIN(START_DATE) for the employee. Delete the records of only those employees who have changed at least two jobs.
--If your query executes correctly, you will get the feedback:
--Hint: Use a correlated DELETE command.
DELETE FROM job_history JH
WHERE employee_id =
(SELECT employee_id
FROM employees E
WHERE JH.employee_id = E.employee_id
AND start_date =
(SELECT MIN(start_date)
FROM job_history JH
WHERE JH.employee_id = E.employee_id)
AND 3 > (SELECT COUNT(*)
FROM job_history JH
WHERE JH.employee_id = E.employee_id
GROUP BY employee_id HAVING COUNT(*) >= 2
));
--33. Write a query to display the job IDs of those jobs whose maximum salary is above half the
--maximum salary in the whole company. Use the WITH clause to write this query. Name thequery MAX_SAL_CALC.
with MAX_SAL_CALC as (select distinct job_id from employees where salary>
(select max(salary)/2 from employees))
select e.job_id,max(e.salary) from employees e ,MAX_SAL_CALC where e.job_id=MAX_SAL_CALC.job_id
group by e.job_id --34. Write a SQL statement to display employee number, last name, start date, and salary,showing:
-- a. De Haan’s direct reports
-- b. The organization tree under De Haan (employee number 102)
SELECT employee_id, last_name, hire_date, salary
FROM employees
WHERE manager_id = (SELECT employee_id
FROM employees
WHERE last_name = 'De Haan');
SELECT employee_id, last_name, hire_date, salary
FROM employees
WHERE employee_id != 102
CONNECT BY manager_id = PRIOR employee_id
START WITH employee_id = 102;--35. Write a hierarchical query to display the employee number, manager number, and employee
--last name for all employees who are two levels below employee De Haan (employeenumber 102).
--Also display the level of the employee.
SELECT employee_id, manager_id, level, last_name
FROM employees
WHERE LEVEL = 3
CONNECT BY manager_id = PRIOR employee_id
START WITH employee_id= 102;--36. Produce a hierarchical report to display the employee number, manager number, the LEVEL
-- pseudocolumn, and employee last name. For every row in the EMPLOYEES table, you
-- should print a tree structure showing the employee, the employee’s manager, then the
-- manager’s manager, and so on. Use indentations for the NAME column.
COLUMN name FORMAT A25
SELECT employee_id, manager_id, LEVEL,
LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') LAST_NAME
FROM employees
CONNECT BY employee_id = PRIOR manager_id;
COLUMN name CLEAR
-- 41.Write a SQL script file to drop all objects (tables, views, indexes, sequences,synonyms, and soon) that you own.Note: The output shown is only a guideline.
SET HEADING OFF ECHO OFF FEEDBACK OFF
SET PAGESIZE 0
SELECT 'DROP ' || object_type || ' ' || object_name || ';'
FROM user_objects
ORDER BY object_type
/
SET HEADING ON ECHO ON FEEDBACK ON
SET PAGESIZE 24
20、with 语句使用
很多时候，我们都希望能够重用一些已经获得的结果集。即使有内嵌视图这个灵活的工具，SQL语句有时也会显得非常繁琐。
【示例18-3】 对于表salary来说，其中存储了员工领取过的工资信息，那么我们可以利用如下SQL语句来获得员工的平均工资水平。
1. SQL> select employee_id, avg(salary) avg_salary from salary
2. 2 group by employee_id
3. 3 /
4.
5. EMPLOYEE_ID AVG_SALARY
6. ----------- ------------
7. 1 8500
8. 2 6800
9. 3 4000
10. 4 4000
11. 5 4500
在该示例中，我们将工资信息按照employee_id进行分组，并获得每个分组的平均值，从而获得每位员工的平均工资。
那么，为了获得所有员工的平均工资可以利用如下SQL语句。
1. SQL> select avg(avg_salary) from (
2. 2 select employee_id, avg(salary) avg_salary from salary
3. 3 group by employee_id)
4. 4 /
5.
6. AVG(AVG_SALARY)
7. ----------------------------
8. 5560
在该SQL语句中，仍然使用到了单个员工平均工资这一信息。如果需求变更为获得工资大于平均工资的员工和工资信息，那么可以利用如下SQL语句：
1. SQL> select * from (
2. 2 select employee_id, avg(salary) avg_salary from salary
3. 3 group by employee_id
4. 4 ) t
5. 5 where t.avg_salary >(
6. 6 select avg(avg_salary) from (
7. 7 select employee_id, avg(salary) avg_salary from salary
8. 8 group by employee_id));
9.
10. EMPLOYEE_ID AVG_SALARY
11. ----------- -------------
12. 1 8500
13. 2 6800
很明显，子查询select employee_id, avg(salary) avg_salary from salary group by employee_id被重复执行。通过with子句，可以将该子查询独立出来，并重用其查询结果，如下所示。
1. SQL> with employee_avg_salary as (
2. 2 select employee_id, avg(salary) avg_salary from salary
3. 3 group by employee_id)
4. 4 select * from employee_avg_salary t
5. 5 where t.avg_salary>(select avg(avg_salary)
from employee_avg_salary) 6. 6 /
7.
8. EMPLOYEE_ID AVG_SALARY
9. ----------- ------------
10. 1 8500
11. 2 6800
with employee_avg_salary as (select employee_id, avg(salary) avg_salary from salary group by employee_id)将子查询select employee_id, avg(salary) avg_salary from salary group by employee_id的查询结果作为临时变量存储起来；在其后的查询中，可以直接使用employee_avg_salary，如同employee_avg_salary是一个真实存在的数据表一样。本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
