Structured Query Language (SQL) stands as the cornerstone of database management and data manipulation. Whether you’re a beginner just starting your coding journey or an experienced developer preparing for technical interviews at top tech companies, having a solid grasp of SQL is essential. This comprehensive SQL cheat sheet will serve as your quick reference guide to the most commonly used SQL commands, functions, and techniques.

Table of Contents

SQL Basics

SQL commands are generally divided into several categories:

Creating and Modifying Database Objects

Creating a Database

CREATE DATABASE database_name;

Creating a Table

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints,
    ...
);

Example of Creating a User Table

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Altering a Table

-- Adding a column
ALTER TABLE table_name ADD column_name datatype constraints;

-- Modifying a column
ALTER TABLE table_name MODIFY column_name new_datatype new_constraints;

-- Dropping a column
ALTER TABLE table_name DROP COLUMN column_name;

Dropping a Table or Database

-- Dropping a table
DROP TABLE table_name;

-- Dropping a database
DROP DATABASE database_name;

SELECT Statements and Data Retrieval

Basic SELECT Statement

SELECT column1, column2, ...
FROM table_name;

Selecting All Columns

SELECT * FROM table_name;

Selecting Distinct Values

SELECT DISTINCT column_name FROM table_name;

Using Aliases

-- Column alias
SELECT column_name AS alias_name FROM table_name;

-- Table alias
SELECT a.column_name FROM table_name AS a;

Filtering and Sorting Data

WHERE Clause

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Common Operators in WHERE Clause

-- Comparison operators
WHERE column_name = value
WHERE column_name > value
WHERE column_name < value
WHERE column_name >= value
WHERE column_name <= value
WHERE column_name <> value  -- Not equal to

-- Logical operators
WHERE condition1 AND condition2
WHERE condition1 OR condition2
WHERE NOT condition

-- BETWEEN operator
WHERE column_name BETWEEN value1 AND value2

-- IN operator
WHERE column_name IN (value1, value2, ...)

-- LIKE operator (pattern matching)
WHERE column_name LIKE 'pattern'
-- % represents zero, one, or multiple characters
-- _ represents a single character

Sorting Results with ORDER BY

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...

Limiting Results

-- MySQL, PostgreSQL, SQLite
SELECT column1, column2, ...
FROM table_name
LIMIT number;

-- SQL Server
SELECT TOP number column1, column2, ...
FROM table_name;

-- Oracle
SELECT column1, column2, ...
FROM table_name
WHERE ROWNUM <= number;

Joins and Relationships

Types of Joins

-- INNER JOIN
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

-- LEFT JOIN (LEFT OUTER JOIN)
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

-- RIGHT JOIN (RIGHT OUTER JOIN)
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

-- FULL JOIN (FULL OUTER JOIN)
SELECT columns
FROM table1
FULL JOIN table2 ON table1.column = table2.column;

-- SELF JOIN
SELECT a.column, b.column
FROM table_name AS a
JOIN table_name AS b ON a.common_column = b.common_column;

Example of Multiple Joins

SELECT o.order_id, c.customer_name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

Aggregation Functions

Common Aggregate Functions

-- COUNT
SELECT COUNT(column_name) FROM table_name;
SELECT COUNT(*) FROM table_name;

-- SUM
SELECT SUM(column_name) FROM table_name;

-- AVG
SELECT AVG(column_name) FROM table_name;

-- MIN
SELECT MIN(column_name) FROM table_name;

-- MAX
SELECT MAX(column_name) FROM table_name;

GROUP BY Clause

SELECT column1, COUNT(column2)
FROM table_name
GROUP BY column1;

HAVING Clause

SELECT column1, COUNT(column2)
FROM table_name
GROUP BY column1
HAVING COUNT(column2) > value;

Subqueries

Subquery in WHERE Clause

SELECT column_name
FROM table1
WHERE column_name IN (SELECT column_name FROM table2);

Subquery with EXISTS

SELECT column_name
FROM table1
WHERE EXISTS (SELECT column_name FROM table2 WHERE condition);

Subquery in FROM Clause

SELECT a.column
FROM (SELECT column FROM table WHERE condition) AS a;

Views

Creating a View

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Using a View

SELECT * FROM view_name;

