标准SQL查询 总结练习

简介: 1、按照部门的编号,分组,求出每个部门的平均薪水?最高薪水? 实现:SQL>select deptno,avg(sal) from emp group by deptno;    DEPTNO   AVG(SAL) ---------- ----------        10 2916.

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后面的等于一定返回一个值,而子查询这次会返回多个值。也是不行的;还有更聪明的同学,可能会想,我不要等于了,我改成inselectempno,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); ——这里leftouter 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、不准用组函数,求出薪水的最高值,所对应的人。(思考:我们把两个表e1e2中的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语句来获得员工的平均工资水平。

 
  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是一个真实存在的数据表一样。

相关文章
|
6天前
|
SQL 数据库 开发者
功能发布-自定义SQL查询
本期主要为大家介绍ClkLog九月上线的新功能-自定义SQL查询。
|
13天前
|
SQL 移动开发 Oracle
SQL语句实现查询连续六天数据的方法与技巧
在数据库查询中,有时需要筛选出符合特定时间连续性条件的数据记录
|
20天前
|
SQL Java 数据库连接
如何使用`DriverManager.getConnection()`连接数据库,并利用`PreparedStatement`执行参数化查询,有效防止SQL注入。
【10月更文挑战第6天】在代码与逻辑交织的世界中,我从一名数据库新手出发,通过不断探索与实践,最终成为熟练掌握JDBC的开发者。这段旅程充满挑战与惊喜,从建立数据库连接到执行SQL语句,再到理解事务管理和批处理等高级功能,每一步都让我对JDBC有了更深的认识。示例代码展示了如何使用`DriverManager.getConnection()`连接数据库,并利用`PreparedStatement`执行参数化查询,有效防止SQL注入。
61 5
|
20天前
|
SQL 数据挖掘 数据库
SQL查询每秒的数据:技巧、方法与性能优化
id="">SQL查询功能详解 SQL(Structured Query Language,结构化查询语言)是一种专门用于与数据库进行沟通和操作的语言
|
20天前
|
SQL 移动开发 大数据
SQL语句查询连续六天满足条件的记录
在数据库管理和数据分析中,我们经常需要查询符合特定时间范围内连续几天的记录
|
22天前
|
SQL 存储 安全
SQL查询数据库:基础概念与操作指南
在数字化时代,数据库已成为信息管理的重要工具之一。作为管理和操作数据库的核心语言,SQL(结构化查询语言)已成为数据管理和查询的关键技能。本文将全面介绍SQL查询数据库的基本概念、语句和操作指南,以帮助初学者快速上手,同时为进阶用户提供有价值的参考。一、数据库与SQL简介数据库是一种存储、管理和检索
30 3
|
1天前
|
SQL 关系型数据库 MySQL
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
6 0
|
12天前
|
SQL 数据可视化 BI
SQL语句及查询结果解析:技巧与方法
在数据库管理和数据分析中,SQL语句扮演着至关重要的角色
|
18天前
|
SQL 监控 关系型数据库
使用SQL语句查询操作耗时的技巧与方法
在数据库管理和优化过程中,了解SQL查询操作的耗时是至关重要的
|
18天前
|
SQL
创建分组总计查询的SQL技巧与方法
在SQL中,创建分组总计查询(也称为聚合查询)是一项非常基础且重要的技能