SQL Programming Resources

🔥 SQL Interview Case Studies (Advanced Business Scenarios) 💯

🧠 Case Study 1: Find Repeat Customers
📊 Orders Table
order_id customer_id
1 101
2 102
3 101

❓ Business Question
Find customers who placed more than 1 order.

✅ Solution
SELECT customer_id,
COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

🧠 Case Study 2: Highest Paid Employee Per Department
❓ Business Question
Find highest paid employee in every department.

✅ Solution
WITH RankedEmployees AS (
SELECT *,
ROW_NUMBER() OVER(
PARTITION BY department
ORDER BY salary DESC
) rn
FROM employees
)
SELECT *
FROM RankedEmployees
WHERE rn = 1;

🧠 Case Study 3: Find Inactive Customers
❓ Business Question
Customers who haven't ordered in the last 6 months.

✅ Solution
SELECT c.customer_id,
c.name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
HAVING MAX(o.order_date) <
DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

🧠 Case Study 4: Product Generating Highest Revenue
📊 Sales Table
product_id amount

✅ Solution
SELECT product_id,
SUM(amount) AS revenue
FROM sales
GROUP BY product_id
ORDER BY revenue DESC
LIMIT 1;

🧠 Case Study 5: Employee Salary Above Department Average

✅ Solution
WITH DeptAvg AS (
SELECT department,
AVG(salary) avg_salary
FROM employees
GROUP BY department
)

SELECT e.*
FROM employees e
JOIN DeptAvg d
ON e.department = d.department
WHERE e.salary > d.avg_salary;

🧠 Case Study 6: Running Total Sales
❓ Business Question
Calculate cumulative sales.

✅ Solution
SELECT order_date,
amount,
SUM(amount) OVER(
ORDER BY order_date
) AS running_total
FROM sales;

🧠 Case Study 7: Top 3 Products Per Category

✅ Solution
WITH RankedProducts AS (
SELECT *,
ROW_NUMBER() OVER(
PARTITION BY category
ORDER BY sales DESC
) rn
FROM products
)
SELECT *
FROM RankedProducts
WHERE rn <= 3;

🎯 Practice Tasks
1️⃣ Find customer with highest number of orders
2️⃣ Find lowest salary employee per department
3️⃣ Find products never sold
4️⃣ Find departments with average salary > company average
5️⃣ Find month with lowest sales

⚡ Mini Challenge 🔥
Banking Scenario
Tables:
Accounts
account_id customer_name

Transactions
transaction_id account_id amount transaction_date

Question
👉 Find the top 3 customers with highest total transaction amount in the last 1 year.

🔥 Interview Tip
When solving case studies:
1️⃣ Understand business question
2️⃣ Identify tables
3️⃣ Identify JOINs
4️⃣ Identify Aggregations
5️⃣ Decide whether Window Function is needed

Double Tap ❤️ For More

How to Build an Impressive Data Analysis Portfolio

As a data analyst, your portfolio is your personal brand. It showcases not only your technical skills but also your ability to solve real-world problems.

Having a strong, well-rounded portfolio can set you apart from other candidates and help you land your next job or freelance project.

Here's how to build a portfolio that will impress potential employers or clients.

1. Start with a Strong Introduction:
Before jumping into your projects, introduce yourself with a brief summary. Include your background, areas of expertise (e.g., Python, R, SQL), and any special achievements or certifications. This is your chance to give context to your portfolio and show your personality.

Tip: Make your introduction engaging and concise. Add a professional photo and link to your LinkedIn or personal website.


2. Showcase Real-World Projects:
The most powerful way to showcase your skills is through real-world projects. If you don’t have work experience yet, create your own projects using publicly available datasets (e.g., Kaggle, UCI Machine Learning Repository). These projects should highlight the full data analysis process—from data collection and cleaning to analysis and visualization.

