Oracle SQL语句面试题一

来源：互联网发布：智讯快客软件编辑：程序博客网时间：2024/05/16 13:22

以下是Oracle 数据库中hr 用户下面的表。下面的sql语句是以hr身份进入的。若是以system 身份进入需要在表名前面加hr. （如hr.employees）。
以下是表的基本结构
employees 表：

EMPLOYEE_ID NUMBER(6)FIRST_NAME VARCHAR2(20)LAST_NAME VARCHAR2(25)EMAIL VARCHAR2(25)PHONE_NUMBER VARCHAR2(20)HIRE_DATE DATEJOB_ID VARCHAR2(10)SALARY NUMBER(8,2)COMMISSION_PCT NUMBER(2,2)MANAGER_ID NUMBER(6)DEPARTMENT_ID NUMBER(4)

departments

DEPARTMENT_ID NUMBER(4)DEPARTMENT_NAME VARCHAR2(30)MANAGER_ID NUMBER(6)LOCATION_ID NUMBER(4)

locations

LOCATION_ID NUMBER(4)STREET_ADDRESS VARCHAR2(40)POSTAL_CODE VARCHAR2(12)CITY VARCHAR2(30)STATE_PROVINCE VARCHAR2(25)COUNTRY_ID CHAR(2)

jobs 表

JOB_ID VARCHAR2(10)JOB_TITLE VARCHAR2(35)MIN_SALARY NUMBER(6)MAX_SALARY NUMBER(6)

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

sql 语句如下:

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

结果

LAST_NAME                 HIRE_DATE------------------------- --------------Atkinson                  30-10月-05Tucker                    30-1月 -05Olsen                     30-3月 -06King                      30-1月 -04

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

1). 通过两条 sql 查询:select manager_idfrom employeeswhere lower(last_name) = 'chen' --返回的结果108select *from employeeswhere employee_id = 1082). 通过一条 sql 查询(自连接):select m.*from employees e, employees mwhere e.manager_id = m.employee_id and e.last_name= 'Chen'3). 通过一条 sql 查询(子查询): select *from employeeswhere employee_id = (select manager_idfrom employeeswhere last_name = 'Chen')

结果

EMPLOYEE_ID FIRST_NAME           LAST_NAME----------- -------------------- -------------------------EMAIL                     PHONE_NUMBER         HIRE_DATE      JOB_ID------------------------- -------------------- -------------- ----------    SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID---------- -------------- ---------- -------------        108 Nancy                GreenbergNGREENBE                  515.124.4569         17-8月 -02     FI_MGR     12008                       101           100

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

SELECT department_id, avg(salary)FROM employees eGROUP BY department_idHAVING avg(salary) > 8000

结果

DEPARTMENT_ID AVG(SALARY)------------- -----------          100  8601.33333           90  19333.3333           20        9500           70       10000          110       10154           80  8955.88235已选择6行。

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

SELECT last_name, salaryFROM employeesWHERE salary = (SELECT min(salary)FROM employees)

结果：

LAST_NAME                     SALARY------------------------- ----------Olson                           2100

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

select * from departmentswhere department_id =(    select department_id    from employees    group by department_id    having avg (salary) =    (        select min (avg(salary))        from employees        group by department_id    ))

结果：

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID------------- ------------------------------ ---------- -----------           50 Shipping                              121        1500

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

select d.*,(select avg(salary)  from employees where department_id=d.department_id)    from departments d     where d.department_id=(        select department_id        from         employees        group by department_id        having avg(salary) =(        select min (avg(salary))        from employees        group by department_id        )    )

结果：

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID------------- ------------------------------ ---------- -----------(SELECTAVG(SALARY)FROMEMPLOYEESWHEREDEPARTMENT_ID=D.DEPARTMENT_ID)------------------------------------------------------------------           50 Shipping                              121        1500                                                        3475.55556

查询平均工资最高的 job

1). 按 job_id 分组, 查询最高的平均工资SELECT max(avg(salary))FROM employeesGROUP BY job_id2). 查询出平均工资等于 1) 的 job_idSELECT job_idFROM employeesGROUP BY job_idHAVING avg(salary) = (SELECT max(avg(salary))FROM employeesGROUP BY job_id)3). 查询出 2) 对应的 job 信息SELECT *WHERE job_id = (SELECT job_idFROM employeesGROUP BY job_idHAVING avg(salary) = (SELECT max(avg(salary))FROM employeesGROUP BY job_id))结果JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY---------- ----------------------------------- ---------- ----------AD_PRES    President                                20080      40000

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

1). 查询出公司的平均工资SELECT avg(salary)FROM employees2). 查询平均工资高于 1) 的部门 IDSELECT department_idFROM employeesGROUP BY department_idHAVING avg(salary) > (SELECT avg(salary)FROM employees)结果：DEPARTMENT_ID-------------          100           90           20           70          110           80           40已选择8行。

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

