Advanced Questions (Union, Intersect, and Minus)

  1. The relational set operators UNION, INTERSECT and MINUS work properly only if the relations are union-compatible. What does union-compatible mean, and how would you check for this condition?
  2. What is the difference between UNION and UNION ALL? Write the syntax for each.
  3. Suppose that you have two tables, Employees and Employees_1. The Employees table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The Employees_1 table contains the records for employees: John Cretchakov and Mary Chen. Given that information, what is the query output for the UNION query? List the query output.
  4. Given the employee information in question 3, what is the query output for the UNION ALL query? List the query output.
  5. Given the employee information in question 3, what is the query output for the INTERSECT query? List the query output.
  6. Given the employee information in question 3, what is the query output for the EXCEPT query? List the query output.
  7. What is a cross join? Give an example of its syntax.
  8. Explain these three join types:
    1. left outer join
    2. right outer join
    3. full outer join
  9. What is a subquery, and what are its basic characteristics?
  10. What is a correlated subquery? Give an example.
  11. Suppose that a Product table contains two attributes, PROD_CODE and VEND_CODE. The values for the PROD_CODE are: ABC, DEF, GHI, and JKL. These are matched by the following values for the VEND_CODE:  125, 124, 124, and 123, respectively (e.g., PROD_CODE value ABC corresponds to VEND_CODE value 125). The Vendor table contains a single attribute, VEND_CODE, with values 123, 124, 125, and 126. (The VEND_CODE attribute in the Product table is a foreign key to the VEND_CODE in the Vendor table.)
  12. Given the information in question 11, what would be the query output for the following? Show values.
    1. A UNION query based on these two tables
    2. A UNION ALL query based on these two tables
    3. An INTERSECT query based on these two tables
    4. A MINUS query based on these two tables