Examples of project ideas:
- Analyzing customer data to identify purchasing trends.
- Predicting stock market trends based on historical data.
- Analyzing social media sentiment around a brand or event.


3. Focus on Impactful Data Visualizations:
Data visualization is a key part of data analysis, and it’s crucial that your portfolio highlights your ability to tell stories with data. Use tools like Tableau, Power BI, or Python (matplotlib, Seaborn) to create compelling visualizations that make complex data easy to understand.

Tips for great visuals:
- Use color wisely to highlight key insights.
- Avoid clutter; focus on clarity.
- Create interactive dashboards that allow users to explore the data.


4. Explain Your Methodology:
Employers and clients will want to know how you approached each project. For each project in your portfolio, explain the methodology you used, including:
- The problem or question you aimed to solve.
- The data sources you used.
- The tools and techniques you applied (e.g., statistical tests, machine learning models).
- The insights or results you discovered.

Make sure to document this in a clear, step-by-step manner, ideally with code snippets or screenshots.


5. Include Code and Jupyter Notebooks:
If possible, include links to your code or Jupyter Notebooks so potential employers or clients can see your technical expertise firsthand. Platforms like GitHub or GitLab are perfect for hosting your code. Make sure your code is well-commented and easy to follow.

Tip: Organize your projects in a structured way on GitHub, using descriptive README files for each project.


6. Feature a Blog or Case Studies:
If you enjoy writing, consider adding a blog or case study section to your portfolio. Writing about the data analysis process and the insights you’ve uncovered helps demonstrate your ability to communicate complex ideas in a digestible way. It also allows you to reflect on your projects and show your thought leadership in the field.

Blog post ideas:
- A breakdown of a data analysis project you’ve completed.
- Tips for aspiring data analysts.
- Reviews of tools and technologies you use regularly.

7. Continuously Update Your Portfolio:
Your portfolio is a living document. As you gain more experience and complete new projects, regularly update it to keep it fresh and relevant. Always add new skills, projects, and certifications to reflect your growth as a data analyst.

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02

Like this post for more content like this 👍♥️

Share with credits: https://t.me/sqlspecialist

Hope it helps :)

🔥 SQL Interview Case Studies & Real-World Business Problems

🧠 Case Study 1: Top 3 Customers by Revenue
📊 Orders Table
order_id customer_id amount
1 101 500
2 102 1000
3 101 700

❓ Business Question
Find the top 3 customers by total revenue.

✅ Solution
SELECT customer_id,
SUM(amount) AS total_revenue
FROM orders
GROUP BY customer_id
ORDER BY total_revenue DESC
LIMIT 3;

🧠 Case Study 2: Department with Highest Average Salary

❓ Business Question
Which department has the highest average salary?

✅ Solution
SELECT department,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 1;

🧠 Case Study 3: Customers Who Never Ordered
📊 Tables
Customers customer_id name
Orders order_id customer_id

❓ Business Question
Find customers who never placed an order.

✅ Solution
SELECT c.customer_id,
c.name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

🧠 Case Study 4: Second Highest Salary

❓ Business Question
Find employees with the second highest salary.

✅ Solution
SELECT *
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
)
);

🧠 Case Study 5: Monthly Sales Trend

❓ Business Question
Calculate monthly sales.

✅ Solution
SELECT YEAR(order_date) AS year,
MONTH(order_date) AS month,
SUM(amount) AS sales
FROM orders
GROUP BY YEAR(order_date),
MONTH(order_date)
ORDER BY year, month;

🎯 Practice Tasks
1️⃣ Find top-selling product
2️⃣ Find employee with highest salary in each department
3️⃣ Find customers with more than 5 orders
4️⃣ Find month with highest sales
5️⃣ Find departments having more than 10 employees

⚡ Mini Challenge 🔥
E-commerce Scenario

Tables:
Customers customer_id name
Orders order_id customer_id amount order_date

Business Question
Find the top 5 customers by total spending in the last 12 months.

