SQL

ESM 261
Fall 2007
James Frew

[single page] [slide show]

SQL: "Intergalactic Dataspeak"

History

SQL Components

Data definition language (DDL)

Data manipulation language (DML)

SQL in Access

Evil twin of a query

Access Query Types

Select

Action

  • Make table
  • Delete
  • Append
  • Update

SQL-only

  • Union
  • Pass-through
  • DDL

Crosstab

Parameter

Access DDL

Usually generated automatically by Table Design View

Statements

[example] [notes]

Access SQL Data Types

SQL Table Design
Data Type Field Size
Boolean
Logical
Yes/No		 Yes/No
Byte
Integer1		 Number Byte
Counter
Autoincrement		 AutoNumber Long Integer
Currency
Money 		Currency
Datetime
Date
Time 		Date/Time
Short
Integer2
Smallint 		Number Integer
Long
Int
Integer
Integer4 		Number Long Integer
SQL Table Design
Data Type Field Size
Single
Float4
Real 		Number Single
Double
Float
Float8
Number
Numeric 		Number Double
Text
Alphanumeric
Char
Character
String 		Text
Longtext
Longchar
Memo
Note		 Memo
Longbinary
General
OLEobject 		(OLE) Object
GUID AutoNumber ReplicationID

Access DML

Usually generated automatically by Query Design View

Statements

  • SELECT
    • select/project rows/columns from source table(s) into new table
  • UNION
    • union of two or more tables
  • UPDATE
    • change column values in existing rows
  • DELETE
    • delete rows from a table
  • INSERT INTO
    • append new rows to a table
  • SELECT INTO
    • save the result of a SELECT in a new table
  • TRANSFORM
    • crosstab query
  • PARAMETERS
    • obtain user input when query is run

SELECT

Simplified form:

SELECT predicate table.column, ...
FROM table, ...
WHERE condition

NB: ignoring (for now):

  • GROUP BY
  • HAVING
  • ORDER BY

Predicates (optional)

  • no duplicate rows
    • DISTINCT
      • based on selected columns only
    • DISTINCTROW
      • based on entire row
  • partial results
    • TOP n
      • (rows)
    • TOP n PERCENT

Stupid (well, not really) SELECT Tricks

Concatenate columns

  • SELECT columna & " " & columnb AS new_column
    FROM ...

Nested joins

  • SELECT column, ...
    FROM tablea INNER JOIN (
        tableb INNER JOIN tablec ON ...
    ) ON ...

Parameters

  • SELECT column, ...
    FROM ...
    WHERE column op [prompt]

SELECT INTO

  • SELECT column, ...
    INTO new_table
    FROM ...

examples

Updatable Queries

A SELECT result is a table...

  • has rows and columns
  • presented as datasheet

...but not necessarily a relation

  • not necessarily a reversible relationship between
    • a row in the result table
    • rows in the base tables
  • e.g.
    • 2 base tables with 1:n relationship
    • 3 or more base tables
    • UNION
    • join without explicit foreign key

The general solution (is it updatable?) is quite complex, and implementation-dependent.

Readings