SQL Interview Cheat Sheet for Software Engineers

Code Life
Why did the SQL query cross the road?
To join the other table!

SQL (Structured Query Language) is a critical skill for software engineers, especially when dealing with databases. This cheat sheet provides a quick reference guide and covers the most common SQL interview questions along with their answers to help you prepare effectively.


Key Concepts to Review

  1. Data Types: Understand common data types like INT, VARCHAR, DATE, etc.
  2. Joins: Know the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.
  3. Indexes: Understand how indexes improve query performance and the difference between clustered and non-clustered indexes.
  4. Normalization: Be familiar with database normalization and the various normal forms.
  5. Transactions: Know how to use transactions, including COMMIT and ROLLBACK.

Common Interview Questions

  1. What is the difference between WHERE and HAVING clauses? Answer:
  • WHERE is used to filter records before any groupings are made.
  • HAVING is used to filter records after groupings have been made.
   SELECT department, COUNT(*) 
   FROM employees 
   WHERE active = 1 
   GROUP BY department 
   HAVING COUNT(*) > 5;
  1. How do you use an INNER JOIN in SQL? Answer:
    An INNER JOIN returns records that have matching values in both tables.
   SELECT employees.name, departments.department_name 
   FROM employees 
   INNER JOIN departments ON employees.department_id = departments.id;
  1. What is a primary key and how do you create one? Answer:
    A primary key is a unique identifier for a record in a table and cannot be NULL.
   CREATE TABLE employees (
       id INT PRIMARY KEY,
       name VARCHAR(100),
       department_id INT
   );
  1. What are the different types of indexes and why are they used? Answer:
  • Clustered Index: Determines the physical order of data in a table. Only one per table.
  • Non-Clustered Index: Does not alter the physical order of the data and can be multiple per table.
   CREATE CLUSTERED INDEX idx_employee_id ON employees(id);
   CREATE NONCLUSTERED INDEX idx_employee_name ON employees(name);
  1. How do you write a SQL query to find the second highest salary from a table? Answer:
    You can use the LIMIT clause along with a subquery.
   SELECT MAX(salary) 
   FROM employees 
   WHERE salary < (SELECT MAX(salary) FROM employees);

Additional Tips

  • Practice writing queries: The best way to get comfortable with SQL is to practice writing and running queries.
  • Understand database design: Be familiar with concepts like ER diagrams, foreign keys, and relationships.
  • Performance tuning: Know how to optimize queries using indexing and query optimization techniques.
  • Learn by doing: Use real databases and try to solve practical problems.

This cheat sheet provides a concise overview of key SQL concepts and common interview questions. Practice these regularly, and you’ll be well-prepared for your SQL interviews. Lets go!