📚 SQL Reference — Complete Glossary

---

Overview

This is a complete glossary of SQL terms, organized into 11 categories and 3 difficulty levels (Beginner, Intermediate, Advanced). Each term includes a definition, example, and syntax where applicable. The glossary is designed to be searchable, filterable, and interactive, making it easy to find the SQL terms you need.

✨ Key Features

📊 11 Comprehensive Categories

Category	Focus	Example Terms	Difficulty
SQL Basics	Fundamentals	SELECT, Data Types, Operators, DDL/DML/DCL	🟢 Beginner
Database	Database operations	CREATE DATABASE, DROP DATABASE, USE	🟢 Beginner
Tables	Table management	CREATE TABLE, ALTER TABLE, TRUNCATE	🟢 Beginner
Queries	Data manipulation	INSERT, UPDATE, DELETE, SELECT	🟢 Beginner
Clauses	Query components	WHERE, GROUP BY, HAVING, ORDER BY, LIMIT	🟢 Beginner
Operators	Logical operations	AND/OR/NOT, LIKE, IN, BETWEEN, EXISTS, CASE	🟢 Beginner
Functions	Built-in functions	Aggregate Functions, Date Functions, String Functions	🟡 Intermediate
Constraints	Data integrity	PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK	🟡 Intermediate
Joins	Table relationships	INNER JOIN, LEFT JOIN, FULL OUTER JOIN, SELF JOIN	🟡 Intermediate
Views	Virtual tables	CREATE VIEW, DROP VIEW	🟡 Intermediate
Advanced	Complex features	Indexes, Subqueries, Window Functions, Stored Procedures, Transactions, Recursive CTEs	🔴 Advanced

---

🎓 Difficulty Levels

Level	Color	Count	Description
Beginner	🟢 Green	~25	Foundational concepts for new SQL learners
Intermediate	🟡 Amber	~15	Practical skills for daily database work
Advanced	🔴 Red	~10	Complex features for performance tuning and analytics

---

🔍 Interactive Features

Smart Search 🔎

• Real-time filtering as you type
• Search by term name, description, or syntax
• Instant results with live term count

Category Filters 📂

• 11 categories covering the entire SQL spectrum
• Visual indicators with color-coded counts
• One-click filtering to focus on specific topics

Difficulty Filter ⚡

• 3-level toggle: Beginner / Intermediate / Advanced
• Color-coded buttons with active states
• Combine with category for precise results

Interactive Examples ▶️

• "Run example" buttons on every card
• Live result tables showing query output
• Toggle visibility to hide/show results
• Realistic sample data for each concept

Random Term 🎲

• One-click random selection for discovery
• Auto-populates search and resets filters
• Great for learning through serendipity

Pagination 📄

• 12 terms per page for manageable scrolling
• Page navigation with prev/next buttons
• Current page indicator and total pages

---

📖 Sample Terms by Category

SQL Basics 🟢

Term	Description	Example
Introduction to SQL	Core language for relational databases	SELECT * FROM customers;
Data Types	INT, VARCHAR, DATE, DECIMAL	CREATE TABLE t (id INT, name VARCHAR(100));
Operators	Arithmetic, comparison, logical	WHERE price > 100 AND category = 'Electronics'
SQL Commands	DDL, DML, DQL, DCL, TCL	CREATE, INSERT, SELECT, GRANT, COMMIT

Queries 🟢

Term	Description	Example
SELECT Statement	Retrieve data from tables	SELECT name, salary FROM employees;
INSERT INTO	Add new rows	INSERT INTO products VALUES (1, 'Laptop', 999.99);
UPDATE Statement	Modify existing rows	UPDATE employees SET salary = salary * 1.05;
DELETE Statement	Remove rows	DELETE FROM orders WHERE order_date < '2020-01-01';

Clauses 🟢

Term	Description	Example
WHERE Clause	Filter rows before grouping	WHERE salary > 50000 AND hire_date >= '2020-01-01'
GROUP BY Clause	Group rows for aggregation	GROUP BY department, location
HAVING Clause	Filter groups after aggregation	HAVING COUNT(*) > 10 AND AVG(salary) > 60000
ORDER BY Clause	Sort result set	ORDER BY salary DESC, hire_date ASC
LIMIT / FETCH	Restrict number of rows	LIMIT 10 OFFSET 20
DISTINCT	Remove duplicates	SELECT DISTINCT department FROM employees;
Aliases (AS)	Temporary column/table names	SELECT e.first_name AS "First Name" FROM employees e;
WITH Clause (CTE)	Common Table Expressions	WITH dept_stats AS (SELECT ...)

Operators 🟢

