Oracle Scott 用户相关sql

1．查询每个月倒数第 2 天入职的员工的信息.

select last_name, hire_date  
from employees  
where hire_date = last_day(hire_date) – 1  

2. 查询出 last_name 为 ‘Chen’ 的 manager 的信息.

select *   
from employees   
where employee_id = (   
select manager_id   
from employees   
where last_name = 'Chen'   
)

查询平均工资高于 8000 的部门 id 和它的平均工资.

SELECT department_id, avg(salary)   
FROM employees e   
GROUP BY department_id   
HAVING avg(salary) > 8000 

####　4. 查询工资最低的员工信息:last_name, salary　####

SELECT last_name, salary   
FROM employees   
WHERE salary = ( SELECT min(salary)  FROM employees  ) 

5.查询平均工资最低的部门信息

SELECT *   
FROM departments   
WHERE department_id =(  
    SELECT department_id   
    FROM employees  
    GROUP BY department_id  
    HAVING avg(salary) =(  
     SELECT min(avg(salary))  
    FROM employees  
    GROUP BY department_id )  
)  

6.查询平均工资最低的部门信息和该部门的平均工资

select d.*, (select avg(salary)   
                      from employees   
                      where department_id = d.department_id)   
from departments d   
where d.department_id = (   
       SELECTdepartment_id FROM employees   
       GROUP BY department_id HAVING avg(salary) =(   
                SELECT min(avg(salary)) FROM employees   
               GROUP BY department_id )   
        )  

数据库存储了1和0，怎么查出男女

--decode函数
select decode(t.sex,'1','男','2','女','未知') from table t

7.查询平均工资最高的 job 信息

1). 按 job_id 分组, 查询最高的平均工资

SELECT max(avg(salary))  
FROM employees   
GROUP BY job_id  

2). 查询出平均工资等于 1) 的 job_id

SELECT job_id   
FROM employees   
GROUP BYjob_id   
HAVING avg(salary) = (   
           SELECT max(avg(salary))   
           FROM employees   
           GROUP BY job_id   
      ) 

3). 查询出 2) 对应的 job 信息

SELECT *   
FROM jobs  
WHERE job_id = (   
          SELECT job_id   
          FROM employees   
          GROUP BY job_id   
          HAVING avg(salary) = (   
                    SELECT max(avg(salary))   
                    FROM employees   
                    GROUP BY job_id )   
          )  

8．查询平均工资高于公司平均工资的部门有哪些?

1). 查询出公司的平均工资

SELECT avg(salary)   
FROM employees   

2). 查询平均工资高于 1) 的部门 ID

SELECT department_id   
FROM employees  
GROUP BY department_id   
HAVING avg(salary) >   ( SELECT avg(salary)  FROM employees)  

9.查询出公司中所有 manager的详细信息.（IN关键字）

1). 查询出所有的 manager_id

SELECT   distinct  manager_id  FROM  employees  

查询出 employee_id 为 1) 查询结果的那些员工的信息

SELECT employee_id, last_name   
FROM employees   
WHERE employee_id  in  (   
           SELECT distinct manager_id   
           FROM employees   
)   

10.各个部门中 最高工资中最低的那个部门的最低工资是多少

1). 查询出各个部门的最高工资

SELECT max(salary)   
FROM employees   
GROUP BY department_id 

2). 查询出 1) 对应的查询结果的最低值: 各个部门中最低的最高工资(无法查询对应的 department_id)

SELECT min(max(salary))   
FROM employees   
GROUP BY department_id  

3). 查询出 2) 所对应的部门 id 是多少

SELECT department_id   
FROM employees   
GROUP BY department_id   
HAVING max(salary) =(   
       SELECT min(max(salary))   
       FROM employees   
       GROUP BY department_id   
      ) 

4). 查询出 3) 所在部门的最低工资

SELECT min(salary)  
FROM employees   
WHERE department_id = (   
         SELECT department_id   
         FROM employees   
         GROUP BY department_id HAVING max(salary) =(   
                   SELECT min(max(salary))   
                   FROM employees   
                   GROUP BY department_id   )   
)  

Having 和Where的区别

having 和where 都是用来筛选用的 ，但是having 是筛选组， 而where是筛选记录。他们有各自的区别：
1》当分组筛选的时候用having

-----------------------------------------------------
用having就一定要和group by连用
-------------------------------------------------------

2》其它情况用where
3》where和having的执行级别不同
在查询过程中聚合语句(sum,min,max,avg,count)要比having子句优先执行。
而where子句在查询过程中优先于聚合语句(sum,min,max,avg,count)。
having就是来弥补where在分组数据判断时的不足，因为where执行优先级别要快于聚合语句。

With子句

with子句-----给子查询定义一个别名，可以通过别名来引用子查询，实现了一次定义多次使用。
WITH子句的格式为： WITH 别名 AS（子查询）
定义好别名就可以在sql中通过别名来引用子查询

子查询可以简化sql语句的编写，但是，在数据库的优化方面，除了sql语句之外，还要尽量降低使用子查询的次数，比如：子查询取得系统中所有年龄或工资与‘Sun’相同的人员信息

        select * from T_Person

        where  FAge=(select  FAge from  T_Person  where  FName='Sun') OR FSalary=(select FSalary from T_Person where FName='Sun')

上面的语句可以实现此功能，但是，子查询被调用了两次，比较消耗系统资源的操作，降低了系统的性能，一个子查询修改其他查询也得修改，造成修改不同步。

上述问题的原因及解决方案：

      1.子查询只能在定义的时候使用，多次使用就必须多次定义。

      2.sql提供了with子句-----给子查询定义一个别名，这样可以通过别名来引用子查询，实现了一次定义多次使用。

 使用WITH子句改造上面的Sql语句：

  WITH person_Sun  AS

 {

  select * from T_Person where FName='Sun'

 }

  select  * from T_Person

 where FAge=person_Sun .FAge

 OR FSalary=person_Sun .FSalary

尤上可知：WITH子句的格式为：

WITH  别名  AS（子查询）

定义好别名就可以在sql中通过别名来引用子查询

还可以在WITH语句中为子查询中的列定义别名，如下代码：

WITH person_Sun（F1,F2,F3） AS

{

SELECT FAge，FName，FSalsry from T_Person where FName=‘Sun’

}

SELECT * from T_Person

where FAg=person_Sun.F1

OR FSalsry=person_Sun.F3

EXISTS 操作符

•     EXISTS 操作符检查在子查询中是否存在满足条件的行 
•     如果在子查询中存在满足条件的行: 
– 不在子查询中继续查找 
– 条件返回 TRUE 
只要()中的where条件成立，不需要返回值，就会输出结果
select employee_id,job_id,last_name,department_id from employees e1
where EXISTS (select 'a' from employees e2  where e1.employee_id=e2.manager_id);