Subqueries, Correlated Queries, and Views

This document provides an in-depth explanation of the topics covered in class and is designed to be your go-to reference as you continue to build your knowledge and skills.

Introduction

SQL isn't just about fetching rows—it's about building powerful, layered logic that mirrors real-world relationships. Subqueries, views, and correlated queries help you express logic like “Who’s the best in their department?” or “Who reports to someone who reports to the president?”

1. Subqueries

1.1 What is a Subquery?

A subquery is a query nested inside another query. It's used when the result of one query is required as input for another.

Think of it like: “Get me all employees who have the same salary as the person earning the highest salary.”

1.1.1 Example: Highest Paid Employee


SELECT * FROM emp 
WHERE sal = (SELECT MAX(sal) FROM emp);

Outer query: fetches full details
Inner query: calculates max salary
The inner query is independent (can run alone)

2. Correlated Subqueries

2.1 What is a Correlated Subquery?

A correlated subquery depends on each row of the outer query. It runs once for every row of the outer query.

Real-world: “Find employees whose salary is the maximum in their own department.”

2.1.1 Example: Department-wise Top Earner


SELECT * 
FROM emp e1 
WHERE e1.sal = (
  SELECT MAX(sal) 
  FROM emp e2 
  WHERE e1.deptno = e2.deptno
);

Inner query uses e1.deptno from outer query
Called for each employee — can be slower

3. More Practical Examples

3.1 Employees whose managers are not managers


SELECT * 
FROM emp 
WHERE mgr IN (
  SELECT empno FROM emp 
  WHERE empno NOT IN (SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL)
);

3.2 Employees working under the president


SELECT * 
FROM emp 
WHERE mgr = (
  SELECT empno FROM emp 
  WHERE job = 'PRESIDENT'
);

3.3 Employees hired before their manager


SELECT w.ename AS worker, m.ename AS manager 
FROM emp w, emp m 
WHERE w.mgr = m.empno 
AND w.hiredate < m.hiredate;

4. Views

4.1 What is a View?

A view is a virtual table based on a SQL query. It doesn't store data, it stores logic. Query results are generated in real-time.

Analogy: A named shortcut to a complex query.

4.2 Create and Use a View


CREATE VIEW v1 AS 
SELECT w.ename AS worker, m.ename AS manager 
FROM emp w, emp m 
WHERE w.mgr = m.empno 
AND w.hiredate < m.hiredate;

SELECT * FROM v1;

Updates in emp will reflect in v1 automatically.
No extra storage used.

4.3 Types of Views

Type	Based On	Updatable	Use Case
Simple View	Single table	Yes	Lightweight, quick operations
Complex View	Joins/Subqueries	No	Analytics, reporting (read-only)

5. Inline Views

5.1 What is an Inline View?

An inline view is a subquery used in the FROM clause—treated like a temporary table.

Real-world: Instead of creating a separate view, just plug it inside the query.

5.2 Example


SELECT deptno, MAX(sal) AS max_sal 
FROM (
  SELECT deptno, sal FROM emp
) AS dept_salaries 
GROUP BY deptno;

Avoids creating a permanent view
Great for on-the-fly aggregations

6. CTE (Common Table Expression)

6.1 What is a CTE?

A CTE (introduced using WITH) is a temporary result set used by the main query.

Like giving a name to a subquery block for reuse and readability.

6.2 Example


WITH MaxSalaries AS (
  SELECT deptno, MAX(sal) AS max_sal
  FROM emp 
  GROUP BY deptno
)
SELECT * 
FROM emp e
JOIN MaxSalaries m 
ON e.deptno = m.deptno AND e.sal = m.max_sal;

7. Predefined Functions You Should Know

Useful with subqueries and views:

Function	Purpose	Example
`MAX()`	Highest value	`MAX(sal)`
`MIN()`	Lowest value	`MIN(sal)`
`AVG()`	Average	`AVG(sal)`
`COUNT()`	Total count	`COUNT(*)`
`COALESCE()`	Handle `NULL` values	`COALESCE(comm, 0)`
`NVL()`	Replace `NULL` (Oracle-specific)	`NVL(comm, 0)`

8. Real World Tips

Use subqueries when filtering by result of another query.
Use correlated subqueries when comparison depends row-by-row.
Use views to simplify repeated queries.
Use inline views for readable one-time complex queries.
Use CTEs for modular and recursive logic.

9. Quick Comparison

Concept	Stored?	Use Case	Performance
Subquery	No	Filters, lookups	Fast (if scalar)
Correlated Subquery	No	Row-wise logic	Slower
View	Logic only	Reusable named query	Fast (depends)
Inline View	No	Temporary table in FROM	Fast
CTE	No	Clean readable subquery	Optimal

10. Apply What You've Learned

Complete the following tasks to test your mastery of subqueries, correlated queries, views, and inline views:

Create a query using a simple subquery to find employees earning the highest salary in the organization.
Write a correlated subquery to list employees getting the highest salary in their respective departments.
Create a view that lists employees who joined before their manager and query from it.
Use an inline view to display each department with its highest-paid employee’s details.
Use a CTE to break down a complex query for readability (e.g., total salary by department).
Use aggregate functions like MAX, AVG, or COUNT inside a join or subquery to answer a real-world scenario (e.g., departments with more than 3 employees).

💡 Submit your queries with a short explanation of why you chose each approach.