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道经典练习题(需要的表的网址:http://download.csdn.net/detail/changyanmanman/5870379)
--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语句来获得员工的平均工资水平。
- SQL> select employee_id, avg(salary) avg_salary from salary
- 2 group by employee_id
- 3 /
- EMPLOYEE_ID AVG_SALARY
- ----------- ------------
- 1 8500
- 2 6800
- 3 4000
- 4 4000
- 5 4500
在该示例中,我们将工资信息按照employee_id进行分组,并获得每个分组的平均值,从而获得每位员工的平均工资。
那么,为了获得所有员工的平均工资可以利用如下SQL语句。
- SQL> select avg(avg_salary) from (
- 2 select employee_id, avg(salary) avg_salary from salary
- 3 group by employee_id)
- 4 /
- AVG(AVG_SALARY)
- ----------------------------
- 5560
在该SQL语句中,仍然使用到了单个员工平均工资这一信息。如果需求变更为获得工资大于平均工资的员工和工资信息,那么可以利用如下SQL语句:
- SQL> select * from (
- 2 select employee_id, avg(salary) avg_salary from salary
- 3 group by employee_id
- 4 ) t
- 5 where t.avg_salary >(
- 6 select avg(avg_salary) from (
- 7 select employee_id, avg(salary) avg_salary from salary
- 8 group by employee_id));
- EMPLOYEE_ID AVG_SALARY
- ----------- -------------
- 1 8500
- 2 6800
很明显,子查询select employee_id, avg(salary) avg_salary from salary group by employee_id被重复执行。通过with子句,可以将该子查询独立出来,并重用其查询结果,如下所示。
- SQL> with employee_avg_salary as (
- 2 select employee_id, avg(salary) avg_salary from salary
- 3 group by employee_id)
- 4 select * from employee_avg_salary t
- 5 where t.avg_salary>(select avg(avg_salary)
from employee_avg_salary) - 6 /
- EMPLOYEE_ID AVG_SALARY
- ----------- ------------
- 1 8500
- 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是一个真实存在的数据表一样。