SQL面试用题

--在 FROM 子句中使用子查询

2. 返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资

[方式一]

select last_name,department_id,salary,

(select avg(salary)from employees e3

where e1.department_id = e3.department_id

group by department_id) avg_salary

from employees e1

where salary >

         (select avg(salary)

          from employees e2  

          where e1.department_id = e2.department_id

          --group by department_id

          )

[方式二]

SELECT  a.last_name, a.salary,

        a.department_id, b.salavg

FROM    employees a, (SELECT   department_id,

                      AVG(salary) salavg

                      FROM     employees

                      GROUP BY department_id) b

WHERE   a.department_id = b.department_id

AND     a.salary > b.salavg;

 

--单列子查询表达式

  • Oracle8i 只在下列情况下可以使用, 例如:
    • SELECT 语句 (FROM 和 WHERE 子句)
    • INSERT 语句中的VALUES列表中
  • Oracle9i中单列子查询表达式可在下列情况下使用:
    • DECODE  和 CASE
    • SELECT 中除 GROUP BY 子句以外的所有子句中

3. 显式员工的employee_id,last_namelocation。其中,若员工department_idlocation_id1800department_id相同,则location’Canada’,其余则为’USA’

SELECT employee_id, last_name,

       (CASE department_id

        WHEN  (SELECT department_id FROM departments

WHERE location_id = 1800)

        THEN 'Canada' ELSE 'USA' END) location

FROM   employees;

 

4. 查询员工的employee_id,last_name,要求按照员工的department_name排序

SELECT   employee_id, last_name

FROM     employees e

ORDER BY (SELECT department_name

  FROM departments d

  WHERE e.department_id = d.department_id);

 

--相关子查询

 

 

本教程由尚硅谷教育大数据研究院出品,如需转载请注明来源。

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值