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.
Overview
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!
Instructions
- 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. - 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.
- 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
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
UNION
SELECT * FROM New_Inventory;
Query 3
SELECT DISTINCT emp_reviewer_id AS employee_id
FROM Orders
UNION ALL
SELECT employee_id
FROM Employee
UNION ALL
SELECT DISTINCT emp_order_creation_id AS employee_id
FROM Orders;
Challenge178_Solution.accdb
Solution Video: Challenge 178|SQL – Unions with Precision