程序博客网 > 查看linux系统版本命令

常见的数据库操作

来源：互联网 发布：查看linux系统版本命令 编辑：程序博客网 时间：2024/05/17 09:01

几个表

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

[plain] view plain copy
print?
  1. select last_name, hire_date  
  2. from employees  
  3. where hire_date = last_day(hire_date) – 1   
 select last_name, hire_date from employees where hire_date = last_day(hire_date) – 1 

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

 1). 通过两条 sql 查询:

[sql] view plain copy
print?
  1.  select manager_id  
  2.  from employees  
  3.  where lower(last_name) = ‘chen’ – 返回的结果为 108   
  4.    
  5. select *   
  6. from employees   
  7. where employee_id = 108    
 select manager_id from employees where lower(last_name) = 'chen' -- 返回的结果为 108 select * from employees where employee_id = 108  

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

[sql] view plain copy
print?
  1. select m.*   
  2. from employees e, employees m   
  3. where e.manager_id = m.employee_id and e.last_name= ‘Chen’   
select m.* from employees e, employees m where e.manager_id = m.employee_id and e.last_name= 'Chen' 

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

[sql] view plain copy
print?
  1. select *   
  2. from employees   
  3. where employee_id = (   
  4. select manager_id   
  5. from employees   
  6. where last_name = ‘Chen’   
  7. )  
select * from employees where employee_id = ( select manager_id from employees where last_name = 'Chen' )

 

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

[sql] view plain copy
print?
  1. SELECT department_id, avg(salary)   
  2. FROM employees e   
  3. GROUP BY department_id   
  4. HAVING avg(salary) > 8000   
SELECT department_id, avg(salary) FROM employees e GROUP BY department_id HAVING avg(salary) > 8000 

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

[sql] view plain copy
print?
  1. SELECT last_name, salary   
  2. FROM employees   
  3. WHERE salary = ( SELECT min(salary)  FROM employees  )  
SELECT last_name, salary FROM employees WHERE salary = ( SELECT min(salary)  FROM employees  )

 

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

[sql] view plain copy
print?
  1. SELECT *   
  2. FROM departments   
  3. WHERE department_id =(  
  4.     SELECT department_id   
  5.     FROM employees  
  6.     GROUP BY department_id  
  7.     HAVING avg(salary) =(  
  8.      SELECT min(avg(salary))  
  9.     FROM employees  
  10.     GROUP BY department_id )  
  11. )  
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.查询平均工资最低的部门信息和该部门的平均工资  

[sql] view plain copy
print?
  1. select d.*, (select avg(salary)   
  2.                       from employees   
  3.                       where department_id = d.department_id)   
  4. from departments d   
  5. where d.department_id = (   
  6.        SELECTdepartment_id FROM employees   
  7.        GROUP BY department_id HAVING avg(salary) =(   
  8.                 SELECT min(avg(salary)) FROM employees   
  9.                GROUP BY department_id )   
  10.         )   
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 分组, 查询最高的平均工资 

[sql] view plain copy
print?
  1. SELECT max(avg(salary))  
  2. FROM employees   
  3. GROUP BY job_id   
SELECT max(avg(salary))FROM employees GROUP BY job_id 

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

[sql] view plain copy
print?
  1. SELECT job_id   
  2. FROM employees   
  3. GROUP BYjob_id   
  4. HAVING avg(salary) = (   
  5.            SELECT max(avg(salary))   
  6.            FROM employees   
  7.            GROUP BY job_id   
  8.       )   
SELECT job_id FROM employees GROUP BYjob_id HAVING avg(salary) = (            SELECT max(avg(salary))            FROM employees            GROUP BY job_id       ) 

 

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

[sql] view plain copy
print?
  1. SELECT *   
  2. FROM jobs  
  3. WHERE job_id = (   
  4.           SELECT job_id   
  5.           FROM employees   
  6.           GROUP BY job_id   
  7.           HAVING avg(salary) = (   
  8.                     SELECT max(avg(salary))   
  9.                     FROM employees   
  10.                     GROUP BY job_id )   
  11.           )  
SELECT * FROM jobsWHERE 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). 查询出公司的平均工资 

[sql] view plain copy
print?
  1. SELECT avg(salary)   
  2. FROM employees    
SELECT avg(salary) FROM employees  

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

[sql] view plain copy
print?
  1. SELECT department_id   
  2. FROM employees  
  3. GROUP BY department_id   
  4. HAVING avg(salary) >   ( SELECT avg(salary)  FROMemployees)  
