Skip to main content

SQL Cheatsheet

This page is a quick reference for commonly used SQL commands and database operations. Whether you're preparing for interviews, learning DBMS, or building backend applications, this cheatsheet covers the essentials.

Database Operations​

Create Database​

CREATE DATABASE company_db;

Use Database​

USE company_db;

Delete Database​

DROP DATABASE company_db;

Table Operations​

Create Table​

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    joining_date DATE
);

View Table Structure​

DESC employees;

Rename Table​

RENAME TABLE employees TO staff;

Delete Table​

DROP TABLE employees;

Insert Data​

Insert Single Row​

INSERT INTO employees
VALUES (1, 'John Doe', 'IT', 50000, '2025-01-15');

Insert Multiple Rows​

INSERT INTO employees
VALUES
(2, 'Alice', 'HR', 45000, '2025-02-10'),
(3, 'Bob', 'Finance', 55000, '2025-03-05');

Select Queries​

Select All Data​

SELECT * FROM employees;

Select Specific Columns​

SELECT name, salary
FROM employees;

Distinct Values​

SELECT DISTINCT department
FROM employees;

Filtering Data​

WHERE Clause​

SELECT *
FROM employees
WHERE salary > 50000;

AND Operator​

SELECT *
FROM employees
WHERE department = 'IT'
AND salary > 40000;

OR Operator​

SELECT *
FROM employees
WHERE department = 'IT'
OR department = 'HR';

BETWEEN​

SELECT *
FROM employees
WHERE salary BETWEEN 40000 AND 60000;

IN​

SELECT *
FROM employees
WHERE department IN ('IT', 'HR');

LIKE​

SELECT *
FROM employees
WHERE name LIKE 'A%';

Wildcards​

  • 'A%' → Starts with A
  • '%A' → Ends with A
  • '%A%' → Contains A
  • '_A%' → Second character is A

Sorting Data​

Ascending Order​

SELECT *
FROM employees
ORDER BY salary ASC;

Descending Order​

SELECT *
FROM employees
ORDER BY salary DESC;

Aggregate Functions​

COUNT​

SELECT COUNT(*) AS total_employees
FROM employees;

SUM​

SELECT SUM(salary)
FROM employees;

AVG​

SELECT AVG(salary)
FROM employees;

MAX​

SELECT MAX(salary)
FROM employees;

MIN​

SELECT MIN(salary)
FROM employees;

GROUP BY​

Department-wise Employee Count​

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

Department-wise Average Salary​

SELECT department,
       AVG(salary)
FROM employees
GROUP BY department;

HAVING Clause​

SELECT department,
       COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

Update Data​

Update Record​

UPDATE employees
SET salary = 60000
WHERE id = 1;

Delete Data​

Delete Specific Row​

DELETE FROM employees
WHERE id = 1;

Delete All Rows​

DELETE FROM employees;

ALTER TABLE​

Add Column​

ALTER TABLE employees
ADD email VARCHAR(100);

Modify Column​

ALTER TABLE employees
MODIFY salary DECIMAL(12,2);

Rename Column​

ALTER TABLE employees
RENAME COLUMN name TO employee_name;

Drop Column​

ALTER TABLE employees
DROP COLUMN email;

SQL Constraints​

Common Constraints​

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT CHECK(age >= 18)
);

Constraint Types​

PRIMARY KEY
FOREIGN KEY
UNIQUE
NOT NULL
CHECK
DEFAULT

Joins​

INNER JOIN​

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

LEFT JOIN​

SELECT *
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id;

RIGHT JOIN​

SELECT *
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.id;

FULL OUTER JOIN​

SELECT *
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.id;

Subqueries​

Above Average Salary​

SELECT *
FROM employees
WHERE salary >
(
    SELECT AVG(salary)
    FROM employees
);

Views​

Create View​

CREATE VIEW high_salary_employees AS
SELECT *
FROM employees
WHERE salary > 50000;

Use View​

SELECT * FROM high_salary_employees;

Indexes​

Create Index​

CREATE INDEX idx_employee_name
ON employees(name);

Remove Index​

DROP INDEX idx_employee_name ON employees;

Transactions​

Commit Transaction​

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;

COMMIT;

Rollback Transaction​

ROLLBACK;

Common Interview Queries​

Second Highest Salary​

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

Find Duplicate Records​

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

Delete Duplicate Records​

DELETE e1
FROM employees e1
INNER JOIN employees e2
ON e1.name = e2.name
AND e1.id > e2.id;

Top 5 Highest Salaries​

SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 5;

SQL Execution Order​

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

References​