SQL Cheat Sheet for Interview Preparation

Preparing for an SQL interview? This SQL Cheat Sheet is your ultimate guide to quickly revise key concepts, syntax, and examples. Whether you’re a beginner or an experienced programmer, this cheat sheet will help you ace your technical interviews with confidence.

---

Why Use This SQL Cheat Sheet?

• Quick Revision: Covers all essential SQL topics in one place.
• Easy to Understand: Clear examples and explanations.
• Interview Ready: Focuses on concepts frequently asked in interviews.
• Practice Material: Includes examples to help you practice and test your knowledge.

---

SQL Cheat Sheet: Key Concepts, Syntax, and Examples

Below is a structured cheat sheet with SQL concepts, syntax, and examples. Use this to revise and practice before your interview.

---

1. Basic SQL Commands

Concept	Description	Example
SELECT	Retrieve data from a table.	SELECT * FROM employees;
WHERE	Filter records based on a condition.	SELECT * FROM employees WHERE salary > 50000;
ORDER BY	Sort the result set in ascending/descending order.	SELECT * FROM employees ORDER BY salary DESC;
LIMIT	Limit the number of records returned.	SELECT * FROM employees LIMIT 10;

---

2. Aggregation Functions

Concept	Description	Example
COUNT	Count the number of rows.	SELECT COUNT(*) FROM employees;
SUM	Calculate the sum of a numeric column.	SELECT SUM(salary) FROM employees;
AVG	Calculate the average of a numeric column.	SELECT AVG(salary) FROM employees;
MIN	Find the minimum value in a column.	SELECT MIN(salary) FROM employees;
MAX	Find the maximum value in a column.	SELECT MAX(salary) FROM employees;

---

3. Grouping Data

Concept	Description	Example
GROUP BY	Group rows that have the same values.	SELECT department, COUNT(*) FROM employees GROUP BY department;
HAVING	Filter groups based on a condition.	SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;

---

4. Joins

Concept	Description	Example
INNER JOIN	Returns records with matching values in both tables.	SELECT employees.name, departments.name FROM employees INNER JOIN departments ON employees.dept_id = departments.id;
LEFT JOIN	Returns all records from the left table and matched records from the right table.	SELECT employees.name, departments.name FROM employees LEFT JOIN departments ON employees.dept_id = departments.id;
RIGHT JOIN	Returns all records from the right table and matched records from the left table.	SELECT employees.name, departments.name FROM employees RIGHT JOIN departments ON employees.dept_id = departments.id;
FULL JOIN	Returns all records when there is a match in either left or right table.	SELECT employees.name, departments.name FROM employees FULL JOIN departments ON employees.dept_id = departments.id;

---

5. Subqueries

Concept	Description	Example
Subquery	A query nested inside another query.	SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

---

6. Modifying Data

Concept	Description	Example
INSERT	Insert new records into a table.	INSERT INTO employees (name, salary) VALUES ('Alice', 60000);
UPDATE	Modify existing records in a table.	UPDATE employees SET salary = 65000 WHERE id = 1;
DELETE	Delete records from a table.	DELETE FROM employees WHERE id = 1;

---

7. Table Operations

Concept	Description	Example
CREATE TABLE	Create a new table.	CREATE TABLE employees (id INT, name VARCHAR(100), salary DECIMAL);
ALTER TABLE	Modify an existing table.	ALTER TABLE employees ADD COLUMN department VARCHAR(100);
DROP TABLE	Delete an existing table.	DROP TABLE employees;

---

8. Constraints

Concept	Description	Example
PRIMARY KEY	Uniquely identifies each record in a table.	CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(100));
FOREIGN KEY	Ensures referential integrity.	CREATE TABLE orders (id INT, employee_id INT, FOREIGN KEY (employee_id) REFERENCES employees(id));
NOT NULL	Ensures a column cannot have NULL values.	CREATE TABLE employees (id INT, name VARCHAR(100) NOT NULL);
UNIQUE	Ensures all values in a column are unique.	CREATE TABLE employees (id INT, email VARCHAR(100) UNIQUE);

---

more examples as below :

1. SQL Basics

-- Creating a Database
CREATE DATABASE my_database;

-- Using a Database
USE my_database;

-- Creating a Table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department VARCHAR(50)
);

