✅ SQL JOINS — Scenario-Based Interview Questions with Answers

Scenario 1: Find customers who have never placed an order

Tables: customers(customer_id, name) orders(order_id, customer_id);

Question: Business wants a list of customers with zero orders.

Answer:

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;

Why this works
• LEFT JOIN keeps all customers
• Orders missing → NULL
• WHERE filters only non-ordering customers

Scenario 2: Get total revenue per customer, including customers with no orders

Question: Show every customer and their total spend. If no orders, show 0.

Answer:

SELECT c.customer_id, c.name, COALESCE(SUM(o.amount), 0) AS total_spend FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.name;

Explanation
• LEFT JOIN keeps all customers
• SUM aggregates orders
• COALESCE converts NULL to 0

Scenario 3: Find orders that don’t have a matching customer

Question: Audit data to find orphan orders.

Answer:

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

Explanation
• LEFT JOIN from orders
• Missing customers become NULL
• Filters invalid data

Scenario 4: Get only customers who have placed at least one order

Question: Marketing wants only active customers.

Answer:

SELECT DISTINCT c.customer_id, c.name FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id;

Explanation
• INNER JOIN keeps only matching rows
• Customers without orders are excluded

Scenario 5: Find customers with more than 3 orders

Answer:

SELECT c.customer_id, c.name, COUNT(o.order_id) AS order_count FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.name HAVING COUNT(o.order_id) > 3;

Explanation
• JOIN combines data
• GROUP BY customer
• HAVING filters aggregated count

Scenario 6: Show latest order for each customer

Answer:

SELECT c.customer_id, c.name, MAX(o.order_date) AS last_order_date FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.name;

Explanation
• JOIN connects customers and orders
• MAX finds latest order per customer

Scenario 7: Find customers who ordered in 2024 but not in 2025

Answer:

SELECT DISTINCT c.customer_id, c.name FROM customers c INNER JOIN orders o2024 ON c.customer_id = o2024.customer_id LEFT JOIN orders o2025 ON c.customer_id = o2025.customer_id AND o2025.order_date >= '2025-01-01' WHERE o2024.order_date BETWEEN '2024-01-01' AND '2024-12-31' AND o2025.customer_id IS NULL;

Explanation
• INNER JOIN ensures 2024 orders
• LEFT JOIN checks absence in 2025
• NULL filter removes 2025 buyers

Scenario 8: Employee-Manager hierarchy (SELF JOIN)

Table: employees(employee_id, name, manager_id);

Answer:

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

Explanation
• Same table joined twice
• Shows reporting structure

Scenario 9: Revenue by city

Answer:

SELECT c.city, SUM(o.amount) AS revenue FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.city;

Explanation
• JOIN links customers to orders
• GROUP BY city
• SUM calculates revenue

Scenario 10: Duplicate explosion after JOIN (classic trap)

Question: Why does this query show inflated revenue?

SELECT SUM(o.amount) FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

Answer:
• Customer table may have duplicates
• JOIN multiplies rows
• Revenue gets inflated

Fix:

SELECT SUM(amount) FROM orders; or deduplicate customers before joining.

Interview golden rule for JOINS
Always explain:
1️⃣ Which table is LEFT
2️⃣ Which table is RIGHT
3️⃣ What rows are kept
4️⃣ Where NULLs appear

Double Tap ♥️ For More

𝟯 𝗙𝗥𝗘𝗘 𝗧𝗲𝗰𝗵 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝗧𝗼 𝗘𝗻𝗿𝗼𝗹𝗹 𝗜𝗻 𝟮𝟬𝟮𝟲 😍

Upgrade your tech skills with FREE certification courses 

𝗔𝗜, 𝗚𝗲𝗻𝗔𝗜 & 𝗠𝗟 :- https://pdlink.in/4bhetTu

𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 :- https://pdlink.in/497MMLw

𝗢𝘁𝗵𝗲𝗿 𝗧𝗼𝗽 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 :- https://pdlink.in/4qgtrxU

🎓 100% FREE | Certificates Provided | Learn Anytime, Anywhere

SQL Detailed Roadmap
|
| | |-- Fundamentals
| |-- Introduction to Databases
| | |-- What SQL does
| | |-- Relational model
| | |-- Tables, rows, columns
| |-- Keys and Constraints
| | |-- Primary keys
| | |-- Foreign keys
| | |-- Unique and check constraints
| |-- Normalization
| | |-- 1NF, 2NF, 3NF
| | |-- ER diagrams

| | |-- Core SQL
| |-- SQL Basics
| | |-- SELECT, WHERE, ORDER BY
| | |-- GROUP BY and HAVING
| | |-- JOINS: INNER, LEFT, RIGHT, FULL
| |-- Intermediate SQL
| | |-- Subqueries
| | |-- CTEs
| | |-- CASE statements
| | |-- Aggregations
| |-- Advanced SQL
| | |-- Window functions
| | |-- Analytical functions
| | |-- Ranking, moving averages, lag and lead
| | |-- UNION, INTERSECT, EXCEPT