SELECT employee_id, last_nameFROM employeesWHERE employee_id in (SELECT distinct manager_idFROM employees)结果EMPLOYEE_ID LAST_NAME----------- -------------------------        148 Cambrault        102 De Haan        147 Errazuriz        121 Fripp        108 Greenberg        201 Hartstein        205 Higgins        103 Hunold        122 Kaufling        100 King        101 Kochhar        124 Mourgos        146 Partners        114 Raphaely        145 Russell        123 Vollman        120 Weiss        149 Zlotkey已选择18行。

各个部门中最高工资中最低的那个部门的

1). 查询出各个部门的最高工资SELECT max(salary)FROM employeesGROUP BY department_id2). 查询出 1) 对应的查询结果的最低值: 各个部门中最低的最高工资(无法查询对应的 department_id)SELECT min(max(salary))FROM employeesGROUP BY department_id3). 查询出 2) 所对应的部门 id 是多少: 各个部门中最高工资等于2) 的那个部门的 idSELECT department_idFROM employeesGROUP BY department_idHAVING max(salary) = (SELECT min(max(salary))FROM employeesGROUP BY department_id)4). 查询出 3) 所在部门的最低工资select min (salary) from employees where department_id =(    select department_idfrom employeesgroup by department_idhaving max(salary) = (    select min (max(salary))    from employees    group by department_id    ))结果：MIN(SALARY)-----------       4400

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

1). 各个部门中, 查询平均工资最高的平均工资是多少SELECT max(avg(salary))FROM employeesGROUP BY department_id2). 各个部门中, 平均工资等于 1) 的那个部门的部门号是多少SELECT department_idFROM employeesGROUP BY department_idHAVING avg(salary) = (SELECT max(avg(salary))FROM employeesGROUP BY department_id)3). 查询出 2) 对应的部门的 manager_idSELECT manager_idFROM departmentsWHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING avg(salary) = (SELECT max(avg(salary))FROM employeesGROUP BY department_id))4). 查询出 employee_id 为 3) 查询的 manager_id 的员工的last_name, department_id, email, salaryselect 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    )    ) )结果：LAST_NAME                 DEPARTMENT_ID EMAIL                         SALARY------------------------- ------------- ------------------------- ----------King                                 90 SKING                          24000

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

1). 查询出 1999 年来公司的所有的员工的 salarySELECT salaryFROM employeesWHERE to_char(hire_date, 'yyyy') = '1999'2). 查询出 1) 对应的结果的最大值SELECT max(salary)FROM employeesWHERE to_char(hire_date, 'yyyy') = '1999'3). 查询工资等于 2) 对应的结果且 1999 年入职的员工信息SELECT *FROM employeesWHERE to_char(hire_date, 'yyyy') = '1999' AND salary= (SELECT max(salary)FROM employeesWHERE to_char(hire_date, 'yyyy') = '1999')

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

SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary < ALL(SELECT salaryFROM employeesWHERE job_id = 'IT_PROG')AND job_id <> 'IT_PROG'

结果：

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY----------- ------------------------- ---------- ----------        185 Bull                      SH_CLERK         4100        192 Bell                      SH_CLERK         4000        193 Everett                   SH_CLERK         3900        188 Chung                     SH_CLERK         3800        137 Ladwig                    ST_CLERK         3600        189 Dilly                     SH_CLERK         3600        141 Rajs                      ST_CLERK         3500        186 Dellinger                 SH_CLERK         3400        133 Mallin                    ST_CLERK         3300        129 Bissot                    ST_CLERK         3300        180 Taylor                    SH_CLERK         3200        138 Stiles                    ST_CLERK         3200        125 Nayer                     ST_CLERK         3200        194 McCain                    SH_CLERK         3200        115 Khoo                      PU_CLERK         3100        196 Walsh                     SH_CLERK         3100        142 Davies                    ST_CLERK         3100        181 Fleaur                    SH_CLERK         3100        187 Cabrio                    SH_CLERK         3000        197 Feeney                    SH_CLERK         3000        116 Baida                     PU_CLERK         2900        190 Gates                     SH_CLERK         2900        134 Rogers                    ST_CLERK         2900        183 Geoni                     SH_CLERK         2800        130 Atkinson                  ST_CLERK         2800        117 Tobias                    PU_CLERK         2800        195 Jones                     SH_CLERK         2800        126 Mikkilineni               ST_CLERK         2700        139 Seo                       ST_CLERK         2700        143 Matos                     ST_CLERK         2600        199 Grant                     SH_CLERK         2600        118 Himuro                    PU_CLERK         2600        198 OConnell                  SH_CLERK         2600        140 Patel                     ST_CLERK         2500        131 Marlow                    ST_CLERK         2500        119 Colmenares                PU_CLERK         2500        191 Perkins                   SH_CLERK         2500        182 Sullivan                  SH_CLERK         2500        144 Vargas                    ST_CLERK         2500        127 Landry                    ST_CLERK         2400        135 Gee                       ST_CLERK         2400        128 Markle                    ST_CLERK         2200        136 Philtanker                ST_CLERK         2200        132 Olson                     ST_CLERK         2100已选择44行。

阅读全文

0 0