kupa kupa - 4 months ago 29
SQL Question

1Z0-007 exam question

Question:
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

But I think right answers are the following: A, B, D, E BUT NOT F. Is not it so???

Answer

Loot at! ...I found something very important,which proves my assertion...

Subqueries in Other DML Statements

Subqueries can be used in DML statements such as INSERT, UPDATE, DELETE,

and MERGE. Following are some examples of subqueries in DML

statements.

To update the salary of all employees to the maximum salary in the

corresponding department (correlated subquery):

UPDATE employees e1

SET  salary = (SELECT MAX(salary)

     FROM  employees e2

     WHERE e1.department_id = e2.department_id);

To delete the records of employees whose salary is below the average

salary in the department (using a correlated subquery):

DELETE FROM employees e

WHERE salary < (SELECT AVG(salary) FROM employees

      WHERE  department_id = e.department_id);

To insert records to a table using a subquery:

INSERT INTO employee_archive

SELECT * FROM employees;

To specify a subquery in the VALUES clause of the INSERT statement:

INSERT INTO departments

       (department_id, department_name)

VALUES ((SELECT MAX(department_id)

         +10 FROM departments), 'EDP');

You can also have a subquery in the INSERT, UPDATE, and DELETE statements in place of the table name. Here is an example:

DELETE FROM

(SELECT * FROM departments

 WHERE department_id < 20)

WHERE department_id = 10;




INSERT INTO (SELECT department_id, department_name

FROM departments

WHERE department_id < 20)

VALUES (35, 'MARKETING');

1 row created.

Comments