HintsToday

Perfect! Let’s now move to the next stage of mastering SQL:
We’ll cover Recursive Queries, Common Table Expressions (CTEs), and Set Operations in depth, with:

• ✅ Full concepts & syntax
• 💡 Real-world + tricky examples
• 🧠 Interview Q&A
• 🧪 Practice-ready sample data

🔁 1. Recursive SQL

✅ What is a Recursive Query?

A recursive query refers to a query that calls itself repeatedly until a condition is met. It’s typically used to traverse hierarchical or tree-like structures.

✅ Typical Use Cases:

• Organization hierarchy (Manager → Employee)
• Bill of Materials (BOM)
• Folder structure (Parent → Child)

🔧 Syntax (Recursive CTE)

WITH RECURSIVE cte_name (column_list) AS (
    -- Anchor member (base case)
    SELECT ...
    FROM ...
    WHERE ...

    UNION ALL

    -- Recursive member (calls CTE again)
    SELECT ...
    FROM cte_name
    JOIN ...
    ON ...
)
SELECT * FROM cte_name;

🧪 Example Dataset

CREATE TABLE employees (
    emp_id INT,
    emp_name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'CEO', NULL),
(2, 'CTO', 1),
(3, 'CFO', 1),
(4, 'Dev Lead', 2),
(5, 'Developer', 4),
(6, 'Intern', 5);

💡 Example: Get Full Hierarchy from CEO Down

WITH RECURSIVE hierarchy AS (
  SELECT emp_id, emp_name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.emp_id, e.emp_name, e.manager_id, h.level + 1
  FROM employees e
  JOIN hierarchy h ON e.manager_id = h.emp_id
)
SELECT * FROM hierarchy
ORDER BY level;

🎯 Interview Q:

Q. How do you find all reports (direct + indirect) of a given employee in a hierarchical org structure?

🧩 2. CTE (Common Table Expressions)

✅ What is a CTE?

A CTE (non-recursive) is a temporary result set defined using WITH. It’s cleaner than subqueries and makes complex queries readable.

🔧 Syntax

WITH cte_name AS (
  SELECT ...
)
SELECT ...
FROM cte_name;

💡 Example: Get Top 3 Salaries Using CTE + RANK()

