SQL Data Definition Language I

Here's a shell session captured using the Unix script command. It demonstrates basic usage of psql, which is a PostgreSQL command line client:

# Initially creating a database, connecting to it within psql, and
# creating a table within the database.  You always connect to the
# username database the first time you connect.  The syntax of psql is:
#
#    psql <database name> <database username>
#
# See the man page for psql for more information.

phoenix:~
* psql 
Password:
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=> -- This is an SQL comment.
template1=> --
template1=> -- Notice the nice help meta-commands above.
template1=> -- 
template1=> create database jillz;
CREATE DATABASE
template1=> -- Note that SQL statements are terminated with
template1=> -- a semicolon.  If you forget, use \g.
template1=> --
template1=> -- Connect to the database I just created.
template1=> --
template1=> \c jillz
You are now connected to database jillz.
jillz=> create table example (
jillz(>    id     integer,
jillz(>    name   char(50) );
CREATE
jillz=> --
jillz=> -- About to exit
jillz=> \q

# Once you've created your database, connect to it directly when you start
# psql:

phoenix:~
* psql jillz jillz
Password: 
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

jillz=> \q
phoenix:~
*
 
 

1. You'll will initially have a database with the name of your username.  You can create other databases and connect to them.
   Be warned: if I find databases on the system of which I can't determine the ownership, I will drop them.
    
2. Once you've created your database, you connect to it and can then create tables, constraints, etc.

Some useful psql meta-commands:

1. \i <filename> --- Read and execute a list of SQL commands in the text file
   <filename>.
    
2. \d <table> --- List <table>'s description.
    
3. \l --- List available databases on the system.
    
4. \dS --- Display list of system tables. Example queries on two system tables:

   SELECT * FROM pg_user;
   SELECT * FROM pg_tables;

5. \dp --- List permissions in current database.
    
6. \dt --- List tables in current database.

SQL DDL

Creating Tables

CREATE TABLE Course (
   CrsCode        CHAR(6),
   DeptId         CHAR(4),
   CrsName        CHAR(20),
   Descr          CHAR(100) );  -- Use text?

Some data types in PostgreSQL:  (Refer to the online documentation for details)

1. Numeric:
   1. INTEGER --- four bytes. Also SMALLINT and BIGINT.
   2. REAL --- single precision. Also DOUBLE PRECISION.
   3. SERIAL --- unique serial number. Some caveats --- see manual.
       
2. Character:
   1. CHAR(n) --- fixed length string of n characters.
   2. VARCHAR(n) --- variable length string of at most n characters.
   3. TEXT --- variable length string without limit. Not SQL standard.
       
3. Others: Monetary, date/time, boolean, etc.
   See manual.

Primary and Candidate Keys

CREATE TABLE Course (
   CrsCode        CHAR(6),
   DeptId         CHAR(4),
   CrsName        CHAR(20),
   Descr          CHAR(100),
   PRIMARY KEY (CrsCode),
   UNIQUE (DeptId, CrsName) );

1. One PRIMARY KEY. Table usually indexed on this.
2. One UNIQUE constraint for each remaining candidate key.

Specifying Constraints

1. Domain and semantic constraint examples:

   CREATE TABLE Course (
      CrsCode        CHAR(6) NOT NULL,
      Limit          INTEGER DEFAULT '10',
      Division       CHAR(5),
      DeptId         CHAR(4),
      CrsName        CHAR(20),
      Descr          CHAR(100),
      CHECK (Limit > 0 AND Limit <= (SELECT MAX(Seats) from Room)),
      CHECK (Division IN ('Upper', 'Lower')) );
   

   1. Use for intra-table constraints.
   2. Chicken and egg problem: use ALTER TABLE to add checks once tables are populated.

Foreign Keys

CREATE TABLE Teaching (
   ProfId         INTEGER,
   CrsCode        CHAR(6),
   Semester       CHAR(6),
   PRIMARY KEY (CrsCode, Semester),
   FOREIGN KEY (CrsCode) REFERENCES Course,
   FOREIGN KEY (ProfID) REFERENCES Professor (Id) );

In PostgreSQL the referenced key must be a primary key!