SQL Programming Resources

✅ Basic SQL Queries Interview Questions With Answers 🖥️

1. What does SELECT do
• SELECT fetches data from a table
• You choose columns you want to see
Example: SELECT name, salary FROM employees;

2. What does FROM do
• FROM tells SQL where data lives
• It specifies the table name
Example: SELECT * FROM customers;

3. What is WHERE clause
• WHERE filters rows
• It runs before aggregation
Example: SELECT * FROM orders WHERE status = 'Delivered';

4. Difference between WHERE and HAVING
• WHERE filters rows before GROUP BY
• HAVING filters groups after aggregation
Example: WHERE filters orders, HAVING filters total_sales

5. How do you sort data
• Use ORDER BY
• Default order is ASC
Example: SELECT * FROM employees ORDER BY salary DESC;

6. How do you sort by multiple columns
• SQL sorts left to right
Example: SELECT * FROM students ORDER BY class ASC, marks DESC;

7. What is LIMIT
• LIMIT restricts number of rows returned
• Useful for top N queries
Example: SELECT * FROM products LIMIT 5;

8. What is OFFSET
• OFFSET skips rows
• Used with LIMIT for pagination
Example: SELECT * FROM products LIMIT 5 OFFSET 10;

9. How do you filter on multiple conditions
• Use AND, OR
Example: SELECT * FROM users WHERE city = 'Delhi' AND age > 25;

10. Difference between AND and OR
• AND needs all conditions true
• OR needs one condition true

Quick interview advice
• Always say execution order: FROM → WHERE → SELECT → ORDER BY → LIMIT
• Write clean examples
• Speak logic first, syntax next¹

Double Tap ❤️ For More

❤6

2.43K viewsedited 09:54

SQL Programming Resources

✅ SQL Joins Interview Questions With Answers 🖥️

1. What is a JOIN in SQL. Explain with an example.
• JOIN combines data from multiple tables
• Tables connect using a common column
• Usually primary key to foreign key
Example tables
Customers
customer_id, name
Orders
order_id, customer_id, amount
Query
SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id;
Explanation
• SQL matches customer_id in both tables
• Output shows only related customer order data

2. What is INNER JOIN. When do you use it.
• INNER JOIN returns only matching rows
• Rows without match are removed
Example
Find customers who placed orders
Query
SELECT c.customer_id, c.name
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id;
Logic
• Customers without orders are excluded
• Only matched records appear

3. What is LEFT JOIN. Explain with use case.
• LEFT JOIN returns all rows from left table
• Matching rows from right table
• Non matches show NULL
Example
Find all customers and their orders
Query
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;
Logic
• Customers without orders still appear
• order_id becomes NULL

4. Difference between INNER JOIN and LEFT JOIN.
• INNER JOIN removes non matching rows
• LEFT JOIN keeps all left table rows
• LEFT JOIN shows NULL for missing matches
Interview tip
Explain using one missing record example

5. What is RIGHT JOIN.
• Returns all rows from right table
• Matching rows from left table
• Rarely used in real projects
Example
SELECT c.name, o.order_id
FROM customers c
RIGHT JOIN orders o
ON c.customer_id = o.customer_id;

6. What is FULL OUTER JOIN.
• Returns all rows from both tables
• Matches where possible
• Non matches show NULL
Example
SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id;
Use case
• Data reconciliation
• Comparing two datasets

7. How do you find records present in one table but not in another.
Find customers with no orders
Query
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
Logic
• LEFT JOIN keeps all customers
• WHERE filters non matched rows

8. Explain JOIN with WHERE clause. Common mistake.
• WHERE runs after JOIN
• Wrong WHERE condition breaks LEFT JOIN
Wrong
LEFT JOIN orders
WHERE orders.amount > 1000
Correct
LEFT JOIN orders
ON (link unavailable) = (link unavailable)
AND orders.amount > 1000

9. How do you join more than two tables.
• JOIN step by step
• Each JOIN needs condition
Example
SELECT c.name, o.order_id, p.product_name
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
JOIN products p
ON o.product_id = p.product_id;

10. SQL execution order for JOIN queries.
• FROM
• JOIN
• WHERE
• GROUP BY
• HAVING
• SELECT
• ORDER BY

Interview advice
• Always explain logic first
• Draw table flow in words
• Then write query

Double Tap ♥️ For More

❤9

2.43K views14:14

SQL Programming Resources

✅ SQL GROUP BY and AGGREGATION Interview Questions 🎓

1. What is GROUP BY in SQL.
• GROUP BY groups rows with same values
• Used with aggregate functions
• One row per group in output

