StudyDaddy Computer Science I have attached question file, starter file for sql. plus i have answer for more than 85%, i just need help on some questions only, and then you have to run the file in sql plus and give me the output

I have attached question file, starter file for sql. plus i have answer for more than 85%, i just need help on some questions only, and then you have to run the file in sql plus and give me the output

spool 'C:\Users\guruj\Desktop\DatabaseProject3\Project3_axu.txt' set echo on --Avani Upadhyaya --INSY 3304-001 --Project 3 --Part I --#1 and #2 SET LINESIZE 150 COLUMN CustID FORMAT a6 COLUMN SalesRepFName FORMAT a15 COLUMN SalesRepLName FORMAT a15 COLUMN CommClass FORMAT a10 -- #3 -- #4 INSERT INTO CUSTOMER_axu VALUES ('T104', 'Wes', 'Thomas' , '4695551215' , 22); -- #5 INSERT INTO PRODUCT_axu VALUES (246, 'Milwaukee Power Drill' , 2, 179.00); -- #6 INSERT INTO ORDER_axu VALUES ((SELECT (MAX(OrderID)+1) FROM ORDER_axu), '28-JAN-2022' , 'T104'); INSERT INTO ORDERDETAIL_axu VALUES ((SELECT (MAX(OrderID)) FROM ORDER_axu), 618, 1, (SELECT ProdPrice FROM PRODUCT_axu WHERE ProdID = 618)); INSERT INTO ORDERDETAIL_axu VALUES ((SELECT (MAX(OrderID)) FROM ORDER_axu), 407, 2, (SELECT ProdPrice FROM PRODUCT_axu WHERE ProdID = 407)); INSERT INTO ORDERDETAIL_axu VALUES ((SELECT (MAX(OrderID)) FROM ORDER_axu), 124, 1, (SELECT ProdPrice FROM PRODUCT_axu WHERE ProdID = 124)); --#7.

INSERT INTO ORDER_axu VALUES ((SELECT (MAX(OrderID)+1) FROM ORDER_axu), '29-JAN-2022' , 'S100'); INSERT INTO ORDERDETAIL_axu VALUES ((SELECT (MAX(OrderID)) FROM ORDER_axu), 535, 3, (SELECT ProdPrice FROM PRODUCT_axu WHERE ProdID = 535)); INSERT INTO ORDERDETAIL_axu VALUES ((SELECT (MAX(OrderID)) FROM ORDER_axu), 246, 1, (SELECT ProdPrice FROM PRODUCT_axu WHERE ProdID = 246)); INSERT INTO ORDERDETAIL_axu VALUES ((SELECT (MAX(OrderID)) FROM ORDER_axu), 610, 2, (SELECT ProdPrice FROM PRODUCT_axu WHERE ProdID = 610)); --#8 UPDATE CUSTOMER_axu SET CustPhone = '8175558918' WHERE CustID = 'B200'; --#9 COMMIT; ------------------------------------------------------------------------------------------------------------------- --Part II -- #1 SELECT SALESREP_axu.SalesRepFName || ' ' || SALESREP_axu.SalesRepLName AS "SaleRep Name" , SALESREP_axu.SalesRepID AS "Sales Rep ID" , COMMISSION_axu.CommClass AS "Commission Class" , COMMISSION_axu.CommRate AS "Commission Rate" FROM SALESREP_axu, COMMISSION_axu WHERE SALESREP_axu.CommClass = COMMISSION_axu.CommClass ORDER BY SalesRepLName; --#2 SELECT OrderID AS "Order ID" , ProdID AS "Product ID" , ProdQty AS "Qty" , TO_CHAR(ProdPrice, '$99,999.99') AS "Price" FROM ORDERDETAIL_axu; --#3 SELECT CUSTOMER_axu.CustID AS "Customer ID", CUSTOMER_axu.CustFName AS "CustFirstName", CUSTOMER_axu.CustLName AS "CustLastName", '('|| SUBSTR (CUSTOMER_axu.CustPhone,1,3) ||')' || SUBSTR (CUSTOMER_axu.CustPhone,4,3) ||'-'|| SUBSTR (CUSTOMER_axu.CustPhone,7) AS "CustPhone", SALESREP_axu.SalesRepID AS "SaleRepID", SALESREP_axu.SalesRepFName AS "SalesRepFirstName", SALESREP_axu.SalesRepLName AS "SalesRepLastName" FROM CUSTOMER_axu, SALESREP_axu WHERE CUSTOMER_axu.SalesRepID = SALESREP_axu.SalesRepID; --#4 --#4.1 SELECT DeptID AS "Dept_ID" , DeptName AS "Dept_Name" , SalesRepID AS "Sales_Rep_ID" , SalesRepFName AS "First_Name" , SalesRepLName AS "Last_Name" , SR.CommClass AS "Commission_Class", C.CommRate AS "Commission_Rate" FROM DEPARTMENT_axu DE, SALESREP_axu SR, COMMISSION_axu C WHERE C.CommClass = SR.CommClass AND (DE.DeptID, CommRate) IN (SELECT DeptID, MAX(CommRate) FROM SALESREP_axu, COMMISSION_axu GROUP BY DeptID) ; --#5 SELECT P.ProdID AS "Product_ID", P.ProdName AS "Product_Name", PC.ProdCatName AS "Category", TO_CHAR (P.ProdPrice, '$9999.99') AS "Price" FROM ORDERDETAIL_axu OD, PRODUCT_axu P, PRODCAT_axu PC, ORDER_axu O WHERE OD.ProdID = P.ProdID AND P.ProdCatID = PC.ProdCatID AND O.OrderID = 100 AND RowNum <=1 ORDER BY OD.ProdPrice DESC; --#6 SELECT D.DeptName AS "DeptName", COUNT(S.SalesRepID) AS "Sales_Rep_Count" FROM DEPARTMENT_axu D, SALESREP_axu S WHERE S.DeptID = D.DeptID GROUP BY DeptName ORDER BY D.DeptName; --#7 --#8 --#9 --#10 SELECT D.DeptID AS "DeptID", DeptName AS "DeptName" , COUNT (S.SalesRepID) AS "SalesRepCount", CONCAT(TO_CHAR(AVG(C.CommRate)*100,'fm00D00'),'%') AS "AvgCommRate" FROM DEPARTMENT_axu D, SALESREP_axu S, COMMISSION_axu C WHERE D.DeptID = S.DeptID AND S.CommClass = C.CommClass GROUP BY D.DeptID , DeptName ORDER BY "AvgCommRate"; --#11 SELECT S.SalesRepID AS "Sales Rep ID", S.SalesRepFName AS "First Name", S.SalesRepLName AS "Last Name", D.DeptName AS "Department Name", C.CommClass AS "Commission Class", C.CommRate AS "Commission Rate" FROM SALESREP_axu S, DEPARTMENT_axu D, COMMISSION_axu C WHERE S.DeptID = D.DeptID AND C.CommClass = S.CommClass AND C.CommClass = 'A' ORDER BY S.SalesRepID; --#12 SELECT S.SalesRepID AS "Slaes_Rep_ID", S.SalesRepFName ||''|| S.SalesRepLName AS "SalesRep_Name", D.DeptID AS "Department ID", D.DeptName AS "Department Name" FROM SALESREP_axu S, DEPARTMENT_axu D, COMMISSION_axu C WHERE S.DeptID = D.DeptID AND C.CommClass = S.CommClass AND C.CommClass = 'A' ORDER BY S.SalesRepID; --#13 SELECT O.OrderID AS "Order_ID", TO_CHAR(SUM(OD.ProdPrice), '$9999.99') AS "Order_Total" FROM ORDER_axu O, ORDERDETAIL_axu OD WHERE O.OrderID = OD.OrderID AND O.OrderID = 104 GROUP BY OD.OrderID; --#14 SELECT TO_CHAR(Avg(OD.ProdPrice), '$9999.999') AS "Avg_Price" FROM ORDERDETAIL_axu OD; --#15 --#15.

