Here are some interview questions for both freshers and experienced applying for a data analyst #SQL

Analyst role:

#ForFreshers:
1. What is SQL, and why is it important in data analysis?
2. Explain the difference between a database and a table.
3. What are the basic SQL commands for data retrieval?
4. How do you retrieve all records from a table named "Employees"?
5. What is a primary key, and why is it important in a database?
6. What is a foreign key, and how is it used in SQL?
7. Describe the difference between SQL JOIN and SQL UNION.
8. How do you write a SQL query to find the second-highest salary in a table?
9. What is the purpose of the GROUP BY clause in SQL?
10. Can you explain the concept of normalization in SQL databases?
11. What are the common aggregate functions in SQL, and how are they used?

ForExperiencedCandidates:

1. Describe a scenario where you had to optimize a slow-running SQL query. How did you approach it?
2. Explain the differences between SQL Server, MySQL, and Oracle databases.
3. Can you describe the process of creating an index in a SQL database and its impact on query performance?
4. How do you handle data quality issues when performing data analysis with SQL?
5. What is a subquery, and when would you use it in SQL? Give an example of a complex SQL query you've written to extract specific insights from a database.
6. How do you handle NULL values in SQL, and what are the challenges associated with them?
7. Explain the ACID properties of a database and their importance.
8. What are stored procedures and triggers in SQL, and when would you use them?
9. Describe your experience with ETL (Extract, Transform, Load) processes using SQL.
10. Can you explain the concept of query optimization in SQL, and what techniques have you used for optimization?

Enjoy Learning 👍👍

✅ Complete Roadmap to Mastering SQL 🚀 🗄️

📂 1. SQL Fundamentals
– What is a database & DBMS
– Basic Syntax: SELECT, FROM, WHERE
– Data Types: INT, VARCHAR, DATE, etc.
– Operators: =, >, <, LIKE, IN
– Aliases & Comments

📂 2. Filtering & Sorting
– WHERE Clause: Advanced conditions
– ORDER BY: Sorting results
– LIMIT: Restricting rows
– DISTINCT: Unique values

📂 3. Aggregate Functions
– COUNT(), SUM(), AVG(), MIN(), MAX()
– GROUP BY: Grouping data
– HAVING: Filtering grouped data

📂 4. Joins & Relationships
– INNER JOIN: Matching rows
– LEFT/RIGHT JOIN: All rows from one table
– FULL OUTER JOIN: All rows from both tables
– Self Join: Joining a table to itself
– Subqueries: Queries within queries

📂 5. Advanced Filtering
– IN, BETWEEN, LIKE operators
– NULL values: IS NULL, IS NOT NULL
– EXISTS operator

📂 6. Subqueries & CTEs
– Subqueries in SELECT, FROM, WHERE
– Common Table Expressions (CTEs): Reusable queries

📂 7. Window Functions
– RANK(), DENSE_RANK(), ROW_NUMBER()
– LAG(), LEAD()
– OVER() clause: Defining the window
– Partitioning: PARTITION BY

📂 8. Data Manipulation
– INSERT: Adding new data
– UPDATE: Modifying existing data
– DELETE: Removing data
– MERGE: Combining data (upsert)

📂 9. Database Design
– Normalization: Reducing redundancy
– Primary & Foreign Keys: Relationships
– Data types & Constraints
– Indexing: Improving query performance

📂 10. Advanced Topics
– Stored Procedures: Precompiled SQL
– Triggers: Automatic actions
– Views: Virtual tables
– Performance Tuning: Optimizing queries
– Security: User permissions

📂 11. Practice & Projects
– Solve coding challenges on platforms like *LeetCode, HackerRank*
– Work on real-world projects using datasets from *Kaggle, Data.gov*
– Build a portfolio to showcase your SQL skills

💬 Tap ❤️ if you found this helpful!

✅ 20 Medium-Level SQL Interview Questions (with Detailed Answers)

1. Write a SQL query to find the second-highest salary.

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

2. How would you optimize a slow SQL query?

•  Use appropriate indexes.
•  Avoid SELECT * and fetch only needed columns.
•  Rewrite correlated subqueries as joins.
•  Use EXPLAIN to analyze query plan.
•  Break complex queries into CTEs.

3. What is the difference between INNER JOIN and OUTER JOIN?