Dropping a View

DROP VIEW view_name;

Transactions

Transaction Control

-- Start a transaction
BEGIN TRANSACTION;

-- Commit changes
COMMIT;

-- Rollback changes
ROLLBACK;

-- Create a savepoint
SAVEPOINT savepoint_name;

-- Rollback to a savepoint
ROLLBACK TO savepoint_name;

Indexes

Creating an Index

-- Simple index
CREATE INDEX index_name ON table_name (column_name);

-- Unique index
CREATE UNIQUE INDEX index_name ON table_name (column_name);

-- Composite index (multiple columns)
CREATE INDEX index_name ON table_name (column1, column2);

Dropping an Index

DROP INDEX index_name;

Advanced SQL Techniques

Common Table Expressions (CTE)

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT * FROM cte_name;

Window Functions

-- ROW_NUMBER()
SELECT column1, column2,
ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_num
FROM table_name;

-- RANK()
SELECT column1, column2,
RANK() OVER (PARTITION BY column1 ORDER BY column2) AS rank_num
FROM table_name;

-- DENSE_RANK()
SELECT column1, column2,
DENSE_RANK() OVER (PARTITION BY column1 ORDER BY column2) AS dense_rank_num
FROM table_name;

CASE Statement

SELECT column1,
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result3
END AS new_column
FROM table_name;

String Functions

-- CONCAT
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- SUBSTRING
SELECT SUBSTRING(column_name, start, length) FROM table_name;

-- UPPER and LOWER
SELECT UPPER(column_name), LOWER(column_name) FROM table_name;

Date Functions

-- Current date and time
SELECT CURRENT_DATE, CURRENT_TIMESTAMP;

-- Date formatting (varies by database system)
SELECT DATE_FORMAT(date_column, '%Y-%m-%d') FROM table_name;

-- Date arithmetic
SELECT date_column + INTERVAL 1 DAY FROM table_name;

SQL for Technical Interviews

When preparing for technical interviews at top tech companies, focus on these SQL concepts:

1. Complex Joins and Subqueries

Practice combining multiple tables and writing nested subqueries to solve complex data problems.

-- Example: Find customers who have placed more than 3 orders
SELECT c.customer_name
FROM customers c
WHERE (
    SELECT COUNT(*)
    FROM orders o
    WHERE o.customer_id = c.customer_id
) > 3;

2. Window Functions for Analytics

Window functions are commonly tested in interviews for data analysis roles.

-- Example: Calculate running total of sales
SELECT 
    order_date,
    order_amount,
    SUM(order_amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

3. Recursive Queries

Recursive CTEs are powerful for handling hierarchical data.

-- Example: Employee hierarchy
WITH RECURSIVE employee_hierarchy AS (
    -- Base case: top-level employees (no manager)
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: employees with managers
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

4. Data Aggregation Challenges

Practice complex GROUP BY scenarios combined with HAVING clauses.

-- Example: Find products that have been ordered more than average
SELECT p.product_name, COUNT(o.order_id) AS order_count
FROM products p
JOIN orders o ON p.product_id = o.product_id
GROUP BY p.product_name
HAVING COUNT(o.order_id) > (
    SELECT AVG(product_order_count)
    FROM (
        SELECT COUNT(order_id) AS product_order_count
        FROM orders
        GROUP BY product_id
    ) AS avg_orders
);

5. Optimization Questions

Be prepared to discuss how to optimize slow queries using indexes and query restructuring.

Conclusion

This SQL cheat sheet covers the essential commands and techniques you'll need for everyday database operations and technical interviews. SQL remains one of the most valuable skills for developers, data analysts, and anyone working with structured data. Regular practice with real-world scenarios will help solidify your understanding and prepare you for the complex SQL challenges often found in technical interviews at top tech companies.

Remember that different database systems (MySQL, PostgreSQL, SQL Server, Oracle, etc.) may have slight variations in syntax. Always refer to the specific documentation for your database system when needed.

As you continue to build your coding skills through platforms like AlgoCademy, incorporating SQL practice into your routine will strengthen your ability to work with data effectively. Whether you're building applications that interact with databases or preparing for coding interviews, a strong command of SQL will serve as a valuable asset throughout your programming career.