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
- Data Types: Understand common data types like INT, VARCHAR, DATE, etc.
- Joins: Know the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.
- Indexes: Understand how indexes improve query performance and the difference between clustered and non-clustered indexes.
- Normalization: Be familiar with database normalization and the various normal forms.
- Transactions: Know how to use transactions, including COMMIT and ROLLBACK.
Common Interview Questions
- 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;
- 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;
- 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
);
- 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);
- How do you write a SQL query to find the second highest salary from a table? Answer:
You can use theLIMIT
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!