较常见的SQL面/笔试题

 

 

 

 

 

日志

 

 

关于我

Andy

一枚流星,在想像的高空逡巡,期待的眼波,寂寞而执着

  加博友    关注他

文章分类

• ·数据库（6）
• ·Cognos 8（1）
• ·J2EE框架（5）
• ·JS脚本（4）
• ·Java/WEB开发（33）
• ·Linux/Unix 程（9）
• ·Android（9）
• ·操作系统（16）
• ·更多 >

LOFTER精选

注册免费冲印20张照片 >

网易考拉推荐

网易新闻

小学启用气膜体育馆 雾霾天学生照样玩

• ·朋友圈"借"现金 80后女老总涉嫌诈骗1.46亿
• ·陕西一女副校长失联 曾向同事朋友借款数百万
• ·警方回应"公示嫖娼者":对头像等信息已做技术处理
• ·山西临汾二氧化硫“爆表” 环保局称正在调查
• ·21年前"杀害押款员抢劫1500万"最后2名疑犯落网
• ·北京长时间雾霾成因几何?区域传输加重污染程度
• ·男子地铁内与女乘客发生口角 "动刀子"划伤对方
• ·男童被公交碾压身亡 违停者等20人遭索赔133万

下载网易新闻客户端 >

 

深入理解Oracle表：三大表连接方式详解之Hash Join的定义，原理，算法，成本，模式和位图

 

面试笔试中最爱考的oracle 查询题，历来被称为经典，面对经典查询你会几个呢？

比较常见的SQL面/笔试题  

2014-07-25 16:18:04|  分类： 数据库 |  标签：oracle  |举报|字号 订阅

       

  下载LOFTER 我的照片书  |

 

  

 

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

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

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

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

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

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

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

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

9. 查询出公司中所有 manager 的详细信息.

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

11. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

12. 查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.

13.返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary

 

************************answers*********************

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

    selectlast_name, hire_date

    from employees

    wherehire_date = last_day(hire_date) – 1

 

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

1). 通过两条sql查询:

 

            selectmanager_id

            from employees

            where lower(last_name) = ‘chen’ –返回的结果为 108

            

            select *

            from employees

            whereemployee_id = 108

            

    2). 通过一条sql查询(自连接):

            

            select m.*

            from employees e, employees m

            wheree.manager_id = m.employee_id and e.last_name = ‘Chen’        

            

    3). 通过一条sql查询(子查询):    

            

            select *

            from employees

            whereemployee_id = (

            selectmanager_id

            from employees

            wherelast_name = ‘Chen’

             )    

3.查询平均工资高于 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

whered.department_id = (

SELECT department_id

FROM employees

GROUP BY department_id

HAVING avg(salary) = (

             SELECT min(avg(salary))

             FROM employees

             GROUP BY department_id

             )

)

        

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 BY job_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 的详细信息.

    1). 查询出所有的manager_id

    SELECT distinct manager_id

    FROM employeess

    

    2). 查询出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 是多少: 各个部门中最高工资等于 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

        )    

    )

 

11. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

    

    1). 各个部门中, 查询平均工资最高的平均工资是多少

    SELECT max(avg(salary))

    FROM employees

    GROUP BY department_id

    

    2). 各个部门中, 平均工资等于 1) 的那个部门的部门号是多少

    SELECT department_id

    FROM employees

    GROUP BY department_id

    HAVING avg(salary) = (

        SELECT max(avg(salary))

        FROM employees

        GROUP BY department_id

    )

    

    3). 查询出 2) 对应的部门的manager_id

    SELECT manager_id

    FROM departments

    WHERE department_id = (

        SELECT department_id

        FROM employees

        GROUP BY department_id

        HAVING avg(salary) = (

            SELECT max(avg(salary))

            FROM employees

            GROUP BY department_id

        )    

    )

    

    4). 查询出employee_id为 3) 查询的manager_id的员工的last_name, department_id, email, salary

    SELECT last_name, department_id, email, salary

    FROM employees

    WHERE employee_id = (

        SELECT manager_id

        FROM departments

        WHERE department_id = (

            SELECT department_id

            FROM employees

            GROUP BY department_id

            HAVING avg(salary) = (

                SELECT max(avg(salary))

                FROM employees

                GROUP BY department_id

            )    

        )    

    )

    

12. 查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.

        1). 查询出 1999 年来公司的所有的员工的 salary

        SELECT salary

        FROM employees

        WHERE to_char(hire_date, ‘yyyy’) = ’1999′

        

        2). 查询出 1) 对应的结果的最大值

        SELECT max(salary)

        FROM employees

        WHERE to_char(hire_date, ‘yyyy’) = ’1999′

        

        3). 查询工资等于 2) 对应的结果且 1999 年入职的员工信息        

        SELECT *

        FROM employees

        WHERE to_char(hire_date, ‘yyyy’) = ’1999′ AND salary = (

            SELECT max(salary)

            FROM employees

            WHERE to_char(hire_date, ‘yyyy’) = ’1999′

        )

        

13.返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary

SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary< ALL

(SELECT salary

FROM employees

WHERE job_id = ‘IT_PROG’)

AND job_id<> ‘IT_PROG’;

 

******************高级子查询******************

