【sql】训练五

1：请从表EMP中查找工资低于2000的雇员的姓名、工作、工资，并按工资降序排列。
SQL> select ename,job,sal from emp where sal<2000 order by sal desc;

2：查询所有雇员的姓名、SAL与之和(显示字段为SC)
SQL> select ename,sal+nvl(comm,0) as sc from emp;

3：查询各部门中81年2月1日以后入职的员工数量
SQL> SELECT deptno, COUNT(*) AS cnt
  2    FROM emp
  3   WHERE hiredate > to_date('19810201', 'yyyymmdd')
  4   GROUP BY deptno
  5   ORDER BY 1 ASC;


4：查询列出各部门的部门名和部门经理的名字?
scott用户下：
SQL> select d.ename,t.dname from emp d,dept t
  2  where d.job=upper('MANAGER') and d.deptno = t.deptno;

列出所有人的部门经理的名称及部门名字
select d.root,d.ename,t.dname from
(select connect_by_root(ename) as root,
       deptno,
       ename
  from emp
 start with job=upper('manager')
connect by prior empno = mgr) d,dept t where d.deptno=t.deptno;

5：统计emp中每个部门的工资总和及每个部门的人数
SQL> select deptno,sum(sal),count(*) from emp group by deptno order by 1;

6：查询部门平均工资最高的部门名称和最低的部门名称
select t.dname,avg(d.sal) avg_sal from emp d,dept t

一：
SQL> with q1 as                                                         
  2   (select e.deptno, d.dname, avg(sal) avg                           
  3      from emp e, dept d                                             
  4     where e.deptno = d.deptno                                       
  5     group by e.deptno, d.dname)                                     
  6  select dname, avg                                                  
  7    from q1                                                          
  8   where avg = (select max(avg) from q1)                             
  9  union all                                                          
 10  select dname, avg from q1 where avg = (select min(avg) from q1);   

DNAME                 AVG
-------------- ----------
ACCOUNTING     2916.66667
SALES          1566.66667

二：
with t1 as
 (select dname deptno, avg(sal) as avgsal
    from emp d, dept t
   where d.deptno = t.deptno
   group by dname),
t2 as
 (select max(avgsal) as sal1, min(avgsal) sal2 from t1)
select d1.deptno, d2.sal1, d3.deptno, d4.sal2
  from t1 d1, t2 d2, t1 d3, t2 d4
 where d1.avgsal = d2.sal1
   and d3.avgsal = d4.sal2;



7：统计2008年8月8日至今总共有多少天？
SQL>  select sysdate-to_date('20080808','yyyymmdd') from dual;

SYSDATE-TO_DATE('20080808','YYYYMMDD')
--------------------------------------
                             2957.4211

8：在HR用户利用CTAS方式创建表EMP，仅创建表结构。
SQL> create table emp as select * from employees where 1=2;

Table created.

SQL> select * from emp;

no rows selected

9：在HR用户，将EMPLOYEES表中，除SALARY字段外，其他字段的值都插入到EMP表中。
sql> insert into emp(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,commission_pct,manager_id,department_id)
  2  select employee_id,first_name,last_name,email,phone_number,hire_date,job_id,commission_pct,manager_id,department_id
  3  from employees;     

107 rows created.

10：在HR用户，通过查询EMPLOYEES表的SALARY字段，更新至EMP表的SALARY字段。

SQL> update emp d set salary=(select salary from employees t where d.employee_id = t.employee_id);

107 rows updated.

11：在HR用户，EMP表中，所有工资小于10000的，增加10%工资。
SQL> update emp set salary=salary*1.1 where salary <10000;

64 rows updated.

12：在HR用户，查询EMPLOYEES表，显示人员ID、姓名、电话号码，要求：电话号码不满18位的，电话号码前面用‘0’补齐，人员ID升序排列。

select employee_id,first_name,lpad(phone_number,18,'0') ph from employees order by 1;

13：在SCOTT用户，更新COMM字段，只更新COMM字段为空的记录，更新的值为薪金的20%。

update emp set comm=SAL*0.2 where comm is null;


14：在SCOTT用户，查询EMP，SALGRADE这两张表，显示每个人的级别。显示字段：人员id，姓名，级别。

SQL> select e.empno,e.ename,s.grade from emp e,salgrade s where e.sal in(s.hisal,s.losal);

     EMPNO ENAME           GRADE
---------- ---------- ----------
      7788 SCOTT               4
      7902 FORD                4

select d.empno,d.ename,t.grade from emp d,salgrade t where sal between losal and hisal


15：在HR用户，查询没有任何职员的部门

select d.department_name,d.department_id from departments d                 
 where d.department_id not in (select e.department_id from employees e      
 where e.department_id is not null);                                        
                                                                            
select department_id, department_name                                       
  from departments                                                          
 where department_id not in                                                 
       (select distinct (department_id)                                     
          from employees                                                    
         where department_id is not null);                                  
                                                                            