•  INNER JOIN: Returns only matching rows between tables.
•  OUTER JOIN: Includes matching rows and non-matching rows with NULLs (LEFT, RIGHT, FULL).

4. Write a SQL query to find the top 3 departments with the highest average salary.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC
LIMIT 3;

5. How do you handle duplicate rows in a SQL query?

Use DISTINCT, or use ROW_NUMBER() in a CTE and filter rows:

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY name, department_id ORDER BY id) AS rn
  FROM employees
)
DELETE FROM cte WHERE rn > 1;

6. Write a SQL query to find the employees who have the same name and work in the same department.

SELECT name, department_id
FROM employees
GROUP BY name, department_id
HAVING COUNT(*) > 1;

7. What is the difference between UNION and UNION ALL?

•  UNION: Removes duplicates.
•  UNION ALL: Keeps all records including duplicates.

8. Write a SQL query to find the departments with no employees.

SELECT d.department_id, d.department_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

9. How do you use indexing to improve SQL query performance?

•  Indexes reduce the data scanned by filtering or joining.
•  Create indexes on frequently used WHERE, JOIN, and ORDER BY columns.

10. Write a SQL query to find the employees who have worked for more than 5 years.

SELECT *
FROM employees
WHERE DATEDIFF(CURDATE(), hire_date) > 5 * 365;

11. What is the difference between SUBQUERY and JOIN?

•  Subquery: Query inside another query, can be nested.
•  Join: Combines rows from multiple tables based on related columns.

12. Write a SQL query to find the top 2 products with the highest sales.

SELECT product_id, SUM(sales) AS total_sales
FROM sales_table
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 2;

13. How do you use stored procedures to improve SQL query performance?

•  Pre-compiled and cached.
•  Reduces network traffic with multiple operations in one call.
•  Enhances modularity and reusability.

14. Write a SQL query to find the customers who have placed an order but have not made a payment.

SELECT o.customer_id
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id
WHERE p.payment_id IS NULL;

15. What is the difference between GROUP BY and HAVING?

•  GROUP BY: Groups rows based on one or more columns.
•  HAVING: Filters groups, like WHERE but for aggregated data.

16. Write a SQL query to find the employees who work in the same department as their manager.

SELECT e.employee_id, e.name
FROM employees e
JOIN employees m ON e.department_id = m.department_id AND e.manager_id = m.employee_id;

17. How do you use window functions to solve complex queries?

•  Use ROW_NUMBER(), RANK(), SUM() OVER(), LAG() etc.
•  They perform calculations across rows without collapsing them.

18. Write a SQL query to find the top 3 products with the highest average price.

SELECT product_id, AVG(price) AS avg_price
FROM products
GROUP BY product_id
ORDER BY avg_price DESC
LIMIT 3;

19. What is the difference between TRUNCATE and DELETE?

•  TRUNCATE: Removes all rows, faster, can’t rollback in most DBs.
•  DELETE: Removes selected rows, supports WHERE, and rollback.

20. Write a SQL query to find the employees who have not taken any leave in the last 6 months.

SELECT e.*
FROM employees e
LEFT JOIN leaves l ON e.employee_id = l.employee_id AND l.leave_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
WHERE l.leave_id IS NULL;

📌 Essential SQL Commands & Functions Cheatsheet 🧑💻

Whether beginner or prepping for data roles, mastering these essentials helps a lot! 💡

⬇️ *Quick SQL reference:*

1) SELECT – Retrieve data
2) WHERE – Filter rows by condition
3) GROUP BY – Aggregate by column(s)
4) HAVING – Filter aggregated groups
5) ORDER BY – Sort results
6) JOIN – Combine tables
7) UNION – Merge query results
8) INSERT INTO – Add new records
9) UPDATE – Modify records
10) DELETE – Remove records
11) CREATE TABLE – Make a new table
12) ALTER TABLE – Modify table structure
13) DROP TABLE – Delete a table
14) TRUNCATE TABLE – Remove all rows
15) DISTINCT – Get unique values
16) LIMIT – Restrict result count
17) IN / BETWEEN – Filter by multiple values/ranges
18) LIKE – Pattern match
19) IS NULL – Filter NULLs
20) COUNT()/SUM()/AVG() – Aggregate functions

