首页 > 代码库 > PLSQL 的简单命令之三

PLSQL 的简单命令之三

-- 查找两个表中ID相等的
select a.id, a.name,b.math  from stu a,scores b where a.id = b.id

-- 右外连接
select b.id, a.name,b.math  from stu a,scores b where a.id(+) = b.id
select b.id, a.name,b.math  from stu a right outer join scores b on a.id = b.id

-- 左外连接
select a.id, a.name,b.math  from stu a,scores b where a.id = b.id(+)
select a.id, a.name,b.math  from stu a left outer join scores b on a.id = b.id

--1.    显示所有员工的姓名,部门号和部门名称。
select a.last_name ,b.department_id,b.department_name 
from employees a ,departments b

--2.    查询90号部门员工的job_id和90号部门的location_id
select a.job_id ,b.location_id from employees a ,departments b 
where b.department_id=90

/*3.    选择所有有奖金的员工的
last_name , department_name , location_id , city
*/
select a.last_name,b.department_name,b.location_id,c.city 
from employees a,departments b,locations c

/*4.    选择city在Toronto工作的员工的
last_name , job_id , department_id , department_name 
*/
select a.last_name,a.job_id,b.department_id,b.department_name
from employees a,departments b ,locations
where locations.city = Toronto

/*5.    选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式
employees    Emp#    manager    Mgr#
kochhar     101       king     100
*/
select a.last_name 
as employees ,a.employee_id 
as emp#,b.last_name 
as manager,b.employee_id 
as mgr# 
from employees a,employees b 
where a.manager_id = b.employee_id(+)

--查询公司员工工资的最大值,最小值,平均值,总和
select max(salary) from employees
select min(salary) from employees
select avg(salary) from employees
select sum(salary) from employees

--查询各job_id的员工工资的最大值,最小值,平均值,总和
select job_id, max(salary),min(salary),avg(salary),sum(salary) 
from employees 
group by job_id 

--选择具有各个job_id的员工人数
select job_id,count(job_id) 
from employees 
group by job_id 

--查询员工最高工资和最低工资的差距(DIFFERENCE)
select max(salary)-min(salary) as difference from employees 

/*查询各个管理者手下员工的最低工资,
其中最低工资不能低于6000,
没有管理者的员工不计算在内
*/
select manager_id,min(salary) 
from employees 
where manager_id is not null 
having min(salary)>=6000
group by manager_id

--查询所有部门的名字,location_id,员工数量和工资平均值
select a.department_name,a.location_id,count(b.employee_id),avg(b.salary)
from departments a full outer join employees b 
on a.department_id = b.department_id
group by a.department_name,a.location_id

/*查询公司在1995-1998年之间,每年雇用的人数,结果类似下面的格式
total    1995    1996    1997    1998
  20     3       4     6     7
*/
/*
select count(*), to_char(hire_date,‘yyyy‘) from employees
where to_char(hire_date,‘yyyy‘) between ‘1995‘ and ‘1998‘
group by to_char(hire_date,‘yyyy‘)
*/

/*decode(字段或字段的运算,值1,值2,值3)
  这个函数运行的结果是,当字段或字段的运算的值等于值1时,该函数返回值2,否则返回值3
当然值1,值2,值3也可以是表达式,这个函数使得某些sql语句简单了许多
*/

select count(*),count(decode(to_char(hire_date,yyyy),1995,1,null)) "1995",
count(decode(to_char(hire_date,yyyy),1996,1,null)) "1996" ,
count(decode(to_char(hire_date,yyyy),1997,1,null)) "1997",
count(decode(to_char(hire_date,yyyy),1998,1,null)) "1998"
from employees
where to_char(hire_date,yyyy) between 1995 and 1998

 

PLSQL 的简单命令之三