Introduction to Databases

Class 3 - More SQL

Welcome!

Overview

Today, we will learn:

• How to create a database
• How to create, alter and drop tables
• A little about column constraints
• How to insert, update and delete records

Create Database

From Windows Command Line Tool:

sqlite> .open test.db

From Mac Terminal:

$ sqlite3 test.db

In each case, SQLite3 attempts to open a database called "test.db", which does not exists. Therefore, it is created.

Create Tables

The CREATE statement is used to create tables. It is also used to create indexes, views and triggers.

To create a table, we give a name to a table and to its columns. Each column can have one of these data types:

• NULL - The value is a NULL value
• INTEGER - a signed integer
• REAL - a floating point value
• TEXT - a text string
• BLOB - a blob of data

sqlite> CREATE TABLE Testing(Id INTEGER);
sqlite> .schema Testing
sqlite> CREATE TABLE Testing(Id INTEGER);

Drop Tables

The DROP statement is used to delete a table from a database.

sqlite> DROP TABLE Testing;
sqlite> .tables

Drop Tables

If you are trying to drop a table that does not exist, you will get an error. We can avoid this with the "IF EXISTS" clause.

sqlite> DROP TABLE IF EXISTS Testing;
sqlite> .tables

Alter Tables

• Rename a table
• Add a column to a table
• In SQLite, ALTER cannot:
  • Rename a column
  • Remove a column
  • Add or remove constraints from a table

sqlite> CREATE TABLE Names(Id INTEGER, Name TEXT);
sqlite> .tables
sqlite> ALTER TABLE Names RENAME TO NamesOfFriends;
sqlite> .schema NamesOfFriends
sqlite> ALTER TABLE NamesOfFriends ADD COLUMN Email TEXT;
sqlite> .schema NamesOfFriends

What if Alter isn't good enough?

Say we really do need to rename or remove a column. What do we do?

• Rename the old table
• Create the new table, based on the old table but with the updated column
• Copy the contents across from the original table
• Drop the old table

The .dump Table command in SQLite will output the instructions for recreating a table and its contents.

sqlite> .dump Media

Primary and Foreign Keys

sqlite> .schema InvoiceLine
CREATE TABLE [InvoiceLine]
(
    [InvoiceLineId] INTEGER  NOT NULL,
    [InvoiceId] INTEGER  NOT NULL,
    [TrackId] INTEGER  NOT NULL,
    [UnitPrice] NUMERIC(10,2)  NOT NULL,
    [Quantity] INTEGER  NOT NULL,
    CONSTRAINT [PK_InvoiceLine] PRIMARY KEY  ([InvoiceLineId]),
    FOREIGN KEY ([InvoiceId]) REFERENCES [Invoice] ([InvoiceId])
		ON DELETE NO ACTION ON UPDATE NO ACTION,
    FOREIGN KEY ([TrackId]) REFERENCES [Track] ([TrackId])
		ON DELETE NO ACTION ON UPDATE NO ACTION
);

We will ignore indexes in this class and discuss them next week.

Constraints

Constraints are placed on columns. They limit the data that can be inserted into tables.

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

NOT NULL CONSTRAINT

A column with a NOT NULL constraint can't have NULL values.

sqlite> CREATE TABLE People(Id INTEGER, LastName TEXT NOT NULL,
	...> FirstName TEXT NOT NULL, City TEXT);
sqlite> INSERT INTO People VALUES(1, 'Hanks', 'Robert', 'New York');
sqlite> INSERT INTO People VALUES(2, NULL, 'Marianne', 'Chicago');
Error: People.LastName may not be NULL

UNIQUE Constraint

The UNIQUE constraint ensures that all data in a column are unique.

sqlite> CREATE TABLE Brands(Id INTEGER, BrandName TEXT UNIQUE);
sqlite> INSERT INTO Brands VALUES(1, 'Coca Cola');
sqlite> INSERT INTO Brands VALUES(2, 'Pepsi');
sqlite> INSERT INTO Brands VALUES(3, 'Pepsi');
Error: column BrandName is not unique

Note: the PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Primary Key Constraint

• Uniquely identifies each row in a database table.
• There can be additional UNIQUE columns, but only one PRIMARY KEY.
• We use prmary keys as unique IDs to refer to table rows.
• Primary keys become foreign keys when creating relations among tables.

Note: Due to an oversight in SQLite, primary keys can be NULL in SQLite. This is not true for other relational databases.

Foreign Key Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table. This acts as a referential constraint between the two tables.

sqlite> CREATE TABLE Authors(AuthorId INTEGER PRIMARY KEY, Name TEXT);
sqlite> CREATE TABLE Books(BookId INTEGER PRIMARY KEY, Title TEXT, AuthorId INTEGER,
	 ...> FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId));

In SQLite, foreign keys are not enforced by default. Configuration is outside the scope of this class, but other databases will enforce by default.

CHECK Constraint

A CHECK clause imposes a validation on the value that is being added or updated to a column.

