Click the Exhibit button and examine the data from the ORDERS and CUSTOMERS tables.
Which SQL statement retrieves the order ID, customer ID, and order total for the orders that are
placed on the same day that Martin placed his orders?
A.
SELECT ord_id, cust_id, ord_total
FROM orders, customers
WHERE cust_name=’Martin’
AND ord_date IN (’18-JUL-2000′,’21-JUL-2000′);
B.
SELECT ord_id, cust_id, ord_total
FROM orders
WHERE ord_date IN (SELECT ord_date
FROM orders
WHERE cust_id = (SELECT cust_id
FROM customers
WHERE cust_name =
‘Martin’));
C.
SELECT ord_id, cust_id, ord_total
FROM orders
WHERE cust_id IN (SELECT cust_id
FROM customers
WHERE cust_name = ‘Martin’);
D.
SELECT ord_id, cust_id, ord_total
FROM orders
WHERE ord_date IN (SELECT ord_date
FROM orders, customers
WHERE cust_name = ‘Martin’);
Explanation:
This query will return the order ID, customer ID, and order total for the order s that are placed on
the same day that Martin places his order s.
Incorrect Answers:
A: This query returns only Martin’s orders for July 18, 2000 and July 21, 2002, not orders of others
that were placed on the same day that Martin placed his orders.
C: This query uses incorrect sub-query to extract dates when Martin placed his orders.
D: This query will return only Martin’s orders.OCP Introduction to Oracle 9i: SQL Exam Guide,
Jason Couchman, p. 145-156Chapter 4: Subqueries