178|SQL – Unions with Precision

BYU Student Author: @Marta_Ellsworth
Reviewers: @Andrew_Wilson, @Sterling_Lane
Estimated Time to Solve: 25 Minutes

This is an intro challenge that is part of the SQL Learning Path.

We provide the solution to this challenge using:

  • SQL

Need a program? Click here.

You’ve just started your new job as an accounting clerk at Precision Plane Parts (PPP). Your boss has discovered that you learned SQL in your intro to Information Systems class, and now he has a few tasks for you. Quickly write the SQL queries he has requested to make a good impression on your new boss!


  1. Your boss wants to send everyone a Christmas card. Create a list of the first name, last name, and email of all the vendors and employees at PPP.
    a. Select the “first_name”, “last_name”, and “employee_email” fields from the “Employee” table.
    b. Since the email field on the vendor table is also titled email change the field “employee_email” to “email”.
    c. Select the “first_name”, “last_name”, and “email” fields from the “Vendor” table.
    d. Union the two queries together.
  2. The purchasing department is thinking about selling a few new items, they have included the table in the database, it is called “New_Inventory”. Union this with the “Inventory” data.
  3. Generate a list for this month’s employee raffle. This month, each employee gets one guaranteed entry. Additionally, each employee who either created or reviewed at least one order gets another entry.
    a. Select the “emp_reviewer_id” from the “Orders” table. Make sure to rename the column to “employee_id” and only include each “emp_reviewer_id” once by using SELECT DISTINCT.
    b. Do the same as above for the “emp_order_creation_id”.
    c. Select the “employee_id” from the “Employee” table. Note that each employee is included only once in this table by default so there’s no need to select a distinct employee ID here.
    d. Union these three queries together, but make sure to do it in a way that the duplicates will not be removed since an employee may have multiple raffle entries.

Data Files

Suggestions and Hints
  • To change the name of a column in the output use AS (employee_email AS email).
  • Remember that “Union” removes duplicates while “Union All” does not.


Solution Code

Query 1

SELECT first_name, last_name, employee_email AS email 
FROM Employee 
UNION SELECT first_name, last_name, email 
FROM Vendor; 

Query 2

SELECT * FROM Inventory 
SELECT * FROM New_Inventory; 

Query 3

SELECT DISTINCT emp_reviewer_id AS employee_id  
FROM Orders 
SELECT employee_id  
FROM Employee 
SELECT DISTINCT emp_order_creation_id AS employee_id 
FROM Orders; 

Solution Video: Challenge 178|SQL – Unions with Precision