Term	Description	Example
Logical Operators	AND, OR, NOT	WHERE (dept = 'Eng' OR dept = 'DS') AND salary > 80000
LIKE Operator	Pattern matching	WHERE product_name LIKE '%wireless%'
IN Operator	Value in list/subquery	WHERE department IN ('Engineering', 'Data Science')
IS NULL / IS NOT NULL	NULL value testing	WHERE manager_id IS NULL
BETWEEN Operator	Inclusive range	WHERE amount BETWEEN 100 AND 1000
CASE Operator	Conditional logic	CASE WHEN salary >= 100000 THEN 'Senior' END

Functions 🟡

Term	Description	Example
Aggregate Functions	COUNT, SUM, AVG, MIN, MAX	SELECT COUNT(*), AVG(salary) FROM employees;
Date Functions	DATE_ADD, DATEDIFF, EXTRACT	SELECT DATE_ADD(order_date, INTERVAL 7 DAY);
String Functions	CONCAT, SUBSTRING, UPPER, REPLACE	SELECT UPPER(product_name) FROM products;
Numeric Functions	ROUND, CEILING, FLOOR, ABS	SELECT ROUND(price, 0) FROM products;
CAST / CONVERT	Type conversion	SELECT CAST(price AS INT) FROM products;

Constraints 🟡

Term	Description	Example
NOT NULL	Column cannot be NULL	first_name VARCHAR(50) NOT NULL
Primary Key	Unique row identifier	employee_id INT PRIMARY KEY
Foreign Key	References parent table	FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
UNIQUE	All values distinct	email VARCHAR(100) UNIQUE
CHECK	Column value validation	CHECK (salary > 0)
DEFAULT	Default column value	order_date DATE DEFAULT CURRENT_DATE

Joins 🟡

Term	Description	Example
INNER JOIN	Matching rows only	FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id
LEFT JOIN	All left rows + matches	FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id
FULL OUTER JOIN	All rows from both	FULL OUTER JOIN projects p ON e.employee_id = p.manager_id
SELF JOIN	Table joined to itself	FROM employees e1 LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id

Views 🟡

Term	Description	Example
CREATE VIEW	Virtual table definition	CREATE VIEW active_employees AS SELECT ...
DROP VIEW	Remove view	DROP VIEW old_view;

Advanced 🔴

Term	Description	Example
Indexes	Speed up data retrieval	CREATE INDEX idx_lastname ON employees (last_name);
Subquery	Nested query	WHERE salary > (SELECT AVG(salary) FROM employees)
Window Functions	Calculations across row windows	RANK() OVER (PARTITION BY dept ORDER BY salary DESC)
Stored Procedures	Saved SQL code with parameters	CALL GetEmployeeReport(1, 70000);
Transactions	ACID-compliant operations	BEGIN; UPDATE accounts; COMMIT;
Recursive CTEs	Hierarchical data traversal	WITH RECURSIVE org AS (SELECT ... UNION ALL SELECT ...)

---

🎨 Design & Aesthetics

Classic Technical Reference 📖