Example

Find total salary per department

FROM employees
GROUP BY department;

Logic
• Rows grouped by department
• SUM runs on each group

2. Why do we use aggregate functions.
• To summarize data
• To calculate totals, averages, counts

Common functions
• COUNT
• SUM
• AVG
• MIN
• MAX

3. What happens if you use GROUP BY without aggregation.
• Output shows unique combinations of grouped columns

Example

FROM employees
GROUP BY department;

Logic
• Acts like DISTINCT

4. Difference between WHERE and HAVING.
• WHERE filters rows
• HAVING filters groups
• WHERE runs before GROUP BY
• HAVING runs after GROUP BY

Example
Find departments with total salary above 5,00,000

FROM employees
GROUP BY department
HAVING SUM(salary) > 500000;

5. Can you use WHERE with GROUP BY.
• Yes
• WHERE filters raw data before grouping

Example

Ignore inactive employees

FROM employees
WHERE status = 'Active'
GROUP BY department;

6. Common GROUP BY interview error.

Why does this query fail

FROM employees
GROUP BY department;

Answer
• Non aggregated column must be in GROUP BY
• name is missing

Correct query

FROM employees
GROUP BY department;

7. What's the difference between COUNT(*) COUNT(column)?
• COUNT(*) counts all rows
• COUNT(column) skips NULL values

Example

SELECT COUNT(delivery_date) FROM orders;

8. Find total orders per customer.

FROM orders
GROUP BY customer_id;

Logic
• One row per customer
• COUNT runs per customer group

9. Find customers with more than 5 orders.

FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5;

Logic
• GROUP first
• Filter groups using HAVING

10. Execution order for GROUP BY queries.
• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT
• ORDER BY

Interview advice
• Say execution order clearly
• Explain using one simple example
• Avoid mixing WHERE and HAVING logic

Double Tap ♥️ For More

❤7

2.76K views20:42

SQL Programming Resources

✅ SQL Window Functions Interview Questions with Answers ✍️

1. What are window functions in SQL?
• Window functions perform calculations across related rows
• They do not reduce rows
• Each row keeps its detail
Key syntax: OVER (PARTITION BY, ORDER BY)

2. Difference between GROUP BY and window functions
• GROUP BY collapses rows
• Window functions keep all rows
• Window functions add calculated columns

3. What is ROW_NUMBER?
• Assigns unique sequential number
• No ties allowed

Example: Rank employees by salary

SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

4. Difference between ROW_NUMBER, RANK, and DENSE_RANK
• ROW_NUMBER gives unique numbers
• RANK skips numbers on ties
• DENSE_RANK does not skip

Example salaries: 100, 100, 90

ROW_NUMBER → 1, 2, 3
RANK → 1, 1, 3
DENSE_RANK → 1, 1, 2

5. What is PARTITION BY?
• PARTITION BY splits data into groups
• Window function runs inside each group

Example: Rank employees per department

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

6. Find top 2 salaries per department

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

7. What is LAG?
• Accesses previous row value
• Used for comparisons

Example: Day over day sales

SELECT date, sales, LAG(sales) OVER (ORDER BY date) AS prev_day_sales
FROM daily_sales;

8. What is LEAD?
• Accesses next row value

Example: Compare today with next day

SELECT date, sales, LEAD(sales) OVER (ORDER BY date) AS next_day_sales
FROM daily_sales;

9. Calculate day over day growth
SELECT date, sales - LAG(sales) OVER (ORDER BY date) AS growth
FROM daily_sales;

10. Common window function interview mistakes
• Forgetting ORDER BY inside OVER
• Using WHERE instead of subquery to filter ranks
• Mixing GROUP BY with window logic incorrectly

Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → WINDOW → ORDER BY

Double Tap ♥️ For More

❤4👍2

2.75K views15:11

SQL Programming Resources

📈 Want to Excel at Data Analytics? Master These Essential Skills! ☑️

Core Concepts:
• Statistics & Probability – Understand distributions, hypothesis testing
• Excel – Pivot tables, formulas, dashboards

Programming:
• Python – NumPy, Pandas, Matplotlib, Seaborn
• R – Data analysis & visualization
• SQL – Joins, filtering, aggregation

Data Cleaning & Wrangling:
• Handle missing values, duplicates
• Normalize and transform data

Visualization:
• Power BI, Tableau – Dashboards
• Plotly, Seaborn – Python visualizations
• Data Storytelling – Present insights clearly

Advanced Analytics:
• Regression, Classification, Clustering
• Time Series Forecasting
• A/B Testing & Hypothesis Testing

