Great! Let’s move to SQL Joins & Subqueries first — since they form the core of complex queries and are frequently asked in interviews. We’ll then move on to Advanced SQL Functions after this.

🔷 SQL JOINS & SUBQUERIES (Oracle-Compatible)

A full guide with:

• ✅ Concept + Syntax
• ⚡ Classic + Challenging Examples
• 🎯 Interview Questions + Answers
• 🧠 Comparison Tables & Use-Cases

🔸 PART 1: SQL JOINS

Joins are used to combine rows from two or more tables based on a related column between them.

✅ 1. INNER JOIN

Concept: Returns rows with matching values in both tables.

Syntax:

SELECT *
FROM emp e
JOIN dept d ON e.dept_id = d.dept_id;

Classic Example:

SELECT e.emp_name, d.dept_name
FROM employee e
JOIN department d ON e.dept_id = d.dept_id;

🎯 Interview Q: What if a match doesn’t exist?

The row is excluded from result in INNER JOIN.

✅ 2. LEFT OUTER JOIN (LEFT JOIN)

Concept: Returns all rows from the left table + matched rows from the right.

Syntax:

SELECT *
FROM emp e
LEFT JOIN bonus b ON e.emp_id = b.emp_id;

Challenging Example:

SELECT e.emp_name, b.bonus_amt
FROM employee e
LEFT JOIN bonus b ON e.emp_id = b.emp_id
WHERE b.bonus_amt IS NULL;  -- find who got no bonus

🎯 Interview Q: How to find unmatched rows?

Use LEFT JOIN ... WHERE right_table.col IS NULL

✅ 3. RIGHT OUTER JOIN

Returns all from right table and matched from left.

Example:

SELECT d.dept_name, e.emp_name
FROM department d
RIGHT JOIN employee e ON d.dept_id = e.dept_id;

✅ 4. FULL OUTER JOIN

Returns all matched and unmatched rows from both tables.

Example:

SELECT *
FROM employee e
FULL OUTER JOIN contractor c ON e.emp_id = c.emp_id;

🎯 Interview Q: How to simulate FULL JOIN in MySQL (which doesn’t support it)?

Use UNION of LEFT JOIN and RIGHT JOIN.

✅ 5. CROSS JOIN (CARTESIAN PRODUCT)

Returns every combination of rows.

Example:

SELECT * FROM region CROSS JOIN product;

🎯 Interview Q: When is CROSS JOIN useful?

Testing, simulation, creating data matrices.

✅ 6. SELF JOIN

A table joins with itself, useful in hierarchical data.

Example: Show employees and their managers.

SELECT e.emp_name AS Employee, m.emp_name AS Manager
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.emp_id;

🧠 Join Comparison Table

🔸 PART 2: SQL SUBQUERIES

A subquery is a query inside another query.

✅ 1. Single-Row Subquery

Returns one value.

SELECT emp_name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee);

🎯 Q: What happens if it returns more than one row?

❌ Error — use IN or ANY.

✅ 2. Multi-Row Subquery

Returns multiple values — use with IN, ANY, ALL, etc.

SELECT emp_name
FROM employee
WHERE dept_id IN (SELECT dept_id FROM department WHERE location = 'Delhi');

✅ 3. Correlated Subquery

Executes row-by-row and depends on outer query.

SELECT emp_name, salary
FROM employee e
WHERE salary > (
  SELECT AVG(salary)
  FROM employee
  WHERE dept_id = e.dept_id
);

🎯 Q: How is it different from a regular subquery?

Regular subqueries are independent; correlated ones depend on outer query.

✅ 4. EXISTS vs IN

EXISTS is used to check presence and is usually faster.

SELECT emp_name
FROM employee e
WHERE EXISTS (
  SELECT 1 FROM bonus b WHERE b.emp_id = e.emp_id
);

🎯 Q: IN vs EXISTS — when to use what?

• Use IN when subquery returns small set
• Use EXISTS when subquery is large or involves joins

✅ 5. Scalar Subquery

Returns a single value as if it were a column.

SELECT emp_name,
       (SELECT dept_name FROM department d WHERE d.dept_id = e.dept_id) AS dept_name
FROM employee e;

🧠 Subquery Types Summary

🎯 INTERVIEW CHALLENGE QUESTIONS

1. Find employees earning more than average salary of their department.
2. List all departments with no employees.
3. List employees whose salary is more than any employee in department 10.
4. Find departments with more than 5 employees.
5. Using a self join, list each employee with their manager name.
6. Simulate FULL OUTER JOIN using LEFT and RIGHT JOIN.