✅ Save & save time in your next SQL task! 😉

✅ 50 Must-Know SQL Concepts for Interviews 🧠📊

📍 Core Concepts
1. SELECT, FROM, WHERE
2. ORDER BY, LIMIT
3. AND, OR, NOT, IN, BETWEEN
4. NULL & IS NULL
5. DISTINCT

📍 Aggregations
6. COUNT(), SUM(), AVG()
7. MIN(), MAX()
8. GROUP BY
9. HAVING

📍 Joins
10. INNER JOIN
11. LEFT JOIN
12. RIGHT JOIN
13. FULL OUTER JOIN
14. SELF JOIN
15. CROSS JOIN

📍 Subqueries & CTEs
16. Subquery in SELECT
17. Subquery in WHERE
18. WITH (Common Table Expression)
19. Correlated Subqueries

📍 Window Functions
20. ROW_NUMBER()
21. RANK(), DENSE_RANK()
22. LEAD(), LAG()
23. OVER(), PARTITION BY

📍 String & Date Functions
24. CONCAT(), LENGTH(), UPPER()/LOWER()
25. SUBSTRING(), REPLACE()
26. DATE(), NOW(), DATEDIFF()

📍 Advanced Features
27. CASE WHEN
28. IF() & IFNULL()
29. COALESCE()
30. Nested Queries
31. Temporary Tables
32. Views
33. Stored Procedures (Basic)
34. Indexes & Performance
35. Transactions (BEGIN, COMMIT, ROLLBACK)
36. Primary & Foreign Keys

📍 Analytics Use Cases
37. Cohort Analysis
38. Retention Metrics
39. Funnel Analysis
40. Rolling Averages
41. AB Testing Query Logic

📍 Best Practices
42. Aliasing (AS)
43. Use EXPLAIN to check query plan
44. Avoid SELECT \* in production
45. Proper indexing
46. Normalize vs Denormalize

📍 Real-Life Scenarios
47. Querying large datasets
48. Optimizing slow queries
49. Joining multiple large tables
50. Writing reusable queries

💡 Tap ❤️ for more!

The Only SQL You Actually Need For Your First Job (Data Analytics)

The Learning Trap: What Most Beginners Fall Into

When starting out, it's common to feel like you need to master every possible SQL concept. You binge YouTube videos, tutorials, and courses, yet still feel lost in interviews or when given a real dataset.

Common traps:

- Complex subqueries

- Advanced CTEs

- Recursive queries

- 100+ tutorials watched

- 0 practical experience


Reality Check: What You'll Actually Use 75% of the Time

Most data analytics roles (especially entry-level) require clarity, speed, and confidence with core SQL operations. Here’s what covers most daily work:

1. SELECT, FROM, WHERE — The Foundation

SELECT name, age
FROM employees
WHERE department = 'Finance';

This is how almost every query begins. Whether exploring a dataset or building a dashboard, these are always in use.

2. JOINs — Combining Data From Multiple Tables

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

You’ll often join tables like employee data with department, customer orders with payments, etc.

3. GROUP BY — Summarizing Data

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Used to get summaries by categories like sales per region or users by plan.

4. ORDER BY — Sorting Results

SELECT name, salary
FROM employees
ORDER BY salary DESC;

Helps sort output for dashboards or reports.

5. Aggregations — Simple But Powerful

Common functions: COUNT(), SUM(), AVG(), MIN(), MAX()

SELECT AVG(salary)
FROM employees
WHERE department = 'IT';

Gives quick insights like average deal size or total revenue.

6. ROW_NUMBER() — Adding Row Logic

SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date DESC) as rn
FROM orders
) sub
WHERE rn = 1;

Used for deduplication, rankings, or selecting the latest record per group.

Credits: https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02

React ❤️ for more

𝗦𝗤𝗟 𝗠𝘂𝘀𝘁-𝗞𝗻𝗼𝘄 𝗗𝗶𝗳𝗳𝗲𝗿𝗲𝗻𝗰𝗲𝘀 📊

Whether you're writing daily queries or preparing for interviews, understanding these subtle SQL differences can make a big impact on both performance and accuracy.

🧠 Here’s a powerful visual that compares the most commonly misunderstood SQL concepts — side by side.

