A Brief SQL Tutorial

Short Description
A Brief SQL Tutorial. Clay Dowling. clay@lazarusid.com. May 31, 2006. Introduction. SQL is a standard language used to communicate with relational databases …

Website: www.lazarusid.com | Filesize: 25kb

Content
A Brief SQL Tutorial
Clay Dowling
clay@lazarusid.com
May 31, 2006
Introduction
SQL is a standard language used to communicate with relational databases. Like
human languages, every engine speaks a slightly different dialect, but there are
commonalities. I will attempt to stick to the common bits, and I’ll explain where
I deviate.
I am going to use PostgreSQL for my examples. PostgreSQL adheres more
closely to the ANSI standard than most other database engines outside of IBM’s
DB2. What I show you should work just as well for SQLite, MySQL or SQL
Server.
1 Creating Tables
The portion of the language used for creating tables, indexes and other relations
is called Data Definition Language or DDL. It is part of the SQL Standard.
CREATE TABLE product (
id serial primary key,
name varchar(40) not null default ”,
price numeric(12,2) not null default 0.00
)
1.1 The Obvious
1. The table has three fields named “id”, “name” and “price”.
12. The fields have the data types “serial”, “varchar” and “numeric” respectively.
3. Two of the fields have default values
4. Strings are delimited by single quotes, not double quotes, just like in Pascal.
1.2 The Not So Obvious
1. The column “id” is actually an integer. The serial datatype means that when
a record is created in this table, provided that no value is supplied for “id” a
value will be automatically generated, one higher than the last value generated.
2. The column “id” is also a primary key. This means that its value can be
used to uniquely identify a row in the database. The database will enforce
this rule, and refuse to accept any record which creates a duplicate id.
3. The column “price” can hold a real number with up to twelve digits, including
exactly two digits to the right of the decimal point.
4. The “serial” datatype is not part of the SQL Standard. It is an extension
specific to PostgreSQL. However, every database engine has this feature
under a different name. MySQL has auto increment fields, SQL Server
has identity, SQLite has integer primary key.
1.3…