This comprehensive SQL cheat sheet uses a practical sales analytics scenario to demonstrate key database operations. We'll work with two interconnected tables: the "rep" table containing sales representative information (name, ID, and state), and the "daily_sales" table tracking individual transactions with corresponding rep IDs and revenue amounts. This real-world example mirrors the data structures you'll encounter in modern business intelligence and analytics workflows.
Syntax
Master these fundamental SQL commands to manipulate data efficiently and extract meaningful insights from your databases.
SQL Command Categories
Data Definition Language (DDL)
Commands like CREATE TABLE and ALTER TABLE that define database structure and schema modifications.
Data Manipulation Language (DML)
Commands including SELECT, INSERT, UPDATE, and DELETE for working with data within tables.
Data Query Language (DQL)
Advanced SELECT operations with joins, aggregations, and filtering for complex data retrieval.
Working with Tables
CREATE TABLE
Create a new table with defined columns and data types
CREATE TABLE table_name (
column_1 datatype,
column_2 datatype,
column_3 datatype);
DELETE
Remove specific records from a table based on defined criteria
DELETE FROM table_name
WHERE column_name = xyz;
ALTER TABLE
Modify existing table structure by adding new columns
ALTER TABLE table_name
ADD column_name datatype;
AS
Create column aliases for cleaner output and improved readability
SELECT column_name AS 'Alias'
FROM table_name;
INSERT
Add new records to an existing table
INSERT INTO table_name (column_1, column_2, column_3)
VALUES (value_1, 'value_2', value_3);
LIMIT
Restrict query results to a specified number of rows for performance optimization
SELECT column_name(s)
FROM table_name
LIMIT number;
UPDATE
Modify existing records based on specified conditions
UPDATE table_name
SET some_column = some_value
WHERE some_column = some_value;
Essential Table Operations
Use the AS keyword to rename columns for better readability in query results. This is especially useful when working with calculated fields or joining multiple tables.
LOGIC
These logical operators and conditions enable sophisticated data filtering and conditional processing essential for business analytics.
BETWEEN
Filter records within a specific range of values
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value_1 AND value_2;
CASE WHEN
Implement conditional logic to create calculated columns based on multiple criteria
SELECT column_name,
CASE
WHEN condition THEN 'Result_1'
WHEN condition THEN 'Result_2'
ELSE 'Result_3'
END AS new_column
FROM table_name;
GROUP BY
Aggregate data by one or more columns, essential for creating summary reports
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;
HAVING
Apply filters to grouped data after aggregation (use instead of WHERE with aggregate functions)
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > value;
IS NULL
Identify records with missing or undefined values
SELECT column_name(s)
FROM table_name
WHERE column_name IS NULL;
LIKE
Perform pattern matching with wildcards for flexible text searches
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE 'xyz';
AND
Combine multiple conditions that must all be true
SELECT column_name
FROM table_name
WHERE column_1 = xyz
AND column_2 = abc;
OR
Select records that meet any one of multiple conditions
SELECT column_name
FROM table_name
WHERE column_name = value_1
OR column_name = value_2;
DISTINCT
Eliminate duplicate values to return only unique records
SELECT DISTINCT column_name
FROM table_name;
Conditional Operators Comparison
| Feature | AND | OR |
|---|---|---|
| Logic Type | All conditions must be true | At least one condition must be true |
| Use Case | Narrow down results | Broaden search criteria |
| Performance | More selective, faster | Less selective, potentially slower |
Building Complex Conditions
Start with Basic WHERE
Begin with simple equality conditions to establish your base dataset
Add Range Filters
Use BETWEEN for date ranges or numerical boundaries to further refine results
Apply Pattern Matching
Implement LIKE with wildcards for flexible text searching capabilities
Combine with Logic Operators
Use AND/OR to create sophisticated multi-criteria filtering conditions
MATH
Statistical and mathematical functions are crucial for data analysis, reporting, and generating business insights from raw data.
COUNT
Calculate the total number of records or non-null values
SELECT COUNT(column_name)
FROM table_name;
MAX
Find the highest value in a numeric or date column
SELECT MAX(column_name)
FROM table_name;
MIN
Identify the lowest value in a numeric or date column
SELECT MIN(column_name)
FROM table_name;
ORDER BY
Sort query results in ascending or descending order for better data presentation
SELECT column_name
FROM table_name
ORDER BY column_name ASC | DESC;
ROUND
Format numeric values to a specified number of decimal places
SELECT ROUND(column_name, integer)
FROM table_name;
SUM
Calculate the total of all values in a numeric column
SELECT SUM(column_name)
FROM table_name;
AVG
Compute the arithmetic mean of values in a numeric column
SELECT AVG(column_name)
FROM table_name;
SQL Aggregate Functions
COUNT
Returns the number of rows that match specified criteria. Essential for data validation and reporting.
SUM & AVG
Calculate totals and averages for numerical data. Critical for financial and statistical analysis.
MAX & MIN
Find extreme values in datasets. Useful for identifying outliers and boundary conditions.
Sorting operations can be resource-intensive on large datasets. Consider adding indexes on frequently sorted columns to improve query performance.
JOINS
Master these join operations to combine data from multiple tables and create comprehensive analytical reports.
INNER JOIN
Combine tables showing only records with matching values in both tables
SELECT column_name(s)
FROM table_1
JOIN table_2
ON table_1.column_name = table_2.column_name;
OUTER JOIN
Combine tables retaining all records from one table plus matching records from another
SELECT column_name(s)
FROM table_1
LEFT JOIN table_2
ON table_1.column_name = table_2.column_name;
JOIN Types Comparison
| Feature | INNER JOIN | LEFT JOIN |
|---|---|---|
| Data Returned | Only matching records | All left table records |
| Missing Data Handling | Excludes non-matches | Shows NULL for missing |
| Best For | Complete data analysis | Comprehensive reporting |
Always ensure proper indexes exist on join columns. Joining large tables without indexes can severely impact query performance and database responsiveness.
Applications
Apply these SQL concepts to solve real business problems using our sales database example. These practical queries demonstrate how to extract actionable insights from your data.
The following examples demonstrate practical applications using sales representative and daily sales data, showing how SQL commands work together in business scenarios.
Find the Name of Rep 555
SELECT name
FROM rep
WHERE ID = '555';
SELECT name FROM rep WHERE ID = '555';Find the Maximum Revenue in One Day
SELECT MAX(revenue)
FROM daily_sales;
SELECT MAX(revenue) FROM daily_sales;Get Total Revenue by Day
SELECT date, SUM(revenue)
FROM daily_sales
GROUP BY date
ORDER BY date;
Revenue by State and by Day
SELECT a.state, b.date, SUM(revenue)
FROM rep a
JOIN daily_sales b
ON a.ID = b.ID
GROUP BY state, date;
SELECT a.state, b.date, sum(revenue) FROM rep a JOIN daily_sales b ON a.ID = b.ID GROUP BY state, date;Query Optimization Checklist
Ensure ID columns in both rep and daily_sales tables have indexes
Add WHERE clauses to limit date ranges for better performance
Ensure ID columns use consistent data types across tables
Verify query logic with known data before running on production