📌 𝗖𝗼𝘃𝗲𝗿𝗲𝗱 𝗶𝗻 𝘁𝗵𝗶𝘀 𝘀𝗻𝗮𝗽𝘀𝗵𝗼𝘁:
🔹 RANK() vs DENSE_RANK()
🔹 HAVING vs WHERE
🔹 UNION vs UNION ALL
🔹 JOIN vs UNION
🔹 CTE vs TEMP TABLE
🔹 SUBQUERY vs CTE
🔹 ISNULL vs COALESCE
🔹 DELETE vs DROP
🔹 INTERSECT vs INNER JOIN
🔹 EXCEPT vs NOT IN

React ♥️ for detailed explanation

📉 20 High-Income Skills to Learn...

✅ 50 Must-Know SQL Concepts for Interviews 💾📊

📍 SQL Basics
1. What is SQL
2. Types of SQL commands (DDL, DML, DCL, TCL)
3. Database vs Table
4. Primary Key & Foreign Key
5. NULL vs NOT NULL

📍 Data Retrieval
6. SELECT statement
7. WHERE clause
8. DISTINCT keyword
9. ORDER BY clause
10. LIMIT / TOP

📍 Filtering & Conditions
11. LIKE, BETWEEN, IN
12. IS NULL & IS NOT NULL
13. AND, OR, NOT
14. CASE statements
15. Aliases using AS

📍 Joins (Very Important)
16. INNER JOIN
17. LEFT JOIN
18. RIGHT JOIN
19. FULL OUTER JOIN
20. SELF JOIN

📍 Aggregation & Grouping
21. COUNT(), SUM(), AVG()
22. MIN(), MAX()
23. GROUP BY clause
24. HAVING vs WHERE
25. GROUP BY with multiple columns

📍 Subqueries & Nested Queries
26. Scalar subqueries
27. Correlated subqueries
28. IN vs EXISTS
29. Using subqueries in SELECT
30. CTE (Common Table Expressions)

📍 Modifying Data
31. INSERT INTO
32. UPDATE
33. DELETE
34. UPSERT (MERGE)
35. TRUNCATE vs DELETE

📍 Constraints & Indexing
36. UNIQUE constraint
37. CHECK constraint
38. DEFAULT values
39. Indexes – clustered vs non-clustered
40. AUTO_INCREMENT / SERIAL

📍 Views & Stored Procedures
41. Creating and using Views
42. Stored Procedures basics
43. Functions vs Procedures
44. Triggers
45. Transactions (COMMIT, ROLLBACK)

📍 Optimization & Tools
46. EXPLAIN or Query Plan
47. Avoiding SELECT *
48. Normalization
49. Denormalization
50. Popular SQL platforms (MySQL, PostgreSQL, MS SQL Server)

SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

💬 Tap ❤️ if you found this helpful!

✅ Top 10 SQL Interview Questions

1️⃣ What is SQL and its types? 
SQL (Structured Query Language) is used to manage and manipulate databases. 
Types: DDL, DML, DCL, TCL 
Example: CREATE, SELECT, GRANT, COMMIT

2️⃣ Explain SQL constraints. 
Constraints ensure data integrity:
⦁ PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK

3️⃣ What is normalization? 
It's organizing data to reduce redundancy and improve integrity (1NF, 2NF, 3NF…).

4️⃣ Explain different types of JOINs with example.
⦁ INNER JOIN: Returns matching rows
⦁ LEFT JOIN: All from left + matching right rows
⦁ RIGHT JOIN: All from right + matching left rows
⦁ FULL JOIN: All rows from both tables

5️⃣ What is a subquery? Give example. 
A query inside another query:

SELECT name FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name='Sales');

6️⃣ How to optimize slow queries? 
Use indexes, avoid SELECT *, use joins wisely, reduce nested queries.

7️⃣ What are aggregate functions? List examples. 
Functions that perform a calculation on a set of values: 
SUM(), COUNT(), AVG(), MIN(), MAX()

8️⃣ Explain SQL injection and prevention. 
A security vulnerability to manipulate queries. Prevent via parameterized queries, input validation.

9️⃣ How to find Nth highest salary without TOP/LIMIT?

SELECT DISTINCT salary FROM employees e1
WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary > e1.salary);

🔟 What is a stored procedure? 
A precompiled SQL program that can be executed to perform operations repeatedly.