ETL & Automation:
• Web Scraping – BeautifulSoup, Scrapy
• APIs – Fetch and process real-world data
• Build ETL Pipelines

Tools & Deployment:
• Jupyter Notebook / Colab
• Git & GitHub
• Cloud Platforms – AWS, GCP, Azure
• Google BigQuery, Snowflake

Hope it helps :)

❤3

2.47K views15:35

SQL Programming Resources

✅ SQL CTEs and Subqueries Interview Questions with Answers 🖥️

1. Find employees who earn more than the average salary.

Table: employees (employee_id, name, salary)

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

Logic: Inner query calculates average salary. Outer query filters employees above average.

2. Find employees who earn the highest salary in each department.

Table: employees (employee_id, name, department, salary)

SELECT name, department, salary 
FROM employees e 
WHERE salary = ( 
  SELECT MAX(salary) 
  FROM employees 
  WHERE department = e.department 
);

Logic: Subquery runs per department. Matches max salary inside same department.

3. Find departments where average salary is greater than 60,000.

SELECT department 
FROM ( 
  SELECT department, AVG(salary) AS avg_salary 
  FROM employees 
  GROUP BY department 
) t 
WHERE avg_salary > 60000;

Logic: Inner query calculates department average. Outer query filters required departments.

4. Same query using CTE.

WITH dept_avg AS ( 
  SELECT department, AVG(salary) AS avg_salary 
  FROM employees 
  GROUP BY department 
) 
SELECT department 
FROM dept_avg 
WHERE avg_salary > 60000;

Logic: CTE stores aggregated result. Final query reads from CTE.

5. Find customers who placed more orders than the average number of orders.

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

SELECT customer_id 
FROM orders 
GROUP BY customer_id 
HAVING COUNT(order_id) > ( 
  SELECT AVG(order_count) 
  FROM ( 
    SELECT COUNT(order_id) AS order_count 
    FROM orders 
    GROUP BY customer_id 
  ) x 
);

Logic: Inner query calculates orders per customer. Next level gets average. HAVING filters customers above average.

6. Find top-selling product by total sales amount.
Table: sales (product_id, amount)

SELECT product_id
FROM sales 
GROUP BY product_id 
HAVING SUM(amount) = ( 
  SELECT MAX(total_sales) 
  FROM ( 
    SELECT SUM(amount) AS total_sales 
    FROM sales 
    GROUP BY product_id 
  ) t 
);

Logic: Inner query calculates sales per product. Outer query finds max and matches it.

7. Rewrite using CTE.

WITH product_sales AS (
  SELECT product_id, SUM(amount) AS total_sales 
  FROM sales 
  GROUP BY product_id 
) 
SELECT product_id 
FROM product_sales 
WHERE total_sales = ( 
  SELECT MAX(total_sales) 
  FROM product_sales 
);

Logic: CTE avoids repeating aggregation. Cleaner and readable.

8. Find employees whose salary is greater than their department average.

WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_salary 
  FROM employees 
  GROUP BY department 
) 
SELECT e.name, e.salary 
FROM employees e 
JOIN dept_avg d ON e.department = d.department 
WHERE e.salary > d.avg_salary;

Logic: First compute department averages. Join back to employees. Filter higher earners.

Double Tap ♥️ For More

❤2

2.53K views19:27

SQL Programming Resources

✅ SQL CASE Statements and Data Cleaning Interview Questions with Answers 📊

1. Classify customers based on total spend
Table: orders (order_id, customer_id, amount)

Requirement:
- Gold if total_spend >= 100000
- Silver if total_spend between 50000 and 99999
- Bronze otherwise

Solution:

SELECT customer_id,
CASE
WHEN SUM(amount) >= 100000 THEN 'Gold'
WHEN SUM(amount) >= 50000 THEN 'Silver'
ELSE 'Bronze'
END AS customer_category
FROM orders
GROUP BY customer_id;

2. Create an age group column for users
Table: users (user_id, age)

Requirement:
- Below 18
- 18 to 30
- Above 30

Solution:

SELECT user_id,
CASE
WHEN age < 18 THEN 'Below 18'
WHEN age BETWEEN 18 AND 30 THEN '18-30'
ELSE 'Above 30'
END AS age_group
FROM users;

3. Replace NULL salary with 0
Table: employees (employee_id, salary)

Solution:

SELECT employee_id, COALESCE(salary, 0) AS salary
FROM employees;

4. Count employees with missing email IDs
Table: employees (employee_id, email)

Solution:

SELECT COUNT(*) AS missing_email_count
FROM employees
WHERE email IS NULL;

5. Remove extra spaces from customer names
Table: customers (customer_id, name)

Solution:

SELECT customer_id, TRIM(name) AS clean_name
FROM customers;

6. Extract first 3 characters from product code
Table: products (product_code)

Solution:

SELECT product_code, SUBSTRING(product_code, 1, 3) AS product_prefix
FROM products;

7. Standardize date format
Table: orders (order_date)

Requirement: Convert to YYYY-MM-DD

Solution:

SELECT CAST(order_date AS DATE) AS clean_order_date
FROM orders;

8. Mark inactive users based on last login
Table: users (user_id, last_login_date)

Requirement: Inactive if last login before 2023-01-01

Solution:

SELECT user_id,
CASE
WHEN last_login_date < '2023-01-01' THEN 'Inactive'
ELSE 'Active'
END AS user_status
FROM users;

9. Handle empty string as NULL
Table: customers (phone_number)

Solution:

SELECT NULLIF(phone_number, '') AS phone_number
FROM customers;

10. Create a clean reporting column using multiple rules
Table: transactions (amount)

Requirement:
- High if amount >= 10000
- Medium if 5000 to 9999
- Low otherwise

Solution:

SELECT amount,
CASE
WHEN amount >= 10000 THEN 'High'
WHEN amount >= 5000 THEN 'Medium'
ELSE 'Low'
END AS transaction_type
FROM transactions;

Double Tap ♥️ For More

❤8

2.56K views13:04

SQL Programming Resources

Real-world SQL Scenario based Interview Questions with Answers

Question 1: Calculate conversion rate for a sales funnel

Tables: events (user_id, event_name, event_date)

Events: signup → product_view → purchase

Requirement: Count users at each stage, compute conversion from signup to purchase

WITH stages AS (
  SELECT user_id,
    MAX(CASE WHEN event_name = 'signup' THEN 1 ELSE 0 END) AS signup,
    MAX(CASE WHEN event_name = 'product_view' THEN 1 ELSE 0 END) AS view,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS purchase
  FROM events
  GROUP BY user_id
)

SELECT 
  SUM(signup) AS signups,
  SUM(purchase) AS purchases,
  ROUND(100.0 * SUM(purchase) / SUM(signup), 2) AS conversion_rate
FROM stages;

Question 2: Build a monthly retention cohort

Tables: users (user_id, signup_date), logins (user_id, login_date)

Requirement: Cohort by signup month, show retained users by login month

WITH cohort AS (
  SELECT user_id, DATE_TRUNC('month', signup_date) AS cohort_month
  FROM users
),
activity AS (
  SELECT l.user_id, DATE_TRUNC('month', l.login_date) AS activity_month
  FROM logins l
)

SELECT 
  c.cohort_month,
  a.activity_month,
  COUNT(DISTINCT a.user_id) AS active_users
FROM cohort c
JOIN activity a ON c.user_id = a.user_id
GROUP BY c.cohort_month, a.activity_month
ORDER BY c.cohort_month, a.activity_month;

Question 3: Calculate monthly churn rate

Tables: subscriptions (user_id, start_date, end_date)

Requirement: Churned users per month, churn rate = churned / active users

WITH active_users AS (
  SELECT DATE_TRUNC('month', start_date) AS month, COUNT(DISTINCT user_id) AS active_users
  FROM subscriptions
  GROUP BY DATE_TRUNC('month', start_date)
),
churned_users AS (
  SELECT DATE_TRUNC('month', end_date) AS month, COUNT(DISTINCT user_id) AS churned_users
  FROM subscriptions
  WHERE end_date IS NOT NULL
  GROUP BY DATE_TRUNC('month', end_date)
)
SELECT 
  a.month,
  a.active_users,
  COALESCE(c.churned_users, 0) AS churned_users,
  ROUND(100.0 * COALESCE(c.churned_users, 0) / a.active_users, 2) AS churn_rate
FROM active_users a
LEFT JOIN churned_users c ON a.month = c.month
ORDER BY a.month;

Question 4: Calculate Daily Active Users

Table: user_activity (user_id, activity_date)

Requirement: DAU per day

SELECT activity_date, COUNT(DISTINCT user_id) AS dau
FROM user_activity
GROUP BY activity_date
ORDER BY activity_date;

Question 5: Revenue by marketing channel

Tables: orders (order_id, user_id, amount, order_date), users (user_id, channel)

Requirement: Total revenue per channel, monthly breakdown

