1、1. 查詢工資大于12000的員工姓名和工資Select last_name|' '|first_name,salary from employees where salary >12000;2. 查詢員工號為176的員工的姓名和部門號Select last_name|' '|first_name,department_id from employees where employee_id=176;3. 選擇工資不在5000到12000的員工的姓名和工資Select last_name|' '|first_name,salary from

2、employees where salary not between 5000 and 12000;4. 選擇雇用時間在1998-02-01到1998-05-01之間的員工姓名，job_id和雇用時間Select last_name|' '|first_name,job_id,hire_date from employees where hire_date between '1-2月-98' and '1-5月-98'5. 選擇在20或50號部門工作的員工姓名和部門號Select last_name|' '|first_name,

3、department_id from employees where department_id in (20,50);6. 選擇在1994年雇用的員工的姓名和雇用時間Select last_name|' '|first_name,hire_date from employees where hire_date like '%94'7. 選擇公司中沒有管理者的員工姓名及job_idSelect last_name|' '|first_name,job_id from employees where Manger_id is null;8. 選擇公

4、司中有獎金的員工姓名，工資和獎金Select last_name|' '|first_name,salary,commission_pct from employees where commission_pct is not null;9. 選擇員工姓名的第三個字母是a的員工姓名Select last_name|' '|first_name from employees where last_name|' '|first_name like '_a%'10. 選擇姓名中有字母a和e的員工姓名Select last_name|

5、9; '|first_name from employees where last_name|first_name like '%a%e%' or last_name|first_name like '%e%a%' 多表查詢11. 顯示所有員工的姓名，部門號和部門名稱。Select e.last_name,d.department_id,d.department_name from employees e , departments d where (e.department_id=d.department_id); 12. 查詢90號部門員工的job_

6、id和90號部門的location_idSelect e.job_id,d.location_id from employees e, departments d where e.department_id=d.deparement_id and d.department_id=90;13. 選擇所有有獎金的員工的last_name , department_name , location_id , citySelect e.last_name , d.department_name , l.location_id , city from employees e,departments d,l

7、ocations l where e.department_id=d.department_id AND d.location_id=l.location_id AND commission_pct is not null;14. 選擇在Toronto工作的員工的last_name , job_id , department_id , department_name Select e.last_name , e.job_id , d.department_id , d.department_name from employees e,departments d ,locations l whe

8、re e.department_id=d.department_id AND d.location_id=l.location_id AND l.city='Toronto'15. 選擇所有員工的姓名，員工號，以及他的管理者的姓名和員工號，結果類似于下面的格式employeesEmp#managerMgr#kochhar101king100Select e.employee_id "employees",e.last_name "Emp#",d.manager_id "Mgr#",d.last_name "m

9、anger" from employees e,employees d where e.manager_id=d.employee_id(+);6. 查詢各部門員工姓名和他們的同事姓名，結果類似于下面的格式Department_idLast_namecolleague20fayhartsteinSelect e.department_id "Department_id", d.last_name "Last_name", e.last_name "colleague" from employees e join employ

10、ees d on(d.department_id=e.department_id) where d.last_name<>e.last_name; 分組查詢16. 組函數處理多行返回一行（true）17. 組函數不計算空值（ false）18. where子句在分組之前對檢索進行過濾 ( true)19. 查詢公司員工工資的最大值，最小值，平均值，總和Select max(salary),min(salary),avg(salary),sum(salary) from employees;20. 查詢各job_id的員工工資的最大值，最小值，平均值，總和Select max(sal

11、ary),min(salary),avg(salary),sum(salary) from employees group by job_id;21. 選擇具有各個job_id的員工人數Select job_id,count(*) from employees group by job_id;22. 查詢員工最高工資和最低工資的差距（DIFFERENCE）Select max(salary)-min(salary) "DIFFERENCE" from employees;23. 查詢各個管理者手下員工的最低工資，其中最低工資不能低于6000，沒有管理者的員工不計算在內Sel

12、ect manager_id,min(salary) from employees where manager_id is not null group by manager_id having min(salary) >=6000;24. 查詢所有部門的名字，location_id，員工數量和工資平均值Select d.department_name,d.location_id,count(e.employee_id),avg(e.salary) from employees e,departments d where e.department_id(+)=d.department_i

