Intermediate SQL — Joins, Subqueries, Views & Aggregations

🌟 Introduction

Welcome to Part 2 of the Oracle Live SQL Mastery Series.

In the previous part, we explored fundamentals of Oracle Live SQL, learning how to write basic queries, filter data, and perform simple joins.

Now, we’re stepping into intermediate SQL — the bridge between beginner-friendly syntax and professional database development.

By the end of this article, you’ll confidently use:
✅ JOINS (Inner, Outer, Self, Cross)
✅ Subqueries
✅ Views
✅ Grouping & Aggregations
✅ Performance tips for production-level SQL

These are the skills most developers need to pass SQL interview tests and build efficient data-driven web applications.

---

🧩 Understanding Joins — The Heart of Relational Databases

A JOIN combines rows from two or more tables based on related columns between them.

Let’s visualize it 👇

🔶 ER Diagram Example

EMPLOYEES (employee_id, first_name, department_id)
DEPARTMENTS (department_id, department_name)

📊 Relationship:
Each employee belongs to one department, but each department can have many employees — a one-to-many relationship.

---

🔗 1. INNER JOIN

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

Explanation:

• Combines only rows that have a match in both tables.

• Employees with no department won’t appear.

✅ Use Case: Most common join in day-to-day analytics and backend queries.

---

🔗 2. LEFT JOIN (LEFT OUTER JOIN)

SELECT e.first_name, e.department_id, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;

Explanation:

• Returns all employees, even if they don’t belong to any department.

• Unmatched department names will be NULL.

🧠 Tip: Use LEFT JOIN when you don’t want to lose data from your main table.

---

🔗 3. RIGHT JOIN (RIGHT OUTER JOIN)

SELECT d.department_name, e.first_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;

Explanation:

• Returns all departments, even those without employees.

✅ Example: See which departments are currently unassigned to employees.

---

🔗 4. FULL OUTER JOIN

SELECT e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

Explanation:

• Returns all records from both tables, whether or not matches exist.

• Missing data is filled with NULL.

---

🔁 5. SELF JOIN

Used when you join a table to itself — perfect for hierarchical data (like managers and employees).

SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id;

📘 Explanation:
Each employee row joins with another employee record acting as a manager.

💡 Common in HR systems where employees report to other employees.

---

💫 6. CROSS JOIN

SELECT e.first_name, d.department_name
FROM employees e
CROSS JOIN departments d;

Explanation:
Generates a Cartesian product — every employee combined with every department.

⚠️ Warning: Avoid unless you intentionally want all combinations (e.g., generating test data).

---

📊 GROUP BY and HAVING

Grouping lets you summarize data, often combined with aggregate functions like SUM(), AVG(), and COUNT().

---

🧮 Example 1: Count Employees per Department

SELECT department_id, COUNT(*) AS total_employees
FROM employees
GROUP BY department_id;

Explanation:
GROUP BY clusters rows with the same department ID and counts them.

---

🧮 Example 2: Use HAVING to Filter Aggregates

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 8000;

Explanation:

• HAVING works like WHERE, but for grouped data.

• Filters groups with an average salary over 8000.

🧠 Note: You cannot use WHERE for aggregated results — that’s why HAVING exists.

---

🔍 Subqueries — Query Inside a Query

A subquery is a query nested inside another. It helps break down complex problems into smaller, modular parts.

---

🔹 Example 1: Find Employees Earning More Than the Average Salary

SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Explanation:

• Inner query computes the average salary.

• Outer query lists employees above that average.

---

🔹 Example 2: Use Subquery in FROM Clause

SELECT department_id, total
FROM (
  SELECT department_id, SUM(salary) AS total
  FROM employees
  GROUP BY department_id
)
WHERE total > 50000;

🧠 Explanation:
This “inline view” first calculates total salary per department, then filters departments exceeding 50,000.

---

🔹 Example 3: Correlated Subquery

SELECT e.first_name, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
);

Explanation:

• The subquery depends on the outer query.

