Home » Other » Training & Certification » Subquery in clauses 'insert' and 'values'
Subquery in clauses 'insert' and 'values' [message #327151] Sat, 14 June 2008 06:09 Go to next message
ruchika19840
Messages: 7
Registered: June 2008
Junior Member
Hi,

Someone can please tell how subquery is working for both Values and Into clause.
According to Sql book it should fail.
but i tried and it worked for both.

suppliers contains 2 column ((supplier_id, supplier_name))
customers contains 3 column (acc_no,name,city)



INSERT INTO suppliers
SELECT account_no, name
FROM customers
WHERE city = 'Newark';

Customer has 1001,sachin,'Newark';


So here I got "1001,sachin" on selecting suppliers

INSERT INTO (select * from suppliers)
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'Newark';
Here as well Answer :1001,sachin

Please explain then please look below also

Que:
In which four clauses can a subquery be used? (Choose four.)
A. in the INTO clause of an INSERT statement
B. in the FROM clause of a SELECT statement
C. in the GROUP BY clause of a SELECT statement
D. in the WHERE clause of a SELECT statement
E. in the SET clause of an UPDATE statement
F. in the VALUES clause of an INSERT statement

Answer: B, D, E, F

Its Should be B,D,E,A,F but one should be wrong as choices asked is 4.

any explanation will be appreciated.
Re: Subquery in clauses 'insert' and 'values' [message #327154 is a reply to message #327151] Sat, 14 June 2008 06:25 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

hey,

thats question number 144 from dumps 1z0-007 ...
keep the answer as B,D,E,F. ..or else u will get wrong for that question..
Re: Subquery in clauses 'insert' and 'values' [message #327155 is a reply to message #327154] Sat, 14 June 2008 06:33 Go to previous messageGo to next message
ruchika19840
Messages: 7
Registered: June 2008
Junior Member
and what about this one.

SELECT *
FROM orders

You use this statement to retrieve data for ______

1) update
2) viewing
3) deleting
4) inserting
5) truncating


INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'Newark';


UPDATE suppliers
SET supplier_name = ( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS
( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id);




here also both insert and update will work then what is the
ans:2,4 or 1,2
Re: Subquery in clauses 'insert' and 'values' [message #327158 is a reply to message #327155] Sat, 14 June 2008 06:40 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

ruchika19840 wrote on Sat, 14 June 2008 06:33
and what about this one.

SELECT *
FROM orders

You use this statement to retrieve data for ______

1) update
2) viewing
3) deleting
4) inserting
5) truncating


INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'Newark';


UPDATE suppliers
SET supplier_name = ( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS
( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id);




here also both insert and update will work then what is the
ans:2,4 or 1,2






tell how u update using select * orders...
gimme example.. ??
Re: Subquery in clauses 'insert' and 'values' [message #327164 is a reply to message #327151] Sat, 14 June 2008 07:03 Go to previous message
ruchika19840
Messages: 7
Registered: June 2008
Junior Member
ok!!! got it...
Thanks !!!
Previous Topic: What happens when a database crashes?
Next Topic: select statement work for both insert and update
Goto Forum:
  


Current Time: Thu Mar 28 23:49:56 CDT 2024