For SQL (Database Management Systems): The tables are CUSTOMERS -CUSTOMER_ID -CU

Question

For SQL (Database Management Systems):

The tables are

CUSTOMERS

-CUSTOMER_ID

-CUSTOMER_FIRST_NAME

-CUSTOMER_LAST_NAME

-CUSTOMER_CITY

ORDER_DETAILS

-ORDER_ID

-ORDER_QTY

ORDERS

-ORDER_ID

-CUSTOMER_ID

A. Find the name and phone number of each vendor who has a cooperating vendor. Hint: the vendors for whom the field Cooperating_Vendor_id is not empty

B. Now find additionally the name and phone number of the cooperating vendor. Present these two columns as: ‘cooperating vendor’ and ‘cooperating vendor phone’.

C. Modify the above query to report all vendors. If they are have a cooperating vendor, report their name if not report ‘N/A’.

Explanation / Answer

A.

Select VendorName,VendorPhone from VENDORS where Cooperating_Vendor_id IS NOT NULL;

B.

Select V1.VendorName,V1.VendorPhone, V2.VendorName as CooperatingVendor,V2.VendorPhone as CooperatingVendorPhone from VENDORS V1 inner join Vendors V2 on V1.Cooperating_Vendor_id = V2.Vendor_id;

this is a type of self join where two instances of the same table VENDOR are joined

C.

Select V1.VendorName,V1.VendorPhone, NVL(V2.VendorName,'N/A') as CooperatingVendor,NVL(V2.VendorPhone,'N/A') as CooperatingVendorPhone from VENDORS V1 right inner join Vendors V2 on V1.Cooperating_Vendor_id = V2.Vendor_id;

NVL() function is used to display 'N/A' if the value of the column is null.

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

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