George Mason SQL Questions
INFO 364 Assignment 3 Deadline: 11:59 PM, EDT, Wednesday, April 26th, 2024 Submission Rules: 1. Submit a .txt file that includes your SQL queries to the Canvas folder for Assignment 3. 2. Do not include any data in your submission, only SQL commands should appear in the .txt file. 3. Use the following structure to organize your answer: a. Your SQL query should be in the immediate next row after the question number. b. There should be one empty row between the last query and the following question number row. — Q1 SELECT … FROM — Q2 SELECT … FROM 4. Name your file as: “INFO_364_HW3_FirstName_MiddleName (if applicable) _LastName.txt”. 5. Submit one single .txt file only. Do not split your submission into multiple files. 6. Submit before the deadline. No extension will be granted. No late submission will be accepted. Questions: Develop a SQL query for each of the following questions using skills and tools you have learned in class. You should utilize the data on the Oracle server to develop and test your queries (5 points each, 100 points in total. Listed after each bullet point are the columns that your SQL queries should return. Do not include unnecessary columns.) 1. Count the number of customers in each state. • Number of Customers, Customer State 1 2. Count the number of orders received by year. • Number of Orders, Year 3. Find desks (products with “Desk” or “desk” in their product description) priced over $500. • Product ID, Product Description, Product Standard Price 4. Count the number of orders each salesperson has helped with. Include salespersons who have not assisted with any order. • Salesperson ID, Salesperson Name, Number of Orders Helped with 5. For each product, calculate the total number of orders it appears in, and the total quantity ordered. Include products that do not appear in any order. • Product ID, Product Description, Number of Orders, Total Quantity Ordered 6. Determine the number of orders in 2018 by month. • Month of 2018, Number of Orders 7. For each product line, list the quantity of products ordered. • Product Line ID, Product Line Name, Quantity Ordered 8. Identify the customer who placed the most orders in 2018. • Customer ID, Customer Name, Orders Placed in 2018 9. Calculate the average standard price of products by finish. • Product Finish, Average Standard Price 10. Calculate the total sales value generated by customers in each state. • Customer State, Total Sales Value • Note: Total Sales Value is calculated by summing up the order values. 11. Define orders containing 0-5 products (in terms of ordered quantity, not the number of unique products) as small, 6-10 products as medium, and more than 10 products as large. Count the number of orders in each category. 2 • Order Category, Order Quantity 12. Calculate the value, or total price of each order. • Order ID, Order Value 13. Identify the order with the highest value. • Order ID, Order Value 14. For each chair-desk combo (one chair plus one desk), determine its total price. • Chair Product Description, Desk Product Description, Total Price • Note: Total Price is the sum of the chair’s price and the desk’s price. 15. Calculate the total value of orders handled by each salesperson. Include the salespersons who have not helped with any order. • Salesperson ID, Salesperson Name, Total Order Value 16. For order number 4, list the customer’s name, address, city, state, ship address, ship city, ship state, ship zip code, product ID, description, standard price, quantity ordered, total price (standard price * quantity), salesperson ID, and salesperson name. • Customer Name, Customer Address, Customer City, Customer State, Ship Address, Ship City, Ship State, Ship Zip Code, Product ID, Product Description, Product Standard Price, Product Ordered Quantity, Product Total Price, Salesperson ID, Salesperson Name. 17. Some customers have a ship state (in the CUSTOMERSHIPADDRESS table). For these customers, calculate the number and total value of orders shipped or to be shipped by state. • Ship State, Number of Orders, Value of Orders 18. Identify the most popular product (most ordered by quantity) for customers in each state. 3 • Customer State, Most Popular Product ID, Most Popular Product Description, Product Ordered Quantity 19. Determine the most popular product finish (finish of the most ordered products) for customers in each state. • Customer State, Most Popular Product Finish, Product Ordered Quantity 20. If all desk products (products with “Desk” or “desk” in their description) receive a 10% discount, calculate the new value of each order. • Order ID, New Order Value 4
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
