SQL Cheatsheet

Table of Contents

1. SQL Basics
2. Data Definition Language (DDL)
3. Data Manipulation Language (DML)
4. Data Query Language (DQL)
5. Joins
6. Aggregate Functions
7. Subqueries
8. Indexes
9. Views
10. Stored Procedures
11. Triggers
12. Transactions
13. Data Control Language (DCL)
14. Performance Optimization
15. Best Practices

SQL Basics

SQL (Structured Query Language) is a standard language for storing, manipulating, and retrieving data in databases.

SQL Data Types

Common SQL data types include:

• INT, BIGINT, SMALLINT, TINYINT
• DECIMAL, NUMERIC
• CHAR, VARCHAR, TEXT
• DATE, TIME, DATETIME, TIMESTAMP
• BOOLEAN

SQL Comments

-- This is a single line comment

/*
This is a
multi-line comment
*/

Data Definition Language (DDL)

DDL is used to define and modify the structure of database objects.

CREATE TABLE

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10, 2)
);

ALTER TABLE

-- Add a column
ALTER TABLE employees ADD email VARCHAR(100);

-- Modify a column
ALTER TABLE employees ALTER COLUMN email VARCHAR(150);

-- Drop a column
ALTER TABLE employees DROP COLUMN email;

DROP TABLE

DROP TABLE employees;

Data Manipulation Language (DML)

DML is used to manipulate data within the database.

INSERT

INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES (1, 'John', 'Doe', '2023-01-15', 50000.00);

UPDATE

UPDATE employees
SET salary = 55000.00
WHERE employee_id = 1;

DELETE

DELETE FROM employees
WHERE employee_id = 1;

Data Query Language (DQL)

DQL is used to retrieve data from the database.

SELECT

SELECT * FROM employees;

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000
ORDER BY last_name ASC;

WHERE Clause

SELECT * FROM employees
WHERE hire_date >= '2023-01-01' AND salary < 60000;

LIKE Operator

SELECT * FROM employees
WHERE last_name LIKE 'S%';

IN Operator

SELECT * FROM employees
WHERE department_id IN (1, 2, 3);

BETWEEN Operator

SELECT * FROM employees
WHERE salary BETWEEN 40000 AND 60000;

Joins

Joins are used to combine rows from two or more tables based on a related column between them.

INNER JOIN

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

LEFT JOIN

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

RIGHT JOIN

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

FULL OUTER JOIN

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single result.

Common Aggregate Functions

SELECT 
    COUNT(*) as total_employees,
    AVG(salary) as average_salary,
    MAX(salary) as highest_salary,
    MIN(salary) as lowest_salary,
    SUM(salary) as total_salary
FROM employees;

GROUP BY

SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id;

HAVING

SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

Subqueries

A subquery is a query nested inside another query.

Subquery in WHERE Clause

SELECT first_name, last_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name = 'IT'
);

Subquery in SELECT

SELECT 
    first_name, 
    last_name,
    salary,
    (SELECT AVG(salary) FROM employees) as avg_salary
FROM employees;

Indexes

Indexes are used to speed up data retrieval operations.

Create Index

CREATE INDEX idx_last_name ON employees (last_name);

Drop Index

DROP INDEX idx_last_name ON employees;

Views

A view is a virtual table based on the result of a SELECT statement.

Create View

CREATE VIEW high_salary_employees AS
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 60000;

Query View

SELECT * FROM high_salary_employees;

Stored Procedures

Stored procedures are prepared SQL statements that can be saved and reused.

Create Stored Procedure

CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentId INT
AS
BEGIN
    SELECT * FROM employees
    WHERE department_id = @DepartmentId;
END;

Execute Stored Procedure

EXEC GetEmployeesByDepartment @DepartmentId = 1;

Triggers

Triggers are special stored procedures that automatically run when certain events occur.

Create Trigger

CREATE TRIGGER trg_UpdateModifiedDate
ON employees
AFTER UPDATE
AS
BEGIN
    UPDATE employees
    SET modified_date = GETDATE()
    WHERE employee_id IN (SELECT employee_id FROM inserted);
END;

Transactions

Transactions ensure that a set of SQL operations are performed as a single unit of work.

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

IF @@ERROR = 0
    COMMIT TRANSACTION;
ELSE
    ROLLBACK TRANSACTION;

Data Control Language (DCL)

DCL is used to control access to data within the database.

GRANT

GRANT SELECT, INSERT ON employees TO user1;

REVOKE

REVOKE INSERT ON employees FROM user1;

Performance Optimization

Using EXPLAIN

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

Optimizing Queries

• Use appropriate indexes
• Avoid using SELECT * when possible
• Use JOINs instead of subqueries when appropriate
• Use LIMIT to restrict the number of rows returned

Best Practices

1. Use meaningful table and column names
2. Always use the appropriate data types
3. Normalize your database design
4. Use indexes wisely
5. Write readable and maintainable SQL code
6. Use parameters in queries to prevent SQL injection
7. Regularly backup your database
8. Keep your database and SQL server updated
9. Monitor and optimize query performance
10. Use transactions for data integrity