🔥 Interview Tip
Most SQL interviews are NOT about syntax.

They're about:
✅ Understanding business problem
✅ Choosing the right approach
✅ Writing efficient SQL

Double Tap ❤️ For More

✅ SQL Aggregations with Interview Q&A 📊🧮

Aggregation functions help summarize large datasets. Combine them with GROUP BY to analyze grouped data.

1️⃣ COUNT()
Returns the number of records.

SELECT COUNT(*) FROM employees;

2️⃣ SUM()
Adds up values in a column.

SELECT dept_id, SUM(salary)  
FROM employees  
GROUP BY dept_id;

3️⃣ AVG()
Returns the average of values.

SELECT AVG(salary) FROM employees;

4️⃣ MAX() / MIN()
Returns the highest/lowest value.

SELECT MAX(salary), MIN(salary) FROM employees;

5️⃣ GROUP BY
Groups rows that have the same values in specified columns.

SELECT dept_id, COUNT(*)  
FROM employees  
GROUP BY dept_id;

6️⃣ HAVING
Filters groups after aggregation (unlike WHERE which filters rows).

SELECT dept_id, AVG(salary)  
FROM employees  
GROUP BY dept_id  
HAVING AVG(salary) > 50000;

————————

Real-World Interview Questions + Answers

Q1: What’s the difference between WHERE and HAVING?
A: WHERE filters rows before grouping. HAVING filters after aggregation.

Q2: Can you use aggregate functions without GROUP BY?
A: Yes. Without GROUP BY, the function applies to the entire table.

Q3: How do you find departments with more than 5 employees?

SELECT dept_id, COUNT(*)  
FROM employees  
GROUP BY dept_id  
HAVING COUNT(*) > 5;

Q4: Can you group by multiple columns?
A: Yes.

GROUP BY dept_id, job_title

Q5: How do you calculate total and average salary per department?

SELECT dept_id, SUM(salary), AVG(salary)  
FROM employees  
GROUP BY dept_id;

💬 Tap ❤️ for more!

🔥 Now, Let’s move to the next topic:

🔥 Dynamic SQL

Frequently Asked for Database Developer Roles 💯

🧠 1. What is Dynamic SQL?
Dynamic SQL is SQL code that is built and executed at runtime.

👉 Query is created dynamically as a string
👉 Executed later by the database

Unlike normal SQL:
SELECT _ FROM employees

Dynamic SQL:
'SELECT _ FROM employees WHERE department = ''IT'''

⚡ 2. Why Use Dynamic SQL?
✔ Flexible queries
✔ Dynamic filtering
✔ Dynamic table names
✔ Dynamic sorting

Used when query structure changes based on user input.

🔥 3. Dynamic SQL Example (MySQL)
SET @sql_query = 'SELECT * FROM employees';
PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

🔥 4. Dynamic Filter Example
SET @dept = 'IT';
SET @sql_query = CONCAT(
'SELECT * FROM employees
WHERE department = ''',
@dept,
''''
);
PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

🔥 5. Dynamic Table Name Example
SET @table_name = 'employees';
SET @sql_query = CONCAT('SELECT * FROM ', @table_name);
PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

⚠️ 6. SQL Injection Risk
Bad Example ❌
SELECT _ FROM users WHERE username = 'user_input'
Can be exploited by attackers.

✅ Safe Approach
Use parameterized queries whenever possible.
PREPARE stmt FROM 'SELECT _ FROM employees WHERE department = ?'

🎯 7. Real-World Uses
✔ Search filters
✔ Reporting systems
✔ Dynamic dashboards
✔ ETL processes
✔ Admin tools

🎯 8. Practice Tasks
1. Create dynamic SELECT query
2. Create dynamic WHERE condition
3. Create dynamic ORDER BY query
4. Query dynamic table name
5. Execute prepared statement

