176|SQL – Precision Plane Data Presentation

Sterling_Lane · April 30, 2024, 12:45am

BYU Student Author: @Sterling_Lane
Reviewers: @Andrew_Wilson, @Marta_Ellsworth
Estimated Time to Solve: 10 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
Welcome to your first day at the Precision Plane Parts accounts payable department! Our company is dedicated to helping the aerospace industry build the safest aircraft possible by providing and delivering high quality equipment. Your role here is to help us in that endeavor by maintaining our expenditures database so we can continue to maintain our inventory and continue to keep good relationships with our vendors. For your first day, you are tasked with getting familiar with our main database. Download the attached Access database and get started!

Instructions
Run each of the queries below and save your results as separate queries in Access. To create a new query, first click the “Query Design” button in the Create pane in Access. From here, switch the view to the SQL view by selecting the “SQL” view button in the top left or bottom right corner of the page. To save your query, simply hit “ctrl + s” to open a dialog box asking you to name your query. This will save each of your queries next to the tables in your database.

Who are the employees that work with us? Write a query displaying all columns from the Employee table.
a. Select all the columns from the Employee table
Who are the vendors that buy from us, and how do we contact them? Write a query displaying the vendor id, company name, and company phone number from the Vendor table.
a. Select the vendor_id, company_name, and phone_number columns from the Vendor table.
How frequently do we sell our inventory items? Write a query showing the inventory name and standard expenditure frequency for each item in the Inventory table.
a. Select the inventory_name and std_expenditure_freq columns from the Inventory table.
b. Rename the std_expenditure_freq column to order_frequency
What types of roles do our employees have? Write a query showing the list of employee roles in our Accounts Payable department.
a. Select the emp_role_description column from the EmployeeRole table.

Data Files

Challenge176_Data.accdb

Suggestions and Hints

To select all the columns in a table, use the ‘*’ key word (SELECT * FROM Table)
All of the requested queries can be done by using only the SELECT and FROM statements.

Solution

Solution Code

Query 1

SELECT * 
FROM Employee;

Query 2

SELECT vendor_id, company_name, phone_number 
FROM Vendor;

Query 3

SELECT inventory_name, std_expenditure_freq AS order_frequency 
FROM Inventory;

Query 4

SELECT emp_role_description 
FROM EmployeeRole;

Challenge176_Solution.accdb
Solution Video: Challenge 176|SQL – Precision Plane Data Presentation

qt_915 · June 28, 2024, 6:36am

Solution:
Q1
SELECT employee_id, first_name, last_name, employee_role_id
FROM Employee;
Q2
SELECT vendor_id, company_name, phone_number
FROM Vendor;
Q3
SELECT inventory_name, std_expenditure_freq AS order_frequency
FROM Inventory;
Q4
SELECT emp_role_description
FROM EmployeeRole;

Gabrielle_Johnson · February 20, 2025, 7:30pm

Time to Complete: 10 minutes
Difficulty: Easy
Solution: See Below
1:
SELECT *
FROM Employee;
2:
SELECT vendor_id, company_name, phone_number
FROM Vendor;
3:
SELECT inventory_name, std_expenditure_freq AS order_frequency
FROM Inventory;
4:
SELECT emp_role_description
FROM EmployeeRole;

Porter_Rich · February 20, 2025, 8:49pm

Time to Complete: 10 mins
Rating: Beginner
Solutions:

SELECT *
FROM Employee;
SELECT vendor_id, company_name, phone_number
FROM Vendor;
SELECT inventory_name, std_expenditure_freq
FROM Inventory;
SELECT emp_role_description
FROM EmployeeRole;

Meadow_Mitchell · February 20, 2025, 9:25pm

CHALLENGE 176
Time to complete: 7 minutes
Difficulty: Beginner
Notes: Enjoyed the challenge!

SELECT *
FROM Employee;
SELECT vendor_id, company_name, phone_number
FROM Vendor;
SELECT inventory_name, std_expenditure_freq AS order_frequency
FROM Inventory;
SELECT emp_role_description
FROM EmployeeRole;

ShortyoShire · February 20, 2025, 9:30pm

Time to Complete: 5 mins
Rating: Beginner
Comments: Definitely a good place to start for those new to SQL
Solutions:

SELECT *
FROM Employee;
SELECT vendor_id, company_name, phone_number
FROM Vendor;
SELECT inventory_name, std_expenditure_freq AS order_frequency
FROM Inventory;
SELECT emp_role_description
FROM EmployeeRole;

Madelyn_Boulton · February 20, 2025, 10:16pm

Time to Complete: 10 minutes
Difficulty: Easy
Solution:
1:
SELECT *
FROM Employee;
2:
SELECT vendor_id, company_name, phone_number
FROM Vendor;
3:
SELECT inventory_name, std_expenditure_freq AS order_frequency
FROM Inventory;
4:
SELECT emp_role_description
FROM EmployeeRole;

Kathryn_Thompson_Whi · February 21, 2025, 5:16pm

Time: 8 mins
Difficulty: Easy
Solution:
1.
SELECT *
FROM Employee;
2.
SELECT vendor_id, company_name, phone_number
FROM Vendor;
3.
SELECT Inventory.inventory_name, Inventory.std_expenditure_freq AS order_frequency
FROM Inventory;
4.
SELECT emp_role_description
FROM EmployeeRole;

Aaron_Greenwood · February 22, 2025, 4:21am

