Code for Life - Introduction to SQL

Mark's slides for this week's Code for Life workshop are now available as a PDF from our website.

SQL code within the presentation is provided separately in db_basics.sql

Facebook event | Schedule: uombio.info/schedule

Talk outline

Tables

• Creating tables with the diagram designer and with SQL

• Field types and nulls

CRUD

• Create

• Retrieve

• Update

• Delete

Keys

• Primary

• Natural vs arbitrary

• Foreign

  • Create another table

  • Populate from CSV file with import/export

  • Link it

Joins

• Inner join

• Left outer join

• Other join types

More advanced stuff

Indexes

• Primary keys are clustered indexes by default

• Foreign key fields should have indexes so joins are fast

• Fields that are part of queries should also have indexes

Stored procedures and functions

• Motivation

• Examples

Transactions

• Motivation

• Examples