當(dāng)前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

Oracle从小白到大牛的刷题之路（建议收藏学习）

發(fā)布時(shí)間：2023/12/10 编程问答 34 豆豆

生活随笔收集整理的這篇文章主要介紹了 Oracle从小白到大牛的刷题之路（建议收藏学习）小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

前言

數(shù)據(jù)表結(jié)構(gòu)

數(shù)據(jù)庫文件（按照順序?qū)?#xff09;

1基本SQL-SELECT

?1.1基本SQL-SELECT語句筆記

1.2 基本SQL-SELECT語句練習(xí)

2過濾和排序數(shù)據(jù)

2.1過濾和排序數(shù)據(jù)筆記

2.2過濾和排序數(shù)據(jù)練習(xí)

3單行函數(shù)

3.1單行函數(shù)筆記

3.2單行函數(shù)練習(xí)

4多表查詢

4.1多表查詢筆記

4.2多表查詢練習(xí)

5分組函數(shù)

5.1分組函數(shù)筆記

5.2分組函數(shù)練習(xí)

6子查詢

6.1子查詢筆記

6.2子查詢練習(xí)

7創(chuàng)建和管理表

7.1創(chuàng)建和管理表筆記

7.2創(chuàng)建和管理表練習(xí)

8數(shù)據(jù)處理

8.1數(shù)據(jù)處理筆記

8.2數(shù)據(jù)處理練習(xí)

9約束

9.1約束筆記

9.2約束練習(xí)

10視圖

10.1視圖筆記

10.2視圖練習(xí)

11其他數(shù)據(jù)庫對象

11.1其他數(shù)據(jù)庫對象筆記

11.2其他數(shù)據(jù)庫對象練習(xí)

結(jié)尾

前言

數(shù)據(jù)表結(jié)構(gòu)

數(shù)據(jù)庫文件（按照順序?qū)?#xff09;

先導(dǎo)入del_data.sql

再導(dǎo)入

hr_cre.sql

最后導(dǎo)入hr_popul.sql

數(shù)據(jù)文件

提取碼:69z3

1基本SQL-SELECT

?1.1基本SQL-SELECT語句筆記

筆記 1. 對于日期型數(shù)據(jù), 做 *, / 運(yùn)算不合法2. 包含空值的數(shù)學(xué)表達(dá)式的值都為空值3. 別名使用雙引號!4. oracle 中連接字符串使用 "||", 而不是 java 中的 "+"5. 日期和字符只能在單引號中出現(xiàn). 輸出 last_name`s email is emailselect last_name || ' `s email is ' || email EMAIL from employees6. distinct 關(guān)鍵字, 以下語法錯(cuò)誤select last_name, distinct department_id from employees

1.2 基本SQL-SELECT語句練習(xí)

練習(xí) 1. SQL*PLUS命令可以控制數(shù)據(jù)庫嗎? 否！ 2. 下面的語句是否可以執(zhí)行成功可以 select last_name , job_id , salary as sal from employees; 3. 下面的語句是否可以執(zhí)行成功可以 select * from employees; 4. 找出下面語句中的錯(cuò)誤標(biāo)點(diǎn)符號需要是英文格式下的。 select employee_id , last_name， salary * 12 “ANNUAL SALARY” from employees; 5. 顯示表departments的結(jié)構(gòu)，并查詢其中的全部數(shù)據(jù) desc departments; select * from departments; 6. 顯示出表employees中的全部job_id（不能重復(fù)） Select distinct job_id from employees; 7. 顯示出表employees的全部列，各個(gè)列之間用逗號連接，列頭顯示成OUT_PUT a) select employee_id ||','|| last_name||','||salary "OUT_PUT" b) from employees

2過濾和排序數(shù)據(jù)

2.1過濾和排序數(shù)據(jù)筆記

