配套其他第一天練習(xí)

1、第一天練習(xí)1.查詢工資大于 12000 的員工select last_name, salary from employeeswhere salary 12000和工資2.查詢員工號(hào)為 176 的員工的select last_name, department_id from employeeswhere employee_id = 176和部門號(hào)3.選擇工資不在 5000 到 12000 的員工的select last_name, salary from employeeswhere salary not betn 5000 and 12000和工資4.選擇雇用時(shí)間在 1998-02-01 到

2、1998-05-01 之間的員工，job_id和雇用時(shí)間select last_name, job_id, hire_date from employeeswhere hire_date betn 1-2 月 -1998 and 1-5 月 -19985.選擇在 20 或 50 號(hào)部門工作的員工select last_name, department_id from employeeswhere department_id in (20, 50)和部門號(hào)6.選擇在 1994 年雇用的員工的select last_name, hire_date from employeeswhere hire_

3、daike % -94和雇用時(shí)間7.選擇公司中沒(méi)有管理者的員工select last_name, job_id from employeeswhere manager_id is null及 job_id8.選擇公司中有獎(jiǎng)金的員工，工資和獎(jiǎng)金級(jí)別select last_name, salary, commis_pctfrom employeeswhere commis_pct is not null9.選擇員工select last_name from employees的第三個(gè)字母是 a 的員工where last_name like a%10. 選擇中有字母 a 和 e 的員工select

4、 last_name from employeeswhere last_name like %a% and last_name like %e%11. 顯示系統(tǒng)時(shí)間select to_char(sysdate, yyyy-mm-dd hh:mi:ss) from dual;12. 查詢員工號(hào)，工資，以及工資提高百分之 20%后的結(jié)果（newsalary）select employee_id, last_name, salary, salary * 1.2 new salary from employees;13. 將員工的按首字母排序，并寫(xiě)出的長(zhǎng)度（length）select last_nam

5、e, length(last_name) from employeesorder by last_name14. 查詢各員工的，并顯示出各員工在公司工作的月份數(shù)（worked_month）。select last_name, months_betn(sysdate, hire_date) worked_month from employees15. 查詢員工的，以及在公司工作的月份數(shù)（worked_month），并按月份數(shù)降序排列select last_name, months_betn(sysdate, hire_date) worked_month from employeesorder

6、by worked_month desc16. 做一個(gè)查詢，產(chǎn)生下面的結(jié)果 earns monthly but wants select last_name | earns | salary | monthly but wants | salary* 3from employees17. 使用 decode 函數(shù)，按照下面的條件：jobgradeAD_PRESAST_MANBIT_PROGCSA_REPDST_CLERKEOthersF產(chǎn)生下面的結(jié)果select last_name, job_id, decode(job_id, AD_PRES, A,ST_MAN, B,IT_PROG, C,

7、SA_REP, D,ST_CLERK, E,F) GRADEfrom employees18. 將第 7 題的查詢用 case 函數(shù)再寫(xiě)一遍。select last_name, job_id, case job_id when AD_PRES then Awhen ST_MAN then Bwhen IT_PROG then Cwhen SA_REP then Dwhen ST_CLERK then Eelse FLast_nameJob_idGradekingAD_PRESADream SalaryKing earns $24000 monthly but wants $72000endfr

8、om employees19. 查詢公司員工工資的最大值，最小值，平均值，總和Select max(salary), min(salary), avg(salary), sum(salary)From employees20. 查詢各 job_id 的員工工資的最大值，最小值，平均值，總和Select job_id, max(salary), min(salary), avg(salary), sum(salary)From employeesGroup by job_id21. 選擇具有各個(gè) job_id 的員工人數(shù)Select job_id, count(employee_id)From

9、employeesGroup by job_id;22. 查詢員工最高工資和最低工資的差距（DIFFERENCE）Select max(salary) min(salary) differenceFrom employees23. 查詢各個(gè)管理者手下員工的最低工資，其中最低工資不能低于6000，沒(méi)有管理者的員工不計(jì)算在內(nèi)Select manager_id, min(salary)From employeesWhere manager_id is not nullGroup by manager_idHaving min(salary) = 600024. 查詢所有部門的名字，location_id，員工數(shù)量和工資平均值Select department_name, location_