【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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [BUUCTF]第五天訓練日誌
- NLP與深度學習(五)BERT預訓練模型深度學習模型
- SQL經典練習題48道之五(31-35)SQL
- 20240622訓練
- 20240610訓練
- 目標檢測入門系列手冊五:YOLO訓練教程YOLO
- acm訓練題ACM
- 擴充訓練
- 4.17訓練賽
- 24.8.18 DP訓練
- 2024.8.20 DS訓練
- 自訓練 + 預訓練 = 更好的自然語言理解模型模型
- 【vjudge訓練記錄】11月個人訓練賽1
- SQL練習題SQL
- SQL練習00012SQL
- SQL練習00015SQL
- E - 樹狀陣列 1【GDUT_22級寒假訓練專題五】陣列
- PyTorch 模型訓練實⽤教程(程式碼訓練步驟講解)PyTorch模型
- 2、PyTorch訓練YOLOv11—訓練篇(detect)—Windows系統PyTorchYOLOv1Windows
- 訓練日誌 2018.10.24
- 1.23訓練總結
- 20240302 專項訓練
- 20240309 專項訓練
- 資料集訓練
- 2024.3 訓練記錄
- 【專題訓練】字串字串
- pytorch指定GPU訓練PyTorchGPU
- CF專項訓練
- 20240927 隨機訓練隨機
- 2024.11.05 刷題訓練
- 專項訓練們
- 2024.9.19訓練記錄
- 2024.9 訓練日記
- dp專題訓練
- 機率期望訓練
- 2024.11.20訓練記錄
- 20241103 訓練記錄
- Pytorch分散式訓練PyTorch分散式
- java小白訓練營Java