筆記 7. WHERE 子句緊隨 FROM 子句8. 查詢 last_name 為 'King' 的員工信息錯(cuò)誤1: King 沒有加上單引號select first_name, last_name from employees where last_name = King錯(cuò)誤2: 在單引號中的值區(qū)分大小寫select first_name, last_name from employees where last_name = 'king'正確select first_name, last_name from employees where last_name = 'King'9. 查詢 1998-4-24 來公司的員工有哪些?注意: 日期必須要放在單引號中, 且必須是指定的格式select last_name, hire_date from employees where hire_date = '24-4月-1998'10. 查詢工資在 5000 -- 10000 之間的員工信息.1). 使用 ANDselect *from employeeswhere salary >= 5000 and salary <= 100002). 使用 BETWEEN .. AND .., 注意: 包含邊界!!select *from employeeswhere salary between 5000 and 1000011. 查詢工資等于 6000, 7000, 8000, 9000, 10000 的員工信息1). 使用 ORselect *from employeeswhere salary = 6000 or salary = 7000 or salary = 8000 or salary = 9000 or salary = 100002). 使用 INselect *from employeeswhere salary in (6000, 7000, 8000, 9000, 10000)12. 查詢 LAST_NAME 中有 'o' 字符的所有員工信息.select *from employeeswhere last_name like '%o%'13. 查詢 LAST_NAME 中第二個(gè)字符是 'o' 的所有員工信息.select *from employeeswhere last_name like '_o%'14. 查詢 LAST_NAME 中含有 '_' 字符的所有員工信息1). 準(zhǔn)備工作:update employeesset last_name = 'Jones_Tom'where employee_id = 1952). 使用 escape 說明轉(zhuǎn)義字符.select *from employeeswhere last_name like '%\_%' escape '\'15. 查詢 COMMISSION_PCT 字段為空的所有員工信息select last_name, commission_pctfrom employeeswhere commission_pct is null16. 查詢 COMMISSION_PCT 字段不為空的所有員工信息select last_name, commission_pctfrom employeeswhere commission_pct is not null17. ORDER BY:1). 若查詢中有表達(dá)式運(yùn)算, 一般使用別名排序2). 按多個(gè)列排序: 先按第一列排序, 若第一列中有相同的, 再按第二列排序. 格式： ORDER BY 一級排序列 ASC/DESC,二級排序列 ASC/DESC；

2.2過濾和排序數(shù)據(jù)練習(xí)

測試 1. 查詢工資大于12000的員工姓名和工資 a) select last_name,salary b) from employees c) where salary > 12000 2. 查詢員工號為176的員工的姓名和部門號 a) select last_name,department_id b) from employees c) where employee_id = 176 3. 選擇工資不在5000到12000的員工的姓名和工資 a) select last_name,salary b) from employees c) --where salary < 5000 or salary > 12000 d) where salary not between 5000 and 12000 4. 選擇雇用時(shí)間在1998-02-01到1998-05-01之間的員工姓名，job_id和雇用時(shí)間 a) select last_name,job_id,hire_date b) from employees c) --where hire_date between '1-2月-1998' and '1-5月-1998' d) where to_char(hire_date,'yyyy-mm-dd') between '1998-02-01' and '1998-05-01' 5. 選擇在20或50號部門工作的員工姓名和部門號 a) select last_name,department_id b) from employees c) where department_id = 20 or department_id = 50 d) --where department_id in (20,50) 6. 選擇在1994年雇用的員工的姓名和雇用時(shí)間 a) select last_name,hire_date b) from employees c) --where hire_date like '%94' d) where to_char(hire_date,'yyyy') = '1994' 7. 選擇公司中沒有管理者的員工姓名及job_id a) select last_name,job_id b) from employees c) where manager_id is null 8. 選擇公司中有獎(jiǎng)金的員工姓名，工資和獎(jiǎng)金級別 a) select last_name,salary,commission_pct b) from employees c) where commission_pct is not null 9. 選擇員工姓名的第三個(gè)字母是a的員工姓名 a) select last_name b) from employees c) where last_name like '__a%' 10. 選擇姓名中有字母a和e的員工姓名 a) select last_name b) from employees c) where last_name like '%a%e%' or last_name like '%e%a%'

3單行函數(shù)

3.1單行函數(shù)筆記

18. 打印出 "2009年10月14日 9:25:40" 格式的當(dāng)前系統(tǒng)的日期和時(shí)間.select to_char(sysdate, 'YYYY"年"MM"月"DD"日" HH:MI:SS')from dual 注意: 使用雙引號向日期中添加字符19. 格式化數(shù)字: 1234567.89 為 1,234,567.89select to_char(1234567.89, '999,999,999.99')from dual20. 字符串轉(zhuǎn)為數(shù)字時(shí)1). 若字符串中沒有特殊字符, 可以進(jìn)行隱式轉(zhuǎn)換:select '1234567.89' + 100from dual2). 若字符串中有特殊字符, 例如 '1,234,567.89', 則無法進(jìn)行隱式轉(zhuǎn)換, 需要使用 to_number() 來完成select to_number('1,234,567.89', '999,999,999.99') + 100from dual21. 對于把日期作為查詢條件的查詢, 一般都使用 to_date() 把一個(gè)字符串轉(zhuǎn)為日期, 這樣可以不必關(guān)注日期格式select last_name, hire_datefrom employeeswhere hire_date = to_date('1998-5-23', 'yyyy-mm-dd') -- where to_char(hire_date,'yyyy-mm-dd') = '1998-5-23'22. 轉(zhuǎn)換函數(shù): to_char(), to_number(), to_date()23. 查詢每個(gè)月倒數(shù)第 2 天入職的員工的信息. select last_name, hire_datefrom employeeswhere hire_date = last_day(hire_date) - 124. 計(jì)算公司員工的年薪--錯(cuò)誤寫法: 因?yàn)榭罩涤?jì)算的結(jié)果還是空值select last_name, salary * 12 * (1 + commission_pct) year_salfrom employees--正確寫法select last_name, salary * 12 * (1 + nvl(commission_pct, 0)) year_salfrom employees25. 查詢部門號為 10, 20, 30 的員工信息, 若部門號為 10, 則打印其工資的 1.1 倍,20 號部門, 則打印其工資的 1.2 倍, 30 號部門打印其工資的 1.3 倍數(shù)--使用 case-when-then-else-endselect last_name, department_id, salary, case department_id when 10 then salary * 1.1when 20 then salary * 1.2when 30 then salary * 1.3end new_salfrom employeeswhere department_id in (10, 20, 30)--使用 decodeselect last_name, department_id, salary, decode(department_id, 10, salary * 1.1,20, salary * 1.2,30, salary * 1.3) new_salfrom employeeswhere department_id in (10, 20, 30)

