PLSQL 的简单命令之二
2016-11-29 18:44 甘雨路 阅读(867) 评论(0) 编辑 收藏 举报--1. 查询工资大于12000的员工姓名和工资 select first_name,last_name,salary from employees where salary > 12000 --2. 查询员工号为176的员工的姓名和部门号 select first_name,last_name,department_id from employees where job_id = '176' --3. 选择工资不在5000到12000的员工的姓名和工资 select first_name,last_name,salary from employees where salary < 5000 and salary > 12000 --4. 选择雇用时间在1998-02-01到1998-05-01之间的员工姓名,job_id和雇用时间 select first_name,last_name,job_id,hire_date from employees where to_char(hire_date,'yyyy-mm-dd')>='1998-02-01' and to_char(hire_date,'yyyy-mm-dd')<='1998-05-01'; --5. 选择在20或50号部门工作的员工姓名和部门号 select first_name,last_name,department_id from employees where department_id in (20,50) --6. 选择在1994年雇用的员工的姓名和雇用时间 select first_name,last_name,hire_date from employees where to_char(hire_date,'yyyy')='1994' --7. 选择公司中没有管理者的员工姓名及job_id select first_name,last_name,job_id from employees where manager_id is null; --8. 选择公司中有奖金的员工姓名,工资和奖金级别 select first_name,last_name,salary,commission_pct from employees where commission_pct is not null --9. 选择员工姓名的第三个字母是a的员工姓名 select first_name,last_name from employees where first_name like '__a%' or last_name like '__a%'; --10. 选择姓名中有字母a和e的员工姓名 select first_name,last_name from employees where lower(first_name) like '%a%'and lower(first_name) like '%e%' or lower(last_name)like '%a%' and lower(last_name)like '%e%'; --单行函数 --1. 显示系统时间(注:日期+时间) select sysdate from dual --2. 查询员工号,姓名,工资,以及工资提高百分之20%后的结果(new salary) select employee_id,first_name,last_name,salary,salary*1.2 as "NEW SALARY" from employees --3. 将员工的姓名按首字母排序,并写出姓名的长度(length) select substr(first_name,1,1) as FIRSTWORD, length(first_name) as "LENGTH" from employees order by substr(first_name,1,1) asc --4. 查询各员工的姓名,并显示出各员工在公司工作的月份数(worked_month)。 select first_name,last_name,round( months_between(sysdate,hire_date))WORKED_MONTHS from employees --5. 查询员工的姓名,以及在公司工作的月份数(worked_month),并按月份数降序排列 select first_name,last_name,round( months_between(sysdate,hire_date))WORKED_MONTHS from employees order by WORKED_MONTHS desc /*6. 做一个查询,产生下面的结果 <last_name> earns <salary> monthly but wants <salary*3> Dream Salary King earns $24000 monthly but wants $72000 */ select last_name || ' earns '||salary||' monthly but wants '||salary*3 as "A GREAT DREAM" from employees /*7. 使用decode函数,按照下面的条件: job grade AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E 产生下面的结果 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') ||' king AD_PRES A' as SOMEWORDS from employees --8. 将第7题的查询用case函数再写一遍。 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 ' ' end ||' king AD_PRES A' as SOMEWORDS from employees