Complete SQL Cheatsheet

Introduction

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. This cheatsheet covers everything from basic concepts to advanced features.

Basic Concepts

Database Fundamentals

A database is an organized collection of structured data, similar to interconnected spreadsheets. Each spreadsheet represents a table containing rows (records) and columns (fields).

Database Operations

-- Create a new database
CREATE DATABASE school;

-- Switch to a database
USE school;

-- Delete a database
DROP DATABASE school;

Table Operations

Creating Tables

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 0),
    grade DECIMAL(4,2),
    enrollment_date DATE DEFAULT CURRENT_DATE
);

Table Structure

-- View table structure
DESCRIBE students;
-- or
DESC students;

Data Manipulation

Insert Data

-- Single row insert
INSERT INTO students (first_name, last_name, age, grade)
VALUES ('John', 'Doe', 18, 85.5);

-- Multiple row insert
INSERT INTO students (first_name, last_name, age, grade) VALUES 
('Jane', 'Smith', 19, 92.5),
('Bob', 'Johnson', 20, 88.0);

Select Data

-- Basic select
SELECT * FROM students;

-- Select specific columns
SELECT first_name, last_name, grade FROM students;

-- Select with conditions
SELECT first_name, last_name, grade 
FROM students 
WHERE grade >= 90;

-- Select with multiple conditions
SELECT first_name, last_name, grade 
FROM students 
WHERE grade >= 90 AND age < 20;

Update Data

-- Update all matching records
UPDATE students 
SET grade = grade + 5 
WHERE grade < 70;

-- Update multiple columns
UPDATE students 
SET 
    grade = 95,
    last_updated = CURRENT_TIMESTAMP
WHERE id = 1;

Delete Data

-- Delete specific records
DELETE FROM students 
WHERE grade < 50;

-- Delete all records
DELETE FROM students;

Advanced Queries

Filtering with WHERE

-- Basic comparison
SELECT * FROM students WHERE age >= 18;

-- Pattern matching
SELECT * FROM students WHERE last_name LIKE 'S%';

-- Multiple conditions
SELECT * FROM students 
WHERE grade >= 90 
AND (age < 20 OR last_name LIKE 'S%');

-- IN operator
SELECT * FROM students 
WHERE age IN (18, 19, 20);

-- BETWEEN operator
SELECT * FROM students 
WHERE grade BETWEEN 80 AND 90;

Sorting and Grouping

-- Sort by one column
SELECT * FROM students 
ORDER BY grade DESC;

-- Sort by multiple columns
SELECT * FROM students 
ORDER BY grade DESC, age ASC;

-- Group by with aggregates
SELECT 
    age,
    COUNT(*) as student_count,
    AVG(grade) as average_grade
FROM students
GROUP BY age
HAVING AVG(grade) >= 80;

Joins

Types of Joins

-- INNER JOIN
SELECT 
    s.first_name,
    s.last_name,
    c.course_name,
    e.grade
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id;

-- LEFT JOIN
SELECT 
    s.first_name,
    s.last_name,
    c.course_name
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
LEFT JOIN courses c ON e.course_id = c.id;

Advanced Features

Subqueries

-- Subquery in WHERE
SELECT first_name, last_name, grade
FROM students
WHERE grade > (SELECT AVG(grade) FROM students);

-- Subquery in FROM
SELECT dept_name, avg_grade
FROM (
    SELECT d.name as dept_name, AVG(s.grade) as avg_grade
    FROM departments d
    JOIN students s ON d.id = s.dept_id
    GROUP BY d.id
) as dept_averages
WHERE avg_grade > 85;

Views

-- Create a view
CREATE VIEW honor_students AS
SELECT first_name, last_name, grade
FROM students
WHERE grade >= 90;

-- Query from view
SELECT * FROM honor_students;

Indexes

-- Create index
CREATE INDEX idx_student_name 
ON students(last_name, first_name);

-- Create unique index
CREATE UNIQUE INDEX idx_email 
ON students(email);

Data Types

Numeric Types

-- Integer types
TINYINT    -- -128 to 127
SMALLINT   -- -32,768 to 32,767
INT        -- -2^31 to 2^31-1
BIGINT     -- -2^63 to 2^63-1

-- Decimal types
DECIMAL(10,2)  -- 10 digits total, 2 after decimal
FLOAT          -- Approximate numeric
DOUBLE         -- Double precision float

String Types

-- String types
CHAR(10)       -- Fixed-length string
VARCHAR(255)   -- Variable-length string
TEXT           -- Long text
ENUM('S','M','L') -- List of possible values

Date/Time Types

-- Date and time types
DATE           -- YYYY-MM-DD
TIME           -- HH:MM:SS
DATETIME       -- YYYY-MM-DD HH:MM:SS
TIMESTAMP      -- Stores as UTC

Transactions

Basic Transactions

-- Basic transaction
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Transaction with rollback
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Something goes wrong
ROLLBACK;

Common Table Expressions (CTEs)

WITH high_performers AS (
    SELECT dept_id, AVG(grade) as avg_grade
    FROM students
    GROUP BY dept_id
    HAVING AVG(grade) > 85
)
SELECT d.name, hp.avg_grade
FROM departments d
JOIN high_performers hp ON d.id = hp.dept_id;

Window Functions

-- Row number by grade
SELECT 
    first_name,
    grade,
    ROW_NUMBER() OVER (ORDER BY grade DESC) as rank
FROM students;

-- Running total
SELECT 
    first_name,
    grade,
    SUM(grade) OVER (ORDER BY id) as running_total
FROM students;

Database Security

User Management

-- Create user
CREATE USER 'john'@'localhost' IDENTIFIED BY 'password123';

-- Grant privileges
GRANT SELECT, INSERT ON school.students TO 'john'@'localhost';

-- Revoke privileges
REVOKE INSERT ON school.students FROM 'john'@'localhost';

Performance Tips

1. Indexing Best Practices

Create indexes on frequently searched columns
Avoid over-indexing (slows down INSERT/UPDATE)
Regular index maintenance

2. Query Optimization

Select only needed columns (avoid SELECT *)
Use EXPLAIN to analyze query performance
Implement efficient WHERE clauses
Use appropriate JOINs

3. Table Design

Normalize data to reduce redundancy
Use appropriate data types
Set proper constraints
Regular database maintenance

Conclusion

This cheatsheet covers the essential aspects of SQL, from basic operations to advanced features. Remember to always consider performance implications and security best practices when working with databases.

Complete SQL Cheatsheet - Incomplete