-- Inserting Data
INSERT INTO employees (id, name, age, department)
VALUES (1, 'Alice', 30, 'HR');

-- Selecting Data
SELECT * FROM employees;
SELECT name, age FROM employees WHERE department = 'HR';

2. SQL Operators

-- Comparison Operators
SELECT * FROM employees WHERE age > 25;
SELECT * FROM employees WHERE age BETWEEN 25 AND 35;

-- Logical Operators
SELECT * FROM employees WHERE department = 'HR' AND age > 25;
SELECT * FROM employees WHERE department = 'HR' OR age > 25;
SELECT * FROM employees WHERE NOT department = 'HR';

3. SQL Joins

-- INNER JOIN
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department = departments.id;

-- LEFT JOIN
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department = departments.id;

-- RIGHT JOIN
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department = departments.id;

-- FULL JOIN (Not supported in all databases)
SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department = departments.id;

4. Aggregation Functions

-- COUNT, SUM, AVG, MIN, MAX
SELECT COUNT(*) FROM employees;
SELECT AVG(age) FROM employees;
SELECT MIN(age), MAX(age) FROM employees;

5. Group By & Having

-- Grouping Data
SELECT department, COUNT(*)
FROM employees
GROUP BY department;

-- Using HAVING to filter grouped data
SELECT department, AVG(age)
FROM employees
GROUP BY department
HAVING AVG(age) > 30;

6. Subqueries

-- Subquery to find employees older than the average age
SELECT * FROM employees
WHERE age > (SELECT AVG(age) FROM employees);

7. Indexing

-- Creating an Index
CREATE INDEX idx_department ON employees(department);

8. Stored Procedures

-- Creating a Stored Procedure
DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
    SELECT * FROM employees;
END //
DELIMITER ;

-- Calling a Stored Procedure
CALL GetAllEmployees();

9. Transactions

-- Using Transactions
START TRANSACTION;
UPDATE employees SET age = 35 WHERE name = 'Alice';
ROLLBACK;  -- Undo changes
COMMIT;    -- Save changes

10. Common SQL Queries

-- Finding Duplicates
SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

-- Deleting Duplicate Records (Keeping the Latest)
DELETE FROM employees
WHERE id NOT IN (
    SELECT MIN(id)
    FROM employees
    GROUP BY name
);

I. Core Concepts

• Relational Databases: Organized into tables with rows (records) and columns (attributes).
• SQL (Structured Query Language): Used to interact with relational databases.
• Tables: Consist of rows and columns. Each column has a data type (e.g., INT, VARCHAR, DATE).
• Primary Key: Uniquely identifies each row in a table.
• Foreign Key: A column in one table that refers to the primary key of another table, establishing a link between the tables.
• Relationships: Define how tables are related (one-to-one, one-to-many, many-to-many).

II. Basic Syntax and Examples

A. Basic SELECT Statement

SQL

SELECT column1, column2, ...
FROM table_name;

-- Select all columns
SELECT *
FROM table_name;

-- Using WHERE clause to filter data
SELECT *
FROM employees
WHERE department = 'Sales';

-- Using ORDER BY to sort results
SELECT *
FROM employees
ORDER BY salary DESC;  -- Descending order

-- Using DISTINCT to get unique values
SELECT DISTINCT department
FROM employees;

B. WHERE Clause and Operators

SQL

-- Comparison operators: =, !=, >, <, >=, <=
SELECT * FROM products WHERE price > 100;

-- Logical operators: AND, OR, NOT
SELECT * FROM employees WHERE department = 'Sales' AND salary > 60000;

-- BETWEEN operator
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31';

-- LIKE operator (for pattern matching)
SELECT * FROM customers WHERE name LIKE 'J%';  -- Starts with J
SELECT * FROM customers WHERE name LIKE '%son'; -- Ends with son
SELECT * FROM customers WHERE name LIKE '%John%'; -- Contains John

-- IN operator
SELECT * FROM products WHERE category IN ('Electronics', 'Clothing');

-- NULL values
SELECT * FROM employees WHERE commission IS NULL;
SELECT * FROM employees WHERE commission IS NOT NULL;

C. Aggregate Functions

SQL

-- COUNT, SUM, AVG, MIN, MAX
SELECT COUNT(*) FROM employees;
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;

-- GROUP BY clause (used with aggregate functions)
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