13、d group by d.location_id,d.department_name;25. 查詢公司的人數，以及在1995-1998年之間，每年雇用的人數，結果類似下面的格式total1995199619971998303467Select count(employee_id),to_char(hire_date,'yyyy') "y"count(*) from employees where to_char(hire_date,'yyyy') between 1995 and 1998 group by to_char(hire_date

14、,'yyyy') order by y;Select 子查詢26. 查詢和zlotkey相同部門的員工姓名和雇用日期Select last_name,hire_date,department_id from employees where department_id=(select department_id from employees where lower(last_name)='zlotkey') ;27. 查詢工資比公司平均工資高的員工的員工號，姓名和工資。Select employee_id,last_name,salary from employe

15、es where salary>(select avg(salary) from employees );28. 查詢和姓名中包含字母u的員工在相同部門的員工的員工號和姓名Select employee_id,last_name,department_id from employees where department_id =any (select department_id from employees where lower(last_name) like '%u%');29. 查詢在部門的location_id為1700的部門工作的員工的員工號，departmen

16、t_id和job_idSelect employee_id,department_id,job_id from employees where department_id = any (select department_id from departments where location_id=1700);30. 查詢管理者是king的員工姓名和工資Select last_name,salary from employees where manager_id=(select employee_id from employees where last_name='King');

17、 創建和管理表31. 創建表deptnameNull?typeidNumber(7)nameVarchar2(25)Create table dept ( id Number(7),name varchar(25);32. 將表departments中的數據插入表dept中Insert into dept( SELECT department_id, department_name FROM departments);33. 創建表empnameNull?typeidNumber(7)First_nameVarchar2(25)Last_nameVarchar2(25)Dept_idNumbe

18、r(7)Create table emp (id Number(7),First_name varchar(25),Last_name varchar(25),Dept_id number(7);34. 將列Last_name的長度增加到50Alter table emp modify (last_name varchar2(50); 35. 查詢數據字典視圖user_tables檢查剛才的操作Select * from user_tables;36. 根據表employees創建employees2Create table employees2 asselect * from employe

19、es;37. 刪除表empDrop table emp;38. 將表employees2重命名為empRename employees2 to emp;39. 在表dept和emp中添加新列test_column，并檢查所作的操作Alter table emp add(test_column varchar(10); 單行函數40. 顯示系統時間Select to_char(sysdate 'DD-MON-YYYY')from dual;41. 查詢員工號，姓名，工資，以及工資提高百分之20%后的結果（new salary）Select employee_id,last_nam

20、e|' '|first_name,salary,salary*1.2 newsalary from employees;42. 將員工的姓名按首字母排序，并寫出姓名的長度（length）Select last_name,length(last_name) from employees order by substr(last_name,1,1) desc;43. 查詢各員工的姓名，并顯示出各員工在公司工作的月份數（worked_month）。Select last_name|" '|first_name,months_between(sysdate,hire_

21、date) worked_month from employees;44. 查詢員工的姓名和工資，按下面的形式顯示Last_nameSALARYking$24000Select lower(last_name) "last_name",lpad(salary,15,'$') "Salary" from employees;45. 查詢員工的姓名，以及在公司工作的月份數（worked_month），并按月份數降序排列Select last_name|' '|first_name,months_between(sysdate,

22、hire_date) worked_month from employees order by worked_month desc;46. 做一個查詢，產生下面的結果<last_name> earns <salary> monthly but wants <salary*3>Dream SalaryKing earns $24000 monthly but wants $72000Select 'King'|' earns '|lpad(salary,6,'$')|' monthly but wants

23、 '|lpad(salary*3,6,'$') "Dream Salary" from employees;47. 做一個查詢，產生下面的結果add_month(6)+4Last_nameHire_datereiewking17-jun-87Monday,the twenty-first of December , 1987Select lower(last_name) "Last_name",hire_date,to_char(add_months(hire_date,6)+4,'DAY,MONTH,YYYY') "Hire_date " from employees where last_name like 'King'9做一個查詢，產生下面的結果Employees_and_their_salarysKing*