Before you keep reading...

Runestone Academy can only continue if we get support from individuals like you. As a student you are well aware of the high cost of textbooks. Our mission is to provide great books to you for free, but we ask that you consider a $10 donation, more if you can or less if $10 is a burden.

Support Runestone Academy Today

Before you keep reading...

Making great stuff takes time and $$. If you appreciate the book you are reading now and want to keep quality materials free for other students please consider a donation to Runestone Academy. We ask that you consider a $10 donation, but if you can give more thats great, if $10 is too much for your budget we would be happy with whatever you can afford as a show of support.

Support Runestone Academy Today

1. SQL

Part 1 of this book is all about SQL, the most popular language used for interacting with relational databases, the main focus of the book. The chapters in this section have been organized to allow topics to be introduced in somewhat different orders according to the instructor’s desired approach. The diagram below shows the strong dependencies between chapters; chapters will assume knowledge introduced in earlier chapters as implied by the graph. Chapters without a strong dependency may still refer to other chapters in passing, with a link to the chapter explaining the reference; these weak dependencies are not critical for understanding the referencing chapter.

Chapter dependency graph for Part 1

Table of contents for Part 1

• 1.1. Basics
  • 1.1.1. Databases and database systems
  • 1.1.2. Tables
  • 1.1.3. Structured Query Language (SQL)
  • 1.1.4. Retrieving data using SELECT
    • 1.1.4.1. Retrieving specific columns
    • 1.1.4.2. Looking ahead
  • 1.1.5. Creating tables and adding data
    • 1.1.5.1. Looking ahead
  • 1.1.6. SQL statement rules and conventions
  • 1.1.7. Comments
  • 1.1.8. Interactive examples
• 1.2. Data retrieval
  • 1.2.1. Tables used in this chapter
  • 1.2.2. Filtering rows: the WHERE clause
  • 1.2.3. Ordering data: the ORDER BY clause
  • 1.2.4. Retrieving unique rows: the DISTINCT keyword
  • 1.2.5. Self-check exercises
• 1.3. Expressions
  • 1.3.1. Tables used in this chapter
  • 1.3.2. Column expressions
  • 1.3.3. Literals
  • 1.3.4. Operators and functions
    • 1.3.4.1. Comparison operators
    • 1.3.4.2. Mathematics
    • 1.3.4.3. Character string operators and functions
    • 1.3.4.4. Boolean operators
    • 1.3.4.5. Date and time operators and functions
  • 1.3.5. NULL
    • 1.3.5.1. Ordering and NULLs
  • 1.3.6. Conditional expressions
  • 1.3.7. Self-check exercises
• 1.4. Queries on multiple tables
  • 1.4.1. Tables used in this chapter
  • 1.4.2. Simple joins
  • 1.4.3. Names of things
    • 1.4.3.1. Name collisions and ambiguity
    • 1.4.3.2. Qualified names
    • 1.4.3.3. Aliasing
    • 1.4.3.4. Reserved names, names with spaces, or mixed-case names
  • 1.4.4. Identity columns
  • 1.4.5. Table relationships
    • 1.4.5.1. One-to-one
    • 1.4.5.2. One-to-many
    • 1.4.5.3. Many-to-many
  • 1.4.6. Inner and outer joins
  • 1.4.7. Implicit join syntax
  • 1.4.8. Self-check exercises
• 1.5. Modifying data
  • 1.5.1. Tables used in this chapter
  • 1.5.2. Adding data using INSERT
    • 1.5.2.1. Specifying columns
    • 1.5.2.2. Inserting multiple rows
    • 1.5.2.3. Inserting query results
  • 1.5.3. Removing data with DELETE
  • 1.5.4. Modifying data with UPDATE
  • 1.5.5. Other data modification statements
  • 1.5.6. Self-check exercises
• 1.6. Data types and table creation
  • 1.6.1. Data types
    • 1.6.1.1. Numbers
    • 1.6.1.2. Character string types
    • 1.6.1.3. Date and time types
    • 1.6.1.4. Additional data types
    • 1.6.1.5. Types in SQLite
  • 1.6.2. Creating tables
    • 1.6.2.1. Creating a table from scratch
    • 1.6.2.2. Dropping tables
    • 1.6.2.3. Creating a table from a query
    • 1.6.2.4. Defaults and auto increments
  • 1.6.3. Self-check exercises
• 1.7. Keys and constraints
  • 1.7.1. Tables used in this chapter
  • 1.7.2. Constraints
  • 1.7.3. Primary keys
    • 1.7.3.1. Creating a primary key
  • 1.7.4. Foreign keys
    • 1.7.4.1. Creating a foreign key constraint
    • 1.7.4.2. Enforcement mechanisms
  • 1.7.5. Other constraints
    • 1.7.5.1. UNIQUE
    • 1.7.5.2. NOT NULL
    • 1.7.5.3. CHECK
• 1.8. Subqueries
  • 1.8.1. Tables used in this chapter
  • 1.8.2. Scalars, rows, and tables
  • 1.8.3. Boolean expressions using subqueries
    • 1.8.3.1. Scalar or row result
    • 1.8.3.2. Table or column result
    • 1.8.3.3. Use in statements
  • 1.8.4. Correlated subqueries
    • 1.8.4.1. EXISTS
  • 1.8.5. Subqueries in other clauses
    • 1.8.5.1. SELECT
    • 1.8.5.2. SET
    • 1.8.5.3. FROM
  • 1.8.6. Comparison with joins
  • 1.8.7. Self-check exercises
• 1.9. Grouping and aggregation
  • 1.9.1. Tables used in this chapter
  • 1.9.2. Aggregate statistics
  • 1.9.3. Grouping
    • 1.9.3.1. Filtering grouped data
  • 1.9.4. Self-check exercises
• 1.10. Set operations
  • 1.10.1. Tables used in this chapter
  • 1.10.2. Sets refresher
  • 1.10.3. Tables as sets
    • 1.10.3.1. Union
    • 1.10.3.2. Intersection
    • 1.10.3.3. Set difference
    • 1.10.3.4. Chaining operations
  • 1.10.4. Self-check exercises
• 1.11. Advanced topics
  • 1.11.1. Tables used in this chapter
  • 1.11.2. Views
  • 1.11.3. Common table expressions
  • 1.11.4. Window functions

You have attempted of activities on this page