|
SQL Tutorial
This document is a general tutorial on the database sublanguage - SQL.
A D V E R T I S E M E N T
It is not
product oriented but rather uses standard SQL (SQL92). The tutorial doesn't
cover all of SQL92; it concentrates on a subset of the standard that is both
usable and commonly supported.
Introduction
SQL (Structured Query Language) is a database sublanguage for querying and
modifying relational databases. It was developed by IBM Research in the mid 70's
and standardized by ANSI in 1986.
The Relational Model defines two root languages for accessing a
relational database -- Relational Algebra and Relational Calculus. Relational
Algebra is a low-level, operator-oriented language. Creating a query in
Relational Algebra involves combining relational operators using algebraic
notation. Relational Calculus is a high-level, declarative language. Creating a
query in Relational Calculus involves describing what results are desired.
SQL is a version of Relational Calculus. The basic structure in SQL is the
statement. Semicolons separate multiple SQL statements.
There are 3 basic categories of SQL Statements:
- SQL-Data Statements --
query and modify tables and columns
- SELECT Statement --
query tables and views in the database
- INSERT Statement
-- add rows to tables
- UPDATE Statement
-- modify columns in table rows
- DELETE Statement
-- remove rows from tables
- SQL-Transaction Statements
-- control transactions
- COMMIT Statement
-- commit the current transaction
- ROLLBACK
Statement -- roll back the current transaction
- SQL-Schema Statements
-- maintain schema (catalog)
- CREATE
TABLE Statement -- create tables
- CREATE VIEW
Statement -- create views
- DROP TABLE
Statement -- drop tables
- DROP VIEW
Statement -- drop views
- GRANT Statement
-- grant privileges on tables and views to other users
- REVOKE Statement
-- revoke privileges on tables and views from other users
Language Structure
SQL is a keyword based language. Each statement begins with a unique keyword.
SQL statements consist of clauses which begin with a keyword.
SQL syntax is not case sensitive.
The other lexical elements of SQL statements are:
- names -- names of database elements: tables, columns, views, users,
schemas; names must begin with a letter (a - z) and may contain digits (0 -
9) and underscore (_)
- literals -- quoted strings, numeric values, datetime values
- delimiters -- + - , ( ) = < > <= >= <> . * / || ? ;
Basic database objects (tables, views) can optionally be qualified by schema
name. A dot -- ".", separates qualifiers:
Column names can be qualified by table name with optional schema qualification.
Note: Names can be case sensitive and contain spaces and other
delimiters and can use keywords, by surrounding them with double
quotation marks ("). For example,
"1 Name w/spaces"
"SELECT"
Quoted names must match exactly on case.
|
Example Tables
In the subsequent text, the following 3 example tables are used:
| p Table (parts)
|
s Table (suppliers)
|
sp Table (suppliers & parts)
|
| pno
|
descr
|
color
|
| P1
|
Widget
|
Blue
|
| P2
|
Widget
|
Red
|
| P3
|
Dongle
|
Green
|
|
| sno
|
name
|
city
|
| S1
|
Pierre
|
Paris
|
| S2
|
John
|
London
|
| S3
|
Mario
|
Rome
|
|
| sno
|
pno
|
qty
|
| S1
|
P1
|
NULL
|
| S2
|
P1
|
200
|
| S3
|
P1
|
1000
|
| S3
|
P2
|
200 |
|
|
