SQL Command Categories and SELECT Basics (Access)

Categories of SQL commands

DDL (Data Definition Language): create, modify, or drop tables
DML (Data Manipulation Language): operate on rows (query data via SELECT, insert rows, delete rows)
DCL (Data Control Language): manage permissions (GRANT, REVOKE)
TCL (Transaction Control Language): transaction control; vendor implementations vary
Persistent Stored Modules: vendor-specific SQL programming (IF, loops, etc.)
Takeaway: three big standardized categories; TCL and Stored Modules vary by vendor

Standards idea covers a small part; most SQL features are vendor-specific
Microsoft Access uses a SQL92-compatible option for easier transition to SQL Server; not 100% compatible but close
For learning in Access: prefer using SQL view over designers to see the actual SQL

Basic form: ext{SELECT } ext{column_list} ext{ FROM } ext{table}
Asterisk (*) means all columns; not a pattern wildcard
Column list: specify one or more columns separated by commas
Column order in the SELECT list determines the order of results
Case sensitivity: not case sensitive for identifiers here
Multiple columns: you can include the same column more than once (machine does not care)
Literals in SELECT: you can include constants (e.g., 100, 'hello') as part of the output
Termination: statements may end with a semicolon (optional in many cases)
Comments in Access prompt: not supported in the prompt; other SQL dialects support comments (e.g., /* … */)
SQL92 mode in Access: can be set via Object Designer for new databases
When building queries in Access:
- Query Design/Wizard can generate SQL, but you should switch to SQL view to study/modify
- For homework, write and edit the SQL directly (not just use the designer)

SELECT clause can include: asterisk, specific column list, DISTINCT, TOP, and literals
DISTINCT removes duplicates from the result set
TOP n limits the number of rows returned (e.g., TOP 5)
Order of columns in the SELECT clause determines output order

To run a query: use the Run button to see results in Datasheet view; switch to SQL view to edit
If you get lost, go back to the Query Designer and then re-open SQL view to see the SQL directly
Prefer writing SQL in SQL view for homework and deeper understanding; avoid relying solely on the Wizard