SELECT department_id FROM employeesGROUP BY department_id HAVING avg(salary) >   ( SELECT avg(salary)  FROMemployees)

 

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

1). 查询出所有的 manager_id 

[sql] view plain copy
print?
  1. SELECT   distinct  manager_id  FROM  employeess   
SELECT   distinct  manager_id  FROM  employeess 

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

[sql] view plain copy
print?
  1. SELECT employee_id, last_name   
  2. FROM employees   
  3. WHERE employee_id  in  (   
  4.            SELECT distinct manager_id   
  5.            FROM employees   
  6. )   
SELECT employee_id, last_name FROM employees WHERE employee_id  in  (            SELECT distinct manager_id            FROM employees ) 

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

 

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

[sql] view plain copy
print?
  1. SELECT max(salary)   
  2. FROM employees   
  3. GROUP BY department_id   
SELECT max(salary) FROM employees GROUP BY department_id 

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

[sql] view plain copy
print?
  1. SELECT min(max(salary))   
  2. FROM employees   
  3. GROUP BY department_id   
SELECT min(max(salary)) FROM employees GROUP BY department_id 

3). 查询出 2) 所对应的部门 id 是多少: 各个部门中最高工资等于的那个部门的 id 

[sql] view plain copy
print?
  1. SELECT department_id   
  2. FROM employees   
  3. GROUP BY department_id   
  4. HAVING max(salary) =(   
  5.        SELECT min(max(salary))   
  6.        FROM employees   
  7.        GROUP BY department_id   
  8.       )   
SELECT department_id FROM employees GROUP BY department_id HAVING max(salary) =(        SELECT min(max(salary))        FROM employees        GROUP BY department_id       ) 

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

[sql] view plain copy
print?
  1. SELECT min(salary)  
  2. FROM employees   
  3. WHERE department_id = (   
  4.          SELECT department_id   
  5.          FROM employees   
  6.          GROUP BY department_id HAVING max(salary) =(   
  7.                    SELECT min(max(salary))   
  8.                    FROM employees   
  9.                    GROUP BY department_id   )   
  10. )  
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). 各个部门中, 查询平均工资最高的平均工资是多少 

[sql] view plain copy
print?
  1. SELECT max(avg(salary))   
  2. FROM employees   
  3. GROUP BY department_id   
SELECT max(avg(salary)) FROM employees GROUP BY department_id 

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

[sql] view plain copy
print?
  1. SELECT department_id   
  2. FROM employees   
  3. GROUP BY department_id   
  4. HAVING avg(salary) = (   
  5.          SELECT max(avg(salary))   
  6.           FROM employees   
  7.          GROUP BY department_id   
  8. )   
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 

[sql] view plain copy
print?
  1. SELECT manager_id   
  2. FROM departments   
  3. WHERE department_id = (   
  4.       SELECT department_id   
  5.       FROM employees   
  6.       GROUP BY department_id   
  7.       HAVING avg(salary) = (   
  8.             SELECT max(avg(salary))   
  9.             FROM employees   
  10.             GROUP BY department_id )  
  11.       )   
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 

[sql] view plain copy
print?
  1. SELECT last_name, department_id, email,salary   
  2. FROM employees   
  3. WHERE employee_id = (   
  4.        SELECT manager_id   
  5.        FROM departments   
  6.         WHERE department_id = (   
  7.                 SELECT department_id   
  8.                 FROM employees   
  9.                 GROUP BY department_id  
  10.                  HAVING avg(salary) = (   
  11.                         SELECT max(avg(salary))   
  12.                         FROM employees   
  13.                         GROUP BY department_id )   
  14.                 )   
  15.         )   
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 

[sql] view plain copy
print?
  1. SELECT salary   
  2. FROM employees   
  3. WHERE to_char(hire_date,‘yyyy’) = ‘1999’    
SELECT salary FROM employees WHERE to_char(hire_date,'yyyy') = '1999'  

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

[sql] view plain copy
print?
  1. SELECT max(salary)   
  2. FROM employees   
  3. WHERE to_char(hire_date, ‘yyyy’) = ‘1999’   
SELECT max(salary) FROM employees WHERE to_char(hire_date, 'yyyy') = '1999' 

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