Absolutely! Here’s a detailed guide to SQL Advanced Functions along with:

1. ✅ Clear explanations
2. 🎯 Classic + tricky examples
3. 💼 Real interview questions with sample data + solutions

👉 SQL Advanced Functions

🔹 1. Aggregate Functions

Used to summarize data. Common ones:

🧪 Example:

SELECT department, COUNT(*) AS num_employees, AVG(salary) AS avg_sal
FROM employees
GROUP BY department;

🔹 2. Analytic / Window Functions

These preserve row granularity but perform calculations over a “window” of rows.

🧪 Example:

SELECT emp_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

📌 Insight: This gives you top earners within each department.

🔹 3. CASE Expression

Multi-condition IF...ELSE.

🧪 Example:

SELECT name, salary,
  CASE 
    WHEN salary > 100000 THEN 'High'
    WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_band
FROM employees;

🔹 4. NVL / COALESCE

Used to handle NULLs.

• NVL(expr1, expr2) returns expr2 if expr1 is NULL.
• COALESCE(expr1, expr2, expr3, ...) returns first non-null.

🧪 Example:

SELECT emp_name, NVL(bonus, 0) AS bonus_amount FROM payroll;

🔹 5. DECODE()

Oracle-specific shorthand for CASE.

🧪 Example:

SELECT emp_name,
       DECODE(dept_id, 10, 'HR', 20, 'IT', 30, 'Finance', 'Other') AS dept_name
FROM employees;

🔥 INTERVIEW SQL TASKS + SOLUTIONS

✅ Task 1: Find 2nd highest salary from employee table

📋 Sample Data:

✅ Query:

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

🔁 Alt: Use DENSE_RANK() over window if ties matter.

✅ Task 2: Top 3 employees in each department by salary

📋 Sample Data:

✅ Query:

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn <= 3;

✅ Task 3: Show all employees whose salary is greater than department average

SELECT emp_id, name, salary, department
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department
);

✅ Task 4: Calculate running total of sales per region

SELECT region, month, sales,
       SUM(sales) OVER (PARTITION BY region ORDER BY month) AS running_total
FROM sales_data;

✅ Task 5: Find employees who joined on same date as someone else

SELECT *
FROM employees
WHERE join_date IN (
    SELECT join_date
    FROM employees
    GROUP BY join_date
    HAVING COUNT(*) > 1
);

✅ Task 6: Detect salary hikes (Show previous salary vs current)

SELECT emp_id, salary,
       LAG(salary) OVER (PARTITION BY emp_id ORDER BY revision_date) AS prev_salary
FROM salary_history;

💼 Common Interview Question Patterns

Absolutely. Here’s a comprehensive breakdown of both requested parts:

👉 Part 1: SQL Advanced Functions

We’ll cover:

• Aggregate Functions
• Analytic/Window Functions
• CASE / DECODE / NVL / COALESCE
• Classic + Challenging Examples
• Interview Questions with Sample Tables

🔸 1. Aggregate Functions

Used to perform a calculation on a set of values and return a single value.

✅ Common Aggregate Functions:

🔹Example:

SELECT department_id, COUNT(*) AS num_employees, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

🎯 Interview Challenge:

Question: Show departments with more than 5 employees having average salary > 5000.

SELECT department_id, COUNT(*) AS emp_count, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5 AND AVG(salary) > 5000;

🔸 2. Analytic / Window Functions

Operate on a set of rows related to the current row — more powerful than aggregate functions.

✅ Common Functions:

🔹Example 1: ROW_NUMBER()

SELECT employee_id, department_id, salary,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM employees;

✔ Helps get top earners per department

🔹Example 2: Running Total

SELECT employee_id, department_id, salary,
       SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS running_total
FROM employees;

🎯 Interview Challenge:

Question: Find second highest salary per department

SELECT *
FROM (
  SELECT employee_id, department_id, salary,
         RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
  FROM employees
) sub
WHERE rnk = 2;

🔸 3. CASE / DECODE / NVL / COALESCE

✅ CASE (if-else)

SELECT employee_id, salary,
       CASE 
           WHEN salary > 10000 THEN 'High'
           WHEN salary > 5000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_level
FROM employees;

✅ DECODE (Oracle specific)

SELECT employee_id, DECODE(department_id, 10, 'Finance', 20, 'HR', 'Other') AS dept_name
FROM employees;

