Oracle 1Z0-007: Subqueries

Subqueries

Describe the types of problems that subqueries can solve.

First, it can also be used to join tables. Also, there are cases where the only way to correlate two tables is through a subquery. The ability to generate a query based on the condition of changing data that is located in another table.

Define subqueries.

A subquery is a SELECT query that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.

It is possible to embed a SQL statement within another. When this is done on the WHERE or the HAVING statements, we have a subquery construct. What is subquery useful for? First, it can also be used to join tables. Also, there are cases where the only way to correlate two tables is through a subquery.

List the types of subqueries.

WHERE EXISTS and WHERE NOT EXISTS
WHERE IN and WHERE NOT IN

Write single-row and multiple-row subqueries.

/*Single-Row Column Subqueries*/

select * from person where exists (select employees from department);


				select first_name, last_name, email

				    from users
 
				    where exists

				        (select top 5 from student where student.last_name = person.last_name;

 
				/*Multiple-Column Subquery*/
 
				select member from relatives where name in
	
				    (select * from foreign_relatives where lastname like '%R');

Describe and explain the behavior of subqueries when NULL values are retrieved.

When using NOT IN, in case the subquery returns a NULL no rows are selected for the main query , this is different from IN in which case if a NULL value is returned no special effect takes place.

When using NOT EXISTS if the subquery returns NULL (no rows selected) the main query will return its rows accordingly, and viceversa if the NOT EXISTS subquery returns at least one row then the main query will return no rows selected.

In the case of EXISTS if the subquery returns at least on row the main query returns its values accordingly, and vice versa if the EXISTS subquery returns NULL (no rows selected) then the main query will return no rows selected.

Write subqueries in the `FROM` clause.

select first_name, last_name, phone_number, age from person where female in

			    (select * from person where age > 21 );

Updated: July 30, 2005; Joe Gakenheimer