[sql] view plain copy
print?
  1. SELECT *   
  2. FROM employees   
  3. WHERE to_char(hire_date, ‘yyyy’) = ‘1999’ AND salary = (   
  4.         SELECT max(salary)  
  5.         FROMemployees   
  6.         WHERE to_char(hire_date, ‘yyyy’) = ‘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 

[sql] view plain copy
print?
  1. SELECT employee_id, last_name, job_id,salary   
  2. FROM    employees   
  3. WHERE salary< ALL  (SELECT salary      
  4.                                         FROM   employees    
  5.                                        WHERE job_id = ‘IT_PROG’)   
  6.                 AND      job_id<> ‘IT_PROG’;   
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

 

[方式一]

[sql] view plain copy
print?
  1. SELECT employee_id,manager_id, department_id   
  2. FROM          employees   
  3. WHERE  manager_id IN (  
  4.                        SELECT manager_id   
  5.                        FROM          employees   
  6.                        WHERE  employee_id IN(174,141)  )   
  7.                  AND     department_id IN (  
  8.                        SELECT department_id   
  9.                        FROM          employees   
  10.                       WHERE  employee_id IN(174,141))   
  11.                 AND   employee_id NOTIN(174,141);  
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);

 

[方式二]

 

[sql] view plain copy
print?
  1. SELECT  employee_id,manager_id, department_id   
  2. FROM  employees   
  3. WHERE     (manager_id,department_id) IN (  
  4.                             SELECTmanager_id, department_id   
  5.                             FROM  employees   
  6.                             WHERE     employee_id IN (141,174))   
  7.                             AND   employee_id NOT IN (141,174);   
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 及平均工资

 

[方式一] 

[sql] view plain copy
print?
  1. select last_name,department_id,salary,  
  2.           (select avg(salary)   
  3.            from employees e3  
  4.           where e1.department_id = e3.department_id  
  5.            groupby department_id )  avg_salary  
  6. from employees e1   
  7. where salary >(select avg(salary)   
  8.                             from employees e2   
  9.                            where e1.department_id = e2.department_id  
  10.                           group by department_id )   
select last_name,department_id,salary,          (select avg(salary)            from employees e3          where e1.department_id = e3.department_id           groupby department_id )  avg_salaryfrom employees e1 where salary >(select avg(salary)                             from employees e2                            where e1.department_id = e2.department_id                          group by department_id ) 

[方式二] 

[sql] view plain copy
print?
  1. SELECT a.last_name,a.salary, a.department_id, b.salavg  
  2. FROM       employees a, (SELECT  department_id,AVG(salary) salavg   
  3.                                               FROM       employees   
  4.                                               GROUP BYdepartment_id) b   
  5. WHERE a.department_id =b.department_id   
  6.                  AND     a.salary> b.salavg  
SELECT a.last_name,a.salary, a.department_id, b.salavgFROM       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’。 

[sql] view plain copy
print?
  1. SELECT employee_id, last_name,    
  2.     ( CASE department_id  
  3.      WHEN ( SELECT department_id FROM departments  
  4.                     WHERE location_id = 1800)   
  5.       THEN ‘Canada’  ELSE  ‘USA’ END) location  
  6. FROM employees;    
SELECT employee_id, last_name,      ( CASE department_id     WHEN ( SELECT department_id FROM departments                    WHERE location_id = 1800)       THEN 'Canada'  ELSE  'USA' END) locationFROM employees;  

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

[sql] view plain copy
print?
  1. SELECT  employee_id,last_name   
  2. FROM      employeese   
  3. ORDER BY (SELECT department_name   
  4.           FROM departments d   
  5.           WHERE e.department_id = d.department_id);   
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 

