Constraints and Triggers Exercises 1
- Review of Abbreviated Books DB With Constraints
- Spotlight: general assertions are different from in-table checks
- Exercises with Dorm Energy DB tables.
- A reminder to reference SQL source materials (e.g., on Foreign Key constraints)
- Some anomalies about SQLite, notably with Primary Keys
- Exercise with FK actions
- Challenge Problem: add a table CoBought to the Books database with fields Book1 (which references an Isbn in the Book table), Book2 (ditto), and an integer Copurchased#, which gives the number of times that the same customer bought the corresponding pair of books (though not necessarily as part of the same transaction). You want to use this information for a recommender system (“Customers who bought book1 also bought book2 lots of times”). Setting aside certain other constraints that would likely be enforced in a real DB (e.g., that to be recorded in the DB, the copurchased books had to be by the same author or in the same topic area), write an SQL query that returns the top 100 pairs of co-purchased books. Your resulting list may actually be greater than 100 (in the case where there are ties between pairs of co-purchases).
- A Preview of Constraints on a Book Seller Database
- Challenge Problem of 2/9 (This file contains possible answers. In the second query of page 2, there are two typos. I left out a ‘)’ to close the nested query in the first WHERE clause, and my reference to ‘Temp2.Copurchased#’ in the third line from bottom of that query should be ‘C2.Copurchased#’)
