176|SQL – Precision Plane Data Presentation

Challenges
, , ,
1

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.

  1. 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
  2. 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.
  3. 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
  4. 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.

Challenge176_Image
Challenge176_Image1189×538 19.3 KB

Data Files

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

2

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;