[sql] view plain copy
print?
  1. SELECT last_name, salary, department_id   
  2. FROM  employees outer   
  3. WHERE salary > (SELECT AVG(salary)   
  4.                 FROM  employees  
  5.                 WHERE     department_id=outer.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 

[sql] view plain copy
print?
  1. SELECT e.employee_id, last_name,e.job_id  
  2. FROM employees e   
  3. WHERE     2<= (SELECT COUNT(*)   
  4.                FROM  job_history   
  5.                WHERE employee_id =e.employee_id);   
SELECT e.employee_id, last_name,e.job_idFROM 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 信息 

[sql] view plain copy
print?
  1. SELECT employee_id, last_name, job_id, department_id   
  2. FROM  employees outer   
  3. WHERE EXISTS ( SELECT ‘X’   
  4.                FROM  employees   
  5.                WHERE     manager_id= outer.employee_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 

[sql] view plain copy
print?
  1. SELECT department_id,department_name   
  2. FROM departments d   
  3. WHERE NOT EXISTS (  
  4.                   SELECT‘X’   
  5.                   FROM  employees   
  6.                   WHERE department_id =d.department_id);   
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相应的部门名称。

[sql] view plain copy
print?
  1. ALTER TABLE employees  ADD(department_name VARCHAR2(14));   
  2.   
  3. UPDATE employees e   
  4. SET          department_name=   
  5.         ( SELECT department_name   
  6.            FROM  departments d   
  7.            WHERE     e.department_id= d.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 表皆有的数据 

[sql] view plain copy
print?
  1. DELETE FROM employees E   
  2. WHERE employee_id in(  
  3.               SELECT employee_id   
  4.               FROM  emp_history  
  5.               WHERE  employee_id =E.employee_id);   
DELETE FROM employees E WHERE employee_id in(              SELECT employee_id               FROM  emp_history              WHERE  employee_id =E.employee_id); 

 

8.WITH 子句 

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

[sql] view plain copy
print?
  1. WITH   
  2. dept_costs AS (   
  3.      SELECT     d.department_name,SUM(e.salary) AS dept_total   
  4.      FROM          employeese, departments d   
  5.     WHERE  e.department_id= d.department_id   
  6.     GROUPBY d.department_name),   
  7. avg_cost          AS(   
  8.      SELECT   SUM(dept_total) / COUNT(*)  AS dept_avg  
  9.      FROM   dept_costs)   
  10.   
  11. SELECT *   
  12. FROM  dept_costs   
  13. WHERE     dept_total> (SELECT dept_avg  FROM avg_cost)   
  14. ORDER BY department_name;   
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 相同即可

 

[sql] view plain copy
print?
  1. select last_name, department_id, salary  
  2. from employees   
  3. where(salary,department_id) in (   
  4.             select salary,department_id   
  5.             from employees   
  6.             where commission_pct is not null   
  7. )   
select last_name, department_id, salaryfrom 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  

[sql] view plain copy
print?
  1. select last_name, job_id, salary   
  2. from employees   
  3. where salary > all(   
  4.      selectsalary   
  5.      from employees   
  6.      where job_id = ‘SA_MAN’ )   
select last_name, job_id, salary from employees where salary > all(      selectsalary      from employees      where job_id = 'SA_MAN' ) 

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

[sql] view plain copy
print?
  1. select last_name   
  2. from employees e1   
  3. where not exists (   
  4.         select ‘A’   
  5.         from employees e2   
  6.        where e1.manager_id = e2.employee_id   
  7. )   
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)

 

[sql] view plain copy
print?
  1. Column dummy noprint;   
  2.    select job_id , department_id ,1dummy   
  3.    from employees   
  4.    wheredepartment_id = 10   
  5. union   
  6.      select job_id , department_id , 2   
  7.      from employees   
  8.      wheredepartment_id = 50   
  9. union   
  10.      select job_id , department_id , 3   
  11.       from employees   
  12.      wheredepartment_id= 20   
  13. order by 3   
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中通过别名来引用子查询

 


阅读全文
0 0
查看linux系统版本命令 查看linux系统版本命令
原创粉丝点击
热门IT博客
淘宝怎么上传产品淘宝怎么上传产品
剑灵龙女数据图剑灵龙女数据图
windows 光盘镜像
websocket nginx
淘宝页面显示不全
如何面试java工程师
单片机蓝牙模块怎么用
获取手机小号软件
js点击显示更多内容
织梦dedecms视频
文章标签数据库设计
网络电视能看电视台吗
index.php
win7网络共享设置软件
mysql当前时间减一小时
淘宝网羊剪绒大衣中长
ubuntu 17.04 iso安装
全球恐怖主义数据库
淘宝旺旺红包怎么收
tensorflow 英文文档
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 背景墙瓷砖 墙砖品牌 阳台瓷砖 木纹瓷砖 厨房卫生间瓷砖选择 瓷砖一线品牌 普通地板砖价格表 厕所瓷砖 仿木纹瓷砖 80 80地板砖价格表 玻璃瓷砖 萨米特瓷砖 如何选择地板砖 瓷砖选择 兴辉瓷砖 瓷砖选购 木纹地板砖 科能瓷砖 利家居陶瓷 瓷砖胶 瓷砖的种类 浴室瓷砖 长谷瓷砖 瓷砖品牌大全 瓷砖排行榜 瓷砖界面剂 宏陶瓷砖 釉面砖 陶瓷砖价格 楼梯瓷砖 磁砖与瓷砖的区别 地板砖多少钱一块 陶仙坊瓷砖 广维瓷砖 来德利瓷砖 罗马里奥瓷砖 依诺瓷砖怎么样 中盛瓷砖 骏程陶瓷 卓马瓷砖 普拉提瓷砖

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里