Relational Databases Part 2: SQL

CS 240 – Advanced Programming Concepts

​

Structured Query Language (SQL)

SQL

• Language for performing relational database operations
• Data Definition Language (DDL)
• Create tables
• Delete tables
• Alter tables
• Data Manipulation Language (DML)
• Insert rows
• Update rows
• Delete rows
• Data Query Language (DQL)
• Search the database tables for data of interest

BLANK SLIDE

SQL Data Types

SQL Data Types – Strings

• Each column in an SQL table declares the type that column may contain
• Character strings
• CHARACTER(n) or CHAR(n) — fixed-width n-character string, padded with spaces as needed
• CHARACTER VARYING(n) or VARCHAR(n) — variable-width string with a maximum size of n characters
• Bit strings
• BIT(n) — an array of n bits
• BIT VARYING(n) — an array of up to n bits

​

SQL Data Types – Numbers and Large Objects

• Numbers
• INTEGER and SMALLINT
• FLOAT, REAL and DOUBLE PRECISION
• NUMERIC(precision, scale) or DECIMAL(precision, scale)
• Large objects
• BLOB – binary large object (images, sound, video, etc.)
• CLOB – character large object (text documents)

​

SQL Data Types – Date and Time

• DATE — for date values (e.g., 2011-05-03)
• TIME — for time values (e.g., 15:51:36). The granularity of the time value is usually a tick (100 nanoseconds).
• TIME WITH TIME ZONE or TIMETZ — the same as TIME, but including details about the time zone.
• TIMESTAMP — This is a DATE and a TIME put together in one value (e.g., 2011-05-03 15:51:36).
• TIMESTAMP WITH TIME ZONE or TIMESTAMPTZ — the same as TIMESTAMP, but including details about the time zone.

​

BLANK SLIDE

Creating and Dropping Tables

Creating Tables

• CREATE TABLE
• Primary Keys
• Null / Not Null
• Autoincrement
• Foreign keys

​

create table book

(

id integer not null primary key auto_increment,

title varchar(255) not null,

author varchar(255) not null,

genre varchar(32) not null,

category_id integer not null,

foreign key(genre) references genre(genre),

foreign key(category_id) references category(id)

);

create table if not exists

(

…

)

Foreign Key Constraints

• Not required – can query without them
• Enforce that values used as foreign keys exist in their parent tables
• Disallow deletes of the parent table row when referenced as a foreign key in another table
• Disallow updates of the parent row primary key value if that would “orphan” the foreign keys
• Can specify that deletes and/or updates to the primary keys automatically affect the foreign key rows
• foreign key(genre) references genre(genre) on update cascade on delete restrict
• Available actions:
• No Action, Restrict, Set Null, Set Default, Cascade

Dropping Tables

• Drop Table
• drop table book;
• drop table if exists book;

​

• When using foreign key constraints, order of deletes matters
• Can’t delete a table with columns being used as foreign keys in another table (delete the table with the foreign keys first)

BLANK SLIDE

Inserting, Updating and Deleting Rows

Inserting Data into Tables

• INSERT
• insert into book

(title, author, genre, category_id) values ('The Work and the Glory', 'Gerald Lund', 'HistoricalFiction', 3);

________________

• Complete Example
• create-db.sql.txt

Updates

UPDATE Table

SET Column = Value, Column = Value, …

WHERE Condition

Change a member’s information

UPDATE member

SET name = ‘Chris Jones’,

email_address = ‘chris@gmail.com’

WHERE id = 3

Set all member email addresses to empty

UPDATE member

SET email_address = ‘’

Deletes

DELETE FROM Table

WHERE Condition

Delete a member

DELETE FROM member

WHERE id = 3

Delete all readings for a member

DELETE FROM books_read

WHERE member_id = 3

Delete all books

DELETE FROM book

BLANK SLIDE

Retrieving Data with SQL Queries

Queries

SELECT Column, Column, …

FROM Table, Table, …

WHERE Condition

Queries (2)

book

List all books

SELECT *

FROM book

result

Queries (3)

book

List the authors and titles of all non-fiction books

SELECT author, title

FROM book

WHERE genre = "NonFiction"

result

Queries (4)

category

List the sub-categories of category ‘Top’

SELECT id, name, parent_id

FROM category

WHERE parent_id = 1

result

Queries – Cartesian Product

List the books read by each member

SELECT member.name, book.title

FROM member, books_read, book

Member x Books_Read x Book (3 x 6 x 4 = 72 rows)

Probably not what you intended

Queries - Join

List the books read by each member

SELECT member.name, book.title

FROM member, books_read, book

WHERE member.id = books_read.member_id AND

book.id = books_read.book_id

result

Queries – Join 2

List the non-fiction books read by each member

SELECT member.name,  book.title

FROM member, books_read,  book

WHERE member.id = books_read.member_id AND 

      book.id = books_read.book_id AND

      genre = "NonFiction"

result

Queries – Join 2 (with Join Clauses)

List the non-fiction books read by each member

SELECT member.name,  book.title

FROM member

INNER JOIN books_read ON member.id = books_read.member_id

INNER JOIN book ON books_read.book_id = book.id

WHERE genre = "NonFiction"

result

BLANK SLIDE

Database Transactions

Database Transactions

• By default, each SQL statement is executed in a transaction by itself
• Transactions are useful when they consist of multiple SQL statements, since you want to make sure that either all of them or none of them succeed
• For a multi-statement transaction,
• BEGIN TRANSACTION;
• SQL statement 1;
• SQL statement 2;
• …
• COMMIT TRANSACTION; or ROLLBACK TRANSACTION;

Blank