-- HAVING clause (filters groups)
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;

D. JOINs

SQL

-- INNER JOIN (returns rows only when there's a match in both tables)
SELECT orders.order_id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

-- LEFT JOIN (returns all rows from the left table and matching rows from the right table)
SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

-- RIGHT JOIN (returns all rows from the right table and matching rows from the left table)
SELECT customers.name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

-- FULL OUTER JOIN (returns all rows from both tables)
-- (Not supported by all SQL databases)
SELECT customers.name, orders.order_id
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;

E. Subqueries

SQL

-- Subquery in WHERE clause
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery in SELECT clause
SELECT employee_id, (SELECT COUNT(*) FROM orders WHERE customer_id = employees.employee_id) AS order_count
FROM employees;

F. Data Manipulation Language (DML)

SQL

-- INSERT
INSERT INTO employees (name, department, salary)
VALUES ('John Doe', 'Marketing', 50000);

-- UPDATE
UPDATE employees
SET salary = 60000
WHERE employee_id = 1;

-- DELETE
DELETE FROM employees
WHERE employee_id = 1;

G. Data Definition Language (DDL)

SQL

-- CREATE TABLE
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(255)
);

-- ALTER TABLE (add, modify, or drop columns)
ALTER TABLE employees
ADD COLUMN hire_date DATE;

-- DROP TABLE
DROP TABLE departments;

H. Other Important Concepts

• Indexes: Improve query performance.
• Views: Virtual tables based on the result-set of an SQL statement.
• Transactions: Ensure data consistency (ACID properties).
• Stored Procedures: Pre-compiled SQL code that can be reused.

III. Tips for Interviews

• Practice: Write lots of SQL queries.
• Understand the data: Know the table structure and relationships.
• Explain your queries: Be able to articulate why you wrote a query a certain way.
• Optimize queries: Think about how to make queries more efficient.
• Know your database: Different databases (MySQL, PostgreSQL, SQL Server, Oracle) have slightly different syntax and features.

This cheat sheet is a good starting point. Focus on practicing and understanding the core concepts for interview success!

Frequently asked SQL interview questions:

---

Basic SQL

1. Write a query to find the second highest salary in a table.

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

Alternatively, you can use LIMIT and OFFSET:

   SELECT salary
   FROM employees
   ORDER BY salary DESC
   LIMIT 1 OFFSET 1;

2. How do you remove duplicate rows from a table? Use a DELETE statement with a CTE (Common Table Expression) or a subquery to identify and delete duplicates:

   WITH CTE AS (
       SELECT id, ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) AS rn
       FROM employees
   )
   DELETE FROM employees
   WHERE id IN (SELECT id FROM CTE WHERE rn > 1);

Alternatively, you can use DISTINCT to create a new table without duplicates:

   CREATE TABLE new_table AS
   SELECT DISTINCT * FROM old_table;

---

Joins

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

• INNER JOIN: Returns only the rows that have matching values in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the matching rows from the right table. If no match is found, NULL values are returned for columns from the right table. Example:

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

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

4. Write a query to find employees who do not belong to any department. Use a LEFT JOIN and filter for NULL values in the department table:

   SELECT e.name
   FROM employees e
   LEFT JOIN departments d ON e.department_id = d.id
   WHERE d.id IS NULL;

---

Aggregation

5. Write a query to find the average salary by department. Use GROUP BY to calculate the average salary for each department:

   SELECT d.department_name, AVG(e.salary) AS average_salary
   FROM employees e
   JOIN departments d ON e.department_id = d.id
   GROUP BY d.department_name;

6. How do you count the number of employees in each department? Use COUNT with GROUP BY:

   SELECT d.department_name, COUNT(e.id) AS employee_count
   FROM employees e
   JOIN departments d ON e.department_id = d.id
   GROUP BY d.department_name;

---

Subqueries

7. Write a query to find employees whose salary is above the average salary. Use a subquery to calculate the average salary and compare it in the WHERE clause:

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

---

Conclusion

This SQL Cheat Sheet is your go-to resource for interview preparation. It covers all the essential SQL concepts, syntax, and examples in an easy-to-understand format. Use it to revise, practice, and ace your SQL interviews with confidence.

---

* ALL THE BEST ***
Visit JaganInfo youtube channel for more valuable content https://www.youtube.com/@jaganinfo