| | |-- Data Management
| |-- Data Types
| | |-- Numeric, text, date, JSON
| |-- Indexes
| | |-- B tree and hash indexes
| | |-- When to create indexes
| |-- Transactions
| | |-- ACID properties
| |-- Views
| | |-- Standard views
| | |-- Materialized views

| | |-- Database Design
| |-- Schema Design
| | |-- Star schema
| | |-- Snowflake schema
| |-- Fact and Dimension Tables
| |-- Constraints for clean data

| | |-- Performance Tuning
| |-- Query Optimization
| | |-- Execution plans
| | |-- Index usage
| | |-- Reducing scans
| |-- Partitioning
| | |-- Horizontal partitioning
| | |-- Sharding basics

| | |-- SQL for Analytics
| |-- KPI calculations
| |-- Cohort analysis
| |-- Funnel analysis
| |-- Churn and retention tables
| |-- Time based aggregations
| |-- Window functions for metrics

| | |-- SQL for Data Engineering
| |-- ETL Workflows
| | |-- Staging tables
| | |-- Transformations
| | |-- Incremental loads
| |-- Data Warehousing
| | |-- Snowflake
| | |-- Redshift
| | |-- BigQuery
| |-- dbt Basics
| | |-- Models
| | |-- Tests
| | |-- Lineage

| | |-- Tools and Platforms
| |-- PostgreSQL
| |-- MySQL
| |-- SQL Server
| |-- Oracle
| |-- SQLite
| |-- Cloud SQL
| |-- BigQuery UI
| |-- Snowflake Worksheets

| | |-- Projects
| |-- Build a sales reporting system
| |-- Create a star schema from raw CSV files
| |-- Design a customer segmentation query
| |-- Build a churn dashboard dataset
| |-- Optimize slow queries in a sample DB
| |-- Create an analytics pipeline with dbt

| | |-- Soft Skills and Career Prep
| |-- SQL interview patterns
| |-- Joins practice
| |-- Window function drills
| |-- Query writing speed
| |-- Git and GitHub
| |-- Data storytelling

| | |-- Bonus Topics
| |-- NoSQL intro
| |-- Working with JSON fields
| |-- Spatial SQL
| |-- Time series tables
| |-- CDC concepts
| |-- Real time analytics

| | |-- Community and Growth
| |-- LeetCode SQL
| |-- Kaggle datasets with SQL
| |-- GitHub projects
| |-- LinkedIn posts
| |-- Open source contributions

Free Resources to learn SQL

• W3Schools SQL
https://www.w3schools.com/sql/

• SQL Programming
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

• SQL Notes
https://whatsapp.com/channel/0029Vb6hJmM9hXFCWNtQX944

• Mode Analytics SQL tutorials
https://mode.com/sql-tutorial/

• Data Analytics Resources
https://news.1rj.ru/str/sqlspecialist

• HackerRank SQL practice
https://www.hackerrank.com/domains/sql

• LeetCode SQL problems
https://leetcode.com/problemset/database/

• Data Engineering Resources
https://whatsapp.com/channel/0029Vaovs0ZKbYMKXvKRYi3C

• Khan Academy SQL basics
https://www.khanacademy.org/computing/computer-programming/sql

• PostgreSQL official docs
https://www.postgresql.org/docs/

• MySQL official docs
https://dev.mysql.com/doc/

• NoSQL Resources
https://whatsapp.com/channel/0029VaxA2hTHgZWe5FpFjm3p

Double Tap ❤️ For More

𝗙𝗿𝗲𝘀𝗵𝗲𝗿𝘀 𝗴𝗲𝘁 𝟮𝟬 𝗟𝗣𝗔 𝗔𝘃𝗲𝗿𝗮𝗴𝗲 𝗦𝗮𝗹𝗮𝗿𝘆 𝘄𝗶𝘁𝗵 𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 & 𝗔𝗜 𝗦𝗸𝗶𝗹𝗹𝘀😍

🚀IIT Roorkee Offering Data Science & AI Certification Program

Placement Assistance With 5000+ companies.

✅ Open to everyone
✅ 100% Online | 6 Months
✅ Industry-ready curriculum
✅ Taught By IIT Roorkee Professors

🔥 90% Resumes without Data Science + AI skills are being rejected

⏳ Deadline:: 8th February 2026

𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗡𝗼𝘄 👇 :- 
 
https://pdlink.in/49UZfkX
 
✅ Limited seats only

✅ Handling NULL Values in SQL