⚡ Mini Challenge 🔥
👉 Create a dynamic SQL query that:
Accepts a department name
Returns employees from that department
Sorts them by salary DESC

🔥 Most asked question: What is the difference between Static SQL and Dynamic SQL?

Aspect: Query
Static SQL: Fixed query
Dynamic SQL: Built at runtime

Aspect: Performance
Static SQL: Faster
Dynamic SQL: More flexible

Aspect: Complexity
Static SQL: Less complex
Dynamic SQL: More complex

Aspect: Risk
Static SQL: Lower risk
Dynamic SQL: Higher risk if not parameterized

Double Tap ❤️ For More

🔥 Now, Let’s move to the next topic:

✅ Recursive CTEs

One of the most advanced and impressive SQL topics for interviews 💯

🧠 1. What is a Recursive CTE?

A Recursive CTE is a CTE that refers to itself.

👉 Used for hierarchical or recursive data

Examples:

✔ Employee-Manager hierarchy

✔ Organization chart

✔ Folder structure

✔ Category trees

⚡ 2. Structure of Recursive CTE

A Recursive CTE has two parts:

1️⃣ Anchor Query

Starting point

2️⃣ Recursive Query

Repeats until condition is met

🔥 3. Basic Example – Generate Numbers 1 to 5

WITH RECURSIVE Numbers AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1
    FROM Numbers
    WHERE num < 5
)

SELECT * FROM Numbers;

✅ Output

num

1

2

3

4

5

🔥 4. Employee Hierarchy Example

Employees Table

emp_id | name | manager_id

1 | CEO | NULL

2 | Amit | 1

3 | Neha | 2

4 | Ravi | 2

Recursive Query

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT
        emp_id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT
        e.emp_id,
        e.name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    JOIN EmployeeHierarchy eh
        ON e.manager_id = eh.emp_id
)

SELECT * FROM EmployeeHierarchy;

🎯 5. Real-World Uses

Organizational Charts

CEO → Manager → Employee

Product Categories

Electronics → Laptop → Gaming Laptop

Folder Structures

Root → Folder → Subfolder

⚡ 6. Important Rule

Every recursive CTE needs:

✔ Anchor Query

✔ Recursive Query

✔ Stopping Condition

Without stopping condition ❌ Infinite loop

🎯 7. Practice Tasks

1. Generate numbers 1–10

2. Generate even numbers

3. Build employee hierarchy

4. Find reporting levels

5. Create category tree

⚡ Mini Challenge 🔥

👉 Generate multiplication table of 5 (5 to 50) using Recursive CTE

Example:

5

10

15

20

...

50

Most asked question:

👉 Difference between CTE and Recursive CTE?

✅ CTE = Temporary result set

✅ Recursive CTE = Temporary result set that references itself

Double Tap ❤️ For More

🔥 Now, Let’s move to the next topic:

User Defined Functions (UDFs) in SQL

🧠 1. What is a User Defined Function (UDF)?

A User Defined Function (UDF) is a custom function created by users.

👉 Accepts input parameters

👉 Performs calculations or logic

👉 Returns a single value

Think like this 👇

✔ Built-in Function → SUM(), AVG(), COUNT()

✔ User Defined Function → Created by YOU

⚡ 2. Why Use UDFs?

✔ Reuse business logic

✔ Reduce code repetition

✔ Improve readability

✔ Easier maintenance

🔥 3. Basic Function Example

👉 Function to Calculate Bonus

DELIMITER //

