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

【sql】訓練五

相關文章