Write the correct code in SQL 1. What is the name and address of the customer...

70.2K

Verified Solution

Question

Programming

Write the correct code in SQL
1. What is the name and address of the customer that placedorder 57?
2. Assume there is only one product with the description CherryEnd Table. List the names of the raw materials that go into makingthat product.
3. List the product id, description, and finish of the leastexpensive products. (Note: A couple of rows show a price of 0.Exclude products with a price of 0 from your query.)
4. How many orders did each customer from New York state (i.e.NY) place? List the answers in order from the most to the leastorders.
5. What are the cheapest oak-finished products?
6. Which products in product lines 2 or 4 are more expensivethan the most expensive birch-finished product? List the results inorder by product id.
7. Assume there is only one customer named Contemporary Casuals.What products has Contemporary Casuals ordered? Include the productid and product description in your answer and order your answer byproduct id.
8. Based on customer id (and no other information about acustomer), how many of the product 48â€ Bookcase (in anyfinish) did each customer order? Only include customers in youranswer who ordered at least 4 such items.
9. How many raw materials are used in each product? Order youranswers by product id. (Note: Based on the limited data in thedatabase, you should only find six products included in youranswer.)
10. How many raw materials are used in each product? Onlyinclude materials supplied by vendor 2. Order your answers byproduct id. (Hint: You will need the supplies table but not thevendor table to answer this question.)
Tables and Fields in Database db_pvfc12_big
(Note: The use of fields in Teradata SQL is not casesensitive.)
CUSTOMER_T
Â Â Â Â Â Â Â Â Â Â Customerid
Â Â Â Â Â Â Â Â Â Â Customername
Â Â Â Â Â Â Â Â Â Â Customeraddress
Â Â Â Â Â Â Â Â Â Â Customercity
Â Â Â Â Â Â Â Â Â Â Customerstate
Â Â Â Â Â Â Â Â Â Â Customerpostalcode
PRODUCT_T
Â Â Â Â Â Â Â Â Â Â Productid
Â Â Â Â Â Â Â Â Â Â Productdescription
Â Â Â Â Â Â Â Â Â Â Productfinish
Â Â Â Â Â Â Â Â Â Â Productstandardprice
Â Â Â Â Â Â Â Â Â Â Productonhand
Â Â Â Â Â Â Â Â Â Â Productlineid
RAWMATERIAL_T
Â Â Â Â Â Â Â Â Â Â Materialid
Â Â Â Â Â Â Â Â Â Â Materialname
Â Â Â Â Â Â Â Â Â Â Thickness
Â Â Â Â Â Â Â Â Â Â Width
Â Â Â Â Â Â Â Â Â Â Size
Â Â Â Â Â Â Â Â Â Â Material
Â Â Â Â Â Â Â Â Â Â Materialstandardprice
Â Â Â Â Â Â Â Â Â Â Unitofmeasure
Â Â Â Â Â Â Â Â Â Â Materialtype
ORDER_T
Â Â Â Â Â Â Â Â Â Â Orderid
Â Â Â Â Â Â Â Â Â Â Orderdate
Â Â Â Â Â Â Â Â Â Â Customerid
Â Â Â Â Â Â Â Â Â Â Fulfillmentdate
Â Â Â Â Â Â Â Â Â Â Salespersonid
Â Â Â Â Â Â Â Â Â Â Shipadrsid
ORDERLINE_T
Â Â Â Â Â Â Â Â Â Â Orderlineid
Â Â Â Â Â Â Â Â Â Â Orderid
Â Â Â Â Â Â Â Â Â Â Productid
Â Â Â Â Â Â Â Â Â Â Orderedquantity
USES_T
Â Â Â Â Â Â Â Â Â Â Productid
Â Â Â Â Â Â Â Â Â Â Materialid
Â Â Â Â Â Â Â Â Â Â Quantityrequired
SUPPLIES_T
Â Â Â Â Â Â Â Â Â Â Vendorid
Â Â Â Â Â Â Â Â Â Â Materialid
Â Â Â Â Â Â Â Â Â Â Supplyunitprice

Answer & Explanation Solved by verified expert

4.4 Ratings (923 Votes)

Solution The SQL queries have been given below The zero priced items have been excluded for the calculation of least price in all such queries Kindly run and confirm if you require any additional information Hope that helps 1 What is the name and address of the customer that placed order 57 Select cCustomernamecCustomeraddress From CUSTOMERT c INNER JOIN ORDERT o ON cCustomerid oCustomerid WHERE oOrderid 57 2 Assume there is only one product with the description Cherry End Table List the names of the raw materials that go into making that product Select rMaterialname From PRODUCTT p INNER JOIN USEST u ON pProductid uProductid INNER JOIN RAWMATERIALT r ON uMaterialid rMaterialid WHERE pProductdescription Cherry End Table 3 List the product id description and finish of the See Answer

Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:

Unlimited Question Access with detailed Answers
Zin AI - 3 Million Words
10 Dall-E 3 Images
20 Plot Generations
Conversation with Dialogue Memory
No Ads, Ever!
Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!

Become a Member