Examples of fullselect queries

The following examples illustrate fullselect queries.

Example 1: Select all columns and rows from the EMPLOYEE table.
```
  SELECT * FROM EMPLOYEE
```
Example 2: List the employee numbers (EMPNO) of all employees in the EMPLOYEE table whose department number (WORKDEPT) either begins with 'E' or who are assigned to projects in the EMP_ACT table whose project number (PROJNO) equals 'MA2100', 'MA2110', or 'MA2112'.
```
  SELECT EMPNO
     FROM EMPLOYEE
     WHERE WORKDEPT LIKE 'E%'
  UNION
  SELECT EMPNO
     FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
```
Example 3: Make the same query as in example 2, and, in addition, "tag" the rows from the EMPLOYEE table with 'emp' and the rows from the EMP_ACT table with 'emp_act'. Unlike the result from example 2, this query might return the same EMPNO more than once, identifying which table it came from by the associated "tag".
```
  SELECT EMPNO, 'emp'
     FROM EMPLOYEE
     WHERE WORKDEPT LIKE 'E%'
  UNION
  SELECT EMPNO, 'emp_act' FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
```

Example 4: Make the same query as in example 2, only use UNION ALL so that no duplicate rows are eliminated.

  SELECT EMPNO
      FROM EMPLOYEE
      WHERE WORKDEPT LIKE 'E%'
  UNION ALL
  SELECT EMPNO
     FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')

Example 5: Make the same query as in Example 3, only include an additional two employees currently not in any table and tag these rows as "new".

  SELECT EMPNO, 'emp'
     FROM EMPLOYEE
     WHEREWORKDEPTLIKE 'E%'
  UNION
  SELECT EMPNO, 'emp_act'
     FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
  UNION
     VALUES ('NEWAAA', 'new'), ('NEWBBB', 'new')

Example 6: This example of EXCEPT produces all rows that are in T1 but not in T2.

   (SELECT * FROM T1)
   EXCEPT ALL
   (SELECT * FROM T2)

If no null values are involved, this example returns the same results as

   SELECT ALL *
      FROM T1
      WHERE NOT EXISTS (SELECT * FROM T2
                          WHERE T1.C1 = T2.C1 AND T1.C2 = T2.C2 AND...)

Example 7: This example of INTERSECT produces all rows that are in both tables T1 and T2, removing duplicates.
```
   (SELECT * FROM T1)
   INTERSECT
   (SELECT * FROM T2)
```
If no null values are involved, this example returns the same result as
```
   SELECT DISTINCT * FROM T1
      WHERE EXISTS (SELECT * FROM T2
                    WHERE T1.C1 = T2.C1 AND T1.C2 = T2.C2 AND...)
```
where C1, C2, and so on represent the columns of T1 and T2.