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 and compatibility notes

  • 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

The SELECT statement (core focus on page 57)

  • 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 options and capabilities

  • 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

Practical tips for working with Access during learning

  • 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