3.2單行函數(shù)練習(xí)

測試 1. 顯示系統(tǒng)時(shí)間(注：日期+時(shí)間) a) select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') b) from dual 2. 查詢員工號，姓名，工資，以及工資提高百分之20%后的結(jié)果（new salary） a) select employee_id,last_name,salary,salary*1.2 "new salary" b) from employees 3. 將員工的姓名按首字母排序，并寫出姓名的長度（length） a) select last_name,length(last_name) b) from employees c) order by last_name asc 4. 查詢各員工的姓名，并顯示出各員工在公司工作的月份數(shù)（worked_month）。 a) select last_name,hire_date,round(months_between(sysdate,hire_date),1) workded_month b) from employees 5. 查詢員工的姓名，以及在公司工作的月份數(shù)（worked_month），并按月份數(shù)降序排列 a) Select last_name,hire_date,round(months_between(sysdate,hire_date),1) workded_month b) from employees c) order by workded_month desc 6. 做一個(gè)查詢，產(chǎn)生下面的結(jié)果 <last_name> earns <salary> monthly but wants <salary*3> Dream Salary King earns $24000 monthly but wants $72000 select last_name || ' earns '|| to_char(salary,'$999999')||' monthly,but wants '||to_char(3*salary,'$999999') "Dream Salary" from employees 7. 使用decode函數(shù)，按照下面的條件： job grade AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E 產(chǎn)生下面的結(jié)果 Last_name Job_id Grade king AD_PRES A select last_name "Last_name",job_id "Job_id", decode(job_id,'AD_PRES','A','ST_MAN','B', 'IT_PROG','C', 'SA_REP','D', 'ST_CLERK','E') "Grade" from employees 8. 將第7題的查詢用case函數(shù)再寫一遍。 a) select last_name "Last_name",job_id "Job_id", case job_id when 'AD_PRES'then 'A' b) when 'ST_MAN' then 'B' c) when 'IT_PROG' then 'C' d) when 'SA_REP' then 'D' e) when 'ST_CLERK' then'E' end "Grade" f) from employees

4多表查詢

4.1多表查詢筆記

26. 多表連接查詢時(shí), 若兩個(gè)表有同名的列, 必須使用表的別名對列名進(jìn)行引用, 否則出錯(cuò)!27. 查詢出公司員工的 last_name, department_name, cityselect last_name, department_name, cityfrom departments d, employees e, locations lwhere d.department_id = e.department_id and d.location_id = l.location_id28. 查詢出 last_name 為 'Chen' 的 manager 的信息. (員工的 manager_id 是某員工的 employee_id) 0). 例如: 老張的員工號為: "1001", 我的員工號為: "1002", 我的 manager_id 為 "1001" --- 我的 manager 是"老張" 1). 通過兩條 sql 查詢:select manager_idfrom employeeswhere lower(last_name) = 'chen' --返回的結(jié)果為 108select *from employeeswhere employee_id = 1082). 通過一條 sql 查詢(自連接):select m.*from employees e, employees mwhere e.manager_id = m.employee_id and e.last_name = 'Chen' 3). 通過一條 sql 查詢(子查詢): select *from employeeswhere employee_id = (select manager_id from employeeswhere last_name = 'Chen') 29. 查詢每個(gè)員工的 last_name 和 GRADE_LEVEL(在 JOB_GRADES 表中). ---- 非等值連接select last_name, salary, grade_level, lowest_sal, highest_salfrom employees e, job_grades jwhere e.salary >= j.lowest_sal and e.salary <= j.highest_sal30. 左外連接和右外連接select last_name, e.department_id, department_namefrom employees e, departments dwhere e.department_id = d.department_id(+)select last_name, d.department_id, department_namefrom employees e, departments dwhere e.department_id(+) = d.department_id理解 "(+)" 的位置: 以左外連接為例, 因?yàn)樽蟊硇枰祷馗嗟挠涗?右表就需要 "加上" 更多的記錄, 所以在右表的鏈接條件上加上 "(+)"注意: 1). 兩邊都加上 "(+)" 符號, 會發(fā)生語法錯(cuò)誤!2). 這種語法為 Oracle 所獨(dú)有, 不能在其它數(shù)據(jù)庫中使用. 31. SQL 99 連接 Employees 表和 Departments 表1).select *from employees join departmentsusing(department_id)缺點(diǎn): 要求兩個(gè)表中必須有一樣的列名.2).select *from employees e join departments don e.department_id = d.department_id3).多表連接select e.last_name, d.department_name, l.cityfrom employees e join departments don e.department_id = d.department_idjoin locations lon d.location_id = l.location_id 32. SQL 99 的左外連接, 右外連接, 滿外連接1).select last_name, department_namefrom employees e left outer join departments don e.department_id = d.department_id2).select last_name, department_namefrom employees e right join departments don e.department_id = d.department_id3).select last_name, department_namefrom employees e full join departments don e.department_id = d.department_id

