Question

Write the correct code in SQL

1. What is the name and address of the customer that placed order 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.

3. List the product id, description, and finish of the least expensive 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 least orders.

5. What are the cheapest oak-finished products?

6. Which products in product lines 2 or 4 are more expensive than the most expensive birch-finished product? List the results in order by product id.

7. Assume there is only one customer named Contemporary Casuals. What products has Contemporary Casuals ordered? Include the product id and product description in your answer and order your answer by product id.

8. Based on customer id (and no other information about a customer), how many of the product 48” Bookcase (in any finish) did each customer order? Only include customers in your answer who ordered at least 4 such items.

9. How many raw materials are used in each product? Order your answers by product id. (Note: Based on the limited data in the database, you should only find six products included in your answer.)

10. How many raw materials are used in each product? Only include materials supplied by vendor 2. Order your answers by product id. (Hint: You will need the supplies table but not the vendor table to answer this question.)

Tables and Fields in Database db_pvfc12_big

(Note: The use of fields in Teradata SQL is not case sensitive.)

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 1

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 c.Customername,c.Customeraddress
From CUSTOMER_T c
INNER JOIN ORDER_T o
    ON ( c.Customerid = o.Customerid )
WHERE o.Orderid = 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 r.Materialname
From PRODUCT_T p
INNER JOIN USES_T u
    ON (p.Productid = u.Productid)
INNER JOIN RAWMATERIAL_T r
    ON (u.Materialid = r.Materialid)
WHERE p.Productdescription = 'Cherry End Table' ;

3. List the product id, description, and finish of the least expensive products. (Note: A couple of rows show a price of 0. Exclude products with a price of 0 from your query.)

Select  Productid,
Productdescription,
Productfinish
From PRODUCT_T
Where  Productstandardprice =
  (Select min(Productstandardprice) from PRODUCT_T
   WHERE PRODUCT_T.Productstandardprice != 0 ) ;

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 least orders.

Select c.Customername, c.Customerid,
count(o.Orderid) numorders
From CUSTOMER_T c
INNER JOIN ORDER_T o
    ON ( c.Customerid = o.Customerid )
WHERE c.customerstate = 'NY'
Group by c.Customername,c.Customerid
ORDER BY numorders DESC ;

5. What are the cheapest oak-finished products?

Select  Productid,
Productdescription,
Productfinish
From PRODUCT_T
Where  Productstandardprice =
  (Select min(Productstandardprice) from PRODUCT_T p
       WHERE  p.Productstandardprice != 0 ) 
and  Productfinish = 'Oak';

6. Which products in product lines 2 or 4 are more expensive than the most expensive birch-finished product? List the results in order by product id.

Select pp.productid, pp.Productdescription from PRODUCT_T pp 
where 
pp.productlineid in (2, 4) and 
pp.Productstandardprice > 
(
  Select max(p.Productstandardprice) maxprice 
  From PRODUCT_T p
  where p.Productfinish = 'birch'
)
order by pp.productid
;

7. Assume there is only one customer named Contemporary Casuals. What products has Contemporary Casuals ordered? Include the product id and product description in your answer and order your answer by product id.

Select p.productid,p.Productdescription
From CUSTOMER_T c
INNER JOIN ORDER_T o 
     on ( c.Customerid = o.Customerid)
INNER JOIN ORDERLINE_T ot 
     on (o.Orderid = ot.Orderid)
INNER JOIN PRODUCT_T p 
    on (ot.productid = p.productid)
where c.Customername = 'Contemporary Casuals' 
order by productid ;

8. Based on customer id (and no other information about a customer), how many of the product 48” Bookcase (in any finish) did each customer order? Only include customers in your answer who ordered at least 4 such items.

Select c.Customerid, count(p.productid) numproducts
From CUSTOMER_T c
INNER JOIN ORDER_T o 
     on ( c.Customerid = o.Customerid)
INNER JOIN ORDERLINE_T ot 
     on (o.Orderid = ot.Orderid)
INNER JOIN PRODUCT_T p 
    on (ot.productid = p.productid)
where p.Productdescription = '48” Bookcase' 
Group by c.Customerid
Having  count(p.productid) >= 4;

9. How many raw materials are used in each product? Order your answers by product id. (Note: Based on the limited data in the database, you should only find six products included in your answer.)

Select p.productid,p.Productdescription,
count(r.Materialid) nummaterialsused
from PRODUCT_T p
   INNER JOIN USES_T u
    ON (p.Productid = u.Productid)
   INNER JOIN RAWMATERIAL_T r
    ON (u.Materialid = r.Materialid)
Group by p.productid,p.Productdescription
Order by p.productid;

10. How many raw materials are used in each product? Only include materials supplied by vendor 2. Order your answers by product id. (Hint: You will need the supplies table but not the vendor table to answer this question.)

Select p.productid,p.Productdescription,
count(r.Materialid) nummaterialsused
from PRODUCT_T p
   INNER JOIN USES_T u
    ON (p.Productid = u.Productid)
   INNER JOIN RAWMATERIAL_T r
    ON (u.Materialid = r.Materialid)
   INNER JOIN SUPPLIES_T s 
    ON (r.Materialid = s.Materialid)
where s.Vendorid = 2
Group by p.productid,p.Productdescription
Order by p.productid;