Sample Video Frame

Created by Zed A. Shaw Updated 2024-10-08 04:45:56

Exercise 1: Creating Tables

In the introduction I said that you can do "Create Read Update Delete" operations to the data inside tables. How do you make the tables in the first place? By doing CRUD on the database schema, and the first SQL statement to learn is CREATE:

View Source file ex1.sql Only

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);

You could put this all on one line, but I want to talk about each line so it's on multiple ones. Here's what each line does:

What You Should See

The easiest way to run this is to simply do: sqlite3 ex1.db < ex1.sql and it should just exit and not show you anything. To make sure it created a database use ls -l:

View Source file ex1.sh-session Only

$ ls -l
total 16
-rw-r--r--  1 zedshaw  staff  2048 Nov  8 16:18 ex1.db
-rw-r--r--  1 zedshaw  staff    92 Nov  8 16:14 ex1.sql

WARNING: On Windows PowerShell the < (redirect input from file) is not implemented. Every time you see me do sqlite3 ex1.db < ex1.sql you should ignore me and do sqlite3 ex1.db -init ex1.sql. This will load your file and drop you right into the sqlite3 shell. Type .quit to exit the shell after you're done playing with the results.

Study Drills

Portability Notes

The types used by SQLite3 are usually the same as other databases, but be careful as one of the ways SQL database vendors differentiated themselves was to "embrace and extend" certain data types. The worst of these is anything to do with date and time.

Previous Lesson Next Lesson

Register for Learn SQL the Hard Way

Register today for the course and get the all currently available videos and lessons, plus all future modules for no extra charge.