Define the following terms and give one example of each.

1. Single-row subquery
2. Multiple-row subquery
3. Inline view
4. Top-N query
5. Correlated subquery

1. Single-row subquery
Single-row subquery: a subquery that returns only one row of data. It is also known as
scalar subquery.
SELECT DeptName FROM dept
WHERE DeptId =
(SELECT DeptId FROM employee WHERE EmployeeId = 111);

2. Multiple-row subquery
Multiple-row subquery: a subquery that returns more than one row of data.
SELECT Lname, Fname FROM employee
WHERE DeptId IN
(SELECT DeptId FROM dept
WHERE UPPER(DeptName) IN (‘SALES’, ‘FINANCE’);

3. Inline view
The inner SELECT statement in the FROM clause is used as the data source for the outer
SELECT statement. Such a subquery is known as an inline view. The inline view is a
subquery that can be given an alias name that you can use in a SQL statement, just like a
table alias. An inline view is not stored as an object like the other views created with a
CREATE VIEW statement. A subquery may not use the ORDER BY clause, but an
inline view may.
SELECT ROWNUM, Lname, Fname, Salary
FROM (SELECT Lname, Fname, Salary FROM employee ORDER BY Salary)

WHERE ROWNUM <= 3;

4. Top-N query
Top-N queries are used to sort rows in a table and then to find the first-N largest values or
first-N smallest values. For example, you want to find out the bottom 5 salaries in a
company, the top 3 room capacities, or the last 10 employees hired by a company.
The Top-N query uses an ORDER BY clause to sort rows in ascending or descending
order. The sorted rows are numbered with a pseudo-column named ROWNUM. If the
rows are sorted in ascending order by the Top-N column, the smallest value of the Top-N
column is at the top of the list. The largest value of the Top-N column is at the top of the
list if the rows are sorted in descending order by the Top-N column. You can display the
required number of rows based on the ROWNUM with < or <= operators. The > and >=
operators are not allowed with ROWNUM pseudocolumn.
SELECT rownum, Building, RoomNo, Capacity

FROM (SELECT Building, RoomNo, Capacity FROM location
ORDER BY Capacity DESC)
WHERE ROWNUM <= 4;
5. Correlated subquery
Correlated queries are different from other subqueries explained earlier in this chapter. In
correalated subquery, the inner (nested) query can reference columns from the outer
query. The inner query is executed once for each row in the outer query. In other
subqueries, the inner query was executed only once. It is a complex, but very powerful
feature.
First, Oracle selects a row from the outer query. Then, it finds the value of the correlated
column(s). Then, it executes the inner query for each row of the outer query. Then, it
sends the result of the inner query to the outer query and executes it. If the row satisfies
condition, then it outputs the row. Then, it selects the next row from outer query, and
repeats the same procedure.
SELECT EmployeeId, Salary, DeptId FROM employee outer

WHERE Salary =
(SELECT MAX(Salary) FROM employee
WHERE DeptId = outer.DeptId
GROUP BY DeptId);