SQL Tutorial

Version 2.1

This document is a general tutorial on the database sublanguage - SQL. 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.

Note: This tutorial describes the subset of SQL92 supported by FirstSQL/J 100% Java ORDBMS. Feel free to download and use the database for learning and educational purposes. The subset is primarily SQL92 Intermediate Level but has some Entry Level features. The tutorial only describes pure SQL92 capabilities. No proprietary extensions are included.

For questions, notes and suggestions on this tutorial contact us at info@firstsql.com.

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:

schema-name . table-name

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

DDL for example tables

SQL Statement Formats

The remainder of this tutorial has 3 major sections:

SQL-Data Statements -- query and modify tables and columns
SQL-Transaction Statements -- control transactions
SQL-Schema Statements -- maintain schema (catalog)

Updated February 2, 2005

Changes in Version 2.1

Sub-section on Quantified Subqueries changed to clarify that the ALL subquery is also True when the subquery is empty.
Sub-section on CHECK Constraints changed to clarify that:

the check condition succeeds if it evaluates to True or Unknown, and
a CHECK constraint is only useful for DELETE statements if it contains a self-referencing subquery.

Changes in Version 2

Truth tables for AND, OR and NOT improved for easier comprehension.
Sub-section on Transaction Isolation extended with full treatment of all 4 transaction isolation levels.
Sub-section on Referential Integrity changed for a more accurate description of Foreign Keys containing null columns.
Other minor cleanups.