oracle-hr表查詢命令練習（超完整的select命令大全）

 

切換到 oracle的 hr使用者下面練習

 

1.  查詢工資大於12000的員工姓名和工資

 

Select initcap(concat(last_name,first_name)) "姓名",salary from employees where salary>12000;

 

2.  查詢員工號為176的員工的姓名和部門號

 

select initcap(concat(last_name,first_name)) "姓名",department_id from employees where employee_id = 176;

 

3.  選擇工資不在5000到12000的員工的姓名和工資

 

select initcap(concat(last_name,first_name)) "姓名", salary from employees where salary<5000 or salary>12000;

 

4.  選擇僱用時間在1908-02-01到1908-05-01之間的員工姓名，job_id和僱用時間

寫法一：

select initcap(concat(last_name,first_name)) "姓名",job_id,hire_date from employees where hire_date between '01-2月 -08' and  '01-5月 -08';

寫法二：

select initcap(concat(last_name,first_name)) "姓名",job_id,hire_date from employees where hire_date between to_date('1908-02-01','YYYY-MM-DD') and to_date('1908-05-01','YYYY-MM-DD');

 

5.  選擇在20或50號部門工作的員工姓名和部門號

寫法一：

Select initcap(concat(last_name,first_name)) "姓名",department_id from employees where department_id=20 or department_id=50;

寫法二：

select initcap(concat(last_name,first_name)) "姓名",department_id from employees where department_id in (20,50);

 

6.  選擇在1908年僱用的員工的姓名和僱用時間

寫法一：

select initcap(concat(last_name,first_name))"姓名",hire_date from employees where hire_date like '%08';

寫法二：

select initcap(concat(last_name,first_name))"姓名",hire_date from employees where hire_date between to_date('1908-1-1','YYYY-MM-DD') and to_date('1908-12-31','YYYY-MM-DD');

 

7.  選擇公司中沒有管理者的員工姓名及job_id

寫法一：

Select initcap(concat(last_name,first_name)) "姓名",job_id from employees where manager_id is null;

寫法二：

select initcap(concat(last_name,first_name)) "姓名",job_id from employees where nvl(manager_id,0)=0;

 

8.  選擇公司中有獎金的員工姓名，工資和獎金級別

寫法一：

Select initcap(concat(last_name,first_name)) "姓名",salary,commission_pct from employees where commission_pct is not null;

寫法二：

Select initcap(concat(last_name,first_name)) "姓名",salary,commission_pct from employees where nvl2(commission_pct, commission_pct,0)>0;

寫法三：

select initcap(concat(last_name,first_name)) "姓名", commission_pct from employees where nvl（commission_pct,0)<>0;

9.  選擇員工姓名的第三個字母是a的員工姓名

 

select initcap(concat(last_name,first_name)) "姓名" from employees where initcap(concat(last_name,first_name)) like '__a%';

 

10. 選擇姓名中有字母a和e的員工姓名

 

select initcap(concat(last_name,first_name)) "姓名" from employees where initcap(concat(last_name,first_name)) like '%a%' and  initcap(concat(last_name,first_name)) like '%e%';

 

11. 顯示系統時間

寫法一：

Select sysdate from dual;

寫法二：

Select current_timestamp from dual;

 

12. 查詢員工號，姓名，工資，以及工資提高百分之20%後的結果（new salary）

 

Select employee_id,initcap(concat(last_name,first_name)) "姓名",salary*1.2 as"new salary" from employees;

 

13. 將員工的姓名按首字母排序，並寫出姓名的長度（length）

 

Select initcap(concat(last_name,first_name)) "姓名",length(initcap(concat(last_name,first_name))) as"名字長度" from employees order by substr(initcap(concat(last_name,first_name)),1,1);

 

14. 查詢各員工的姓名，並顯示出各員工在公司工作的月份數

 

Select initcap(concat(last_name,first_name)) "姓名",trunc(months_between(sysdate,hire_date),0) "在職時間" from employees;

 

15. 查詢員工的姓名，以及在公司工作的月份數（worked_month），並按月份數降序排列

 

Select initcap(concat(last_name,first_name)) "姓名",trunc(months_between(sysdate,hire_date),0) "在職時間" from employees order by trunc(months_between(sysdate,hire_date),0) desc;

 

16.做一個查詢，產生下面的結果

<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,'$99999')||' monthly but wants '||to_char(salary*3,'$99999') as "Dream Salary" from employees;

 

17.使用decode函式，按照下面的條件：

job                  grade

AD_PRES            A

ST_MAN             B

IT_PROG             C

SA_REP              D

ST_CLERK           E

Others                     F

產生下面的結果

Last_name	Job_id	Grade
king	AD_PRES	A

 

寫法一：

select last_name,job_id,decode(job_id,'AD_PRES','A','ST_MAN','B','IT_PROG','C','SA_REP ','D','ST_CLERK','E','F')Grade from employees;

寫法二：

Select last_name,job_id,

case job_id

when 'AD_PRES' then 'A'

when 'ST_MAN' then 'B'

when 'IT_PROG' then 'C'

when 'SA_REP' then 'D'

when 'ST_CLERK' then 'E'

else 'F' end "Grage" from employees;

 

18.查詢公司員工工資的最大值，最小值，平均值，總和

 

select max(salary) "最大值",min(salary) "最小值",avg(salary) "平均值",sum(salary) "總和" from employees;

 

19.查詢各job_id的員工工資的最大值，最小值，平均值，總和

 

select job_id,max(salary) "最大值",min(salary) "最小值",avg(salary) "平均值",sum(salary) "總和" from employees group by job_id;

 

20.選擇具有各個job_id的員工人數

 

Select job_id,count(*) from employees group by job_id;

 

21.查詢員工最高工資和最低工資的差距（DIFFERENCE）

 

Select max(salary)-min(salary) as "DIFFERENCE" from employees;

 

22.查詢各個管理者手下員工的最低工資，其中最低工資不能低於6000，沒有管理者的員工不計算在內

 

Select manager_id,min(salary) from employees group by manager_id having min(salary)>=6000 and manager_id is not null;

 

23.查詢所有部門的名字，location_id，員工數量和工資平均值

 

Select department_name,location_id,count(e.job_id) as "部門總人數",avg(e.salary) as "平均工資" from departments d,employees e where d.department_id=e.department_id group by department_name,location_id;