🔥 React for more! ❤️

SQL Cheatsheet 📝

This SQL cheatsheet is designed to be your quick reference guide for SQL programming. Whether you’re a beginner learning how to query databases or an experienced developer looking for a handy resource, this cheatsheet covers essential SQL topics.

1. Database Basics
- CREATE DATABASE db_name;
- USE db_name;

2. Tables
- Create Table: CREATE TABLE table_name (col1 datatype, col2 datatype);
- Drop Table: DROP TABLE table_name;
- Alter Table: ALTER TABLE table_name ADD column_name datatype;

3. Insert Data
- INSERT INTO table_name (col1, col2) VALUES (val1, val2);

4. Select Queries
- Basic Select: SELECT * FROM table_name;
- Select Specific Columns: SELECT col1, col2 FROM table_name;
- Select with Condition: SELECT * FROM table_name WHERE condition;

5. Update Data
- UPDATE table_name SET col1 = value1 WHERE condition;

6. Delete Data
- DELETE FROM table_name WHERE condition;

7. Joins
- Inner Join: SELECT * FROM table1 INNER JOIN table2 ON table1.col = table2.col;
- Left Join: SELECT * FROM table1 LEFT JOIN table2 ON table1.col = table2.col;
- Right Join: SELECT * FROM table1 RIGHT JOIN table2 ON table1.col = table2.col;

8. Aggregations
- Count: SELECT COUNT(*) FROM table_name;
- Sum: SELECT SUM(col) FROM table_name;
- Group By: SELECT col, COUNT(*) FROM table_name GROUP BY col;

9. Sorting & Limiting
- Order By: SELECT * FROM table_name ORDER BY col ASC|DESC;
- Limit Results: SELECT * FROM table_name LIMIT n;

10. Indexes
- Create Index: CREATE INDEX idx_name ON table_name (col);
- Drop Index: DROP INDEX idx_name;

11. Subqueries
- SELECT * FROM table_name WHERE col IN (SELECT col FROM other_table);

12. Views
- Create View: CREATE VIEW view_name AS SELECT * FROM table_name;
- Drop View: DROP VIEW view_name;

✅Top 10 SQL Interview Questions 🔥

1️⃣ What is a table and a field in SQL?
⦁ Table: Organized data in rows and columns
⦁ Field: A column representing data attribute

2️⃣ Describe the SELECT statement.
⦁ Fetch data from one or more tables
⦁ Use WHERE to filter, ORDER BY to sort

3️⃣ Explain SQL constraints.
⦁ Rules for data integrity: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK

4️⃣ What is normalization?
⦁ Process to reduce data redundancy & improve integrity (1NF, 2NF, 3NF…)

5️⃣ Explain different JOIN types with examples.
⦁ INNER, LEFT, RIGHT, FULL JOIN: Various ways to combine tables based on matching rows

6️⃣ What is a subquery? Give example.
⦁ Query inside another query:

SELECT name FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name='Sales');

7️⃣ How to optimize slow queries?
⦁ Use indexes, avoid SELECT *, simplify joins, reduce nested queries

8️⃣ What are aggregate functions? Examples?
⦁ Perform calculations on sets: SUM(), COUNT(), AVG(), MIN(), MAX()

9️⃣ What is SQL injection? How to prevent it?
⦁ Security risk manipulating queries
⦁ Prevent: parameterized queries, input validation

🔟 How to find the Nth highest salary without TOP/LIMIT?

SELECT DISTINCT salary FROM employees e1
WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary > e1.salary);

🔥 Double Tap ❤️ For More!

SQL From Basic to Advanced level

Basic SQL is ONLY 7 commands:
- SELECT
- FROM
- WHERE (also use SQL comparison operators such as =, <=, >=, <> etc.)
- ORDER BY
- Aggregate functions such as SUM, AVERAGE, COUNT etc.
- GROUP BY
- CREATE, INSERT, DELETE, etc.
You can do all this in just one morning.

