subquery/exists-subquery/exists-having.sql

0001 -- Tests HAVING clause in subquery.
0002
0003 CREATE TEMPORARY VIEW EMP AS SELECT * FROM VALUES
0004   (100, "emp 1", date "2005-01-01", 100.00D, 10),
0005   (100, "emp 1", date "2005-01-01", 100.00D, 10),
0006   (200, "emp 2", date "2003-01-01", 200.00D, 10),
0007   (300, "emp 3", date "2002-01-01", 300.00D, 20),
0008   (400, "emp 4", date "2005-01-01", 400.00D, 30),
0009   (500, "emp 5", date "2001-01-01", 400.00D, NULL),
0010   (600, "emp 6 - no dept", date "2001-01-01", 400.00D, 100),
0011   (700, "emp 7", date "2010-01-01", 400.00D, 100),
0012   (800, "emp 8", date "2016-01-01", 150.00D, 70)
0013 AS EMP(id, emp_name, hiredate, salary, dept_id);
0014
0015 CREATE TEMPORARY VIEW DEPT AS SELECT * FROM VALUES
0016   (10, "dept 1", "CA"),
0017   (20, "dept 2", "NY"),
0018   (30, "dept 3", "TX"),
0019   (40, "dept 4 - unassigned", "OR"),
0020   (50, "dept 5 - unassigned", "NJ"),
0021   (70, "dept 7", "FL")
0022 AS DEPT(dept_id, dept_name, state);
0023
0024 CREATE TEMPORARY VIEW BONUS AS SELECT * FROM VALUES
0025   ("emp 1", 10.00D),
0026   ("emp 1", 20.00D),
0027   ("emp 2", 300.00D),
0028   ("emp 2", 100.00D),
0029   ("emp 3", 300.00D),
0030   ("emp 4", 100.00D),
0031   ("emp 5", 1000.00D),
0032   ("emp 6 - no dept", 500.00D)
0033 AS BONUS(emp_name, bonus_amt);
0034
0035 -- simple having in subquery.
0036 -- TC.01.01
0037 SELECT dept_id, count(*)
0038 FROM   emp
0039 GROUP  BY dept_id
0040 HAVING EXISTS (SELECT 1
0041                FROM   bonus
0042                WHERE  bonus_amt < min(emp.salary));
0043
0044 -- nested having in subquery
0045 -- TC.01.02
0046 SELECT *
0047 FROM   dept
0048 WHERE  EXISTS (SELECT dept_id,
0049                       Count(*)
0050                FROM   emp
0051                GROUP  BY dept_id
0052                HAVING EXISTS (SELECT 1
0053                               FROM   bonus
0054                               WHERE bonus_amt < Min(emp.salary)));
0055
0056 -- aggregation in outer and inner query block with having
0057 -- TC.01.03
0058 SELECT dept_id,
0059        Max(salary)
0060 FROM   emp gp
0061 WHERE  EXISTS (SELECT dept_id,
0062                       Count(*)
0063                FROM   emp p
0064                GROUP  BY dept_id
0065                HAVING EXISTS (SELECT 1
0066                               FROM   bonus
0067                               WHERE  bonus_amt < Min(p.salary)))
0068 GROUP  BY gp.dept_id;
0069
0070 -- more aggregate expressions in projection list of subquery
0071 -- TC.01.04
0072 SELECT *
0073 FROM   dept
0074 WHERE  EXISTS (SELECT dept_id,
0075                         Count(*)
0076                  FROM   emp
0077                  GROUP  BY dept_id
0078                  HAVING EXISTS (SELECT 1
0079                                 FROM   bonus
0080                                 WHERE  bonus_amt > Min(emp.salary)));
0081
0082 -- multiple aggregations in nested subquery
0083 -- TC.01.05
0084 SELECT *
0085 FROM   dept
0086 WHERE  EXISTS (SELECT dept_id,
0087                       count(emp.dept_id)
0088                FROM   emp
0089                WHERE  dept.dept_id = dept_id
0090                GROUP  BY dept_id
0091                HAVING EXISTS (SELECT 1
0092                               FROM   bonus
0093                               WHERE  ( bonus_amt > min(emp.salary)
0094                                        AND count(emp.dept_id) > 1 )));