Creating Queries using SQL
Creating Queries using SQL
SSB would like for you to generate several queries using the SQL “Query Design” feature in Access. The queries are designed to provide an overview of the data stored in the data mart and techniques for answering specific questions about customers, products, and employees.
Query 1 – Employee List
Design a query to list all employees in the organization. The query should display the following information: employee ID, employee first name, employee last name, employee address, employee salary, department name, and region name. After executing the query and verifying the query works correctly, save the query as “Query1”.
Query 2 – Sales Representative List
Design a query to list all sales people in the organization. The query should display the following information for the sales people in the organization: employee ID, sales rep first name, sales rep last name, sales rep address, sales rep salary, sales rep department name, sales rep region name. After executing the query and verifying the query works correctly, save the query as “Query2”.
Query 3 – Customer List
Design a query to list all customers in alphabetical order by customer last name. The query should display the following information: customer ID, customer first name, and customer last name. After executing the query and verifying the query works correctly, save the query as “Query3”.
Query 4 – Sales Orders in July
Design a query to list all sales orders for the month of July sorted in ascending order by purchase order date. The query should display the following information: PO date, order ID, product ID, product description, quantity sold, and unit price. After executing the query and verifying the query works correctly, save the query as “Query4”.
Query 5 – Finding a Sales Order
Construct a query to list all the products purchased by Jason Witten on July 14th, 2012. The query should display the following information: PO Date, order ID, customer ID, customer first name, customer last name, product ID, product description, quantity sold, and unit price. After executing the query and verifying the query works correctly, save the query as “Query5”.
Query 1 - Employee List
Design a query to list all employees in the organization. The query should display the following information: employee ID, employee first name, employee last name, employee address, employee salary, department name, and region name. After executing the query and verifying the query works correctly, save the query as "Query1".
Query 2 - Sales Representative List
Design a query to list all sales people in the organization. The query should display the following information for the sales people in the organization: employee ID, sales rep first name, sales rep last name, sales rep address, sales rep salary, sales rep department name, sales rep region name. After executing the query and verifying the query works correctly, save the query as "Query2".
Query 3 - Customer List
Design a query to list all customers in alphabetical order by customer last name. The query should display the following information: customer ID, customer first name, and customer last name. After executing the query and verifying the query works correctly, save the query as "Query3".
Query 4 - Sales Orders in July
Design a query to list all sales orders for the month of July sorted in ascending order by purchase order date. The query should display the following information: PO date, order ID, product ID, product description, quantity sold, and unit price. After executing the query and verifying the query works correctly, save the query as "Query4".
Query 5 - Finding a Sales Order
Construct a query to list all the products purchased by Jason Witten on July 14th, 2012. The query should display the following information: PO Date, order ID, customer ID, customer first name, customer last name, product ID, product description, quantity sold, and unit price. After executing the query and verifying the query works correctly, save the query as "Query5".