Once you know these, take the next step and learn commands like:
- LEFT JOIN
- INNER JOIN
- LIKE
- IN
- CASE WHEN
- HAVING (undertstand how it's different from GROUP BY)
- UNION ALL
This should take another day.

Once both basic and intermediate are done, start learning more advanced SQL concepts such as:
- Subqueries (when to use subqueries vs CTE?)
- CTEs (WITH AS)
- Stored Procedures
- Triggers
- Window functions (LEAD, LAG, PARTITION BY, RANK, DENSE RANK)
These can be done in a couple of days.
Learning these concepts is NOT hard at all

- what takes time is practice and knowing what command to use when. How do you master that?
- First, create a basic SQL project
- Then, work on an intermediate SQL project (search online) -

Lastly, create something advanced on SQL with many CTEs, subqueries, stored procedures and triggers etc.

This is ALL you need to become a badass in SQL, and trust me when I say this, it is not rocket science. It's just logic.

Remember that practice is the key here. It will be more clear and perfect with the continous practice

Best telegram channel to learn SQL: https://news.1rj.ru/str/sqlanalyst

Data Analyst Jobs👇
https://news.1rj.ru/str/jobs_SQL

Join @free4unow_backup for more free resources.

Like this post if it helps 😄❤️

ENJOY LEARNING 👍👍

✅ Advanced SQL Interview Questions with Answers 💼🧠

1️⃣ What are Window Functions in SQL?
Answer: They perform calculations across rows related to the current row without collapsing the result set.
Example:

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;

2️⃣ Difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
– RANK(): skips numbers for ties
– DENSE_RANK(): doesn't skip numbers
– ROW_NUMBER(): gives unique numbers to each row
Use: Sorting, pagination, leaderboard design

3️⃣ What is the use of COALESCE()?
Answer: Returns the first non-null value in a list.
Example:

SELECT name, COALESCE(nickname, 'No Nick') FROM users;

4️⃣ How does CASE work in SQL?
Answer: It's used for conditional logic.
Example:

SELECT name, 
  CASE 
    WHEN score >= 90 THEN 'A' 
    WHEN score >= 80 THEN 'B' 
    ELSE 'C' 
  END AS grade 
FROM students;

5️⃣ Explain CTE (Common Table Expression).
Answer: Temporary result set for readable and reusable queries.
Example:

WITH TopSales AS (
  SELECT emp_id, SUM(sales) AS total_sales 
  FROM sales 
  GROUP BY emp_id
)  
SELECT * FROM TopSales WHERE total_sales > 5000;

6️⃣ What is the difference between EXISTS and IN?
– EXISTS: stops after finding the first match (more efficient)
– IN: compares a list of values
Use EXISTS for subqueries when checking existence

7️⃣ What are Indexes in SQL?
Answer: They improve read performance but slow down write operations.
– Types: Single-column, Composite, Unique

8️⃣ How to optimize SQL queries?
– Use proper indexes
– Avoid SELECT *
– Use WHERE filters early
– Analyze query plan

💬 Double Tap ❤️ For More!

✅ Advanced SQL Practice Questions with Answers 🧠📝

1️⃣ Get the second highest salary from the employees table.

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

2️⃣ List employees who earn more than the average salary.

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

3️⃣ Show department-wise highest paid employee.

SELECT department, name, salary  
FROM (
  SELECT *,  
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk  
  FROM employees
) AS ranked  
WHERE rnk = 1;

4️⃣ Display total sales made by each employee in 2023.

SELECT emp_id, SUM(amount) AS total_sales  
FROM sales  
WHERE YEAR(sale_date) = 2023  
GROUP BY emp_id;

5️⃣ Retrieve products with price above average in their category.

SELECT p.name, p.category, p.price  
FROM products p  
WHERE price > (
  SELECT AVG(price)  
  FROM products  
  WHERE category = p.category
);

6️⃣ Identify duplicate emails in the users table.

SELECT email, COUNT(*)  
FROM users  
GROUP BY email  
HAVING COUNT(*) > 1;

7️⃣ Rank customers based on total purchase amount.

SELECT customer_id,
SUM(amount) AS total_spent,  
       RANK() OVER (ORDER BY SUM(amount) DESC) AS rank  
FROM orders  
GROUP BY customer_id;

💬 Double Tap ❤️ For More!

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: You have 2 minutes to solve this SQL query.
Retrieve the department name and the highest salary in each department from the employees table, but only for departments where the highest salary is greater than $70,000.

𝗠𝗲: Challenge accepted!

SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department
HAVING MAX(salary) > 70000;

I used GROUP BY to group employees by department, MAX() to get the highest salary, and HAVING to filter the result based on the condition that the highest salary exceeds $70,000. This solution effectively shows my understanding of aggregation functions and how to apply conditions on the result of those aggregations.

𝗧𝗶𝗽 𝗳𝗼𝗿 𝗦𝗤𝗟 𝗝𝗼𝗯 𝗦𝗲𝗲𝗸𝗲𝗿𝘀:
It's not about writing complex queries; it's about writing clean, efficient, and scalable code. Focus on mastering subqueries, joins, and aggregation functions to stand out!

React with ❤️ for more

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: Write a query to get the names of all employees along with their department names. If an employee is not assigned to a department, still include them.

𝗠𝗲: Here’s my SQL solution using a LEFT JOIN:

SELECT e.name AS employee_name, d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

✔ Why it works:
– LEFT JOIN ensures all employees are shown, even those without departments.
– It joins the employees and departments tables on department_id.
– Clean, readable, and interview-ready!

🔎 Bonus Insight:
Always understand the difference between INNER JOIN, LEFT JOIN, and RIGHT JOIN. In real-world databases, missing data is common — use joins wisely to handle it.

💬 Tap ❤️ if this helped you!

✅ SQL Mini-Challenge! 🔍💻

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: List all employees and their managers. If an employee doesn’t have a manager, still include them.

𝗠𝗲: Using a self-join with LEFT JOIN:

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

✔ Why it works:
– LEFT JOIN ensures employees without managers are still included.
– Self-join allows referencing the same table for managers.
– Simple and clean solution for interviews.

🔎 Bonus Tip:
Always consider null values in joins; LEFT JOIN helps preserve the main table rows even if the related data is missing.

💬 Tap ❤️ if this helped you!

✅ SQL Scenario-Based Question & Answer 💻📊

Scenario:
You have two tables:

Employees

| emp_id | name  | dept_id | salary |
| ------ | ----- | ------- | ------ |
| 1      | John  | 10      | 5000   |
| 2      | Alice | 20      | 6000   |
| 3      | Bob   | 10      | 4500   |
| 4      | Mary  | NULL    | 7000   |

Departments

| dept_id | dept_name |
| ------- | --------- |
| 10      | Sales     |
| 20      | Marketing |
| 30      | HR        |

Question:
Write a query to display all employees with their department name. If an employee doesn't belong to any department, show "Not Assigned" instead.

Answer:

SELECT e.name AS employee_name,
       COALESCE(d.dept_name, 'Not Assigned') AS department_name
FROM Employees e
LEFT JOIN Departments d
ON e.dept_id = d.dept_id;

Explanation:
⦁ LEFT JOIN ensures all employees are included, even those without a matching dept_id (like Mary).
⦁ COALESCE picks the first non-NULL value: dept_name if available, otherwise "Not Assigned".
⦁ This handles NULLs gracefully while keeping the full employee list intact.

Result:

| employee_name | department_name |
| ------------- | --------------- |
| John          | Sales           |
| Alice         | Marketing       |
| Bob           | Sales           |
| Mary          | Not Assigned    |

💬 Double Tap ❤️ if this helped you!

✅ SQL Interview Challenge! 🧠💻

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: Retrieve all employees along with their department names. Only include employees who belong to a department.

𝗠𝗲: Using INNER JOIN:

SELECT e.name AS employee_name,
       d.dept_name AS department_name
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id;

✔ Why it works:
– INNER JOIN returns only rows with matching values in both tables based on the ON condition, excluding employees without a dept_id match (or vice versa).
– This ensures we get complete, valid pairs—perfect for reports focusing on assigned staff, and it's efficient for large datasets in 2025's analytics tools.

💬 Tap ❤️ if this helped you!

✅ SQL Interview Challenge! 📊🧠

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: Find the average salary of employees in each department.

𝗠𝗲: Using GROUP BY and AVG():
SELECT d.dept_name,
AVG(e.salary) AS avg_salary
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

✔ Why it works:
– AVG() computes the mean salary per group, handling decimals nicely.
– GROUP BY clusters rows by department name for department-level aggregates.
– INNER JOIN links employee salaries to department names only for matching records—add ORDER BY avg_salary DESC for ranked insights!

💬 Tap ❤️ for more!