SQL 101

Tarball has scripts to walk through the presentation.

IMDB data, messed it up to make it more inefficient. Available with attribution

Walk through the data doing work as if a DBA

List off some RDBMs.

runme

Imported the CSV data

The schema that was created was the default based on the CSV file.

Section 1

Explaining CHAR vs VARCHAR

Convention of uppercasing the SQL keywords

SELECT statement * Basic * LIMIT * GROUP BY * COUNT() * ORDER BY - specify column by number or name - DESC/ASC * WHERE clause * LIKE comparison

SELECT from the results of a SELECT statement

Section 2

Normalization of data

Separate the table and normalize

Talk about temp tables

Trailing semicolons

INSERT INTO table from SELECT

DROP TABLE

replace()

substr(), '||' (concat operator) Arrays in SQL are 1-based

PRIMARY KEY

INSERT into a new table with primary key CREATE INDEX for some columns on table

JOINs (also ON clause)

AS on table name as well as columns

Removing title by building new table

Move person information into people table. Move role information Move show information

Use an INSERT from SELECT with multiple joins to replace the strings with the appropriate ids. This is pretty complicated, but understandable.

Foreign key logic

VACUUM

We had 6 people attending the remote meeting (YouTube streaming) this month.