• It compares each employee’s salary with their department’s average.

💡 Performance Tip: Correlated subqueries can be slower — optimize using joins where possible.

---

🏗️ Views — Creating Virtual Tables

A view is a stored SQL query that behaves like a table.

You can query it, join it, or even base other views on top of it.

---

🧱 Example: Creating and Using a View

CREATE VIEW high_salary_employees AS
SELECT first_name, last_name, salary, department_id
FROM employees
WHERE salary > 10000;

Then you can query it like a regular table:

SELECT * FROM high_salary_employees
ORDER BY salary DESC;

Benefits:
✅ Simplifies complex queries.
✅ Improves readability and security.
✅ Lets developers reuse query logic efficiently.

---

🧩 Updating Through Views

If your view is based on a single table (without aggregates or joins), you can update data through it:

UPDATE high_salary_employees
SET salary = salary + 1000
WHERE first_name = 'Steven';

Oracle updates the underlying employees table automatically.

---

🧮 Aggregations in Real Projects

Imagine you’re building a JavaScript dashboard showing departmental salary insights.

Here’s a practical query you’d run in Oracle Live SQL:

SELECT d.department_name,
       COUNT(e.employee_id) AS total_employees,
       ROUND(AVG(e.salary), 2) AS avg_salary,
       MAX(e.salary) AS highest_salary
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY avg_salary DESC;

This dataset could be sent via an API to your frontend to visualize data using Chart.js, Recharts, or D3.js.

---

📈 Visualizing the Flow of SQL Execution

Let’s visualize how Oracle processes your SQL step-by-step:

1️⃣ FROM → identifies base tables
2️⃣ JOIN → combines related rows
3️⃣ WHERE → filters row-level data
4️⃣ GROUP BY → groups data
5️⃣ HAVING → filters groups
6️⃣ SELECT → picks output columns
7️⃣ ORDER BY → sorts final output

Understanding this execution order helps debug and optimize complex SQL.

---

⚙️ Query Optimization Best Practices

When working in Oracle Live SQL, even small optimizations make a huge difference:

1. Use Indexes Wisely

   CREATE INDEX idx_department_id ON employees(department_id);
   

   Helps Oracle locate rows faster.

2. **Avoid SELECT ***
   Specify columns to reduce load.

3. Replace Subqueries with Joins
   Joins are often faster than correlated subqueries.

4. Use ANALYZE and EXPLAIN PLAN

   EXPLAIN PLAN FOR SELECT * FROM employees WHERE salary > 10000;
   

   View the plan in the “Execution Plan” tab in Live SQL.

5. Use View Caching
   Precompute frequent reports with materialized views.

---

💡 Materialized Views (Advanced Peek)

Unlike normal views, materialized views store data physically — ideal for analytics dashboards.

CREATE MATERIALIZED VIEW dept_salary_summary AS
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

🧠 These can be refreshed automatically to keep analytics fast and up-to-date.

---

🧑‍💻 Practice Challenge

1️⃣ Find departments where the total salary exceeds 50,000.
2️⃣ List employees earning above their department’s average.
3️⃣ Create a view showing top 10 earners company-wide.

All can be practiced directly on Oracle Live SQL.

---

🧠 Interview-Focused Questions from This Module

Question	Explanation
What’s the difference between WHERE and HAVING?	WHERE filters rows; HAVING filters groups.
Can we update data through a view?	Yes, if it’s based on one table without aggregates.
What is a correlated subquery?	A subquery referencing columns from the outer query.
Difference between INNER JOIN and LEFT JOIN?	INNER shows matches only; LEFT keeps unmatched rows too.
What are materialized views used for?	Precomputed data storage for faster reporting.

---

🧾 Summary

In this part, you learned how to:
✅ Use Joins effectively (Inner, Outer, Self, Cross).
✅ Write Subqueries for complex filtering.
✅ Build Views to simplify queries.
✅ Group and aggregate data professionally.
✅ Optimize SQL for real-world performance.