SQL Commands

CREATE TABLE

This command creates a new table

CREATE TABLE "TableNameGoesHere" (
  "ColumnOneNameGoesHere"  TEXT,
  "ColumnTwoNameGoesHere"  TEXT,
);

Example:

CREATE TABLE "Books" (
  "Title"          TEXT NOT NULL,
  "PrimaryAuthor"  TEXT,
  "YearPublished"  INT,
);

Column types

These are the most common column types, but they certainly aren't the only ones.

NULL versus NOT NULL

By default all the column types above are NULLable meaning that you do not have to specify a value and thus can be the special value NULL that means "lacks a value".

If you want a column that must be supplied, add NOT NULL to its specification.

Example:

CREATE TABLE "Books" (
  "Title"          TEXT NOT NULL,
  "PrimaryAuthor"  TEXT,
  "YearPublished"  INT,
);

PRIMARY KEY

To create a primary key column we add the qualifier PRIMARY KEY. It is also useful to use the SERIAL type which creates an automatically increasing integer for each row inserted. This allows us to omit that column and allow the database to supply it and automatically keep track of it.

CREATE TABLE "Books" (
  "Id"             SERIAL PRIMARY KEY
  "Title"          TEXT NOT NULL,
  "PrimaryAuthor"  TEXT,
  "YearPublished"  INT,
);

INSERT data

To insert data we can use the INSERT INTO command.

NOTE: Notice textual values are surrounded by SINGLE quotes.

Format:

INSERT INTO "TableName" ("ColumnA", "ColumnB", "ColumnC")
VALUES ('columnAValue', 'columnBValue', 'columnCValue');

For an example of inserting data into our Books table:

INSERT INTO "Books" ("Title",  "PrimaryAuthor", "YearPublished", "Genre")
VALUES ('Night of the Living Dummy', 'R. L. Stine', 1993, 'horror');
INSERT INTO "Books" ("Title",  "PrimaryAuthor", "YearPublished", "Genre")
VALUES ('The Lost World', 'Michael Crichton', 1995, 'sci-fi');

SELECT data

To select data:

SELECT <column name(s)>
FROM <table name>

Example:

SELECT "Title", "YearPublished"
FROM "Books";

You can also use the special name * to mean All Columns.

SELECT *
FROM "Books";

We can also do computations with SELECT

To see the number of books:

SELECT COUNT(*)
FROM "Books";

To see the average, largest, and smallest year of publication

SELECT AVG("YearPublished"), MAX("YearPublished"), MIN("YearPublished")
FROM "Books";

SELECT data but limit the rows to return

Using a WHERE clause we can limit which rows are selected.

For example, to see all the books published before 1990 we use:

SELECT *
FROM "Books"
WHERE "YearPublished" < 1990

To see all the books published by Michael Crichton:

SELECT *
FROM "Books"
WHERE "PrimaryAuthor" = 'Michael Crichton';

SELECT data but order the results

The data from a SELECT statement will come back in an unpredictable order. Even though running the same SELECT twice may give the data in the same order, it is not guaranteed.

To specify an order:

SELECT *
FROM "Books"
ORDER BY "Genre";

You can also order descending:

SELECT *
FROM "Books"
ORDER BY "Genre" DESC;

The ORDER BY can also order numeric types.

UPDATE existing data

To change a value in existing rows we use the UPDATE command. The UPDATE command will make the changes specified in the SET statements for ALL rows that match the WHERE

Format:

UPDATE "TableName"
SET "ColumnA" = 'new value'
WHERE "ColumnB" = 'some value'

Example:

Word of warning, if the WHERE clause is left off, then all rows will be updated.

Example, change the Genre into children horror for all the books where the PrimaryAuthor is R. L. Stine

UPDATE "Books" SET "Genre" = 'children horror' WHERE "PrimaryAuthor" = 'R. L. Stine';

NOTE: You can apply multiple changes with the SET command

UPDATE "Books" SET "Genre" = 'children horror', "YearPublished" = 1995 WHERE "PrimaryAuthor" = 'R. L. Stine';

DELETE existing data

We use the DELETE statement to remove multiple rows from the database.

Format:

DELETE
FROM "TableName"
WHERE "ColumnB" = 'some value'

Example:

Word of warning, if the WHERE clause is left off, then all rows will be deleted.

Example, to delete all the rows where the PrimaryAuthor is R. L. Stine

DELETE FROM "Books"  WHERE "PrimaryAuthor" = 'R. L. Stine';

ALTER TABLE - Renaming a table

We can rename a table if we use the wrong name.

Example of renaming a table:

ALTER TABLE "Band" RENAME TO "Bands";

ALTER TABLE "OldName" RENAME TO "NewName";

ALTER TABLE - Adding a column, Renaming a Column, Changing a Type

The structure of our tables is not set in stone. They can be modified at a later date by using the ALTER TABLE query.

Example of adding a column:

ALTER TABLE "Books" ADD COLUMN "Genre" VARCHAR(15);

Example of renaming a column:

ALTER TABLE "Books" RENAME COLUMN "Genre" TO "SpecificGenre";

Example of changing a column data type:

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type;

To change our YearPublished to a TEXT column:

ALTER TABLE "Books" ALTER COLUMN "YearPublished" TYPE TEXT;

FOREIGN KEYS - Adding to an existing table

To add a foreign key to an existing table:

ALTER TABLE "ExistingTableNameHere" ADD COLUMN "SingularNameOfOtherTableFollowedById" INTEGER NULL REFERENCES "NameOfOtherTableHere" ("Id");

Example of adding a column to Books to relate to the Ratings table.

ALTER TABLE "Books" ADD COLUMN "RatingId" INTEGER NULL REFERENCES "Ratings" ("Id");

FOREIGN KEYS - Adding to the list of columns when you CREATE a table

CREATE TABLE "Books" (
  "Id"             SERIAL PRIMARY KEY
  "Title"          TEXT NOT NULL,
  "PrimaryAuthor"  TEXT,
  "YearPublished"  INT,
  "RatingId"       INTEGER REFERENCES "Ratings" ("Id"),
);