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?

  1. SELECT ord_id, cust_id, ord_total FROM orders, customers WHERE cust_name='Martin' AND ord_date IN ('18-JUL-2000','21-JUL-2000');

  2. 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'));

  3. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders, customers WHERE cust_name = 'Martin');

  4. SELECT ord_ id, cust_id, ord_total FROM orders WHERE cust_id IN (SELECT cust_id FROM customers WHERE cust name = 'Martin');


Correct Option: B
Explanation:

To solve this question, the user needs to know SQL syntax and how to retrieve data from a database table. The user must select the appropriate columns from the orders table where the order date is the same as the order date for Martin's orders.

Option A: This option is incorrect because it joins the orders and customers tables but does not specify a relationship between them. Also, it selects orders on two specific dates (18-JUL-2000 and 21-JUL-2000) which may not be the dates Martin placed his orders.

Option B: This option is correct. It selects the order ID, customer ID, and order total from the orders table where the order date is the same as the order date for Martin's orders. It uses subqueries to find the order date and customer ID for Martin and then selects orders with the same order date.

Option C: This option joins the orders and customers tables but does not specify a relationship between them, so it is incorrect. Also, it selects orders on the same dates as Martin's orders, which may not be the correct dates.

Option D: This option selects orders where the customer ID is the same as Martin's customer ID, but it does not specify a relationship between the orders and customers tables. Therefore, it may not retrieve the correct orders.

The Answer is: 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'));

Find more quizzes: