Skip to content
LunaNotes home

Complete SQL Interview Prep: 15 Must-Know Queries & Concepts

Convert to note

SQL Interview Mastery: From Basics to Advanced Queries

This comprehensive guide covers the most critical SQL interview questions, ranging from fundamental queries to complex scenarios. Each topic is explained with real-world examples and clear logic to ensure you can confidently answer any SQL question in your interview.

Core SQL Command Categories

  • DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE (manages table structure)
  • DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE (manages database content)
  • DCL (Data Control Language): GRANT, REVOKE (manages user permissions)
  • TCL (Transactional Control Language): COMMIT, ROLLBACK, SAVEPOINT (manages transactions)

Key Query Execution Order

Understanding the order of execution is crucial for writing correct SQL queries.

  1. FROM: Identifies the table(s)
  2. JOIN: Combines tables
  3. WHERE: Filters rows based on conditions
  4. GROUP BY: Groups rows sharing a property
  5. HAVING: Filters groups
  6. SELECT: Chooses columns to display
  7. ORDER BY: Sorts the final result
  8. LIMIT: Restricts the number of rows returned

Essential Single-Table Queries

Question 1: Filtering and Sorting

  • Query: Display all Engineering employees, sorted by salary from highest to lowest.
  • Solution: 
    SELECT * FROM employee WHERE department = 'Engineering' ORDER BY salary DESC;
  • Key Concepts: WHERE clause for filtering and ORDER BY ... DESC for descending sort. Omitting ASC or DESC defaults to ascending.

Question 2: Aggregation with GROUP BY

  • Query: Find the number of employees in each department.
  • Solution: 
    SELECT department, COUNT(*) AS employee_count FROM employee GROUP BY department;
  • Key Concepts: GROUP BY groups rows, and aggregate functions like COUNT summarize the data in each group. AS creates an alias for the column name.

Question 3: Filtering Groups with HAVING

  • Variation: Find departments with more than 2 employees.
  • Solution: 
    SELECT department, COUNT(*) AS employee_count FROM employee GROUP BY department HAVING COUNT(*) > 2;
  • Key Concepts: HAVING is used to filter groups, while WHERE filters rows before grouping.

Question 4: Finding Second Highest Salary

  • Query: Find the second highest distinct salary.
  • Solution: 
    SELECT MAX(salary) AS second_highest_salary FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);
  • Key Concepts: This uses a subquery to find the max salary, then finds the max salary that is less than that value. The DISTINCT keyword can ensure unique values.

Question 5: Subqueries with Aggregate Functions

  • Query: Find employees earning more than the company’s average salary.
  • Solution: 
    SELECT * FROM employee WHERE salary > (SELECT AVG(salary) FROM employee);
  • Key Concepts: A subquery calculates the average salary, and the main query filters rows based on that value.

Question 6: Max Salary per Department

  • Query: Find the highest salary in each department.
  • Solution: 
    SELECT department, MAX(salary) FROM employee GROUP BY department;
  • Key Concepts: GROUP BY combined with MAX finds the highest value for each group. Any column in SELECT must either be in GROUP BY or be an aggregate function.

Advanced Joins and Self-Joins

Question 7: Self-Join to Compare Employee and Manager Salaries

  • Query: Find employees whose salary is higher than their manager’s salary.
  • Solution: 
    SELECT e.employee_name, e.salary FROM employee e JOIN employee m ON e.manager_id = m.employee_id WHERE e.salary > m.salary;
  • Key Concepts: A self-join treats one table as two separate entities using aliases (e for employee, m for manager). This allows comparing different rows within the same table.

Question 8: INNER JOIN for Displaying Order and Customer Data

  • Query: Display each order with its customer name.
  • Solution: 
    SELECT o.order_id, c.customer_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id;
  • Key Concepts: An INNER JOIN returns only rows where there is a match in both tables based on the common column (customer_id).

Question 9: LEFT JOIN to Find Customers with No Orders

  • Query: Find customers who have never placed an order.
  • Solution: 
    SELECT c.customer_name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL;
  • Key Concepts: A LEFT JOIN will keep all rows from the left table (customers). WHERE o.order_id IS NULL identifies customers with no matching records in the orders table.