What is NULL in SQL?
NULL means missing or unknown data.

It does NOT mean:
- 0
- Empty string ''
- False

👉 NULL = no value at all

Why NULLs exist in real data

Real business data is messy:
- Customer didn’t provide city
- Order amount not updated yet
- Employee not assigned a manager

So databases allow NULLs.

Example Table

Data: customers
customer_id: 1, name: Rahul, city: Pune, email: rahul@gmail.com
customer_id: 2, name: Neha, city: NULL, email: neha@gmail.com
customer_id: 3, name: Aman, city: Delhi, email: NULL

🚫 Biggest Beginner Mistake
❌ This is WRONG

SELECT * FROM customers WHERE city = NULL;

👉 This will return no rows

Why? Because NULL cannot be compared using = or !=

✅ Correct Way to Handle NULL
1️⃣ IS NULL: Used to find missing values

SELECT * FROM customers WHERE city IS NULL;

What this query does:
- Scans all rows
- Returns customers where city is missing

2️⃣ IS NOT NULL: Used to exclude missing values

SELECT * FROM customers WHERE email IS NOT NULL;

What this query does:
- Returns customers who have email
- Removes rows with NULL email

NULLs in JOINs (Very Important)
Customers data:
customer_id: 1, name: Rahul
customer_id: 2, name: Neha
customer_id: 3, name: Aman

Orders data:
order_id: 101, customer_id: 1, amount: 5000
order_id: 102, customer_id: 1, amount: 3000

LEFT JOIN with NULL check
👉 Find customers with NO orders

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;

What this query does:
- Keeps all customers
- Matches orders where possible
- Filters customers without orders

NULLs in Aggregations
COUNT behavior (INTERVIEW FAVORITE)

SELECT COUNT(*) FROM customers;

👉 Counts all rows

SELECT COUNT(city) FROM customers;

👉 Counts only non-NULL cities

Example

SELECT COUNT(email) FROM customers;

✔ Counts customers who have email
❌ Ignores NULL emails

Handling NULL using COALESCE

SELECT name, COALESCE(city, 'Unknown') AS city
FROM customers;

What this query does:
- If city exists → show city
- If city is NULL → show “Unknown”

NULLs in SUM / AVG

SELECT SUM(amount) FROM orders;

👉 NULL values are ignored, not treated as 0
But if all rows are NULL, result is NULL.
Safe approach:

SELECT COALESCE(SUM(amount), 0) FROM orders;

NULL vs Empty String
- NULL: No value
- '': Empty value

WHERE email IS NULL -- missing
WHERE email = '' -- empty but exists

Common NULL Mistakes (Must Avoid)
❌ Using = NULL
❌ Forgetting NULLs in LEFT JOIN
❌ Assuming COUNT(column) counts NULL
❌ Ignoring NULL replacement in reports

Interview One-Liner 💡
> NULL represents missing data and must be handled using IS NULL, IS NOT NULL, or COALESCE, not with =.

Double Tap ♥️ For More

📊 𝟭𝟬𝟬% 𝗙𝗥𝗘𝗘 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲😍

✅ Free Online Course
💡 Industry-Relevant Skills
🎓 Certification Included

Upskill now and Get Certified 🎓

𝐋𝐢𝐧𝐤 👇:- 
 
https://pdlink.in/497MMLw
 
Get the Govt. of India Incentives on course completion🏆

Here are some commonly asked SQL interview questions along with brief answers:

1. What is SQL?
- SQL stands for Structured Query Language, used for managing and manipulating relational databases.

2. What are the types of SQL commands?
- SQL commands can be broadly categorized into four types: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL).

3. What is the difference between CHAR and VARCHAR data types?
- CHAR is a fixed-length character data type, while VARCHAR is a variable-length character data type. CHAR will always occupy the same amount of storage space, while VARCHAR will only use the necessary space to store the actual data.

4. What is a primary key?
- A primary key is a column or a set of columns that uniquely identifies each row in a table. It ensures data integrity by enforcing uniqueness and can be used to establish relationships between tables.

5. What is a foreign key?
- A foreign key is a column or a set of columns in one table that refers to the primary key in another table. It establishes a relationship between two tables and ensures referential integrity.

6. What is a JOIN in SQL?
- JOIN is used to combine rows from two or more tables based on a related column between them. There are different types of JOINs, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

7. What is the difference between INNER JOIN and OUTER JOIN?
- INNER JOIN returns only the rows that have matching values in both tables, while OUTER JOIN (LEFT, RIGHT, FULL) returns all rows from one or both tables, with NULL values in columns where there is no match.

8. What is the difference between GROUP BY and ORDER BY?
- GROUP BY is used to group rows that have the same values into summary rows, typically used with aggregate functions like SUM, COUNT, AVG, etc., while ORDER BY is used to sort the result set based on one or more columns.

