Skip to main content

SQL Cheatsheet

pragya0129
EditReport

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โ€‹

Telemetry Integration

Completed working through this block? Sync progress to workspace.