4.2多表查詢練習(xí)

測試 1. 顯示所有員工的姓名，部門號和部門名稱。 a) select last_name,e.department_id,department_name b) from employees e,departments d c) where e.department_id = d.department_id(+)方法二： select last_name,e.department_id,department_name from employees e left outer join departments d on e.department_id = d.department_id 2. 查詢90號部門員工的job_id和90號部門的location_id a) select distinct job_id,location_id b) from employees e left outer join departments d c) on e.department_id = d.department_id d) where d.department_id = 90 3. 選擇所有有獎(jiǎng)金的員工的 last_name , department_name , location_id , city select last_name,department_name,d.location_id,city from employees e join departments d on e.department_id = d.department_id join locations l on d.location_id = l.location_id where e.commission_pct is not null 4. 選擇city在Toronto工作的員工的 last_name , job_id , department_id , department_name select last_name , job_id , e.department_id , department_name from employees e ,departments d,locations l where e.department_id = d.department_id and l.city = 'Toronto' and d.location_id = l.location_id 5. 選擇指定員工的姓名，員工號，以及他的管理者的姓名和員工號，結(jié)果類似于下面的格式 employees Emp# manager Mgr# kochhar 101 king 100 select e1.last_name "employees",e1.employee_id "Emp#",e2.last_name"Manger",e2.employee_id "Mgr#" from employees e1,employees e2 where e1.manager_id = e2.employee_id(+)

5分組函數(shù)

5.1分組函數(shù)筆記

33. 查詢 employees 表中有多少個(gè)部門select count(distinct department_id)from employees 34. 查詢?nèi)惊?jiǎng)金基數(shù)的平均值(沒有獎(jiǎng)金的人按 0 計(jì)算)select avg(nvl(commission_pct, 0))from employees 35. 查詢各個(gè)部門的平均工資--錯(cuò)誤: avg(salary) 返回公司平均工資, 只有一個(gè)值; 而 department_id 有多個(gè)值, 無法匹配返回select department_id, avg(salary)from employees **在 SELECT 列表中所有未包含在組函數(shù)中的列都應(yīng)該包含在 GROUP BY 子句中--正確: 按 department_id 進(jìn)行分組select department_id, avg(salary)from employeesgroup by department_id36. Toronto 這個(gè)城市的員工的平均工資SELECT avg(salary) FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id WHERE city = 'Toronto' 37. (有員工的城市)各個(gè)城市的平均工資SELECT city, avg(salary) FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id GROUP BY city 38. 查詢平均工資高于 8000 的部門 id 和它的平均工資. SELECT department_id, avg(salary) FROM employees e GROUP BY department_id HAVING avg(salary) > 8000 39. 查詢平均工資高于 6000 的 job_title 有哪些SELECT job_title, avg(salary) FROM employees e join jobs j ON e.job_id = j.job_id GROUP BY job_title HAVING avg(salary) > 6000

5.2分組函數(shù)練習(xí)

測試 1. 組函數(shù)處理多行返回一行嗎? 是 2. 組函數(shù)不計(jì)算空值嗎？是 3. where子句可否使用組函數(shù)進(jìn)行過濾? 不可以，用having替代 4. 查詢公司員工工資的最大值，最小值，平均值，總和 a) select max(salary),min(salary),avg(salary),sum(salary) b) from employees 5. 查詢各job_id的員工工資的最大值，最小值，平均值，總和 a) select job_id,max(salary),min(salary),avg(salary),sum(salary) b) from employees c) group by job_id 6. 選擇具有各個(gè)job_id的員工人數(shù) a) select job_id,count(employee_id) b) from employees c) group by job_id 7. 查詢員工最高工資和最低工資的差距（DIFFERENCE） a) select max(salary),min(salary),max(salary)-min(salary) "DIFFERENCE" b) from employees 8. 查詢各個(gè)管理者手下員工的最低工資，其中最低工資不能低于6000，沒有管理者的員工不計(jì)算在內(nèi) a) select manager_id,min(salary) b) from employees c) where manager_id is not null d) group by manager_id e) having min(salary) >= 6000 9. 查詢所有部門的名字，location_id，員工數(shù)量和工資平均值 a) select department_name,location_id,count(employee_id),avg(salary) b) from employees e right outer join departments d c) on e.department_id = d.department_id d) group by department_name,location_id 10. 查詢公司在1995-1998年之間，每年雇用的人數(shù)，結(jié)果類似下面的格式 total 1995 1996 1997 1998 20 3 4 6 7select count(*) "total",count(decode(to_char(hire_date,'yyyy'),'1995',1,null)) "1995",count(decode(to_char(hire_date,'yyyy'),'1996',1,null)) "1996",count(decode(to_char(hire_date,'yyyy'),'1997',1,null)) "1997",count(decode(to_char(hire_date,'yyyy'),'1998',1,null)) "1998" from employees where to_char(hire_date,'yyyy') in ('1995','1996','1997','1998')

