EECS 484, Database Management Systems
SQL Queries for Booktown and University Schemas
Twelve Oracle SQL queries and a view answering questions over a bookstore schema (authors, books, editions, publishers, subjects) and a university schema (students, courses, enrollments, projects).
What I built
- Wrote GROUP BY / HAVING queries with conditional aggregation (COUNT DISTINCT CASE, SUM CASE) to find authors spanning two subjects and students whose partners took specific course combinations.
- Expressed relational division with NOT EXISTS over a MINUS to find authors who covered every subject J. K. Rowling wrote in.
- Created a StudentPairs view using a self-join on enrollments with ordered pairs and an anti-join on shared projects.
- Received full marks on the autograder.