各种SQL子查询实例

返回订单表中订单价值超过5000美元的那些客户的详细信息。
SELECT * FROM customers
WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders WHERE order_value > 5000);

子查询也可以与INSERT语句一起使用。
INSERT INTO premium_customers 
SELECT * FROM customers 
WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders WHERE order_value > 5000);
通过使用子查询返回的数据，将高级客户的记录插入名为premium_customers的表中。 这里的高级客户是已下订单价值超过5000美元的客户。

带有UPDATE语句的子查询
还可以将子查询与UPDATE语句结合使用，以更新表中的单列或多列，
UPDATE orders
SET order_value = order_value + 10
WHERE cust_id IN (SELECT cust_id FROM customers WHERE postal_code = 75016);
通过将当前订单值增加10美元，更新订单（orders）表中邮政编码为75016的地区的客户的订单值。

带有DELETE语句的子查询
可以将子查询与DELETE语句结合使用，以删除表中的单行或多行，
DELETE FROM orders
WHERE order_id IN (SELECT order_id FROM order_details WHERE product_id = 5);
从包含product_id为5的产品的订单表中删除这些订单。

 

SELECT *
FROM EMP
WHERE SAL < (SELECT AVG(SAL) FROM EMP);
查询薪资比平均薪资低的员工信息

HAVING子句是对分组统计函数进行过滤的子句，也可以在HAVING子句中使用子查询
SELECT JOB,AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING AVG(SAL) = (SELECT MAX(AVG(SAL)) FROM EMP GROUP BY JOB);
查询平均薪资最高的职位及其平均薪资

FROM子查询
FROM子查询就是将一个查询结构（一般多行多列）作为主查询的数据源
SELECT JOB,AVG(SAL)
FROM (SELECT JOB,AVG(SAL) AS AVGSAL FROM EMP GROUP BY JOB)TEMP
WHERE TEMP.AVGSAL>2000;
查询平均薪资高于2000的职位以及该职位的平均薪资

SELECT子查询
SELECT子查询在SELECT子句中使用查询的结果(一般会和dual空表一起使用)
SELECT (SELECT COUNT(*) FROM EMP WHERE JOB = 'SALESMAN')/(SELECT COUNT(*) FROM EMP)
FROM DUAL;
职位是SALESMAN的员工占总员工的比例

EXISIT子查询
将主查询的数据带到子查询中验证，如果成功则返回true，否则发水false。主查询接收true是就会显示这条数据,flase就不会显示。
SELECT *
FROM EMP E
WHERE EXISIT (
    SELECT *
    FROM DEPT D
    WHERE E.DEPTNO = D.DEPTNO);
查询有部门的员工信息

查询薪资排名的员工信息
SELECT *
FROM EMP
WHERE SAL = (SELECT MIN(SAL) 
            FROM (SELECT ROWNUM,SAL 
                   FROM (SELECT SAL FROM EMP GROUP BY SAL ORDER BY SAL DESC)
                 WHERE ROWNUM<=n));
查询薪资排名第n个员工的信息（包括并列排名）
思路：
1.先按薪资降序分组
2.再取前n名薪资中最低的薪资，即第n名的薪资。
3.最后在原表中找出薪资与最低薪资相同的员工信息。

查询平均成绩比‘王五’大的学生id，姓名，平均成绩
select s.s_id , s.s_name , avg(score)
from t_student s join t_grade g on s.s_id = g.s_id
group by s.s_id , s.s_name
having avg(score) > (select avg(score)
                     from t_student s join t_grade g on s.s_id = g.s_id
                     where s.s_name = '王五'
                     group by s.s_id)  

查询其他课程中比课程id为‘400004’课程的任一分数低的学生的学号、姓名、课程号、课程名、分数(ANY)
select s.s_id , s_name , c.c_id , c_name , score
from t_grade g join t_student s on g.s_id = s.s_id
               join t_course c on g.c_id = c.c_id
having c.c_id != 400004 and avg (score) < any (
                                        select avg(score)
                                        from t_grade 
                                        where c_id = 400004
                                        group by (c_id)
                                        )
group by c.c_id , s.s_id , s.s_name , c_name ,score

查询其他课程中比课程id为‘400004’课程的所有分数高的学生的学号、姓名、课程号、课程名、分数(ALL)
select s.s_id , s_name , c.c_id , c_name , score
from t_grade g join t_student s on g.s_id = s.s_id
               join t_course c on g.c_id = c.c_id
having c.c_id != 400004 and score > all (
                                        select score
                                        from t_grade 
                                        where c_id = 400004
                                        )
group by c.c_id , s.s_id , s.s_name , c_name ,score

内联视图子查询
       (1)SELECT ename,job,sal,rownum
          FROM (SELECT ename,job,sal FROM EMP ORDER BY sal)；
       (2)SELECT ename,job,sal,rownum
          FROM ( SELECT ename,job,sal FROM EMP ORDER BY sal)
          WHERE rownum<=5；
  
在HAVING子句中使用子查询
        SELECT deptno,job,AVG(sal) FROM EMP GROUP BY deptno,job HAVING AVG(sal)>(SELECT sal FROM EMP WHERE ename='MARTIN')； 

列出其销售目标超过各个销售人员定额综合的销售点。
SELECT CITY
FROM OFFICES
WHERE TARGET > (SELECT SUM(QUOTA)
                             FROM SALESREPS
                             WHERE REP_OFFICES = OFFICE)

子查询编写的许多查询也可以写成多表查询或连接：
    列出在西部地区销售点工作的销售人员（表1）的名字（表2）；

SELECT NAME,AGE 
FROM SALESREPS
WHERE REP_OFFICE IN (SELECT OFFICE
                                      FROM OFFICES
                                     WHERE REGION = ‘Western’)

 
SELECT NAME,AGE
FROM SALESREPS,OFFICES
WHERE REP_OFFICE = OFFICE
AND REGION = 'Western'

列出销售量超过平均定额的销售人员的名字和年龄

SELECT NAME,AGE
FROM SALESREPS
WHERE QUOTA > (SELECT AGE(QUOTA)
                            FROM SALESREPS)
(在这个例子中，内部查询是一个汇总查询，外部查询不是，所以不能把两个查询组合成一个连接。)

列出对ACI生产的产品，其取得的平均订单大小超过了总的平均订单大小的销售人员
SELECT NAME,AVG(AMOUNT)
FROM SALESREPS,ORDERS
WHERE EMPL_NUM = REP
AND MFR = ‘ACI’
GROUP BY NAME
HAVING AVG(AMOUNT) > (SELECT AVG(AMOUNT) 
                                        FROM ORDERS)