• 书写多列子查询
• 在FROM 子句中使用子查询
• 在SQL中使用单列子查询
• 书写相关子查询
• 使用EXISTS 和NOT EXISTS 操作符
• 使用子查询更新和删除数据
• 使用WITH 子句

–多列子查询（不成对比较&成对比较）

1.查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id

[方式一]

SELECT employee_id, manager_id, department_id

FROM employees

WHERE manager_id IN 
(SELECT manager_id

FROM employees

WHERE employee_id IN (174,141))

AND department_id IN 
(SELECT department_id

FROM employees

WHERE employee_id IN (174,141))

AND    employee_id NOT IN(174,141);

[方式二]

SELECT    employee_id, manager_id, department_id

FROM    employees

WHERE (manager_id, department_id) IN

(SELECT manager_id, department_id

FROM employees

WHERE employee_id IN (141,174))

AND    employee_id NOT IN (141,174);

 

–在 FROM 子句中使用子查询

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

[方式一]

selectlast_name,department_id,salary,

(selectavg(salary)from employees e3

where e1.department_id = e3.department_id

group by department_id) avg_salary

from employees e1

where salary >

(selectavg(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_name和location。其中，若员工department_id与location_id为1800的department_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);

—相关子查询： 

 

 

5.查询员工中工资大于本部门平均工资的员工的last_name,

salary和其department_id

SELECT last_name, salary, department_id

FROM employees outer

WHERE salary>

(SELECT AVG(salary)

FROM employees

WHERE department_id =

outer.department_id) ;

 

6.若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id

SELECT e.employee_id, last_name,e.job_id

FROM employees e

WHERE 2<= (SELECT COUNT(*)

FROM job_history

WHERE employee_id = e.employee_id);

 

–EXISTS 操作符

• EXISTS 操作符检查在子查询中是否存在满足条件的行
• 如果在子查询中存在满足条件的行:
  • 不在子查询中继续查找
  • 条件返回TRUE

7.查询公司管理者的employee_id,last_name,job_id,

department_id信息

SELECT employee_id, last_name, job_id, department_id

FROM employees outer

WHERE EXISTS ( SELECT ‘X’

FROM employees

WHERE manager_id =

outer.employee_id);

 

8.查询departments表中，不存在于employees表中的部门的department_id和department_name

SELECT department_id, department_name

FROM departments d

WHERE NOT EXISTS (SELECT ‘X’

FROM employees

WHERE department_id

= d.department_id);

 

–关于数据更新

9.修改表employees,添加department_name列，赋予department_id相应的部门名称。

 

ALTER TABLE employees

ADD(department_name VARCHAR2(14));

 

UPDATE employees e

SET department_name =

(SELECT department_name

     FROM departments d

    WHERE e.department_id = d.department_id);

 

–关于数据删除

10.删除表employees中，其与emp_history表皆有的数据

DELETE FROM employees E

WHERE employee_id in

(SELECT employee_id

FROM emp_history

WHERE employee_id = E.employee_id);

 

–WITH 子句

11.查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息

WITH 
dept_costs AS (

SELECT d.department_name, SUM(e.salary) AS dept_total

FROM employees e, departments d

WHERE e.department_id = d.department_id

GROUP BY d.department_name),

avg_cost AS (

SELECT SUM(dept_total)/COUNT(*) AS dept_avg

FROM dept_costs)

SELECT *

FROM dept_costs

WHERE dept_total>

(SELECT dept_avg

FROM avg_cost)

ORDER BY department_name;

 

附加题目：

12.查询员工的last_name, department_id, salary.其中员工的salary,department_id与有奖金的任何一个员工的salary,

department_id相同即可

 

selectlast_name,department_id,salary

fromemployees

where(salary,department_id)in(

selectsalary,department_id

fromemployees

wherecommission_pctisnotnull

)

 

13.选择工资大于所有JOB_ID = ‘SA_MAN’的员工的工资的员工的last_name, job_id, salary

 

selectlast_name,job_id,salary

fromemployees

wheresalary>all(

selectsalary

fromemployees

wherejob_id=’SA_MAN’

)

 

14.选择所有没有管理者的员工的last_name

 

selectlast_name

fromemployeese1

wherenotexists(

select’A’

fromemployeese2

wheree1.manager_id=e2.employee_id

)

15.查询10，50，20号部门的job_id，department_id并且department_id按10，50，20的顺序排列

Column dummy noprint;

selectjob_id , department_id ,1 dummy

from employees

wheredepartment_id = 10

union

selectjob_id , department_id , 2

from employees

wheredepartment_id = 50

union

selectjob_id , department_id , 3

from employees

wheredepartment_id= 20

order by 3

 

 

 

阅读(3345) |  评论(0)

       

喜欢 推荐 转载

 

深入理解Oracle表：三大表连接方式详解之Hash Join的定义，原理，算法，成本，模式和位图

 

面试笔试中最爱考的oracle 查询题，历来被称为经典，面对经典查询你会几个呢？

关闭

玩LOFTER，免费冲印20张照片，人人有奖！      我要抢>

评论

  登录后你可以发表评论，请先登录。 登录>>

 

 

 

 

 

 

 

 

 

 

 

我的照片书  -  博客风格  -  手机博客  -  下载LOFTER APP  -  订阅此博客

网易公司版权所有 ©1997-2017