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
PLSQL 的简单命令之二
标签：months dream plsql style where _id sel length desc
