Question

More on JOINS

1. Log in to your ApEX SQL account.

Two Table Join

2. Create a SQL query that gets the order ID, order_mode, customer ID and product ID from the ORDERS and ORDER_ITEMS tables. You will have to join the tables on some common column.

Two Table Join With Subquery

3. Modify the previous query and add a subquery that looks for the existence of a customer ID in the CUSTOMERS table. This is a correlated sub-query so you will have to match the customer ID from the sub-query to the customer ID in the outer query.

Three Table Join with Subquery

4. Modify the previous SQL query the add the translated name column from the PRODUCT_DESCRIPTIONS table.

Answer 1

If you have any doubts, please give me comment...

-- 1)

SELECT O.order_id, order_mode, customer_id, product_id

FROM ORDERS AS O INNER JOIN ORDER_ITEMS AS OI ON O.order_id = OI.order_id;

-- 2)

SELECT O.order_id, order_mode, customer_id, product_id

FROM ORDERS AS O INNER JOIN ORDER_ITEMS AS OI ON O.order_id = OI.order_id

WHERE EXISTS(

    SELECT *

    FROM CUSTOMERS C

    WHERE O.customer_id = C.customer_id);

-- 3)

SELECT O.order_id, order_mode, customer_id, P.product_id, description

FROM (ORDERS AS O INNER JOIN ORDER_ITEMS AS OI ON O.order_id = OI.order_id) INNER JOIN PRODUCT_DESCRIPTIONS P ON OI.product_id = P.product_id

WHERE EXISTS(

    SELECT *

    FROM CUSTOMERS C

    WHERE O.customer_id = C.customer_id);