Gregory M. Kapfhammer
Data definition language (DDL)
Data manipulation language (DML)
Integrity constraint specification
View definition
Transaction control
Embedded and dynamic SQL
Authentication and authorization
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!
Part of the data definition language
What are the fundamental components?
Check the diagram in the SQLite documentation!
Attributes and their domains
Integrity constraints
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?
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
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?
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
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?