Database Management and Applications

Homework 2

Write SQL queries for the Pine Valley Furniture Company Database to answer the following questions:

1. Write a SQL query to display order ID and order date for all the orders made by customers in the territory of Southwest (use WHERE command to join tables).

SELECT ORDER_ID, ORDER_DATE

FROM ORDER_T, CUSTOMER_T,Territory_T,Does_Business_In_T

WHERE CUSTOMER_T.CUSTOMER_ID = ORDER_T.CUSTOMER_ID

AND Customer_T.Customer_ID = Does_Business_IN_T.Customer_ID

AND Territory_T.Territory_ID = Does_Business_In_T.Territory_ID

AND TERRITORY_Name = 'SOUTHWEST';

2. Use sub-query technique to write a SQL query to display order ID and order date for all the orders made by customers in the territory of Southwest.

SELECT ORDER_ID, ORDER_DATE

FROM ORDER_T

WHERE CUSTOMER_ID IN (SELECT CUSTOMER_T.CUSTOMER_ID

FROM CUSTOMER_T, TERRITORY_T,DOES_BUSINESS_IN_T

WHERE CUSTOMER_T.CUSTOMER_ID = DOES_BUSINESS_IN_T.CUSTOMER_ID

AND TERRITORY_T.TERRITORY_ID =

DOES_BUSINESS_IN_T.TERRITORY_ID

AND TERRITORY_NAME = 'SOUTHWEST');

3. What are the product ID and product description for the products with a price larger than the price of the product “Computer Desk”?

SELECT PRODUCT_ID

FROM PRODUCT_T

WHERE STANDARD_PRICE > (SELECT STANDARD_PRICE

FROM PRODUCT_T

WHERE PRODUCT_DESCRIPTION = 'COMPUTER DESK' AND PRODUCT_FINISH = 'WALNUT');

4. What are the order IDs of the orders that have included the product with the largest price?

SELECT ORDER_ID

FROM ORDER_LINE_T, PRODUCT_T

WHERE PRODUCT_T.PRODUCT_ID = ORDER_LINE_T.PRODUCT_ID

AND STANDARD_PRICE = (SELECT MAX(STANDARD_PRICE)

FROM PRODUCT_T);

5. Give 10% discount to all the products manufactured by the product line 3.

UPDATE PRODUCT_T

SET PRODUCT_PRICE= 0.9 *(PRODUCT_PRICE)

WHERE PRODUCT_LINE_ID =3;