SQL Course Appendices: Quick Reference Guides

 

Appendices: Quick Reference Guides

As you venture beyond the core chapters, these appendices become your trusted sidekick. Whether you’re knee-deep in a complex query or refreshing your memory on a particular term, you’ll find everything at your fingertips.

1. SQL Syntax Cheat Sheet

A one-page snapshot of essential commands lets you work quickly without hunting through documentation. Keep this section open while you code:

Data Definition Language (DDL)

• CREATE TABLE CREATE TABLE table_name (col1 INT PRIMARY KEY, col2 VARCHAR(50) NOT NULL);

• ALTER TABLE ALTER TABLE table_name ADD COLUMN col3 DATE;

• DROP TABLE DROP TABLE IF EXISTS table_name;

Data Manipulation Language (DML)

• SELECT SELECT col1, col2 FROM table_name WHERE col3 = 'value';

• INSERT INSERT INTO table_name (col1, col2) VALUES (1, 'text');

• UPDATE UPDATE table_name SET col2 = 'new' WHERE col1 = 1;

• DELETE DELETE FROM table_name WHERE col1 = 1;

Transaction Control

• BEGIN / START TRANSACTION BEGIN;

• COMMIT COMMIT;

• ROLLBACK ROLLBACK;

Query Clauses

• WHERE — filter rows

• GROUP BY — aggregate buckets

• HAVING — filter aggregates

• ORDER BY — sort results

• LIMIT / TOP — constrain row count

Set Operations

• UNION / UNION ALL — merge result sets

• INTERSECT — find common rows

• EXCEPT / MINUS — subtract row sets

2. Glossary of Terms

A quick-scan list of SQL jargon and definitions keeps you in sync with precise vocabulary:

• Atom The smallest indivisible unit of data in a column.

• Cardinality The uniqueness of values in a column (high cardinality = many distinct values).

• Derived Table A subquery in the FROM clause treated like a virtual table.

• Execution Plan The database engine’s roadmap for retrieving your query results.

• Index A data structure that accelerates lookups at the cost of storage and write overhead.

• Normalization The process of organizing tables to eliminate redundancy.

• Partitioning Splitting a large table into smaller, manageable pieces for performance.

• Surrogate Key A system-generated unique identifier (e.g., auto-increment, UUID).

• Transactional Integrity Ensuring operations follow ACID properties: Atomicity, Consistency, Isolation, Durability.

• Window Function A function that performs calculations across a set of rows related to the current row (e.g., ROW_NUMBER() OVER()).

3. Sample Database Schema Walkthrough

Hands-on practice with a real schema cements your understanding. Below is a simplified e-commerce layout:

Table Definitions

sql

CREATE TABLE customers (
  customer_id   SERIAL   PRIMARY KEY,
  first_name    VARCHAR(50),
  last_name     VARCHAR(50),
  email         VARCHAR(100) UNIQUE
);

CREATE TABLE products (
  product_id    SERIAL   PRIMARY KEY,
  name          VARCHAR(100),
  price         DECIMAL(10,2)
);

CREATE TABLE orders (
  order_id      SERIAL   PRIMARY KEY,
  customer_id   INT      REFERENCES customers(customer_id),
  order_date    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
  order_item_id SERIAL   PRIMARY KEY,
  order_id      INT      REFERENCES orders(order_id),
  product_id    INT      REFERENCES products(product_id),
  quantity      INT      CHECK (quantity > 0)
);

Example Queries

1. List recent orders with customer names:

   sql

   SELECT o.order_id,
          c.first_name || ' ' || c.last_name AS customer,
          o.order_date
   FROM orders o
   JOIN customers c ON o.customer_id = c.customer_id
   WHERE o.order_date > NOW() - INTERVAL '7 days';
   

2. Calculate total spend per customer:

   sql

   SELECT c.customer_id,
          c.first_name,
          SUM(p.price * oi.quantity) AS total_spent
   FROM customers c
   JOIN orders o ON c.customer_id = o.customer_id
   JOIN order_items oi ON o.order_id = oi.order_id
   JOIN products p ON oi.product_id = p.product_id
   GROUP BY c.customer_id, c.first_name;
   

4. Recommended Resources

Deepen your learning with these authoritative references:

Books

• “Learning SQL” by Alan Beaulieu A reader-friendly introduction with practical exercises.

• “SQL Cookbook” by Anthony Molinaro Problem-solution recipes for real-world challenges.

• “High Performance MySQL” by Schwartz, Tkachenko & Zaitsev In-depth performance and scaling strategies.

Blogs & Websites

• Use The Index, Luke! — insights on indexing and query performance

• — community-driven articles on SQL Server

• Planet PostgreSQL — aggregated news and tutorials from the PostgreSQL ecosystem

Online Courses

• Udemy: The Complete SQL Bootcamp

• Coursera: Databases and SQL for Data Science by IBM

• edX: Databases: Advanced Topics in SQL by Stanford University

Dear readers, we’ve covered a tremendous amount of ground—from the basics of SELECT all the way to advanced performance tuning and appendices. It’s been a hard-fought journey, and you’ve earned a well-deserved break. Perhaps it’s time to close your laptop, step away for a vacation, and recharge. Thank you so much for your attention and passion. Happy querying, and enjoy your time off!