Question

GIVE ME QUERIES: d) List Order IDs and the associated Customer Full Names for orders that are ordered from either 'VIRGINIA' or 'NEW YORK'. I want you to connect three tables with SQL JOINS and use WHERE clause to filter certain customers. (Hint: Customer Table and Order Table are connected by Customer ID and Customer Table and State Table can be connected by CUST_STATE and ST, use those conditions in your JOINs)

e) Find me the Product Names from the Orders of John Dulles. You need to connect multiple tables with SQL joins and use WHERE clause to filter certain customers. I want you to list only Product Names (nothing else). Make sure you don't have duplicate Product Names in your results.

Answer 1

d) select o.order_id, c.fname,c.lname from Customer c, Order o where c.customer_id=o.customer_id and c.CUST_STATE in (select ST from State where ST in ('VIRGINIA','NEW YORK');

In the above query i am aliasing customer table, order table with letters c and o respectively. and i have used sub query to know whether orders come from 'VIRGINIA' or 'NEW YORK' these states or not.

e) select distinct(o.product_Name) from Orders o, Customer c where c.customer_id=o.customer_id and c.fname='John' and c.lname='Dulles';

distinct() is used to list unique product names for customer named 'john Dulles'.