Question 10: Customers with More Than One Order

  • Query: Find customers who placed more than one order.
  • Solution: 
    SELECT c.customer_id, c.customer_name FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.customer_name HAVING COUNT(*) > 1;
  • Key Concepts: A JOIN combines the tables, GROUP BY creates groups per customer, and HAVING filters for those with a count greater than one.

Question 11: Finding the Highest Spender

  • Query: Find the customer who spent the most on delivered orders.
  • Solution: 
    SELECT c.customer_name, SUM(o.amount) AS total_spent FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE o.status = 'Delivered' GROUP BY c.customer_id, c.customer_name ORDER BY total_spent DESC LIMIT 1;
  • Key Concepts: Combines filtering (WHERE), aggregation (SUM), grouping, sorting (ORDER BY ... DESC), and limiting the output (LIMIT 1).

Data Control & Administrative Commands

Question 12: Granting SELECT-Only Permission

  • Scenario: A new intern should only be able to view the employee table, not modify it.
  • Solution: GRANT SELECT ON employee TO user_name;
  • Key Concepts: DCL commands. GRANT gives permissions, REVOKE takes them away. Permissions can be for SELECT, INSERT, UPDATE, DELETE.

Question 13: Understanding Query Execution Order

  • Problem: An alias created in the SELECT list cannot be referenced in the WHERE clause.
  • Explanation: The FROM and WHERE clauses are executed before the SELECT clause. The alias doesn't exist yet when the WHERE clause is evaluated.

Advanced SQL Functions and Scenarios

Question 14: RANK vs. DENSE_RANK vs. ROW_NUMBER

  • Scenario: A query with duplicate salaries results in different ranking behaviors.
  • Explanation:
    • ROW_NUMBER(): Assigns a unique sequential number to each row (1, 2, 3, 4).
    • RANK(): Assigns the same rank to duplicate values but skips the next rank (1, 2, 2, 4).
    • DENSE_RANK(): Assigns the same rank to duplicate values without skipping (1, 2, 2, 3).

Question 15: COUNT(), COUNT(column), and COUNT(DISTINCT)

  • Scenario: A table with duplicate and NULL values.
  • Explanation:
    • COUNT(*): Counts all rows including NULLs.
    • COUNT(column_name): Counts non-NULL values in that column.
    • COUNT(DISTINCT column_name): Counts unique, non-NULL values.

Question 16: COALESCE for Prioritizing Non-NULL Values

  • Scenario: A report needs to show the first available phone number: Primary, then Alternate, then Emergency, else "Not Available".
  • Solution: 
    SELECT COALESCE(primary_phone, alternate_phone, emergency_phone, 'Not Available') AS phone_to_use FROM contacts;
  • Key Concepts: COALESCE returns the first non-NULL value from a list of arguments.

Question 17: CASE Statement for Conditional Logic

  • Query: Classify employees into 'Low', 'Medium', and 'High' salary groups.
  • Solution: 
    SELECT employee_name, salary, CASE WHEN salary > 80 THEN 'High' WHEN salary > 50 THEN 'Medium' ELSE 'Low' END AS salary_band FROM employee;
  • Key Concepts: The CASE expression allows conditional logic, similar to if-else statements.

Question 18: Handling Division by Zero with NULLIF

  • Scenario: Calculate percentage achievement (achieved/target) while avoiding division by zero errors.
  • Solution: SELECT achieved / NULLIF(target, 0) AS percentage FROM sales;
  • Key Concepts: NULLIF returns NULL if the two expressions are equal, turning a division by zero into a NULL result instead of an error.

Advanced Window Functions and Data Cleaning

Question 19: LAG() to Find Previous Login Date

  • Query: Find each user's previous login date.
  • Solution: 
    SELECT user_id, login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS previous_login FROM logins;
  • Key Concepts: LAG() is a window function that accesses data from a previous row within the same result set. PARTITION BY divides the rows into groups (per user), and ORDER BY defines the order within each partition.

Question 20: Deleting Duplicate Records

  • Query: Delete duplicate customer records while keeping the newest one for each email.
  • Solution: 
    WITH ranked_customers AS (
    SELECT customer_id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS row_num FROM customers
)
DELETE FROM customers WHERE customer_id IN (SELECT customer_id FROM ranked_customers WHERE row_num > 1);
  • Key Concepts: A CTE (Common Table Expression) is used to create a temporary result set. The ROW_NUMBER() window function assigns a rank (1 for the newest record per email). The DELETE statement then removes all rows with a rank greater than 1.

