SQL Syntax Notes#

Basic Select Statement#

SELECT * FROM table;

SELECT col1, col2 FROM table;

SELECT * FROM table WHERE col1 = 'ABC';

SELECT * FROM table WHERE col1 IS NULL;

SELECT * FROM table WHERE col1 = 'ABC' ORDER BY col1, col2;

SELECT * FROM table WHERE col1 <> 'ABC' ORDER BY col1 DESC;

SELECT DISTINCT col1, col2 FROM table;

SELECT COUNT(*) FROM table WHERE col1 = 'ABC';

SELECT col1, col2 FROM tbl1, tbl2 WHERE tbl1.col3 = tbl2.col3;

SELECT a.col1, b.col2 FROM tbl1 a, tbl2 b WHERE a.col3 = b.col3;

Changing Data#

UPDATE table SET field=value;    --  this changes every record!

UPDATE table SET field=value WHERE keyfield = keyvalue;

UPDATE table SET field=value WHERE EXISTS (SELECT field2 FROM table2 WHERE table.field = table2.field2);

Deleting Data#

DELETE FROM table;    --  this deletes every record!

DELETE FROM table WHERE keyfield = keyvalue;

Adding Data#

INSERT INTO table (col1, col2, col3) VALUES (val1, val2, val3);

Joining Tables#

New (SQL92) syntax:

select * from table1 a
    [inner] join table2 b
    on a.fld1 = b.fld2;

"inner" is defaulted or implied.

inner joins exclude records that don't contain matches.

outer joins include rows with missing matches and provides nulls for the missing data.

"left outer" means the left side of the ON clause is the driving table (or the table that must have data to create a record).

"right outer" means the table on the right of the ON clause is the driving table (or the table that must have data to create a records).

select * from table1 a
    left outer join table2 b
    on a.fld1 = b.fld2;

Basically you should always use the table in the FROM clause on the left side and use a LEFT OUTER join.

If the field names in each table are the same you can use:

select * from table1 a
    [inner] join table2 b
    using (fld);

You can also join to the result of another query (a sub-query). In this case table2 would be replaced with (select ...).

The joined table can also be the same table, i.e. table1 joins to table1.

Creating and Altering a Table#

CREATE TABLE table (
    col1 CHARACTER(5) NOT NULL PRIMARY KEY,
    col2 VARCHAR(30),
    col3 INTEGER UNIQUE,
    col4 INTEGER DEFAULT 0 NOT NULL,
    col5 DOUBLE PRECISION,
    col6 SMALLINT,
    col7 TIMESTAMP,
    CONSTRAINT cname PRIMARY KEY (col1, col2),
    CONSTRAINT cname UNIQUE (col2, col3),
    CONSTRAINT cname FOREIGN KEY (col1) REFERENCES other_table (ocol1),
    CONSTRAINT cname CHECK (col3 > 4)
);

DROP TABLE table;

CREATE [UNIQUE] INDEX name ON table (col1, col2);

CREATE [UNIQUE] INDEX name ON table (lower(col1), col2);

DROP INDEX name;

ALTER TABLE table ADD COLUMN newcol VARCHAR(20);

ALTER TABLE table DROP COLUMN oldcol;

ALTER TABLE table ALTER COLUMN col SET DEFAULT val;

ALTER TABLE table ALTER COLUMN col DROP DEFAULT;