SQL Interview Cheat Sheet for Software Engineers

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;

2. 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;

3. 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
   );

4. 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);

5. 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!