subquery/exists-subquery/exists-within-and-or.sql

0001 -- Tests EXISTS subquery support. Tests EXISTS
0002 -- subquery within a AND or OR expression.
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
0037 -- Or used in conjunction with exists - ExistenceJoin
0038 -- TC.02.01
0039 SELECT emp.emp_name
0040 FROM   emp
0041 WHERE  EXISTS (SELECT dept.state
0042                FROM   dept
0043                WHERE  emp.dept_id = dept.dept_id)
0044         OR emp.id > 200;
0045
0046 -- all records from emp including the null dept_id
0047 -- TC.02.02
0048 SELECT *
0049 FROM   emp
0050 WHERE  EXISTS (SELECT dept.dept_name
0051                FROM   dept
0052                WHERE  emp.dept_id = dept.dept_id)
0053         OR emp.dept_id IS NULL;
0054
0055 -- EXISTS subquery in both LHS and RHS of OR.
0056 -- TC.02.03
0057 SELECT emp.emp_name
0058 FROM   emp
0059 WHERE  EXISTS (SELECT dept.state
0060                FROM   dept
0061                WHERE  emp.dept_id = dept.dept_id
0062                       AND dept.dept_id = 20)
0063         OR EXISTS (SELECT dept.state
0064                    FROM   dept
0065                    WHERE  emp.dept_id = dept.dept_id
0066                           AND dept.dept_id = 30);
0067 ;
0068
0069 -- not exists and exists predicate within OR
0070 -- TC.02.04
0071 SELECT *
0072 FROM   bonus
0073 WHERE  ( NOT EXISTS (SELECT *
0074                      FROM   emp
0075                      WHERE  emp.emp_name = emp_name
0076                             AND bonus_amt > emp.salary)
0077           OR EXISTS (SELECT *
0078                      FROM   emp
0079                      WHERE  emp.emp_name = emp_name
0080                              OR bonus_amt < emp.salary) );
0081
0082 -- not exists and in predicate within AND
0083 -- TC.02.05
0084 SELECT * FROM bonus WHERE NOT EXISTS
0085 (
0086        SELECT *
0087        FROM   emp
0088        WHERE  emp.emp_name = emp_name
0089        AND    bonus_amt > emp.salary)
0090 AND
0091 emp_name IN
0092 (
0093        SELECT emp_name
0094        FROM   emp
0095        WHERE  bonus_amt < emp.salary);
0096