Gregory M. Kapfhammer
Data definition language (DDL)
Data manipulation language (DML)
Integrity constraint specification
Embedded and dynamic SQL
Authentication and authorization
Similarities and differences in character types?
numeric(p, d) for specific
float(n) for a specific
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
The distinction between DML and DDL can be confusing
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
Some examples of
Queries on a single table or on multiple tables
Wait, how does the DBMS execute the
A wide variety of additional basic operations
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?