sqlite> CREATE TABLE Orders(Id INTEGER PRIMARY KEY, OrderPrice INTEGER CHECK(OrderPrice>0), Customer TEXT);
sqlite> INSERT INTO Orders(OrderPrice, Customer) VALUES(-10, 'Johnson');
Error: constraint failed

If we try to insert an order wtih a negative price, we get an error.

DEFAULT Constraint

The DEFAULT constraint inserts a default value in a column if no value is provided.

sqlite> CREATE TABLE Hotels(Id INTEGER PRIMARY KEY, Name TEXT,
	...> City TEXT DEFAULT 'not available');
sqlite> INSERT INTO Hotels(Name, City) VALUES('Kyjev', 'Bratislava');
sqlite> INSERT INTO Hotels(Name) VALUES('Slovan');
sqlite> .width 3 8 17
sqlite> SELECT * FROM Hotels;
Id   Name      City
---  --------  -----------------
1    Kyjev     Bratislava
2    Slovan    not available 

In the first statement, we provided values for both name and city. In the second statement, we only provided the hotel name.

Let's Develop It!

Create your own version of the Chinook database. Use the diagram below as a guide.

Let's Develop It!

Hint: Order matters due to foreign key constraints.

• Artist
• Album
• Employee
• Customer
• Genre
• Invoice
• MediaType
• Track
• InvoiceLine
• Playlist
• PlaylistTrack

Solution

The solution can be found by executing the .schema command on each table for the Chinook database.

Alternatively, you can download this list of CREATE statements.

Inserting Rows

The INSERT statement is used to insert data into tables. Let's create a new table as an example.

sqlite> CREATE TABLE Books(Id INTEGER PRIMARY KEY, Title TEXT, Author TEXT, ISBN TEXT DEFAULT 'not available');

Now, we will use the INSERT statement to create a row with all the values we want to store.

sqlite> INSERT INTO Books(Id, Title, Author, ISBN)
   ...> VALUES(1, 'War and Peace', 'Leo Tolstoy', '978-0345472403');

We omitted the Id column, as it was defined as an INTEGER PRIMARY KEY. In SQLite, this will autoincrement for us.

Inserting Rows

Let's add one more:

sqlite> INSERT INTO Books(Title, Author, ISBN)
   ...> VALUES('The Brothers Karamazov', 'Fyodor Dostoyevsky', '978-0486437910');

And we can use a SELECT statement to view what we inserted.

sqlite> SELECT * FROM Books;
Id|Title|Author|ISBN
1|War and Peace|Leo Tolstoy|978-0345472403
2|The Brothers Karamazov|Fyodor Dostoyevsky|978-0486437910

Inserting Rows

We can insert a record without specifying column names.

sqlite> INSERT INTO Books VALUES(3, 'Crime and Punishment', 'Fyodor Dostoevsky',
   ...> '978-1840224306');

In this case, we must supply all values.

Null Values

By default, SQLite will show empty strings for NULL values. We can change this behavior.

sqlite> .nullvalue NULL

Omitting Columns

We can also insert records that are missing values for some columns.

sqlite> INSERT INTO Books(Id, Title) VALUES(4, 'Paradise Lost');

Note the difference between the NULL value for the unspecified column and the default value of "not available" for the description.

sqlite> SELECT * FROM Books WHERE Id=4;
Id|Title|Author|ISBN
4|Paradise Lost|NULL|not available

Updating Records

Let's say we want to add the missing information for "Paradise Lost".

sqlite> INSERT INTO Books VALUES(4, 'Paradise Lost', 'John Milton', '978-0486442877');
Error: PRIMARY KEY must be unique
sqlite> INSERT OR REPLACE INTO Books VALUES(4, 'Paradise Lost', 'John Milton',
   ...> '978-0486442877');

More commonly, you would use an UPDATE statement.

Updating Records

The UPDATE statement is used to change the value of columns in selected rows of a table.

Say we want to change "Leo Tolstoy" to "Lev Nikolayevich Tolstoy" in our Books table.

sqlite> UPDATE Books SET Author='Lev Nikolayevich Tolstoy' WHERE Id=1;
sqlite> SELECT * FROM Books WHERE Id=1;
Id|Title|Author|ISBN
1|War and Peace|Lev Nikolayevich Tolstoy|978-0345472403

The row is correctly updated.

Deleting Records

The DELETE keyword is used to delete data from tables.

sqlite> DELETE FROM Books WHERE Id=1;
SELECT * FROM Books;
Id|Title|Author|ISBN
2|The Brothers Karamazov|Fyodor Dostoyevsky|978-0486437910
3|Crime and Punishment|Fyodor Dostoevsky|978-1840224306
4|Paradise Lost|NULL|not available

WARNING: Like the DROP statement, DELETE can be destructive. Mind your WHERE clause!

Deleting Records

Appetite for Destruction

sqlite> DELETE FROM Books;

No WHERE clause means that this statement deletes all data in the table!

Let's Develop It!

Using the database you created in the first exercise, practice inserting, updating and deleting records.

Questions?