+1 (315) 557-6473 

Sql Program to Solve Business Queries Assignment Solution.


GET FREE QUOTE
  1. Home
  2. Program To Solve Business Queries Assignment Solution.

Instructions

Objective
Write a SQL assignment to solve business queries.

Requirements and Specifications

INFO 364-901
Fall 2021
CE-2
Due Date: Nov 21, 11:59 PM
Using the PVF Script file that was uploaded during last class, work on SQL queries below:
At the minimum, your submission must include:
  •  Individual screenshot must be provided to show execution of each query
  •  Each screenshot must clearly show the name of your respective schema, SQL code, and output.
  •  Refer to Appendix 2 for example of the screenshot requirement.
The ERD and sample records for the tables are provided within Appendix 1:
Write SQL query and provide the corresponding outputs:
Query LabelBusiness Query  Results
Q1Display information for all customers.
Q2

Display all information for those product(s) with a standard price of less than $200.


Q3

What is the address of the customer named Home Furnishing? Use Alias,Name for customer name.


Q4Q4 Display the average standard price of products.
Q5Display the price of most expensive product in inventory
Q6Display the count of all orders .
You will provide 2 separate queries.
(Hint – Refer individual table S_order_t & s_orderline_t for the respective query)

Q7How many different kinds of items were ordered on order number 1006?
Q8How many different kinds of items were ordered on order number 1006?
Q9Alphabetically, what is the last product name in the PRODUCT table? Use an alias NAME.
APPENDIX 1: ERD & SNAPSHOT OF SAMPLE DATA

ERD
   

   

   
   		    
   
   
                                                                                                                         Table   Name & Snapshot of Sample Data   
S_customer_t
   

   
   		    
   
S_order_t     







    
   
 S_product_t   







    
   
 S_orderline_t   








    
   

APPENDIX 2: SCREENSHOT EXAMPLE

Source Code

-- QUESTION 1

SELECT * FROM S_CUSTOMER_T;

-- QUESTION 2

SELECT * FROM S_PRODUCT_T WHERE STANDARD_PRICE <200;

-- QUESTION 3

SELECT CUSTOMER_ADDRESS FROM S_CUSTOMER_T WHERE CUSTOMER_NAME = 'Home Furnishings';

-- QUESTION 4

SELECT AVG(STANDARD_PRICE) AS "Average Standard Price" FROM S_PRODUCT_T;

-- QUESTION 5

SELECT STANDARD_PRICE FROM S_PRODUCT_T ORDER BY STANDARD_PRICE DESC FETCH FIRST 1 ROWS ONLY;

-- QUESTION 6

SELECT S_ORDER_T.ORDER_ID, COUNT(*) FROM S_ORDER_T INNER JOIN S_ORDER_LINE_T ON S_ORDER_T.ORDER_ID = S_ORDER_LINE_T.ORDER_ID GROUP BY S_ORDER_T.ORDER_ID;

-- QUESTION 7

SELECT COUNT(*) AS "Number of Items with ID 1006" FROM S_ORDER_LINE_T WHERE ORDER_ID = 1006;

-- QUESTION 8

SELECT * FROM S_ORDER_T WHERE ORDER_DATE < TO_DATE('10/24/2007', 'MM/dd/yyyy');

-- QUESTION 9

SELECT PRODUCT_DESCRIPTION AS "NAME" FROM S_PRODUCT_T ORDER BY PRODUCT_DESCRIPTION DESC FETCH FIRST 1 ROWS ONLY;