6子查詢

6.1子查詢筆記

40. 誰的工資比 Abel 高?1). 寫兩條 SQL 語句.SELECT salaryFROM employeesWHERE last_name = 'Abel'--返回值為 11000SELECT last_name, salaryFROM employeesWHERE salary > 110002). 使用子查詢 -- 一條 SQL 語句SELECT last_name, salaryFROM employeesWHERE salary > (SELECT salaryFROM employeesWHERE last_name = 'Abel')子查詢注意: 1). 子查詢要包含在括號內(nèi)2). 將子查詢放在比較條件的右側(cè) 41. 查詢工資最低的員工信息: last_name, salary 42. 查詢平均工資最低的部門信息43*. 查詢平均工資最低的部門信息和該部門的平均工資44. 查詢平均工資最高的 job 信息45. 查詢平均工資高于公司平均工資的部門有哪些?46. 查詢出公司中所有 manager 的詳細(xì)信息.47. 各個(gè)部門中最高工資中最低的那個(gè)部門的最低工資是多少48. 查詢平均工資最高的部門的 manager 的詳細(xì)信息: last_name, department_id, email, salary49. 查詢 1999 年來公司的人所有員工的最高工資的那個(gè)員工的信息./*************************************************************************************************/41. 查詢工資最低的員工信息: last_name, salary SELECT last_name, salaryFROM employeesWHERE salary = (SELECT min(salary)FROM employees)42. 查詢平均工資最低的部門信息SELECT *FROM departmentsWHERE department_id = (SELECT department_idFROM employeesGROUP BY department_id HAVING avg(salary) = (SELECT min(avg(salary))FROM employeesGROUP BY department_id) )43. 查詢平均工資最低的部門信息和該部門的平均工資select d.*, (select avg(salary) from employees where department_id = d.department_id) from departments d where d.department_id = (SELECT department_idFROM employeesGROUP BY department_id HAVING avg(salary) = (SELECT min(avg(salary))FROM employeesGROUP BY department_id) )44. 查詢平均工資最高的 job 信息1). 按 job_id 分組, 查詢最高的平均工資 SELECT max(avg(salary))FROM employeesGROUP BY job_id2). 查詢出平均工資等于 1) 的 job_idSELECT job_idFROM employeesGROUP BY job_idHAVING avg(salary) = (SELECT max(avg(salary))FROM employeesGROUP BY job_id)3). 查詢出 2) 對應(yīng)的 job 信息SELECT *FROM jobsWHERE job_id = (SELECT job_idFROM employeesGROUP BY job_idHAVING avg(salary) = (SELECT max(avg(salary))FROM employeesGROUP BY job_id))45. 查詢平均工資高于公司平均工資的部門有哪些?1). 查詢出公司的平均工資SELECT avg(salary)FROM employees2). 查詢平均工資高于 1) 的部門 IDSELECT department_idFROM employeesGROUP BY department_idHAVING avg(salary) > (SELECT avg(salary)FROM employees)46. 查詢出公司中所有 manager 的詳細(xì)信息.1). 查詢出所有的 manager_idSELECT distinct manager_idFROM employeess2). 查詢出 employee_id 為 1) 查詢結(jié)果的那些員工的信息SELECT employee_id, last_nameFROM employeesWHERE employee_id in (SELECT distinct manager_idFROM employees)47. 各個(gè)部門中最高工資中最低的那個(gè)部門的最低工資是多少1). 查詢出各個(gè)部門的最高工資SELECT max(salary)FROM employeesGROUP BY department_id2). 查詢出 1) 對應(yīng)的查詢結(jié)果的最低值: 各個(gè)部門中最低的最高工資(無法查詢對應(yīng)的 department_id)SELECT min(max(salary))FROM employeesGROUP BY department_id3). 查詢出 2) 所對應(yīng)的部門 id 是多少: 各個(gè)部門中最高工資等于 2) 的那個(gè)部門的 idSELECT department_idFROM employeesGROUP BY department_id HAVING max(salary) = (SELECT min(max(salary))FROM employeesGROUP BY department_id)4). 查詢出 3) 所在部門的最低工資SELECT min(salary)FROM employeesWHERE department_id = (SELECT department_idFROM employeesGROUP BY department_id HAVING max(salary) = (SELECT min(max(salary))FROM employeesGROUP BY department_id) )48. 查詢平均工資最高的部門的 manager 的詳細(xì)信息: last_name, department_id, email, salary1). 各個(gè)部門中, 查詢平均工資最高的平均工資是多少SELECT max(avg(salary))FROM employeesGROUP BY department_id2). 各個(gè)部門中, 平均工資等于 1) 的那個(gè)部門的部門號是多少SELECT department_idFROM employeesGROUP BY department_idHAVING avg(salary) = (SELECT max(avg(salary))FROM employeesGROUP BY department_id)3). 查詢出 2) 對應(yīng)的部門的 manager_idSELECT manager_idFROM departmentsWHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING avg(salary) = (SELECT max(avg(salary))FROM employeesGROUP BY department_id) )4). 查詢出 employee_id 為 3) 查詢的 manager_id 的員工的 last_name, department_id, email, salarySELECT last_name, department_id, email, salaryFROM employeesWHERE employee_id = (SELECT manager_idFROM departmentsWHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING avg(salary) = (SELECT max(avg(salary))FROM employeesGROUP BY department_id) ) )49. 查詢 1999 年來公司的人所有員工的最高工資的那個(gè)員工的信息.1). 查詢出 1999 年來公司的所有的員工的 salarySELECT salaryFROM employeesWHERE to_char(hire_date, 'yyyy') = '1999'2). 查詢出 1) 對應(yīng)的結(jié)果的最大值SELECT max(salary)FROM employeesWHERE to_char(hire_date, 'yyyy') = '1999'3). 查詢工資等于 2) 對應(yīng)的結(jié)果且 1999 年入職的員工信息 SELECT *FROM employeesWHERE to_char(hire_date, 'yyyy') = '1999' AND salary = (SELECT max(salary)FROM employeesWHERE to_char(hire_date, 'yyyy') = '1999')50. 多行子查詢的 any 和 allselect department_idfrom employeesgroup by department_idhaving avg(salary) >= any(--所有部門的平均工資select avg(salary)from employeesgroup by department_id)any 和任意一個(gè)值比較, 所以其條件最為寬松, 所以實(shí)際上只需和平均工資最低的比較, 返回所有值而 all 是和全部的值比較, 條件最為苛刻, 所以實(shí)際上返回的只需和平均工資最高的比較, 所以返回平均工資最高的 department_id

6.2子查詢練習(xí)

測試 1. 查詢和Zlotkey相同部門的員工姓名和雇用日期 a) select last_name,hire_date b) from employees c) where department_id = ( d) select department_id e) from employees f) where last_name = 'Zlotkey' g) ) h) and last_name <> 'Zlotkey' 2. 查詢工資比公司平均工資高的員工的員工號，姓名和工資。 a) select last_name,employee_id,salary b) from employees c) where salary > (select avg(salary) d) from employees) 3. 查詢各部門中工資比本部門平均工資高的員工的員工號, 姓名和工資 a) select employee_id,last_name,salary b) from employees e1 c) where salary > ( d) select avg(salary) e) from employees e2 f) where e1.department_id = e2.department_id g) group by department_id h) ) 4. 查詢和姓名中包含字母u的員工在相同部門的員工的員工號和姓名 a) select employee_id,last_name b) from employees c) where department_id in ( d) select department_id e) from employees f) where last_name like '%u%' g) ) h) and last_name not like '%u%' 5. 查詢在部門的location_id為1700的部門工作的員工的員工號 select employee_id from employees where department_id in (select department_idfrom departmentswhere location_id = 1700) 6.查詢管理者是King的員工姓名和工資 select last_name,salary from employees where manager_id in (select employee_idfrom employeeswhere last_name = 'King')

7創(chuàng)建和管理表

7.1創(chuàng)建和管理表筆記

51. 利用子查詢創(chuàng)建表 myemp, 該表中包含 employees 表的 employee_id(id), last_name(name), salary(sal), email 字段1). 創(chuàng)建表的同時(shí)復(fù)制 employees 對應(yīng)的記錄create table myemp asselect employee_id id, last_name name, salary sal, email from employees 2). 創(chuàng)建表的同時(shí)不包含 employees 中的記錄, 即創(chuàng)建一個(gè)空表create table myemp asselect employee_id id, last_name name, salary sal, email from employees where 1 = 252. 對現(xiàn)有的表進(jìn)行修改操作1). 添加一個(gè)新列ALTER TABLE myemp ADD(age number(3))2). 修改現(xiàn)有列的類型ALTER TABLE myemp MODIFY(name varchar2(30));3). 修改現(xiàn)有列的名字ALTER TABLE myemp RENAME COLUMN sal TO salary;4). 刪除現(xiàn)有的列ALTER TABLE myemp DROP COLUMN age;53. 清空表(截?cái)?#xff1a; truncate), 不能回滾!! 54. 1). 創(chuàng)建一個(gè)表, 該表和 employees 有相同的表結(jié)構(gòu), 但為空表: create table emp2 as select * from employees where 1 = 2;2). 把 employees 表中 80 號部門的所有數(shù)據(jù)復(fù)制到 emp2 表中: insert into emp2 select * from employees where department_id = 80;

