DATABASE QUERY LANGUAGE v2024

Master the Art of
Data Manipulation

From basic SELECT statements to complex window functions. Interactive, visual, and comprehensive SQL mastery guide.

START LEARNING
20+
Topics
100+
Examples
∞
Possibilities
0
Prior Knowledge

> SQL Cheat Sheet Matrix

DATA MANIPULATION

  • SELECT Read data
  • INSERT Add records
  • UPDATE Modify records
  • DELETE Remove records

DATA DEFINITION

  • CREATE New table/db
  • ALTER Modify structure
  • DROP Delete table
  • TRUNCATE Clear data

QUERY MODIFIERS

  • WHERE Filter rows
  • JOIN Combine tables
  • GROUP BY Aggregate
  • ORDER BY Sort results

01. Database Fundamentals

Understanding the foundation of relational databases and SQL syntax.

01 What is SQL?

SQL (Structured Query Language) is the standard language for communicating with relational databases. Think of it as the bridge between you and your data.

// PRO TIP

SQL became an ANSI standard in 1986 and ISO standard in 1987. While implementations vary slightly between MySQL, PostgreSQL, and SQL Server, the core syntax remains universal.

database_structure.sql 
-- Database > Tables > Records > Fields
DATABASE ShopDB
├── TABLE Customers
│   ├── COLUMN CustomerID (INT)
│   ├── COLUMN Name (VARCHAR)
│   └── COLUMN Email (VARCHAR)
└── TABLE Orders
    ├── COLUMN OrderID (INT)
    ├── COLUMN CustomerID (INT)
    └── COLUMN Total (DECIMAL)

02 Syntax Rules

;

Semicolon

Statements end with a semicolon. Some databases require it, others don't—but always use it for safety.

Aa

Case Sensitivity

SQL keywords are case-insensitive, but convention uses UPPERCASE for keywords, lowercase for identifiers.

--

Comments

Single line with -- or multi-line with /* */

02. Data Retrieval

Mastering SELECT statements, filtering, sorting, and table relationships.

03 The SELECT Statement 

SELECT CustomerName, City 
FROM Customers
WHERE Country = 'Germany'
ORDER BY City DESC;
SELECT → FROM → WHERE → ORDER BY
Result Set
CustomerName City
Müller GmbH Munich
Berlin Tech Berlin
Hamburg Logistik Hamburg

04 Filtering with WHERE

Comparison Operators

= Equal to
!= or <> Not equal
> < Greater/Less than
>= <= Greater/Less or equal

Logical Operators

AND

Both conditions must be true

OR

At least one condition true

NOT

Negates the condition

Pattern Matching with LIKE

'A%' Starts with A
'%a' Ends with a
'%or%' Contains 'or'
'_a%' Second char is 'a' 
-- Find all emails from Gmail
SELECT * FROM Users
WHERE Email LIKE '%@gmail.com';

-- Find 5-letter names starting with J
SELECT * FROM Customers
WHERE Name LIKE 'J____';

05 JOIN Operations

Combining tables is where SQL power truly shines. Here are the 4 essential JOIN types:

INNER JOIN

Returns only matching records from both tables.

SELECT Orders.OrderID, Customers.Name
FROM Orders
INNER JOIN Customers 
  ON Orders.CustomerID = Customers.CustomerID;
A
∩
B

LEFT JOIN

All records from left table, matched records from right. 

SELECT Customers.Name, Orders.OrderID
FROM Customers
LEFT JOIN Orders 
  ON Customers.CustomerID = Orders.CustomerID;
A
B

RIGHT JOIN

All records from right table, matched from left.

A
B

FULL OUTER JOIN

All records when there's a match in either table.

A
∪
B

06 Aggregation & Grouping

Aggregate Functions

COUNT(*) Count all rows
SUM(column) Total sum
AVG(column) Average value
MAX/MIN(column) Extreme values 
-- Sales by Country with filtering
SELECT 
    Country,
    COUNT(*) AS TotalCustomers,
    AVG(Age) AS AvgAge,
    SUM(Spending) AS TotalRevenue
FROM Customers
WHERE Status = 'Active'
GROUP BY Country
HAVING COUNT(*) > 5
ORDER BY TotalRevenue DESC;
Note: HAVING filters groups after aggregation, WHERE filters rows before.

03. Advanced Concepts

Window functions, CTEs, constraints, and optimization techniques.

07 Window Functions

Perform calculations across sets of rows related to the current row without grouping.

ROW_NUMBER()

Unique sequential numbers

RANK()

Ranking with gaps

DENSE_RANK()

Ranking without gaps

-- Running total and ranking in one query
SELECT
    EmployeeName,
    Department,
    Salary,
    ROW_NUMBER() OVER (
        PARTITION BY Department 
        ORDER BY Salary DESC
    ) AS DeptRank,
    SUM(Salary) OVER (
        ORDER BY HireDate
    ) AS RunningPayroll
FROM Employees;

08 Common Table Expressions (CTEs)

Break complex queries into readable, reusable temporary result sets. 

WITH RecursiveEmployeeCTE AS (
    -- Anchor member: Top-level managers
    SELECT 
        EmployeeID, 
        ManagerID, 
        Name, 
        0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- Recursive member: Employees with managers
    SELECT 
        e.EmployeeID, 
        e.ManagerID, 
        e.Name, 
        r.Level + 1
    FROM Employees e
    INNER JOIN RecursiveEmployeeCTE r
        ON e.ManagerID = r.EmployeeID
)
SELECT * FROM RecursiveEmployeeCTE;

09 Constraints

  • 🔒
    PRIMARY KEY

    Unique identifier, not null

  • 🔗
    FOREIGN KEY

    Links to another table

  • ✓
    CHECK

    Validates condition

  • ∅
    NOT NULL

    Required value

10 Indexing

Speed up queries at the cost of storage and write speed. 

-- Create index
CREATE INDEX idx_email 
ON Users(Email);

-- Composite index
CREATE INDEX idx_name 
ON Users(LastName, FirstName);

-- Unique index
CREATE UNIQUE INDEX idx_ssn 
ON Employees(SSN);

SQL Best Practices

DO
  • Use specific columns instead of SELECT *
  • Parameterize queries to prevent injection
  • Use indexes on frequently queried columns
  • Normalize data to reduce redundancy
  • Use transactions for multi-step operations
DON'T
  • Forget WHERE clause on UPDATE/DELETE
  • Use cursors when set operations work
  • Ignore NULL handling in comparisons
  • Create too many indexes (slows writes)
  • Use SELECT DISTINCT as a fix for bad joins

04. Practice Playground

Test your knowledge with interactive examples.

query_editor.sql
SELECT CustomerName, City, Country FROM Customers WHERE Country = 'Germany' ORDER BY City;

Edit the query above and click RUN to see results

Result Set (3 rows) ✓ 0.04s
CustomerName City Country
Alfreds Futterkiste Berlin Germany
Drachenblut Delikatessen Aachen Germany
Frankenversand München Germany