当前位置:数据库 > Oracle >>

oracle-hr表查询命令练习(超完整的select命令大全)

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) "最
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,