select department_id, department_name                                       
  from departments d                                                        
 where not exists (select null                                              
          from employees                                                    
        where department_id = d.department_id);    
                         
16：打印2016-06-15~2016-07-14之间的连续日期

select to_date('2016-06-15','yyyy-mm-dd')+rownum-1 from dual connect by rownum<=30；

17：在SCOTT用户，利用下面语句建表：
create table emp_enmo as select * from emp where 1=2;
insert into emp_enmo select empno,ename,null as job,mgr,hiredate,sal,comm,deptno from emp where sal>=3000;
要求：使用merge完成对表emp_enmo数据的补全。

语法：
MERGE INTO [schema .] table [t_alias]
USING [schema .] { table | view | subquery } [t_alias]
ON ( condition )--------------------匹配必须为主键或唯一键
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;


SQL> merge into emp_enmo d
  2  using emp t
  3  on (d.empno = t.empno)
  4  when matched then
  5    update
  6       set d.ename    = t.ename,
  7           d.job      = t.job,
  8           d.mgr      = t.mgr,
  9           d.hiredate = t.hiredate,
 10           d.sal      = t.sal,
 11           d.comm     = t.comm,
 12           d.deptno   = t.deptno
 13  when not matched then
 14    insert
 15    values
 16      (t.empno, t.ename, t.job, t.mgr, t.hiredate, t.sal, t.comm, t.deptno);

14 rows merged.

18：在SCOTT用户，只查EMP表，显示：人员id，姓名，及部门名称，要求：部门名称利用decode函数来完成显示部门名称，按照人员id升序排列。
SQL> select empno,ename,decode(deptno,10,'ACCOUNTING',20,
  2  'RESEARCH',30,'SALES',40,'OPERATIONS'，deptno) from emp
  3  order by 1;
字段，10（如果），,'ACCOUNTING'（那么），....deptno(否则）。

19：在HR用户，查询employees表，要求：大于5人的job_id的最大薪金，最小薪金，人数。

SQL> select job_id,max(salary),min(salary)，count(*) from employees
  2  group by job_id having count(*) > 5 ;


20：TOP N
1）获取所有成绩的top-3
2）每门课程成绩的前三名；（注意：如果遇到了相同成绩的情况，改如何考虑？）
测试表如下：
--创建表
create TABLE sc
(
sno NUMBER(5),
cno NUMBER(5),
grade NUMBER(4,1)
);
SQL> create table sc(sno number(5),cno number(5),grade number(4,1));

Table created.


--表中插入测试数据
insert into sc values(1,1,91);
insert into sc values(1,2,92);
insert into sc values(1,3,93);
insert into sc values(2,1,88);
insert into sc values(2,2,92);
insert into sc values(2,3,99);
insert into sc values(3,1,65);
insert into sc values(3,2,75);
insert into sc values(3,3,85);
insert into sc values(4,1,80);
insert into sc values(4,2,88);
insert into sc values(4,3,93);
commit;
SQL> select * from sc;

答：
1)：
select sno,cno,grade from (select sno,cno,grade from sc order by grade desc) where rownum<=3;

2）：
HR@ORA11GR2>
要求2：遇到相同成绩，则并列，并列后，占用排名名次
HR@ORA11GR2>select * from
(SELECT sno,cno,grade,rank() over(partition by cno
order by grade desc) as rn FROM sc)
where rn<=3;

 SNO        CNO     GRADE         RN
---- -------- --------- --------
   1          1         91          1
   2          1         88          2
   4          1         80          3
   1          2         92          1
   2          2         92          1
   4          2         88          3
   2          3         99          1
   1          3         93          2
   4          3         93          2

9 rows selected.

HR@ORA11GR2>
要求3：遇到相同成绩，则并列，并列后，并列不多占用名次
HR@ORA11GR2>select * from
(SELECT sno,cno,grade,dense_rank() over(partition by
cno order by grade desc) as rn FROM sc)
where rn<=3;

 SNO        CNO     GRADE         RN
---- -------- --------- --------
   1          1         91          1
   2          1         88          2
   4          1         80          3
   1          2         92          1
   2          2         92          1
   4          2         88          2
   3          2         75          3
   2          3         99          1
   1          3         93          2
   4          3         93          2
   3          3         85          3

11 rows selected.

HR@ORA11GR2>





附加题：请查询出scott.emp表中根据工资（SAL字段）从多到少排名，工资位于第5-第10的记录。
select * from(
select ename,sal,rownum as rn from(select ename,sal from emp order by sal desc ) t)
where rn >=5 and rn<=10;

分析函数：
select d.*,row_number() over(order by sal) from emp d
select d.*,dense_rank() over(order by sal) from emp d
select d.*,rank() over(order by sal) from emp d

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2127037/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2127037/