9. What is a subquery?
- A subquery is a query nested within another query, used to return data that will be used in the main query. Subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements.

10. What is normalization in SQL?
- Normalization is the process of organizing data in a database to reduce redundancy and dependency. It involves dividing large tables into smaller tables and defining relationships between them to improve data integrity and efficiency.

Around 90% questions will be asked from sql in data analytics interview, so please make sure to practice SQL skills using websites like stratascratch. ☺️💪

𝗧𝗼𝗽 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀 𝗢𝗳𝗳𝗲𝗿𝗲𝗱 𝗕𝘆 𝗜𝗜𝗧 𝗥𝗼𝗼𝗿𝗸𝗲𝗲, 𝗜𝗜𝗠 & 𝗠𝗜𝗧😍

Placement Assistance With 5000+ Companies 

𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝘄𝗶𝘁𝗵
𝗣𝘆𝘁𝗵𝗼𝗻 :- https://pdlink.in/4khp9E5

𝗕𝘂𝘀𝗶𝗻𝗲𝘀𝘀 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗪𝗶𝘁𝗵 𝗔𝗜 :- https://pdlink.in/4qkC4GP

𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗪𝗶𝘁𝗵 𝗔𝗜 :- https://pdlink.in/4rwqIAm

Hurry..Up👉 Only Limited Seats Available

SQL Interview Questions for 0-1 year of Experience (Asked in Top Product-Based Companies).

Sharpen your SQL skills with these real interview questions!

Q1. Customer Purchase Patterns -
You have two tables, Customers and Purchases: CREATE TABLE Customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(255) ); CREATE TABLE Purchases ( purchase_id INT PRIMARY KEY, customer_id INT, product_id INT, purchase_date DATE );
Assume necessary INSERT statements are already executed.
Write an SQL query to find the names of customers who have purchased more than 5 different products within the last month. Order the result by customer_name.

Q2. Call Log Analysis -
Suppose you have a CallLogs table: CREATE TABLE CallLogs ( log_id INT PRIMARY KEY, caller_id INT, receiver_id INT, call_start_time TIMESTAMP, call_end_time TIMESTAMP );
Assume necessary INSERT statements are already executed.
Write a query to find the average call duration per user. Include only users who have made more than 10 calls in total. Order the result by average duration descending.

Q3. Employee Project Allocation - Consider two tables, Employees and Projects:
CREATE TABLE Employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(255), department VARCHAR(255) ); CREATE TABLE Projects ( project_id INT PRIMARY KEY, lead_employee_id INT, project_name VARCHAR(255), start_date DATE, end_date DATE );
Assume necessary INSERT statements are already executed.
The goal is to write an SQL query to find the names of employees who have led more than 3 projects in the last year. The result should be ordered by the number of projects led.

🎓 𝗨𝗽𝘀𝗸𝗶𝗹𝗹 𝗪𝗶𝘁𝗵 𝗚𝗼𝘃𝗲𝗿𝗻𝗺𝗲𝗻𝘁-𝗔𝗽𝗽𝗿𝗼𝘃𝗲𝗱 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝗙𝗼𝗿 𝟭𝟬𝟬% 𝗙𝗥𝗘𝗘 😍

✅ AI & ML
✅ Cloud Computing
✅ Cybersecurity
✅ Data Analytics & Full Stack Development

Earn industry-recognized certificates and boost your career 🚀

𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:- 
 
https://pdlink.in/4qgtrxU
 
Get the Govt. of India Incentives on course completion🏆

SQL Interview Trap 🚨 Consecutive Orders Logic

You have a table:
orders

order_id | customer_id | order_date | amount

👉 Question:

Find customers who placed orders on 3 or more consecutive days,
but return only the first date of each such streak per customer.

⚠️ No temp tables.
⚠️ Assume multiple orders per day are possible.

🧠 Most candidates fail because they:

- Forget to handle multiple orders on the same day
- Misuse ROW_NUMBER()
- Miss the date gap logic

✅ Correct SQL Solution:

WITH distinct_orders AS (
SELECT DISTINCT customer_id, order_date
FROM orders
),
grp AS (
SELECT
customer_id,
order_date,
order_date - INTERVAL '1 day' *
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS grp_id
FROM distinct_orders
)
SELECT
customer_id,
MIN(order_date) AS streak_start_date
FROM grp
GROUP BY customer_id, grp_id
HAVING COUNT(*) >= 3;

💡 Why this works (Interview Gold):

- DISTINCT removes same-day duplicates
- ROW_NUMBER() creates a sequence
- Date minus row number groups consecutive dates
- HAVING COUNT(*) >= 3 filters valid streaks

🔥 React with 🔥 if this bent your brain
📌 Follow the channel for REAL interview-level SQL Content