View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
There is only one customer with the cust_last_name column having value Roberts.
Which INSERT statement should be used to add a row into the ORDERS table for the customer whose
CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?
A.
INSERT INTO orders
VALUES (1,’10-mar-2007′, ‘direct’,
(SELECT customer_id
FROM customers
WHERE cust_last_name=’Roberts’ AND
credit_limit=600), 1000);
B.
INSERT INTO orders (order_id,order_date,order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name=’Roberts’ AND
credit_limit=600),order_total)
VALUES(1,’10-mar-2007′, ‘direct’, &&customer_id, 1000);
C.
INSERT INTO(SELECT o.order_id, o.order_date,o.order_mode,c.customer_id, o.order_total
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
AND c.cust_last_name=’Roberts’ ANDc.credit_limit=600 )
VALUES (1,’10-mar-2007′, ‘direct’,(SELECT customer_id
FROM customers
WHERE cust_last_name=’Roberts’ AND
credit_limit=600), 1000);
D.
INSERT INTO orders (order_id,order_date,order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name=’Roberts’ AND
credit_limit=600),order_total)
VALUES(1,’10-mar-2007′, ‘direct’, &customer_id, 1000);
In the previous question 143 it says a subquery cannot be used in values clause of insert statement.
How Option A is correct here?
In this Question Subquery written to fetch the only customer Id.
But in the Previous Question Insert Data with Select Statement.
Here, the subquery has to return only one value for customer_ID.
In question 143, the subquery was used to insert a row, so in that case it cannot be used with Values clause
A is correct. The questions tells that only one row will be returned from the customer table for that name and credit_limit
A.
INSERT INTO orders
VALUES (1,’10-mar-2007′, ‘direct’,
(SELECT customer_id
FROM customers
WHERE cust_last_name=’Roberts’ AND
credit_limit=600), 1000);