7.2創(chuàng)建和管理表練習(xí)

測試 1. 創(chuàng)建表dept1 name Null? type id Number(7) name Varchar2(25)create table dept1( id number(7), name varchar2(25)) 2. 將表departments中的數(shù)據(jù)插入新表dept2中 a) create table dept2 b) as c) select * from departments 3. 創(chuàng)建表emp5 name Null? type id Number(7) First_name Varchar2(25) Last_name Varchar2(25) Dept_id Number(7) create table emp5( id number(7), first_name varchar2(25), last_name varchar2(25), dept_id number(7) )4. 將列Last_name的長度增加到50 a) alter table emp5 b) modify (last_name varchar2(50)) 5. 根據(jù)表employees創(chuàng)建employees2 a) create table employees2 b) as c) select * from employees 6. 刪除表emp5 drop table emp5; 7. 將表employees2重命名為emp5 rename employees2 to emp5 8. 在表dept和emp5中添加新列test_column，并檢查所作的操作 alter table dept add(test_column number(10));desc dept; 9. 在表dept和emp5中將列test_column設(shè)置成不可用，之后刪除 a) alter table emp5 b) set unused column test_columnalter table emp5 drop unused columns 10. 直接刪除表emp5中的列 dept_id Alter table emp5 drop column dept_id

8數(shù)據(jù)處理

8.1數(shù)據(jù)處理筆記

55. 更改 108 員工的信息: 使其工資變?yōu)樗诓块T中的最高工資, job 變?yōu)楣局衅骄べY最低的 job1). 搭建骨架update employees set salary = (), job_id = () where employee_id = 108;2). 所在部門中的最高工資 select max(salary)from employeeswhere department_id = (select department_idfrom employeeswhere employee_id = 108)3). 公司中平均工資最低的 jobselect job_idfrom employeesgroup by job_idhaving avg(salary) = (select min(avg(salary))from employeesgroup by job_id)4). 填充update employees e set salary = (select max(salary)from employeeswhere department_id = e.department_id), job_id = (select job_idfrom employeesgroup by job_idhaving avg(salary) = (select min(avg(salary))from employeesgroup by job_id)) where employee_id = 108;56. 刪除 108 號員工所在部門中工資最低的那個(gè)員工.1). 查詢 108 員工所在的部門 idselect department_idfrom employees where employee_id = 108;2). 查詢 1) 部門中的最低工資:select min(salary)from employeeswhere department_id = (select department_idfrom employees where employee_id = 108)3). 刪除 1) 部門中工資為 2) 的員工信息:delete from employees ewhere department_id = (select department_idfrom employees ewhere employee_id = 108) and salary = (select min(salary)from employeeswhere department_id = e.department_id)

8.2數(shù)據(jù)處理練習(xí)

測試 1. 運(yùn)行以下腳本創(chuàng)建表my_employees Create table my_employee ( id number(3), first_name varchar2(10),Last_name varchar2(10),User_id varchar2(10),Salary number(5));2. 顯示表my_employees的結(jié)構(gòu) DESC my_employees; 3. 向表中插入下列數(shù)據(jù) ID FIRST_NAME LAST_NAME USERID SALARY 1 patel Ralph Rpatel 895 2 Dancs Betty Bdancs 860 3 Biri Ben Bbiri 1100 4 Newman Chad Cnewman 750 5 Ropeburn Audrey Aropebur 1550INSERT INTO my_employeeVALUES(1,’patel’,’Palph’,’Rpatel’895); 4. 提交 COMMIT; 5. 將3號員工的last_name修改為“drelxer” UPDATE my_employees SET last_name = ‘drelxer’ WHERE id = 3; 6. 將所有工資少于900的員工的工資修改為1000 UPDATE my_employees SET salary = 1000 WHERE salary< 900 7. 檢查所作的修正 SELECT * FROM my_employees WHERE salary < 900 8. 提交 COMMIT; 9. 刪除所有數(shù)據(jù) DELETE FROM my_employees; 10. 檢查所作的修正 SELECT * FROM my_employees; 11. 回滾 ROLLBACK; 12. 清空表my_employees TRUNCATE TABLE my_employees

9約束

9.1約束筆記