SELECT 
  DATE_TRUNC('month', o.order_date) AS month,
  u.channel,
  SUM(o.amount) AS revenue
FROM orders o
JOIN users u ON o.user_id = u.user_id
GROUP BY DATE_TRUNC('month', o.order_date), u.channel
ORDER BY month, revenue DESC;

Question 6: Identify returning customers

Table: orders (order_id, customer_id, order_date)

Requirement: Customers with more than one order

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;

Question 7: Top product by revenue per month

Table: sales (product_id, amount, sale_date)

WITH monthly_sales AS (
  SELECT 
    DATE_TRUNC('month', sale_date) AS month,
    product_id,
    SUM(amount) AS revenue
  FROM sales
  GROUP BY DATE_TRUNC('month', sale_date), product_id
),
ranked AS (
  SELECT *, RANK() OVER (PARTITION BY month ORDER BY revenue DESC) AS rnk
  FROM monthly_sales
)

SELECT month, product_id, revenue
FROM ranked
WHERE rnk = 1;

Double Tap ♥️ For More

❤6

2.82K views08:06

SQL Programming Resources

✅ Complete SQL Roadmap in 2 Months

Month 1: Strong SQL Foundations
Week 1: Database and query basics
• What SQL does in analytics and business
• Tables, rows, columns
• Primary key and foreign key
• SELECT, DISTINCT
• WHERE with AND, OR, IN, BETWEEN
Outcome: You understand data structure and fetch filtered data.

Week 2: Sorting and aggregation
• ORDER BY and LIMIT
• COUNT, SUM, AVG, MIN, MAX
• GROUP BY
• HAVING vs WHERE
• Use case like total sales per product
Outcome: You summarize data clearly.

Week 3: Joins fundamentals
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• Join conditions
• Handling NULL values
Outcome: You combine multiple tables correctly.

Week 4: Joins practice and cleanup
• Duplicate rows after joins
• SELF JOIN with examples
• Data cleaning using SQL
• Daily join-based questions
Outcome: You stop making join mistakes.

Month 2: Analytics-Level SQL
Week 5: Subqueries and CTEs
• Subqueries in WHERE and SELECT
• Correlated subqueries
• Common Table Expressions
• Readability and reuse
Outcome: You write structured queries.

Week 6: Window functions
• ROW_NUMBER, RANK, DENSE_RANK
• PARTITION BY and ORDER BY
• Running totals
• Top N per category problems
Outcome: You solve advanced analytics queries.

Week 7: Date and string analysis
• Date functions for daily, monthly analysis
• Year-over-year and month-over-month logic
• String functions for text cleanup
Outcome: You handle real business datasets.

Week 8: Project and interview prep
• Build a SQL project using sales or HR data
• Write KPI queries
• Explain query logic step by step
• Daily interview questions practice
Outcome: You are SQL interview ready.

Practice platforms
• LeetCode SQL
• HackerRank SQL
• Kaggle datasets

Double Tap ♥️ For Detailed Explanation of Each Topic

❤11

2.87K views09:22

SQL Programming Resources

Glad to see the amazing response on SQL roadmap. ❤️

Today, let's start with the first topic of SQL roadmap:

✅ Introduction to SQL

SQL is the language you use to ask questions from data stored in databases. Companies store all important data in databases. Sales. Users. Payments. Inventory. When a manager asks a question, SQL pulls the answer.

What a database is
A database is an organized storage system for data. Think of it as a digital cupboard where each drawer holds related data. Each drawer is called a table.

What a table is
A table looks like an Excel sheet. It has rows and columns. Each table stores one type of data.

Example table: customers
- Columns
- customer_id
- name
- email
- city
- signup_date
- Rows
- Each row represents one customer
- One row equals one real-world record

How rows and columns work together
- Columns define what kind of data you store
- Rows hold actual values
- All rows follow the same column structure

Example row
- customer_id: 101
- name: Rahul
- email: [email protected]
- city: Pune
- signup_date: 2024-03-10

Why structure matters
- Clean structure makes data reliable
- Easy to filter, count, and analyze
- Required for accurate reporting

How SQL interacts with tables
- SQL reads data from tables
- SQL filters rows
- SQL selects columns
- SQL summarizes results

Simple SQL example
You ask the database to show names and cities of customers.

SELECT name, city FROM customers;

What happens behind the scenes
- Database scans the customers table
- Picks name and city columns
- Returns matching rows

Where you will use this daily
- Pull user lists
- Check sales numbers
- Validate data issues

Double Tap ♥️ For More

❤9

2.55K views08:57

SQL Programming Resources

