Some Quick Lessons on SQL

Summary: This document gives you basic instructions for creating and using an SQL database.

Contents

• Background
• Creating a New Database
• Interacting with Your Database
• Important Operations
  • Creating Tables
  • Inserting Values
  • Selecting Values
  • Creating Sequences

Background

SQL, the Structured Query Language, is a standard language for building and using relational databases. There are a variety of relational database managers that support SQL. Many students at Grinnell like MySQL, perhaps because Mr. Stone runs a MySQL server. I will admit that I tend to use PostgreSQL. What are the differences? Once upon a time, MySQL did not support transactions and PostgreSQL did. I'm not sure what the big differences are now.

Right now, PostgreSQL only runs on glimmer.cs.grinnell.edu. You'll need to connect to glimmer before using PostgreSQL. You do so with

ssh glimmer.cs.grinnell.edu
username@glimmer.cs.grinnell.edu's password: XXXXXXXX

Creating a New Database

As a RDBMS, PostgreSQL can handle multiple databases, each with its own set of tables and permissions. You should start by creating a new database for your project. To do so, you use the createdb command.

createdb dbname

Interacting with Your Database

There are a number of ways to interact with your database. You can

• Use psql, the command-line SQL interpreter.
• Use a graphical SQL client.
• Write Perl (or other langauge) scripts.

We'll start by using psql and quickly move into a Perl interface.

You start psql by typing psql and the name of your database.

psql dbname

Important Operations

Creating Tables

CREATE TABLE "tablename" (
    "column-name" type contraint
    "column-name" type contraint
    ...
    "column-name" type contraint
);

Valid types include:

• text - arbitrary length text
• integer - a number
• character varying(##) - a maximum-length text field
• timestamp - a date

Valid constraints include:

• PRIMARY KEY - is a key for this table
• NOT NULL - can't be empty
• REFERENCES TABLE.FIELD - matches the specified field in another table.
• DEFAULT expression - use the following default value if it's not specified.
• UNIQUE - illegal to have to rows with this field identical.

For example,

CREATE TABLE "users" (
    "uid"	integer NOT NULL PRIMARY KEY
  , "lname"     character varying(16)
  , "fname"     character varying(16)
  , "age"       integer
  , "added"     timestamp DEFAULT 'now' NOT NULL
);

Inserting Values

Syntax

INSERT INTO table
  (column ... column)
VALUES
  (expression ... expression) ;

For example

INSERT into users
  (uid,lname,fname,age)
VALUES
  (1,'Rebelsky','Samuel',37);

What happens if you don't include one of the fields (e.g., no last name or no user id)? Try it and see.

Selecting Values

To select all the entries in a table

SELECT * FROM table;

To sort the selected elements add

ORDER BY expression

For example,

SELECT * FROM users ORDER BY lname;

or

SELECT * FROM users ORDER BY lname,fname;

To select only a subset of the entries, add

WHERE condition

For example

SELECT * FROM users WHERE age < 40 AND age > 20;

To select only certain fields replace the * by the list of fields you want. For example,

SELECT lname,fname FROM users;

When you have multiple tables, you can do many more interesting things. We'll return to that issue a little later.

Creating Sequences

For values like uid in the earlier example, we'd often like to have the values filled in for us as we add new elements.

  CREATE SEQUENCE "users_uid_seq" start 1 increment 1
     maxvalue 2147483647 minvalue 1 cache 1 ;

 DEFAULT nextval('"users_uid_seq"'::text)