subquery/exists-subquery/exists-cte.sql

0001 -- Tests EXISTS subquery used along with
0002 -- Common Table Expressions(CTE)
0003
0004 CREATE TEMPORARY VIEW EMP AS SELECT * FROM VALUES
0005   (100, "emp 1", date "2005-01-01", 100.00D, 10),
0006   (100, "emp 1", date "2005-01-01", 100.00D, 10),
0007   (200, "emp 2", date "2003-01-01", 200.00D, 10),
0008   (300, "emp 3", date "2002-01-01", 300.00D, 20),
0009   (400, "emp 4", date "2005-01-01", 400.00D, 30),
0010   (500, "emp 5", date "2001-01-01", 400.00D, NULL),
0011   (600, "emp 6 - no dept", date "2001-01-01", 400.00D, 100),
0012   (700, "emp 7", date "2010-01-01", 400.00D, 100),
0013   (800, "emp 8", date "2016-01-01", 150.00D, 70)
0014 AS EMP(id, emp_name, hiredate, salary, dept_id);
0015
0016 CREATE TEMPORARY VIEW DEPT AS SELECT * FROM VALUES
0017   (10, "dept 1", "CA"),
0018   (20, "dept 2", "NY"),
0019   (30, "dept 3", "TX"),
0020   (40, "dept 4 - unassigned", "OR"),
0021   (50, "dept 5 - unassigned", "NJ"),
0022   (70, "dept 7", "FL")
0023 AS DEPT(dept_id, dept_name, state);
0024
0025 CREATE TEMPORARY VIEW BONUS AS SELECT * FROM VALUES
0026   ("emp 1", 10.00D),
0027   ("emp 1", 20.00D),
0028   ("emp 2", 300.00D),
0029   ("emp 2", 100.00D),
0030   ("emp 3", 300.00D),
0031   ("emp 4", 100.00D),
0032   ("emp 5", 1000.00D),
0033   ("emp 6 - no dept", 500.00D)
0034 AS BONUS(emp_name, bonus_amt);
0035
0036 -- CTE used inside subquery with correlated condition
0037 -- TC.01.01
0038 WITH bonus_cte
0039      AS (SELECT *
0040          FROM   bonus
0041          WHERE  EXISTS (SELECT dept.dept_id,
0042                                  emp.emp_name,
0043                                  Max(salary),
0044                                  Count(*)
0045                           FROM   emp
0046                                  JOIN dept
0047                                    ON dept.dept_id = emp.dept_id
0048                           WHERE  bonus.emp_name = emp.emp_name
0049                           GROUP  BY dept.dept_id,
0050                                     emp.emp_name
0051                           ORDER  BY emp.emp_name))
0052 SELECT *
0053 FROM   bonus a
0054 WHERE  a.bonus_amt > 30
0055        AND EXISTS (SELECT 1
0056                    FROM   bonus_cte b
0057                    WHERE  a.emp_name = b.emp_name);
0058
0059 -- Inner join between two CTEs with correlated condition
0060 -- TC.01.02
0061 WITH emp_cte
0062      AS (SELECT *
0063          FROM   emp
0064          WHERE  id >= 100
0065                 AND id <= 300),
0066      dept_cte
0067      AS (SELECT *
0068          FROM   dept
0069          WHERE  dept_id = 10)
0070 SELECT *
0071 FROM   bonus
0072 WHERE  EXISTS (SELECT *
0073                FROM   emp_cte a
0074                       JOIN dept_cte b
0075                         ON a.dept_id = b.dept_id
0076                WHERE  bonus.emp_name = a.emp_name);
0077
0078 -- Left outer join between two CTEs with correlated condition
0079 -- TC.01.03
0080 WITH emp_cte
0081      AS (SELECT *
0082          FROM   emp
0083          WHERE  id >= 100
0084                 AND id <= 300),
0085      dept_cte
0086      AS (SELECT *
0087          FROM   dept
0088          WHERE  dept_id = 10)
0089 SELECT DISTINCT b.emp_name,
0090                 b.bonus_amt
0091 FROM   bonus b,
0092        emp_cte e,
0093        dept d
0094 WHERE  e.dept_id = d.dept_id
0095        AND e.emp_name = b.emp_name
0096        AND EXISTS (SELECT *
0097                    FROM   emp_cte a
0098                           LEFT JOIN dept_cte b
0099                                  ON a.dept_id = b.dept_id
0100                    WHERE  e.emp_name = a.emp_name);
0101
0102 -- Joins inside cte and aggregation on cte referenced subquery with correlated condition
0103 -- TC.01.04
0104 WITH empdept
0105      AS (SELECT id,
0106                 salary,
0107                 emp_name,
0108                 dept.dept_id
0109          FROM   emp
0110                 LEFT JOIN dept
0111                        ON emp.dept_id = dept.dept_id
0112          WHERE  emp.id IN ( 100, 200 ))
0113 SELECT emp_name,
0114        Sum(bonus_amt)
0115 FROM   bonus
0116 WHERE  EXISTS (SELECT dept_id,
0117                       max(salary)
0118                FROM   empdept
0119                GROUP  BY dept_id
0120                HAVING count(*) > 1)
0121 GROUP  BY emp_name;
0122
0123 -- Using not exists
0124 -- TC.01.05
0125 WITH empdept
0126      AS (SELECT id,
0127                 salary,
0128                 emp_name,
0129                 dept.dept_id
0130          FROM   emp
0131                 LEFT JOIN dept
0132                        ON emp.dept_id = dept.dept_id
0133          WHERE  emp.id IN ( 100, 200 ))
0134 SELECT emp_name,
0135        Sum(bonus_amt)
0136 FROM   bonus
0137 WHERE  NOT EXISTS (SELECT dept_id,
0138                           Max(salary)
0139                    FROM   empdept
0140                    GROUP  BY dept_id
0141                    HAVING count(*) < 1)
0142 GROUP  BY emp_name;