【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
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【sql】訓練四SQL
- 【sql】訓練三SQL
- 【sql】訓練二SQL
- 【sql】訓練一SQL
- [BUUCTF]第五天訓練日誌
- NLP與深度學習（五）BERT預訓練模型深度學習模型
- 【訓練】sql訓練、建立和管理表、約束、檢視及其他資料庫物件SQL資料庫物件
- 目標檢測入門系列手冊五：YOLO訓練教程YOLO
- 【vjudge訓練記錄】11月個人訓練賽1
- acm訓練題ACM
- IOS Swift 訓練iOSSwift
- 4.17訓練賽
- 自訓練 + 預訓練 = 更好的自然語言理解模型模型
- SQL經典練習題48道之五(31-35)SQL
- E - 樹狀陣列 1【GDUT_22級寒假訓練專題五】陣列
- 【專題訓練】字串字串
- java小白訓練營Java
- 機器學習的訓練集機器學習
- YOLO2訓練YOLO
- 資料集訓練
- CF專項訓練
- 機率期望訓練
- 24.8.18 DP訓練
- dp專題訓練
- 專項訓練們
- PyTorch 模型訓練實⽤教程（程式碼訓練步驟講解）PyTorch模型
- 2、PyTorch訓練YOLOv11—訓練篇（detect）—Windows系統PyTorchYOLOv1Windows
- sql 練習SQL
- 【學校訓練記錄】10月個人訓練賽3個人題解
- 【預訓練語言模型】 使用Transformers庫進行BERT預訓練模型ORM
- 【LLM訓練系列】NanoGPT原始碼詳解和中文GPT訓練實踐NaNGPT原始碼
- 自我訓練 – PHP函式PHP函式
- java工廠模式訓練Java模式
- 演算法訓練 排序演算法排序
- 1.23訓練總結
- Pytorch分散式訓練PyTorch分散式
- fasttext訓練模型程式碼AST模型
- SSD演算法訓練演算法