CREATE FUNCTION CalculateBonus(
    salary DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN salary ** 0.10;
END //

DELIMITER ;

▶️ 4. Execute Function

SELECT CalculateBonus(50000);

Output: 5000

🔥 5. Using Function in Query

SELECT
    name,
    salary,
    CalculateBonus(salary) AS bonus
FROM employees;

⚡ 6. Function with Multiple Parameters

DELIMITER //

CREATE FUNCTION TotalIncome(
    salary DECIMAL(10,2),
    bonus DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN salary + bonus;
END //

DELIMITER ;

▶️ Execute

SELECT TotalIncome(50000, 5000);

Output: 55000

🔥 7. Difference Between Function & Procedure

Feature : Function : Procedure

Must return value : Yes : May or may not return

Used inside SELECT : Yes : Called using CALL

Focus : Calculations : Actions

🎯 8. Practice Tasks

1. Create function to calculate tax

2. Create function to calculate annual salary

3. Create function to calculate total income

4. Use function inside SELECT query

5. Compare function vs procedure

⚡ Mini Challenge 🔥

👉 Create a function: EmployeeGrade(salary)

Rules:

• salary ≥ 70000 → 'A'

• salary ≥ 50000 → 'B'

• otherwise → 'C'

Then use it for all employees.

🔥 Pro Tip (Interview Gold)

Most asked interview question:

👉 When should you use Function instead of Procedure?

✅ Use Function when:

• A value must be returned

• Logic is reusable inside SELECT statements

• Calculations are needed frequently

Double Tap ❤️ For More

💫 𝗔𝗧𝗧𝗘𝗡𝗧𝗜𝗢𝗡 𝗦𝗧𝗨𝗗𝗘𝗡𝗧𝗦 & 𝗙𝗥𝗘𝗦𝗛𝗘𝗥𝗦 🔥

This could be the biggest opportunity you join in 2026!

🏆 Win from ₹50 Lakh+ Prize Pool
🎓 Open to All Students
🤖 Explore AI & Innovation
📜 Earn Recognition
💯 Registration is FREE

Imagine adding a national innovation challenge to your resume before graduation.

⚡ Registration Closes Soon

𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-

https://pdlink.in/4fFWOqX

Share with your friends, classmates, teammates & colleagues who shouldn't miss this opportunity.

🚀 Now, Let’s move to the next topic:

✅ SQL Views vs Materialized Views

🧠 1. What is a View?

A View is a virtual table created from a query.

👉 Stores only the SQL query

👉 Does NOT store actual data

👉 Always shows latest data from underlying tables

CREATE VIEW employee_view AS

SELECT * FROM employees;

🧠 2. What is a Materialized View?

A Materialized View stores the query result physically.

👉 Stores actual data

👉 Faster for reporting queries

👉 Needs refresh to get latest data

CREATE MATERIALIZED VIEW employee_summary AS

SELECT department,

AVG(salary) AS avg_salary

FROM employees

GROUP BY department;

⚡ 3. View vs Materialized View

Feature : View : Materialized View

Stores Data : ❌ No : ✅ Yes

Storage Space : Very Low : Higher

Query Speed : Slower : Faster

Real-Time Data : ✅ Yes : ❌ Needs Refresh

Best For : OLTP Systems : Reporting & Analytics

🔥 4. Example

View

SELECT * FROM employee_view;

Every execution runs the underlying query again.

Materialized View

SELECT * FROM employee_summary;

Reads precomputed data directly.

🔄 5. Refresh Materialized View

REFRESH MATERIALIZED VIEW employee_summary;

Updates stored results with latest data.

🎯 6. Real-World Usage

Views Used In:

✔ Banking Applications

✔ HR Systems

✔ Transaction Systems

Materialized Views Used In:

✔ BI Dashboards

✔ Data Warehouses

✔ Reporting Systems

🎯 7. Practice Tasks

1. Create a view for high-salary employees

2. Query data from a view

3. Create department salary summary view

4. Create materialized view for sales summary

5. Refresh materialized view

⚡ Mini Challenge 🔥

👉 Create a materialized view showing:

• department

• total employees

• average salary

Then query it to find the department with the highest average salary.

Most asked interview question:

👉 When would you choose a Materialized View over a View?

✅ Answer: "When query execution is expensive and data changes less frequently, Materialized Views improve performance significantly."

Double Tap ❤️ For More