✅ NVL (Oracle) — Replace NULL

SELECT employee_id, NVL(commission_pct, 0) AS comm
FROM employees;

✅ COALESCE — First non-NULL value

SELECT employee_id, COALESCE(commission_pct, bonus_pct, 0) AS payout
FROM employees;

🔥 Bonus Interview Task

Question: Find employee details with department name and flag as Top Performer if salary is in top 3 in the department.

SELECT e.*, d.department_name,
       CASE 
           WHEN rnk <= 3 THEN 'Top Performer'
           ELSE 'Regular'
       END AS performance_flag
FROM (
    SELECT e.*, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
    FROM employees e
) e
JOIN departments d ON e.department_id = d.department_id;

🎯 Part 2: Real SQL Interview Tasks + Solutions

Let’s take 3 powerful examples with sample schema:

⚙️ Sample Schema

-- Employees
CREATE TABLE employees (
  emp_id INT,
  name VARCHAR(50),
  salary INT,
  manager_id INT,
  department_id INT,
  hire_date DATE
);

-- Departments
CREATE TABLE departments (
  department_id INT,
  department_name VARCHAR(100)
);

🔥 Problem 1: Get employees who joined in last 1 year and earn more than the avg salary of their department.

SELECT e.*
FROM employees e
JOIN (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
) avg_dept ON e.department_id = avg_dept.department_id
WHERE e.salary > avg_dept.avg_sal
  AND e.hire_date > ADD_MONTHS(SYSDATE, -12);

🔥 Problem 2: Show manager name and count of employees under them.

SELECT m.name AS manager_name, COUNT(e.emp_id) AS team_size
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
GROUP BY m.name;

🔥 Problem 3: Find top 2 salaried employees in each department.

SELECT *
FROM (
  SELECT e.*, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
  FROM employees e
) sub
WHERE rnk <= 2;

🔥 Problem 4: Show department with max avg salary

SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal DESC
FETCH FIRST 1 ROW ONLY;

🔥 Problem 5: List employees with salary higher than their manager

SELECT e.name AS emp_name, e.salary AS emp_salary,
       m.name AS mgr_name, m.salary AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

⚠️ Real-World Tip (Interview Trick)

Many companies ask:

“Can you find duplicate records, or the second most frequent salary?”

-- Duplicates by name
SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

-- Second highest frequency salary
SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
  FROM employees
  GROUP BY salary
) WHERE rnk = 2;

✅ Summary Sheet

Perfect. Let’s now give you everything step-by-step for your next deep dive.

✅ [1] Practice Dataset (CSV + Schema) for All SQL Concepts

Here’s a clean schema + sample data you can use to practice:

📦 Dataset: Sales_DB

Table 1: employees.csv

emp_id,emp_name,department,salary,manager_id,hire_date
101,John Doe,Sales,60000,201,2018-03-15
102,Jane Smith,HR,75000,202,2016-07-20
103,Bob Martin,Sales,58000,201,2019-11-02
104,Lisa Ray,Finance,72000,202,2017-01-25
105,Raj Patel,Sales,60000,201,2020-09-10
106,Maria Jones,IT,80000,203,2015-05-14
107,Anil Kapoor,IT,82000,203,2021-06-18

Table 2: departments.csv

dept_id,dept_name,location
101,Sales,New York
102,HR,London
103,Finance,Bangalore
104,IT,Singapore

Table 3: orders.csv

order_id,emp_id,order_date,amount,region
201,101,2021-07-01,5000,North
202,103,2021-07-03,8000,South
203,101,2021-07-05,12000,North
204,105,2021-07-07,4000,East
205,103,2021-07-08,6000,South
206,101,2021-07-09,15000,West
207,105,2021-07-11,3000,East
208,101,2021-07-12,25000,North
209,106,2021-07-13,10000,East

You can import these using:

-- If using SQL Developer or DBMS:
-- Upload CSVs and create tables
-- Or create manually:

CREATE TABLE employees (
  emp_id INT,
  emp_name VARCHAR(50),
  department VARCHAR(50),
  salary INT,
  manager_id INT,
  hire_date DATE
);

CREATE TABLE departments (
  dept_id INT,
  dept_name VARCHAR(50),
  location VARCHAR(50)
);

CREATE TABLE orders (
  order_id INT,
  emp_id INT,
  order_date DATE,
  amount INT,
  region VARCHAR(50)
);

Pages: 1 2 3 4 5