Fred: Database: Index

These notes started as SQL command summary pages, and a few more pages have been added. Here's a zipped version of these notes: notes-db-2004-01-31.zip (200 KB).

If you want online tutorials, look at Tutorials.

Design Issues

• Relational Database Terminology
• More DB terms.
• Heuristics for relationship schemas
• Data Integrity
  • Data Integrity - Problems and solutions.
  • NULLs Unknown or missing values.
  • Autoincrement - Automatically create unique keys.
• Relationships and Referential Integrity
  • Keys - Why declare primary and foreign keys.
  • Referential Integrity - Problems and solutions.
• Normalization
• Misc

SQL Commands

Queries (SELECT)

• SELECT Syntax Summary of common features.
• Logical order in which SELECT clauses are used.
• SELECT Columns - Introduction
• SELECT Rows - Introduction
• DISTINCT
• Joins
  • Joins - Usual inner cross join
  • Joins - Syntax Variations
  • Joins - SQL-92 additions
  • Joins - Difficult Joins - Steps to use in working out difficult joins.
  • Joins - Outer
• Summarizing
  • GROUP BY
  • HAVING
  • Summary Functions - COUNT(), AVG(), SUM() ...
• Subqueries
  • Subqueries - Using SELECTS within other commands.
  • Correlated Subqueries

Domains (Data Types)

• Datatypes - Table of common SQL, MS Access, and MySQL datatypes.

Tables - Creating, Updating, Altering, ...

• Table commands - CREATE TABLE, ALTER TABLE, DROP TABLE
• Insert, Delete, and Update
• Bulk Loads and Saves

Security

• Security - Privileges, GRANT and REVOKE commands

Transactions

• Transactions
• Locking
• ACID Compliance

Database Software

• Misc Database Software
• Access
  • Creating a new Access database
  • Making an SQL query
• ODBC - Defining a Data Source.

Resources

• www.databasecentral.com - Lots of database resources.
• www.wiscorp.com - Mike Gorman of Whitemarsh has some interesting things to read, especially if you want an overview of where SQL is headed.

Examples

• Bookbiz Database example
• University Database example
• Team Database example