Time to complete: 5-10 minutes
Difficulty: Easy
Solution:

SELECT *
FROM Employee;

SELECT vendor_id, company_name, phone_number
FROM Vendor;

SELECT inventory_name, std_expenditure_freq AS order_frequency
FROM Inventory;

SELECT emp_role_description
FROM EmployeeRole;

Miles_Maynes · February 22, 2025, 7:08am

Time to complete: 5 minutes
Rating:Beginner
Solution:

SELECT *
FROM [Employee]
SELECT vendor_id, company_name, phone_number
FROM [Vendor]
SELECT inventory_name, std_expenditure_freq
FROM [Inventory]
4.SELECT emp_role_description
FROM[EmployeeRole]

Emily_Cook · February 22, 2025, 4:16pm

Time to Complete: 5-10 Minutes
Difficulty: Easy
Solution:

SELECT *
FROM Employee
SELECT vendor_id, company_name, phone_number
FROM Vender;
SELECT inventory_name, std_expenditure_freq AS order_frequency
FROM Inventory;
SELECT emp_role_description
FROM EmployeeRole

Joshua_Anderson · February 22, 2025, 4:39pm

Time to Complete: 10 Minutes
Difficulty: Easy

Q1)
SELECT *
FROM Employee

Q2)
SELECT vendor_id, company_name, phone_number
FROM Vendor

Q3)
SELECT inventory_name, std_expenditure_freq AS order_frequency
FROM Inventory

Q4)
SELECT emp_role_description
FROM EmployeeRole

Nolan_Brewer · February 22, 2025, 4:42pm

Time to Complete: 10 minutes
Difficulty: Easy
Solution: See Below
Q1
SELECT *
FROM Employee;
Q2
SELECT vendor_id, company_name, phone_number
FROM Vendor;
Q3
SELECT inventory_name, std_expenditure_freq AS order_frequency
FROM Inventory;
Q4
SELECT emp_role_description
FROM EmployeeRole;

Lucas_Argente · February 22, 2025, 6:48pm

Time to complete: 10 Minutes
Difficulty: Easy
Solution:
Q1:
SELECT *
FROM Employee;
Q2:
SELECT vendor_id, company_name, phone_number
FROM Vendor;
Q3:
SELECT inventory_name, std_expenditure_freq AS order_frequency
FROM Inventory;
Q4:
SELECT emp_role_description
FROM EmployeeRole;

Cooper_Wise · February 22, 2025, 10:51pm

Time to complete: 10 Minutes
Difficulty: Easy
Solution:
1.
SELECT *
FROM Employee
2.
SELECT v.vendor_id, v.company_name, v.phone_number
FROM vendor as v
3.
SELECT i.inventory_name, i.std_expenditure_freq AS order_frequency
FROM inventory AS i
4.
SELECT emp_role_description
FROM EmployeeRole

McKenna_Hall · February 22, 2025, 11:31pm

Time to complete: 7 minutes
Rating: Easy
Solution:

SELECT *
FROM Employee;
SELECT v.vendor_id, v.company_name, v.phone_number
FROM Vendor as v;
SELECT i.inventory_name, i.std_expenditure_freq as order_frequency
FROM Inventory as i;
SELECT er.emp_role_description
FROM EmployeeRole as er;

Cragun_Bastian · February 23, 2025, 2:40am

Time to Complete: 15 min
Rating: Easy

SELECT *
FROM Employee;
SELECT vendor_id, company_name, phone_number
FROM Vendor;
SELECT inventory_name, std_expenditure_freq AS order_frequency
FROM Inventory;
SELECT emp_role_description
FROM EmployeeRole;

Meilan_Rhoton · February 23, 2025, 3:23am

Time to complete: 6 minutes
Rating: Easy
Solution:

SELECT *
FROM Employee;
SELECT v.vendor_id, v.company_name, v.phone_number
FROM Vendor as v;
SELECT i.inventory_name, i.std_expenditure_freq as order_frequency
FROM Inventory as i;
SELECT emp_role_description
FROM EmployeeRole;

Amanda_Garton · February 23, 2025, 3:26am

SELECT *
FROM Employee;
SELECT vendor_id, company_name, phone_number
FROM Vendor;
SELECT inventory_name, std_expenditure AS order_frequency
FROM Inventory;
SELECT emp_role_description
FROM EmployeeRole;

Shane_Hawkins · February 23, 2025, 3:51am

Time to Complete: 10 minutes
Difficulty: Easy
Solution: See Below

1: SELECT *
from employee;
2: SELECT vendor_id, company_name, phone_number
from Vendor;
3: SELECT inventory_name, std_expenditure_freq as Order_Frequency
from Inventory;
4: SELECT emp_role_description
from EmployeeRole

Topic		Replies	Views
181\|SQL – Precision Plane Aggregation Adventure Challenges managerial , sql , intro , planes , having	12	159	March 13, 2025
177\|SQL – Intro to Join Statements Challenges managerial , sql , intro , learningpath	323	1108	February 27, 2025
178\|SQL – Unions with Precision Challenges sql , union , intro	319	989	February 27, 2025
180\|SQL – Intro to Filtering and Ordering Data Challenges filter , sql , intro , order	320	1050	February 27, 2025
179\|SQL – Group By for Greatness Challenges sql , intro , group_by	316	1026	February 27, 2025

176|SQL – Precision Plane Data Presentation

Related topics