SELECT P.ProdID, P.ProdName, 0.ProdPrice FROM ORDERDETAIL_axu 0, PRODUCT_axu P WHERE P.ProdID = 0.ProdID AND P.ProdPrice = 0.ProdPrice HAVING COUNT (OrderID)= SELECT ProdPrice, COUNT(OrderID) FROM ORDERDETAIL_axu GROUP BY --#16 SELECT PC.ProdCatID AS "Cat_ID", P.ProdID AS "Prod_ID", P.ProdName AS "Prod_Name", TO_CHAR(MIN(P.ProdPrice), '$9999.99') AS "Price" FROM PRODCAT_axu PC, PRODUCT_axu P WHERE PC.ProdCatID = P.ProdCatID GROUP BY PC.ProdCatID, P.ProdID, P.ProdName; --#17 SELECT P.ProdID AS "Product_ID", P.ProdName AS "Product Name", PC.ProdCatName AS "Category Name", TO_CHAR(P.ProdPrice, '$9999.99') AS "Price" FROM PRODUCT_axu P, PRODCAT_axu PC WHERE P.ProdCatID = PC.ProdCatID AND P.ProdPrice > (SELECT AVG(ProdPrice) FROM PRODUCT_axu); --#18 SELECT O.OrderID AS "Order ID", TO_CHAR(O.OrderDate, 'mm-dd-yyyy') AS "Order Date", C.CustFName ||''|| C.CustLName AS "Name", C.CustPhone AS "Phone" FROM CUSTOMER_axu C, ORDER_axu O WHERE C.CustID = O.CustID AND TO_CHAR(O.OrderDate) <='26-JAN-22' ORDER BY OrderDate; --#19 SELECT CustID AS "CustID", CustFName AS "FirstName", CustLName AS "LastName" FROM CUSTOMER_axu WHERE CustFName LIKE 'A%' ORDER BY CustLName; --#20 SELECT CustID AS "Customer ID", C.CustFName ||''|| C.CustLName AS "Name", '('|| SUBSTR(C.CustPhone, 1,3) ||')' ||'-'|| SUBSTR(C.CustPhone, 4,3) || '-' || SUBSTR(C.CustPhone,7) AS "Phone" FROM CUSTOMER_axu C, SALESREP_axu S WHERE S.SalesRepID = C.SalesRepID AND C.SalesRepID = 12; spool off set echo Off

Have a similar question?

Continue to edit or attach image(s).

  • Fast and convenient

    Fast and convenient

    Simply post your question and get it answered by professional tutor within 30 minutes. It's as simple as that!

  • Any topic, any difficulty

    Any topic, any difficulty

    We've got thousands of tutors in different areas of study who are willing to help you with any kind of academic assignment, be it a math homework or an article.

  • 100% Satisfied Students

    100% Satisfied Students

    Join 3,4 million+ members who are already getting homework help from StudyDaddy!