• Dark background (#0c0c0e) — easy on the eyes for extended reading
• Amber accent (#d4952a) for SQL keywords and highlights
• Teal (#3aafa0) for usage hints and interactive elements
• Green (#5a9e6e) for beginner-level content
• Red (#c45c4a) for advanced topics
• Grid texture background for depth

Typography ✍️

• Playfair Display — Elegant serif headers, main title
• JetBrains Mono — SQL syntax, technical content, monospaced tables
• DM Sans — Body text, descriptions, UI elements

Card Design 🃏

• Gradient headers with term name
• Color-coded tags for category and difficulty
• Clear descriptions with plain language
• Usage hints with 💡 icons for practical guidance
• Syntax blocks with syntax highlighting
• Interactive result tables with clean formatting

SQL Syntax Highlighting 🎨

Element	Color	Example
Keywords	🟡 Amber	SELECT, FROM, WHERE, JOIN
Strings	🟢 Green	'Laptop', 'Pending'
Numbers	🟠 Orange	1000, 999.99
Comments	⚫ Muted	-- This is a comment
Functions	🔵 Blue	COUNT(), SUM(), DATE_ADD()

---

🛠️ Technical Implementation

Architecture

┌─────────────────────────────────────┐
│        SQL Reference Glossary        │
├─────────────────────────────────────┤
│                                     │
│  ┌─────────────────────────────┐   │
│  │   Data Layer                │   │
│  │   • GLOSSARY array (50+ terms) │
│  │   • Categories: 11          │   │
│  │   • Difficulty: 3 levels    │   │
│  └─────────────────────────────┘   │
│                                     │
│  ┌─────────────────────────────┐   │
│  │   Filter Engine             │   │
│  │   • Search (term/desc/syntax)│   │
│  │   • Category filter (11)    │   │
│  │   • Difficulty filter (3)   │   │
│  │   • Combined filtering logic│   │
│  └─────────────────────────────┘   │
│                                     │
│  ┌─────────────────────────────┐   │
│  │   Render Engine             │   │
│  │   • Cards with syntax hl    │   │
│  │   • Interactive examples    │   │
│  │   • Result tables           │   │
│  │   • Pagination (12/page)    │   │
│  └─────────────────────────────┘   │
└─────────────────────────────────────┘

Key Functions

// Core filtering
filter()                          // Apply search, category, difficulty filters
updateCounts()                     // Update category count badges

// Rendering
render()                           // Render filtered glossary cards
hlSQL(code)                         // SQL syntax highlighting
runExample(btn)                     // Show/hide example results

// Pagination
renderPagination()                  // Render page navigation
updateInfo(a, b, total)              // Update results info text

// Event handlers
searchInput.addEventListener         // Real-time search
filterBtn.addEventListener           // Category filter
complexityBtn.addEventListener       // Difficulty filter

// Utilities
showRandom()                         // Show random term
resetAll()                           // Reset all filters
scrollToTop()                        // Smooth scroll to top

---

📊 Content Breakdown

Category	Count	Difficulty	Key Terms
SQL Basics	4	Beginner	Introduction, Data Types, Operators, Commands
Database	3	Beginner	CREATE DATABASE, DROP DATABASE, USE
Tables	6	Beginner	CREATE TABLE, DROP TABLE, TRUNCATE, ALTER TABLE, COPY TABLE, TEMP TABLE
Queries	4	Beginner	SELECT, INSERT, UPDATE, DELETE
Clauses	8	Beginner	WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, DISTINCT, Aliases, WITH (CTE)
Operators	7	Beginner	Logical, LIKE, IN, IS NULL, BETWEEN, CASE, UNION
Functions	5	Intermediate	Aggregate, Date, String, Numeric, CAST
Constraints	6	Intermediate	NOT NULL, PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT
Joins	4	Intermediate	INNER, LEFT, FULL OUTER, SELF
Views	2	Intermediate	CREATE VIEW, DROP VIEW
Advanced	6	Advanced	Indexes, Subqueries, Window Functions, Stored Procedures, Transactions, Recursive CTEs
TOTAL	55		Complete SQL reference

---

🎥 Video Demo Script (45-60 seconds)

Time	Scene	Action
0:00	Header	Show "SQL Reference — Complete Glossary" with amber accents
0:05	Filters	Type "JOIN" in search → Cards filter to 4 join terms
0:10	Category	Click "Advanced" category → 6 advanced terms displayed
0:15	Difficulty	Click "Advanced" difficulty → Red-tagged cards appear
0:20	Random	Click "Random term" → Random card appears (e.g., Window Functions)
0:25	Card	Hover over card → Border highlight and slight elevation
0:30	Syntax	Show syntax block with color-coded highlighting
0:35	Example	Click "Run example" → Result table appears with sample data
0:40	Pagination	Scroll to bottom → Show page 1 of 5
0:45	Reset	Click "Reset filters" → All 50+ terms reappear

---

🚦 Performance

• Load Time: < 1 second (no external dependencies)
• Memory Usage: < 30 MB
• CPU Usage: Minimal (event-driven filtering)
• Network: Zero requests after initial load

---

🛡️ Security Notes

SQL Reference — Complete Glossary is a completely safe educational tool:

• ✅ No actual database connections
• ✅ All examples simulated in browser
• ✅ No data collection or tracking
• ✅ No external dependencies
• ✅ Pure HTML/CSS/JavaScript
• ✅ Educational purposes only — learn SQL concepts safely

---

📝 License

MIT License — see LICENSE file for details.

---

🙏 Acknowledgments

• SQL Standard (ISO/IEC 9075) — Official SQL specification
• MySQL — Open-source database inspiration
• PostgreSQL — Advanced database features
• SQLite — Lightweight database reference
• Microsoft SQL Server — T-SQL extensions
• Oracle Database — PL/SQL reference

---

📧 Contact

• GitHub Issues: Create an issue
• Website: https://willie-conway.github.io/SQL-Reference/

---

🏁 Future Enhancements

• Add more terms (75+ total)
• Include database-specific variations (MySQL vs PostgreSQL)
• Add query plan visualization
• Include performance tips for each term
• Add practice exercises
• Include schema diagram examples
• Export as PDF reference guide
• Dark/light theme toggle
• Print-friendly styles
• Mobile-optimized compact view

---

📚 SQL Reference — Complete Glossary — Your Ultimate SQL Companion 📚

---

Last updated: March 2026