Computer Science 380:

Principles of Database Systems

Chapter 3

Gregory M. Kapfhammer


creative commons licensed ( BY-NC-SA ) flickr photo shared by danmachold

SQL Features

Data definition language (DDL)

Data manipulation language (DML)

Integrity constraint specification

View definition

Transaction control

Embedded and dynamic SQL

Authentication and authorization

Data Types

char(n) and varchar(n)

Similarities and differences in character types?

int and smallint

numeric(p, d) for specific p and d

float(n) for a specific n

See page 59 for a discussion of the basic SQL types!


creative commons licensed ( BY-NC ) flickr photo shared by delete08

Create Table

Part of the data definition language

What are the fundamental components?

Check the diagram in the SQLite documentation!

Attributes and their domains

Integrity constraints

Important Comparison

The distinction between DML and DDL can be confusing

Compare: drop table r and delete from r

How are their actions similar and different?

What about DML?

Which one is DDL?

Selecting Data

The general form of a SELECT

Some examples of SELECT statements

Queries on a single table or on multiple tables

Wait, how does the DBMS execute the SELECT?

A wide variety of additional basic operations

Examining a SELECT in JoSQL

NULL Values

Review: How do NULL values get into the database?

We're in trouble! Different DBMSs have different rules!

"But the descriptions in the SQL standards on how to handle NULLs seem ambiguous."

For more details, please read NULL Values in SQLite

What are some standard rules for NULL values?


creative commons licensed ( BY-NC-ND ) flickr photo shared by Separate Sky

Aggregation

Use functions like MIN, MAX, AVG, SUM, and COUNT

Used in conjunction with GROUP BY clauses

The HAVING clause can be applied to each group

Declarative summarization of a data set

Transactions

Demarcated by a BEGIN and END

Two possible options: COMMIT or ROLLBACK

Goal: Uphold the ACID properties

Integrity constraint violation during transactions?

Does SQLite provide full transaction support?


creative commons licensed ( BY-NC-ND ) flickr photo shared by chrisowenrichards