Conclusion

Mastering these 20+ must-know SQL queries and concepts will give you a significant advantage in any interview. Practice each example, understand the underlying logic rather than memorizing, and you will be well-prepared to tackle any question that comes your way. Good luck!

Keep this summary

Save it to LunaNotes and it becomes a real note in your library — editable, searchable, and ready to turn into flashcards or a diagram. Free to start.

Save to LunaNotes

Or summarise for another video.

This summary and transcript were automatically generated using AI with the Free YouTube Transcript Summary Tool by LunaNotes.

Related summaries

SQL Job Interview Prep: What Companies Really Ask (Watch Real Calls)

SQL Job Interview Prep: What Companies Really Ask (Watch Real Calls)

Watch real SQL job interview calls to see exactly what questions companies ask and how candidates answer (and sometimes struggle). The instructor breaks down each interview question, from T-SQL constructs and indexes to SSIS and temp tables, revealing exactly what you need to know to get hired as a data professional.

Master SQL: Comprehensive Guide to Advanced Data Analytics and Optimization

Master SQL: Comprehensive Guide to Advanced Data Analytics and Optimization

Explore an extensive SQL course covering fundamentals to advanced topics including data warehousing, analytics, complex querying, performance tuning, and AI-powered coding assistance. Learn practical techniques, real-world project workflows, and best practices to excel in data engineering and analysis using SQL.

Comprehensive SQL Course: From Basics to Advanced Database Design

Comprehensive SQL Course: From Basics to Advanced Database Design

Master SQL with this full beginner-friendly course covering database fundamentals, MySQL installation, table creation, data manipulation, complex querying, joins, triggers, ER diagrams, and converting ER diagrams to schemas. Learn practical examples and advanced techniques to design and manage relational databases effectively.

Lifting the Veil Career Paths: SQL Training & Job Stacking Strategy Revealed

Lifting the Veil Career Paths: SQL Training & Job Stacking Strategy Revealed

This video reveals the new Lifting the Veil IT Academy platform, featuring structured career paths (Data Analyst, Data Engineer, AI Engineer, etc.) and job stacking strategies. The instructor discusses the TSQL-based curriculum, resume-building labs, industry secrets about certification cheating in India, and explains database normalization (1NF, 2NF, 3NF) plus the difference between star and snowflake schemas.

SQL Views Explained: Deep Dive into Use Cases, Benefits & Best Practices

SQL Views Explained: Deep Dive into Use Cases, Benefits & Best Practices

This comprehensive tutorial goes beyond basic view creation, explaining why SQL views are powerful tools for data projects. Learn how views improve reusability, security, and abstraction, and discover six critical use cases including central business logic, hiding complexity, and implementing data security.

Most viewed summaries

A Comprehensive Guide to Using Stable Diffusion Forge UI

A Comprehensive Guide to Using Stable Diffusion Forge UI

Explore the Stable Diffusion Forge UI, customizable settings, models, and more to enhance your image generation experience.

Kolonyalismo at Imperyalismo: Ang Kasaysayan ng Pagsakop sa Pilipinas

Kolonyalismo at Imperyalismo: Ang Kasaysayan ng Pagsakop sa Pilipinas

Tuklasin ang kasaysayan ng kolonyalismo at imperyalismo sa Pilipinas sa pamamagitan ni Ferdinand Magellan.

Mastering Inpainting with Stable Diffusion: Fix Mistakes and Enhance Your Images

Mastering Inpainting with Stable Diffusion: Fix Mistakes and Enhance Your Images

Learn to fix mistakes and enhance images with Stable Diffusion's inpainting features effectively.

Pamamaraan at Patakarang Kolonyal ng mga Espanyol sa Pilipinas

Pamamaraan at Patakarang Kolonyal ng mga Espanyol sa Pilipinas

Tuklasin ang mga pamamaraan at patakaran ng mga Espanyol sa Pilipinas, at ang epekto nito sa mga Pilipino.

How to Install and Configure Forge: A New Stable Diffusion Web UI

How to Install and Configure Forge: A New Stable Diffusion Web UI

Learn to install and configure the new Forge web UI for Stable Diffusion, with tips on models and settings.

Found this summary useful?

Take it with you. One click puts it in your own LunaNotes library.

Save to LunaNotes

Start taking better notes today with LunaNotes

Start for free