Glad to see the amazing response on SQL roadmap. ❤️

Today, let's move to the next topic of SQL roadmap:

✅ Database Basics: Primary Key and Foreign Key

Why Keys Exist
Databases store millions of rows. Keys help identify and connect data correctly. Without keys, data breaks fast.

Primary Key
- A primary key uniquely identifies each row in a table
- No two rows share the same primary key
- It never stays empty

Example Table: customers
- Columns: customer_id, name, email, city
- Primary key: customer_id

Why Primary Key Matters
- Prevents duplicate records
- Helps find a row fast
- Keeps data consistent

Foreign Key
- A foreign key links one table to another
- It creates relationships between tables

Example Table: orders
- Columns: order_id, customer_id, order_date, amount
- Foreign key: customer_id

Relationship Explained
- customers.customer_id is primary key
- orders.customer_id is foreign key
- One customer has many orders

Why Foreign Keys Matter
- Enable joins
- Prevent orphan records
- Maintain data integrity

Simple Join Idea
SQL matches customer_id in both tables. This gives customer name with order amount.

Where Beginners Go Wrong
- Using names instead of IDs
- Allowing duplicate primary keys
- Ignoring missing foreign key values

Checkpoint
- You understand primary keys
- You understand foreign keys
- You know how tables connect

Double Tap ♥️ For More

❤4

2.52K viewsedited 21:52

SQL Programming Resources

SELECT name, city FROM customers;

Output
name | city
Rahul | Pune
Neha | Mumbai
Amit | Delhi
Priya | Pune

What Happens
- Database scans all rows
- Returns name and city columns
- No filtering yet

Why SELECT Matters
- Smaller output
- Faster queries
- Clear analysis

What WHERE Does
- WHERE filters rows
- It answers conditions like who, when, how much

Think Like This
- SELECT decides columns
- WHERE decides rows

Basic WHERE Example

SELECT name, city FROM customers WHERE city = 'Pune';

Output
name | city
Rahul | Pune
Priya | Pune

Common Operators
- = equal
- != not equal
- > greater than
- < less than
- > =, <=

Example

SELECT name, signup_date FROM customers WHERE signup_date >= '2024-03-01';

Output
name | signup_date
Amit | 2024-03-05
Priya | 2024-04-20

Logical Filters
AND
- All conditions must match
Example: SELECT name FROM customers WHERE city = 'Pune' AND signup_date >= '2024-01-01';

Output
name
Rahul
Priya

OR
- Any condition can match
Example: SELECT name FROM customers WHERE city = 'Pune' OR city = 'Mumbai';

Output
name
Rahul
Neha
Priya

IN
- Shortcut for multiple OR conditions
Example: SELECT name FROM customers WHERE city IN ('Pune','Mumbai','Delhi');

Output
name
Rahul
Neha
Amit
Priya

BETWEEN
- Filters within a range
- Inclusive of start and end
Example: SELECT name FROM customers WHERE signup_date BETWEEN '2024-01-01' AND '2024-03-31';

Output
name
Rahul
Neha
Amit

Filtering Numbers
Example table: orders
order_id | customer_id | amount | order_date
1 | 101 | 3000 | 2024-01-16
2 | 102 | 6000 | 2024-02-11
3 | 103 | 4000 | 2024-03-06

SELECT order_id, amount FROM orders WHERE amount > 5000;

Output
order_id | amount
2 | 6000

Filtering Text
- Text values go inside quotes
- Case sensitivity depends on database

Filtering NULL Values
- NULL means missing value
- = NULL does not work
Correct way: SELECT name FROM customers WHERE city IS NULL;

Assume city is NULL for customer_id 103
Output
name
Amit

Exclude NULL: SELECT name FROM customers WHERE city IS NOT NULL;

Output
name
Rahul
Neha
Priya

How SELECT and WHERE Work Together
- FROM picks the table
- WHERE filters rows
- SELECT picks columns
- Result is sent back

Real Business Example
- Manager asks for Pune customers with orders above 5,000
- WHERE applies city and amount filters
- SELECT shows required columns

Assume orders table has customer_id 101 with amount 6000

SELECT name FROM customers WHERE city = 'Pune' AND customer_id IN (SELECT customer_id FROM orders WHERE amount > 5000);

Output
name
Rahul

Common Beginner Mistakes
- Using WHERE before FROM
- Forgetting quotes for text
- Using = NULL
- Writing SELECT * always

Double Tap ♥️ For More

❤9

2.57K views09:58

SQL Programming Resources

Today, let's move to the next topic of SQL Roadmap:

✅ Basic SQL Queries: ORDER BY and LIMIT - Sorting and Controlling Output

Why Sorting
- Raw data has no order
- Sorted data reveals patterns
- Analysts sort data in almost every query

What ORDER BY Does
- ORDER BY sorts result rows
- Sorting happens after filtering
- Default order is ascending

Basic Syntax

SELECT column_name
FROM table_name
ORDER BY column_name;

SELECT name, city
FROM customers
ORDER BY name;

- Rows sorted alphabetically by name (A to Z)

Descending Order
- Use DESC for reverse order

SELECT name, signup_date
FROM customers
ORDER BY signup_date DESC;

Use Cases
- Latest users first
- Highest sales first
- Recent transactions on top

Sorting Numbers

SELECT order_id, amount
FROM orders
ORDER BY amount DESC;

Sorting by Multiple Columns
- First column sorts primary
- Second column breaks ties

SELECT city, signup_date
FROM customers
ORDER BY city, signup_date DESC;

LIMIT Explained
- LIMIT restricts number of rows returned
- Used to preview data
- Used to get top results

SELECT name, signup_date
FROM customers
ORDER BY signup_date DESC
LIMIT 5;

Top N Queries

SELECT name, amount
FROM orders
ORDER BY amount DESC
LIMIT 10;

Filtering + Sorting + Limiting Together
Execution order:
1. FROM
2. WHERE
3. ORDER BY
4. LIMIT

SELECT name, amount
FROM orders
WHERE amount > 5000
ORDER BY amount DESC
LIMIT 3;

Double Tap ♥️ For More

❤7

2.86K views08:11

SQL Programming Resources

Today, let's move to the next topic of SQL Roadmap:

✅ Basic SQL Queries: Aggregations and GROUP BY

• Why aggregations matter
• Raw rows hide patterns
• Businesses care about totals, averages, counts
• Aggregations turn rows into answers

• Common aggregate functions
• COUNT: counts rows
• SUM: adds values
• AVG: finds average
• MIN: finds smallest value
• MAX: finds largest value

• Examples
SELECT COUNT(*) FROM orders; -- total orders
SELECT SUM(amount) FROM orders; -- total revenue
SELECT AVG(amount) FROM orders; -- average order value
SELECT MIN(amount), MAX(amount) FROM orders; -- spend range

• GROUP BY
• Groups rows by a column
• Applies aggregation per group
• One result per group
SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id; -- total spend per customer
SELECT order_date, COUNT(*) FROM orders GROUP BY order_date; -- daily order volume

• Important rule
Every column in SELECT must be aggregated or present in GROUP BY.

• Using WHERE with GROUP BY
SELECT customer_id, SUM(amount) FROM orders
WHERE amount > 5000
GROUP BY customer_id; -- high-value orders per customer

Real business use:
• Revenue per customer
• Orders per day
• Average order value per city

SQL Roadmap: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v/1615

Double Tap ♥️ For More

❤6

2.92K views10:02

SQL Programming Resources

Today, let's move to the next topic of SQL Roadmap:

✅ HAVING vs WHERE

Why HAVING exists

- WHERE filters individual rows.
- Aggregations work on groups.
- You need HAVING to filter groups.

Key difference
- WHERE filters rows before grouping
- HAVING filters groups after aggregation

Think in order
- FROM reads table
- WHERE filters rows
- GROUP BY creates groups
- HAVING filters groups

Example table. orders
order_id | customer_id | amount | order_date

WHERE with aggregation

SELECT customer_id, SUM(amount)
FROM orders
WHERE amount > 5000
GROUP BY customer_id;

What this query does
- Removes orders below 5,000
- Groups remaining orders by customer
- Calculates total high value spend per customer

HAVING example

SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 20000;

What this query does
- Groups all orders by customer
- Calculates total spend per customer
- Keeps only customers with total spend above 20,000

WHERE vs HAVING side by side

Use WHERE when
- Filtering raw rows
- Condition does not involve aggregate functions

Use HAVING when
- Filtering aggregated results
- Condition uses SUM, COUNT, AVG

Combined WHERE and HAVING

SELECT customer_id, COUNT(*)
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
HAVING COUNT(*) >= 5;

What this query does
- Keeps orders from 2024 onward
- Groups orders by customer
- Counts orders per customer
- Returns customers with at least 5 orders

Common beginner mistakes
- Using HAVING without GROUP BY
- Using WHERE with SUM or COUNT
- Mixing WHERE and HAVING logic

Interview one-liner
WHERE filters rows. HAVING filters groups.

Double Tap ❤️ For More

