1) Use joining technique (WHERE commands is preferred[1]) to write a SQL query t

Question

1) Use joining technique (WHERE commands is preferred[1]) to write a SQL query to display customer ID, customer name, and customer address for all the customers who are doing business with PVFC in the territory of Southwest. 2) Use subquery technique to write a SQL query to display customer ID, customer name, and customer address for all the customers who are doing business with PVFC in the territory of Southwest. 3) Use joining technique (WHERE command is preferred[1]) to write a SQL query to display order ID and order ID and order date for all the orders made by customers in the territory of Southwest. 4) Use subquery technique to write a SQL query to display order ID and order date for all orders made by customers in the territory of Southwest

Explanation / Answer

Please rate it withe 5 star ratings..

Schema i Followed is :

CREATE TABLE CUSTOMER_t(

Customer_Id NUMBER NOT NULL ,

Customer_Name VARCHAR(25) ,

Customer_Address VARCHAR(30) ,

Customer_City VARCHAR(20) ,

Customer_State VARCHAR(2) ,

Postal_Code VARCHAR(9) ,

CONSTRAINT CUSTOMER_PK PRIMARY KEY (Customer_Id) )

CREATE TABLE TERRITORY_t(

Territory_Id NUMBER NOT NULL ,

Territory_Name VARCHAR(50) ,

CONSTRAINT TERRITORY_PK PRIMARY KEY (Territory_Id) )

CREATE TABLE DOES_BUSINESS_IN_t(

Customer_Id NUMBER NOT NULL ,

Territory_Id NUMBER NOT NULL ,

CONSTRAINT DOES_BUSINESS_IN_PK PRIMARY KEY (Customer_Id, Territory_Id) ,

CONSTRAINT DOES_BUSINESS_IN_FK1 FOREIGN KEY (Customer_Id) REFERENCES CUSTOMER_t(Customer_Id ) ,

CONSTRAINT DOES_BUSINESS_IN_FK2 FOREIGN KEY (Territory_Id) REFERENCES TERRITORY_t(Territory_Id ) )

CREATE TABLE ORDER_t(

Order_Id INTEGER NOT NULL ,

Customer_Id NUMBER ,

Order_Date DATE ,

CONSTRAINT ORDER_PK PRIMARY KEY (Order_Id) ,

CONSTRAINT ORDER_FK1 FOREIGN KEY (Customer_Id) REFERENCES CUSTOMER_t(Customer_Id ) )

-- 1) Using Joins

Select C.Customer_Id,C.Customer_Name,C.Customer_Address From CUSTOMER_t C,TERRITORY_t T,DOES_BUSINESS_IN_t DB where C.Customer_Id=DB.Customer_Id and T.Territory_Id=DB.Territory_Id and T.Territory_Name='Southwest'

-- 2) Using Sub-Querys

Select C.Customer_Id,C.Customer_Name,C.Customer_Address From CUSTOMER_t C where C.Customer_Id in ( Select DB.Customer_Id from DOES_BUSINESS_IN_t DB where DB.Territory_Id in ( Select T.Territory_Id from TERRITORY_t T where T.Territory_Name='Southwest'))

-- 3) Using Joins

Select O.Order_Id, O.Order_Date From ORDER_t O, CUSTOMER_t C,TERRITORY_t T,DOES_BUSINESS_IN_t DB where O.Customer_Id=C.Customer_Id and C.Customer_Id=DB.Customer_Id and T.Territory_Id=DB.Territory_Id and T.Territory_Name='Southwest'

-- 4) Using Sub-Querys

Select O.Order_Id, O.Order_Date From ORDER_t O where O.Customer_Id in ( Select C.Customer_Id From CUSTOMER_t C where C.Customer_Id in ( Select DB.Customer_Id from DOES_BUSINESS_IN_t DB where DB.Territory_Id in ( Select T.Territory_Id from TERRITORY_t T where T.Territory_Name='Southwest')))

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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