57. 定義非空約束1). 非空約束只能定義在列級.2). 不指定約束名create table emp2 (name varchar2(30) not null, age number(3));3). 指定約束名 create table emp3(name varchar2(30) constraint name_not_null not null, age number(3));58. 唯一約束1). 列級定義①. 不指定約束名create table emp2 (name varchar2(30) unique, age number(3));②. 指定約束名create table emp3 (name varchar2(30) constraint name_uq unique, age number(3));2). 表級定義: 必須指定約束名①. 指定約束名create table emp3 (name varchar2(30), age number(3), constraint name_uq unique(name));58.1 主鍵約束：唯一確定一行記錄。表明此屬性：非空，唯一 59. 外鍵約束1). 列級定義①. 不指定約束名create table emp2(emp_id number(6), name varchar2(25), dept_id number(4) references dept2(dept_id))②. 指定約束名create table emp3(emp_id number(6), name varchar2(25), dept_id number(4) constraint dept_fk3 references dept2(dept_id))2). 表級定義: 必須指定約束名①. 指定約束名create table emp4(emp_id number(6), name varchar2(25), dept_id number(4),constraint dept_fk2 foreign key(dept_id) references dept2(dept_id))60. 約束需要注意的地方1). ** 非空約束(not null)只能定義在列級2). ** 唯一約束(unique)的列值可以為空3). ** 外鍵(foreign key)引用的列起碼要有一個(gè)唯一約束 61. 建立外鍵約束時(shí)的級聯(lián)刪除問題:1). 級聯(lián)刪除:create table emp2(id number(3) primary key, name varchar2(25) unique, dept_id number(3) references dept2(dept_id) on delete cascade)2). 級聯(lián)置空create table emp3(id number(3) primary key, name varchar2(25) unique, dept_id number(3) references dept2(dept_id) on delete set null)

9.2約束練習(xí)

測試1. 向表emp2的id列中添加PRIMARY KEY約束（my_emp_id_pk） ALTER table emp2 ADD constraint my_emp_id_pk primary key(id);2. 向表dept2的id列中添加PRIMARY KEY約束（my_dept_id_pk） ALTER table dept2 ADD constraint my_dept_id_pk primary key(id)3. 向表emp2中添加列dept_id，并在其中定義FOREIGN KEY約束，與之相關(guān)聯(lián)的列是dept2表中的id列。 ALTER table emp2 ADD (dept_id number(10) constraint emp2_dept_id_fk references dept2(id));準(zhǔn)備工作:create table emp2 as select employee_id id, last_name name, salary from employeescreate table dept2 as select department_id id, department_name dept_name from departments

10視圖

10.1視圖筆記

測試 1. 使用表employees創(chuàng)建視圖employee_vu，其中包括姓名（LAST_NAME），員工號（EMPLOYEE_ID），部門號(DEPARTMENT_ID). a) create or replace view employee_vu b) as c) select last_name,employee_id,department_id d) from employees2. 顯示視圖的結(jié)構(gòu) desc employee_vu;3. 查詢視圖中的全部內(nèi)容 SELECT * FROM employee_vu;4. 將視圖中的數(shù)據(jù)限定在部門號是80的范圍內(nèi) a) create or replace view employee_vu b) as c) select last_name,employee_id,department_id d) from employees e) where department_id = 805. 將視圖改變成只讀視圖create or replace view employee_vu as select last_name,employee_id,department_id from employees where department_id = 80 with read only

10.2視圖練習(xí)

測試 1. 創(chuàng)建序列dept_id_seq，開始值為200，每次增長10，最大值為10000 a) create sequence dept_id_seq b) start with 200 c) increment by 10 d) maxvalue 10000 2. 使用序列向表dept中插入數(shù)據(jù) a) insert into dept01 b) values(dept_id_seq.nextval,'Account') 附： create table dept as select department_id id,department_name name from departments where 1=2

11其他數(shù)據(jù)庫對象

11.1其他數(shù)據(jù)庫對象筆記

65. 創(chuàng)建序列: 1). create sequence hs increment by 10 start with 102). NEXTVAL 應(yīng)在 CURRVAL 之前指定，二者應(yīng)同時(shí)有效65. 序列通常用來生成主鍵:INSERT INTO emp2 VALUES (emp2_seq.nextval, 'xx', ...) 總結(jié)： what -- why -- how 表table 視圖view 序列sequence 索引index 同義詞synonym

11.2其他數(shù)據(jù)庫對象練習(xí)

結(jié)尾

這是歌謠學(xué)習(xí)oracle的相關(guān)數(shù)據(jù)筆記，沒事會拿出來讀讀。每個(gè)技術(shù)棧都會有計(jì)劃有時(shí)間的看完，期待你的努力和成長。

我是歌謠，歡迎一起溝通交流，前端學(xué)習(xí)ing。一個(gè)執(zhí)著于技術(shù)的沉迷者。

推薦鏈接其他文件目錄參照

“睡服“面試官系列之各系列目錄匯總（建議學(xué)習(xí)收藏）

總結(jié)

以上是生活随笔為你收集整理的Oracle从小白到大牛的刷题之路（建议收藏学习）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇：前端学习（1909）vue之电商管理系统
下一篇：泛微OA漏洞（综合）