WITH ranked_salaries AS (
  SELECT emp_id, emp_name, salary,
         RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT * FROM ranked_salaries WHERE rnk <= 3;

🎯 Interview Q:

Q. What’s the difference between CTE and a subquery? When do you prefer CTE?

A: CTEs are reusable and modular; better for readability and recursive tasks.

🔁 3. Set Operations

Set operators combine two result sets.

💡 Example: UNION vs UNION ALL

SELECT emp_name FROM employees
WHERE department = 'Sales'

UNION

SELECT emp_name FROM employees
WHERE department = 'Marketing';

Use UNION ALL to preserve duplicates for performance.

💡 Example: Find Employees Who Changed Departments

SELECT emp_id FROM old_dept
INTERSECT
SELECT emp_id FROM new_dept;

🎯 Interview Q:

Q. When is UNION ALL preferred over UNION?

When duplicates are needed or performance is key.

🧠 Real Interview Challenge: Recursive + CTE + Set

Scenario:

You have a table of employees and want to:

1. Get all subordinates of a given manager.
2. List employees who switched departments.
3. Find level of hierarchy for each employee.

All can be solved using the three concepts above.

✅ Practice Dataset: CSV

Absolutely! Here’s your complete deep-dive on:

🔍 Views, Indexing & Performance Optimization in SQL

1. ✅ SQL Views

What is a View?

A View is a virtual table based on a SQL query. It does not store data physically but gives a dynamic result set when queried.

CREATE VIEW active_customers AS
SELECT customer_id, name, city
FROM customers
WHERE status = 'active';

📌 Key Points

• Acts like a table but doesn’t store data.
• Query is run when view is accessed.
• Helps in abstraction, simplification, and security.

🔁 Types of Views

🚧 Example: Complex View

CREATE VIEW order_summary AS
SELECT c.name, COUNT(o.order_id) AS total_orders
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name;

❓ Interview Q: Can we insert into a view?

• Simple view with all base table columns? ✅ Yes
• View with joins/aggregates? ❌ No
• Views with INSTEAD OF triggers can allow INSERT/UPDATE on complex views.

2. 📌 Indexes in SQL

What is an Index?

An index is a data structure (like B-Tree) that improves the speed of data retrieval at the cost of additional space and slower INSERT/UPDATE.

🎯 Syntax:

CREATE INDEX idx_customer_name ON customers(name);

🔎 How it Works

Index works like a book’s table of contents—it avoids full table scans.

⚖️ Types of Indexes:

🔁 Example: Composite Index

CREATE INDEX idx_customer_city_state 
ON customers(city, state);

💡 Indexing Best Practices

• Index columns used in WHERE, JOIN, ORDER BY
• Avoid indexing frequently updated columns
• Don’t over-index: each index slows down INSERT/UPDATE

❓ Interview Q: Will this use an index?

SELECT * FROM customers WHERE UPPER(name) = 'JOHN';

➡️ No, unless you create a function-based index:

CREATE INDEX idx_upper_name ON customers(UPPER(name));

3. 🚀 SQL Performance Optimization

🔧 Query Tuning Tips

🔬 Example 1: EXISTS vs IN

-- Slower for large subqueries
SELECT name FROM customers 
WHERE customer_id IN (SELECT customer_id FROM orders);

-- Faster alternative
SELECT name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

🔬 Example 2: Indexed JOIN

-- If both customer_id fields are indexed, this is efficient
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

🔍 EXPLAIN PLAN Usage

EXPLAIN PLAN FOR 
SELECT * FROM orders WHERE customer_id = 101;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

❓ Interview Challenge:

Query:

Find the top 3 customers based on order amount in last 30 days.

SELECT customer_id, SUM(order_amount) AS total_spent
FROM orders
WHERE order_date >= SYSDATE - 30
GROUP BY customer_id
ORDER BY total_spent DESC
FETCH FIRST 3 ROWS ONLY;

👉 Ensure order_date and customer_id are indexed for speed.

🧪 Practice Dataset (Schema):

customers.csv

orders.csv

🧠 Summary:

Absolutely! Here’s a complete hands-on SQL practice set with:

🧪 50+ Hands-on Interview SQL Queries (with Real-World Focus)

🔹 Difficulty Tags:

• 🟢 Beginner
• 🟡 Intermediate
• 🔴 Advanced
• ⭐ Interview Favorite

📁 Schema: employees.csv

emp_id,emp_name,department,salary,manager_id,hire_date
101,John Doe,Sales,50000,201,2019-06-01
102,Jane Smith,Marketing,60000,202,2020-01-15
103,Bob Brown,Sales,45000,201,2018-09-10
104,Alice White,IT,70000,203,2021-07-01
105,Tom Black,IT,75000,203,2020-03-23
106,Lisa Green,Marketing,55000,202,2019-11-30

📁 Schema: departments.csv

department_id,department_name,location
201,Sales,New York
202,Marketing,San Francisco
203,IT,Bangalore

📁 Schema: projects.csv

project_id,project_name,department,budget
1,Website Redesign,Marketing,15000
2,Cloud Migration,IT,40000
3,Product Launch,Sales,30000
4,CRM Upgrade,Sales,20000
5,Security Audit,IT,10000

🔍 50+ SQL Hands-On Queries

1. 🟢 List all employees with salary > 55000

SELECT * FROM employees WHERE salary > 55000;

2. 🟡 Display total salary department-wise

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

3. 🔴 Find second highest salary

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

4. 🟡 Find employees hired after 2020

SELECT emp_name FROM employees
WHERE hire_date > '2020-01-01';

5. 🔴 Find employees whose name contains “a” and is from IT

SELECT * FROM employees
WHERE emp_name LIKE '%a%' AND department = 'IT';

6. 🔴 Find employees who earn more than their manager

SELECT e.emp_name, e.salary, m.emp_name AS manager_name, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

7. 🔴 Find employees not assigned to any project

SELECT emp_name
FROM employees
WHERE department NOT IN (SELECT DISTINCT department FROM projects);

8. 🔴 Top 2 highest paid per department (Window Function)

SELECT *
FROM (
  SELECT emp_name, department, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk <= 2;

9. 🔴 Count employees per manager

SELECT manager_id, COUNT(*) AS emp_count
FROM employees
GROUP BY manager_id;

10. 🔴 Department with max average salary

SELECT department
FROM employees
GROUP BY department
ORDER BY AVG(salary) DESC
LIMIT 1;

11. 🟡 Use CASE to categorize salaries

SELECT emp_name,
  CASE
    WHEN salary >= 70000 THEN 'High'
    WHEN salary BETWEEN 50000 AND 69999 THEN 'Medium'
    ELSE 'Low'
  END AS salary_range
FROM employees;

12. 🔴 Employees who joined in same year as their manager

SELECT e.emp_name, m.emp_name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE YEAR(e.hire_date) = YEAR(m.hire_date);

13. 🟡 Total budget by department

SELECT department, SUM(budget) AS total_budget
FROM projects
GROUP BY department;

14. 🔴 Which employee is working on the most expensive project in their department?

SELECT e.emp_name, e.department
FROM employees e
JOIN (
  SELECT department, MAX(budget) AS max_budget
  FROM projects
  GROUP BY department
) p ON e.department = p.department;

💼 SQL Project-Based Challenges (Mini Projects)

🔧 Project 1: Employee Manager Hierarchy

✅ Objective:

Create a hierarchy tree of employees and their managers using CTE.

✅ Sample Query:

WITH RECURSIVE emp_mgr AS (
  SELECT emp_id, emp_name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.emp_id, e.emp_name, e.manager_id, em.level + 1
  FROM employees e
  JOIN emp_mgr em ON e.manager_id = em.emp_id
)
SELECT * FROM emp_mgr ORDER BY level;

🔧 Project 2: Department Performance Analysis

✅ Objective:

Aggregate salary, avg salary, max salary, and number of employees.

✅ Sample Query:

SELECT department,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary,
       MAX(salary) AS max_salary,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

🔧 Project 3: Duplicate Project Budgets

✅ Objective:

Find departments with 2 or more projects having same budget.

✅ Sample Query:

SELECT department, budget, COUNT(*) as project_count
FROM projects
GROUP BY department, budget
HAVING COUNT(*) >= 2;

Great! Let’s dive into the next advanced section of SQL—which focuses on Triggers, Procedures, Packages, and PL/SQL Control Flow in Oracle SQL/PLSQL with:

• 📘 Detailed explanation
• 🔍 Classic & tricky examples
• 🧠 Real interview questions + answers
• ✅ Hands-on syntax + challenge

🔥 1. PL/SQL Control Flow (IF, CASE, LOOPs)

💡 Summary

Used inside procedures/functions/triggers to control the flow of logic using conditional and iterative statements.

✅ Syntax & Examples

1. IF THEN ELSIF ELSE

DECLARE
  marks NUMBER := 85;
BEGIN
  IF marks >= 90 THEN
    DBMS_OUTPUT.PUT_LINE('Grade: A');
  ELSIF marks >= 75 THEN
    DBMS_OUTPUT.PUT_LINE('Grade: B');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Grade: C');
  END IF;
END;

2. CASE Expression

DECLARE
  grade CHAR := 'B';
BEGIN
  CASE grade
    WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Good');
    ELSE DBMS_OUTPUT.PUT_LINE('Average');
  END CASE;
END;

3. LOOP / FOR / WHILE

DECLARE
  i NUMBER := 1;
BEGIN
  WHILE i <= 3 LOOP
    DBMS_OUTPUT.PUT_LINE('i = ' || i);
    i := i + 1;
  END LOOP;
END;

🚀 2. Stored Procedures

✅ Syntax:

CREATE OR REPLACE PROCEDURE proc_example(p_name IN VARCHAR2) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello ' || p_name);
END;

📌 Usage:

EXEC proc_example('Rajeev');

❓ Interview Q:

How is a procedure different from a function?

✔️ Procedure: May not return a value
✔️ Function: Must return exactly one value

🔥 3. Functions (User Defined)

CREATE OR REPLACE FUNCTION get_area(r NUMBER)
RETURN NUMBER IS
BEGIN
  RETURN 3.14 * r * r;
END;

SELECT get_area(10) FROM dual;  -- Outputs: 314

💥 4. Triggers

✅ Syntax:

CREATE OR REPLACE TRIGGER trg_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  :NEW.created_at := SYSDATE;
END;

💡 Trigger Types:

🎯 Interview Question:

What is the difference between a row-level and statement-level trigger?

✔️ Row-level: Fires once for each row
✔️ Statement-level: Fires once per SQL statement

📦 5. Packages

📌 Why use?

• Group procedures/functions/types
• Modularity + reusability
• Improved performance

✅ Syntax:

Spec (Declaration):

CREATE OR REPLACE PACKAGE emp_utils AS
  PROCEDURE log_insert(p_id NUMBER);
END emp_utils;

Body (Implementation):

CREATE OR REPLACE PACKAGE BODY emp_utils AS
  PROCEDURE log_insert(p_id NUMBER) IS
  BEGIN
    INSERT INTO emp_log(emp_id, action_time) VALUES (p_id, SYSDATE);
  END;
END emp_utils;

Usage:

EXEC emp_utils.log_insert(101);

🧠 Real Interview Tricky Task

✅ Problem: Create a trigger to prevent deletion of employees with salary > 100000

CREATE OR REPLACE TRIGGER prevent_high_salary_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
  IF :OLD.salary > 100000 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Cannot delete employee with high salary!');
  END IF;
END;

🧪 Mini Project Task

🎯 Use Case:

• Table: accounts(account_id, balance)
• Create trigger: before insert → default balance = 1000 if null
• Procedure: deposit or withdraw, checks for negative balance
• Function: check current balance

📁 Practice Dataset

Table: students (id, name, marks)
Table: employees (id, name, dept_id, salary)
Table: departments (dept_id, dept_name)

📌 Final Tips

Absolutely. Let’s now cover:

✅ Materialized Views, Virtual Columns, and Exception Handling in PL/SQL

All concepts come with real-world use cases, interview insights, and hands-on examples.

🔷 1. Materialized Views in Oracle

📌 Definition:

A Materialized View stores the physical copy of the query result (unlike normal views which are just stored queries). They are useful when performance is critical for large/complex joins or aggregations.

🔧 Syntax:

CREATE MATERIALIZED VIEW sales_summary_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT region, SUM(sales) AS total_sales
FROM sales
GROUP BY region;

🔁 Refresh Types:

• FAST: Only changes (requires materialized view log).
• COMPLETE: Full refresh.
• ON COMMIT: Refreshes when base table changes.
• ON DEMAND: Manual refresh.

✅ Use Case:

You need fast dashboard queries on pre-aggregated sales metrics across 10M rows.

❓ Interview Q:

Q: What is the difference between a view and a materialized view?
A: A view is virtual (no data stored), whereas a materialized view stores query output physically. MV can be refreshed to keep in sync.

🧪 Challenge:

You have a products table and a sales table with millions of rows. Create a materialized view to show product_id, product_name, and total revenue, refreshing on demand.

CREATE MATERIALIZED VIEW product_revenue_mv
BUILD IMMEDIATE
REFRESH ON DEMAND
AS
SELECT p.product_id, p.product_name, SUM(s.amount) AS total_revenue
FROM products p
JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_id, p.product_name;

🔷 2. Virtual Columns

📌 Definition:

A virtual column is not physically stored, but derived from other columns using an expression.

🛠 Syntax:

CREATE TABLE employees (
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  full_name AS (first_name || ' ' || last_name)
);

You can index virtual columns and filter by them.

✅ Use Case:

Generate derived fields like age, discounted_price, total_tax, etc., without storing extra data.

❓ Interview Q:

Q: Can you create an index on a virtual column?
A: Yes, if the virtual column is deterministic.

🧪 Challenge:

Add a virtual column to a table orders that calculates total_amount as (unit_price * quantity).

ALTER TABLE orders ADD total_amount AS (unit_price * quantity);

🔷 3. Exception Handling in PL/SQL

📌 Purpose:

Catch and handle errors gracefully using EXCEPTION blocks.

🔧 Basic Structure:

BEGIN
   -- code that may raise error
   UPDATE employees SET salary = salary * 2 WHERE department_id = 100;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No employee found');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Some unexpected error occurred: ' || SQLERRM);
END;

🎯 Common Exceptions:

• NO_DATA_FOUND
• TOO_MANY_ROWS
• ZERO_DIVIDE
• VALUE_ERROR
• OTHERS

✅ Use Case:

Validate salary changes and handle invalid employee ID scenarios without crashing the procedure.

❓ Interview Q:

Q: What happens if there is no EXCEPTION block and an error occurs?
A: The error propagates up and halts execution unless caught in a calling block.

🧪 Challenge:

Write a PL/SQL block to divide two numbers passed in. Handle division by zero exception.

DECLARE
   a NUMBER := 100;
   b NUMBER := 0;
   result NUMBER;
BEGIN
   result := a / b;
   DBMS_OUTPUT.PUT_LINE('Result is: ' || result);
EXCEPTION
   WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('Error: Cannot divide by zero.');
END;

📦 Bonus: Combining All

CREATE TABLE sales_data (
  id NUMBER,
  product_price NUMBER,
  quantity NUMBER,
  total_price AS (product_price * quantity)
);

CREATE MATERIALIZED VIEW sales_totals_mv
REFRESH FAST ON COMMIT
AS
SELECT SUM(product_price * quantity) AS grand_total FROM sales_data;

BEGIN
   -- Try inserting faulty data
   INSERT INTO sales_data (id, product_price, quantity) VALUES (1, 500, NULL);
EXCEPTION
   WHEN VALUE_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('Invalid input');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

📚 Summary Table:

Pages: 1 2 3 4 5