❤4

2.88K views12:09

SQL Programming Resources

Please go through this top 5 SQL projects with Datasets that you can practice and can add in your resume

🚀1. Web Analytics:
(https://www.kaggle.com/zynicide/wine-reviews)

🚀2. Healthcare Data Analysis:
(https://www.kaggle.com/cdc/mortality)

📌3. E-commerce Analysis:
(https://www.kaggle.com/olistbr/brazilian-ecommerce)

🚀4. Inventory Management:
(https://www.kaggle.com/code/govindji/inventory-management)

🚀 5. Analysis of Sales Data:
(https://www.kaggle.com/kyanyoga/sample-sales-data)

Small suggestion from my side for non tech students: kindly pick those datasets which you like the subject in general, that way you will be more excited to practice it, instead of just doing it for the sake of resume, you will learn SQL more passionately, since it’s a programming language try to make it more exciting for yourself.

Hope this piece of information helps you

Join for more -> https://t.iss.one/addlist/4q2PYC0pH_VjZDk5

ENJOY LEARNING 👍👍

👍2❤1

2.74K views06:12

SQL Programming Resources

Today, let's move to the next topic of SQL Roadmap:

✅ SQL JOINS

What a JOIN is
• A JOIN combines data from two or more tables
• Tables connect using a common column
• That column is usually an ID
• JOIN answers questions one table cannot answer

Why JOINs exist
• Customer details sit in one table
• Orders sit in another table
• JOIN links customers to their orders

Example tables
customers
customer_id | name | city
orders
order_id | customer_id | amount

Connection
• customers.customer_id is primary key
• orders.customer_id is foreign key
• This shared column enables JOIN

Types of JOINs you must know
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• SELF JOIN

INNER JOIN
• Returns only matching rows from both tables
• Drops anything without a match

SELECT name, amount 
FROM customers 
INNER JOIN orders 
ON customers.customer_id = orders.customer_id;

• Matches customers with their orders
• Shows only customers who placed orders
• Removes customers with no orders
• Removes orders without customers

LEFT JOIN
• Returns all rows from left table
• Matches data from right table
• Shows NULL when no match exists

SELECT name, amount 
FROM customers 
LEFT JOIN orders 
ON customers.customer_id = orders.customer_id;

• Returns every customer
• Shows order amount if available
• Shows NULL if customer never ordered

RIGHT JOIN
• Returns all rows from right table
• Matches data from left table
• Opposite of LEFT JOIN

SELECT name, amount 
FROM customers 
RIGHT JOIN orders 
ON customers.customer_id = orders.customer_id;

• Returns all orders
• Shows customer name if exists
• Shows NULL for missing customer data

FULL JOIN
• Returns all rows from both tables
• Matches where possible
• Shows NULL when no match

SELECT name, amount 
FROM customers 
FULL JOIN orders 
ON customers.customer_id = orders.customer_id;

• Shows all customers
• Shows all orders
• Includes unmatched data from both sides

SELF JOIN
• Table joins with itself
• Used for hierarchy or comparison

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

• Matches employee with manager
• Uses same table twice
• Shows reporting hierarchy

JOIN Comparison Summary
• INNER JOIN: Only matching data
• LEFT JOIN: All left table rows
• RIGHT JOIN: All right table rows
• FULL JOIN: Everything from both tables
• SELF JOIN: Table joins itself

Double Tap ♥️ For More

❤8

3.16K views17:34

SQL Programming Resources

If you want to Excel at using the most used database language in the world, learn these powerful SQL features:

• Wildcards (%, _) – Flexible pattern matching
• Window Functions – ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG()
• Common Table Expressions (CTEs) – WITH for better readability
• Recursive Queries – Handle hierarchical data
• STRING Functions – LEFT(), RIGHT(), LEN(), TRIM(), UPPER(), LOWER()
• Date Functions – DATEDIFF(), DATEADD(), FORMAT()
• Pivot & Unpivot – Transform row data into columns
• Aggregate Functions – SUM(), AVG(), COUNT(), MIN(), MAX()
• Joins & Self Joins – Master INNER, LEFT, RIGHT, FULL, SELF JOIN
• Indexing – Speed up queries with CREATE INDEX

Like it if you need a complete tutorial on all these topics! 👍❤️

#sql

👍6❤4

2.77K views15:20

SQL Programming Resources

❤7👏2

3.02K views20:35

SQL Programming Resources

✅ 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

❤7👍1👏1

3.24K views06:38

SQL Programming Resources

❤8

3.05K views12:57

About

Blog

Apps

Platform