数据库SQL Server查询语句列题

几个表

employees 表： 

EMPLOYEE_ID              NUMBER(6) 

FIRST_NAME                VARCHAR2(20) 

LAST_NAME                 VARCHAR2(25) 

EMAIL                               VARCHAR2(25) 

PHONE_NUMBER       VARCHAR2(20) 

HIRE_DATE                  DATE 

JOB_ID                            VARCHAR2(10) 

SALARY                         NUMBER(8,2) 

COMMISSION_PCT     NUMBER(2,2) 

MANAGER_ID              NUMBER(6) 

DEPARTMENT_ID       NUMBER(4)  

jobs 表： 

JOB_ID                   VARCHAR2(10) 

JOB_TITLE            VARCHAR2(35) 

MIN_SALARY       NUMBER(6) 

MAX_SALARY     NUMBER(6)

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)

 

job_grades 表： 

GRADE_LEVEL      VARCHAR2(3) 

LOWEST_SAL         NUMBER 

HIGHEST_SAL      NUMBER

1 一般查询

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 天入职的员工的信息.

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

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

 1). 通过两条 sql 查询:

select manager_id
from employees
where lower(last_name) = 'chen' -- 返回的结果为 108 
 
select * 
from employees 
where employee_id = 108

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

select m.* 
from employees e, employees m 
where e.manager_id = m.employee_id and e.last_name= 'Chen'

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

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 ) 
        )

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)  FROMemployees)

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

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 是多少: 各个部门中最高工资等于的那个部门的 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 

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)
        FROMemployees 
        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';

主要学会问题的拆分，由内而外 将大问题逐个拆分为小问题。 

2高级子查询

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

书写多列子查询 

在 FROM 子句中使用子查询 

在 SQL 中使用单列子查询 

书写相关子查询 

使用 EXISTS 和 NOT EXISTS 操作符 

使用子查询更新和删除数据 

使用 WITH 子句

 

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

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 NOTIN(174,141);

[方式二]

SELECT  employee_id,manager_id, department_id 
FROM  employees 
WHERE     (manager_id,department_id) IN (
                            SELECTmanager_id, department_id 
                            FROM  employees 
                            WHERE     employee_id IN (141,174)) 
                            AND   employee_id NOT IN (141,174);

2.在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
           groupby 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 BYdepartment_id) b 
WHERE a.department_id =b.department_id 
                 AND     a.salary> b.salavg

3.单列子查询表达式 

•     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      employeese 
ORDER BY (SELECT department_name 
          FROM departments d 
          WHERE e.department_id = d.department_id);

单列、from、orderby等都可以进行子查询

4.相关子查询

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);

5.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);

6.关于数据更新 

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);

7.关于数据删除

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

DELETE FROM employees E 
WHERE employee_id in(
              SELECT employee_id 
              FROM  emp_history
              WHERE  employee_id =E.employee_id);

8.WITH 子句 

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

WITH 
dept_costs AS ( 
     SELECT     d.department_name,SUM(e.salary) AS dept_total 
     FROM          employeese, departments d 
    WHERE  e.department_id= d.department_id 
    GROUPBY 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;

3 附加题目 

NOTNULL/ALL/EXISTS/NOT EXISTS 

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

select last_name, department_id, salary
from employees 
where(salary,department_id) in ( 
            select salary,department_id 
            from employees 
            where commission_pct is not null 
)

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

select last_name, job_id, salary 
from employees 
where salary > all( 
     selectsalary 
     from employees 
     where job_id = 'SA_MAN' )

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

select last_name 
from employees e1 
where not exists ( 
        select 'A' 
        from employees e2 
       where e1.manager_id = e2.employee_id 
)

15. 查询 10，50，20 号部门的job_id，department_id 并且

department_id 按 10，50，20 的顺序排列(union) 

Column dummy noprint; 
   select job_id , department_id ,1dummy 
   from employees 
   wheredepartment_id = 10 
union 
     select job_id , department_id , 2 
     from employees 
     wheredepartment_id = 50 
union 
     select job_id , department_id , 3 
      from employees 
     wheredepartment_id= 20 
order by 3

4 总结

1 Having 和Where的区别

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

1》当分组筛选的时候用having

-----------------------------------------------------

用having就一定要和group by连用

用group by不一有having （它只是一个筛选条件用的）

-------------------------------------------------------

2》其它情况用where 

3》where和having的执行级别不同
在查询过程中聚合语句(sum,min,max,avg,count)要比having子句优先执行。

而where子句在查询过程中优先于聚合语句(sum,min,max,avg,count)。

having就是来弥补where在分组数据判断时的不足，因为where执行优先级别要快于聚合语句。

2 With子句

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

WITH子句的格式为：   WITH  别名 AS（子查询）

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