Make db_pvfc11_big as your default database when you log SQL. Assistant. All SEL

Question

Make db_pvfc11_big as your default database when you log SQL. Assistant. All SELECT statements have to have CURRENT_USER column. Query 1. List the customers who live in Honda or Texas Order then, by zip code. from high to low. Query 2. List the number of customers living at each state. Query 3. List all raw materials that are made of oak and that have dimensions (thickness and width) of 12 by 12. Query 4 For every product that has been ordered, display the product ID and the total quantity ordered (label this result TotalOrdered). List the most popular product first and the least popular last. Query 5. For each salesperson, list the total number of orders. Query 6. Display the territory ID and the number of salespersons in the territory for all territories that have more than one salesperson. Label the number of salespersons NumSalespersons. Query 7. List Product ID. ProductDescription. ProductFinish, and ProductStandandPrice for oak products with a ProductStandardPrice greater than $528 or cherry products with a StandardPrice less than $255. Show how you Query 8: List product name, material name and required quantity for ProductID=18 using tables Product_T, RawMaterials_T and Uses_T Query 9. List the average number of customers per state (including only the states that are included in the Customer T table). Query 10: List product name, material name and required quantity for ProductID=18 using tables Product_T, RawMaterials_T and Uses_T Query 11: List products and number of materials used to manufacture them that is more than 10. Query 12: Display order id, product name and ordered quantity where ordered quantity is more than or equal to 12.

Explanation / Answer

needs the column information for correct output but this will give you a hint for queries

--COMMENTS

--ASSUMING THERE ARE COLUMNS NAMED CUSTOMER, CURRENT_USER, STATE, ZIPCODE, ETC.

--ASSUMING TABLE NAME TO BE TABLE1

--QUERY1

SELECT CUSTOMER, CURRENT_USER, STATE, ZIPCODE FROM TABLE1 WHERE STATE=’FLORIDA’ OR STATE=’TEXAS’ ORDER BY ZIPCODE DESC

--QUERY 2

SELECT COUNT (CUSTOMER), CURRENT_USER FROM TABLE1 GROUP BY STATE

--QUERY3

SELECT RAW_MATERIAL, CURRENT_USER FROM TABLE1 WHERE MADEBY=’OAK’ AND THICKNESS=12 AND WIDTH=12

--QUERY4

SELECT PRODUCTID, COUNT (QUANTITY) AS TOTALORDERED, CURRENT_USER FROM TABLE1 WHERE ORDERSTATUS=ORDERED GROUP BY PRODUCTID ORDER BY POPULARITY DESC

--QUERY 5

SELECT COUNT (ORDER), CURRENT_USER FROM TABLE1 GROUP BY SALESPERSON

--QUERY 6

SELECT TERRITORYID, COUNT (SALESPERSONS) AS NUMSALESPERSON, CURRENT_USER FROM TABLE1 WHERE NUMSALESPERSON >1 GROUP BY TERRITORYID

--QUERY 7

SELECT PRODUCTID, PRODUCTDESCRIPTION, PRODUCTFINISH, PRODUCTSTANDARDPRICE, CURRENT_USER FROM TABLE1 WHERE (PRODUCT=’OAK’ AND PRODUCTSTANDARDPRICE>528) OR (PRODUCT=’CHERRY’ AND PRODUCTSTANDARDPRICE>255)

PLEASE COMMENT IF YOU HAVE ANY QUERY

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.