Description
In this assignment, you will practice working with SQL as discussed in lectures SQL Part 1, SQL Part 2, and Views.1 Your solutions, containing the
PostgreSQL statements for solving the problems, should be submitted to IUCanvas in the required format. (See previous announcement.) It is strongly
recommended that you include comments in this file to elaborate on your solutions.
In this assignment, we will use the following relation schemas about students
and books.
Student(Sid, Sname)
Major(Sid, M ajor)
Book(BookNo, T itle, P rice)
Cites(BookNo, CitedBookNo)
Buys(Sid, BookNo)
The relation Major stores students and their majors. A student can have
multiple majors but we also allow that a student has no major. major. A tuple
(b, c) in the relation Cites indicates that the book with book number b cites the
book with book number c. Note that a book may cite multiple other books.
Also, a book does not have to cited.
The primary keys of the relations are the underlined attributes and we assume the following foreign keys:
Attribute in Relation References Primary Key of Relation
Sid in Major Sid in Student
BookNo in Cites BookNo in Book
CitedBookNo in Cites BookNo in Book
Sid in Buys Sid in Student
BookNo in Buys BookNo in Book
Furthermore, assume the following domains for the attributes:
Attribute Domain
Sid INTEGER
Sname TEXT
Major TEXT
BookNo INTEGER
Title TEXT
Price INTEGER
CitedBookNo INTEGER
1Restrictions on SQL code: You can use views but you can not use the GROUP BY clause
and aggregate functions. You can also not use the INNER JOIN (or other joins) operators.
Solutions with SQL statements that do not obey these requirements will not receive credit.
1
To do this assignment, you will have to create the above relations, including
the primary and foreign keys. For data, use the data.sql file provided with
this assignment.
Formulate the following queries in SQL. In these queries, unless otherwise
specified, you can not use views (including temporary and parameterized views).
1. Find the sid and name of each student who majors in CS and who bought
a book that cost more than $10.
(a) Formulate this query in SQL without using subqueries and set predicates.
(b) Formulate this query in SQL by only using the IN or NOT IN set
predicates.
(c) Formulate this query in SQL by only using the SOME or ALL set predicates.
(d) Formulate this query in SQL by only using the EXISTS or NOT EXISTS
set predicates.
2. Find the bookno, title, and price of each book that was not bought by any
Math student.
(a) Formulate this query in SQL without using subqueries and set predicates.
(b) Formulate this query in SQL by only using the IN or NOT IN set
predicates.
(c) Formulate this query in SQL by only using the SOME or ALL set predicates.
(d) Formulate this query in SQL by only using the EXISTS or NOT EXISTS
set predicates.
3. Find the bookno, title, and price of each book that cites at least two books
that cost less than $60.
(a) Formulate this query in SQL without using subqueries and set predicates.
(b) Formulate this query in SQL by only using the IN or NOT IN set
predicates.
(c) Formulate this query in SQL by only using the EXISTS or NOT EXISTS
set predicates.
2
4. Find the sid and name of each student along with the title and price of
the most expensive book(s) bought by that student.
(a) Formulate this query in SQL without using subqueries. (Observe
that a most expensive book is a book wherefore there does not exists
another book that is more expensive.)
(b) Formulate this query in SQL by using subqueries and set predicates.
5. Find the sid and name of each student who bought at most one book that
cost more than $20.
6. Without using the ALL or SOME set predicates, find the booknos and titles
of books with the next to highest price.
7. Find the bookno, title, and price of each book that cites a book which is
not among the most expensive books.
8. Find the sid and name of each student who has a single major and such
that none of the book(s) bought by that student cost less than $40.
9. Find the bookno and title of each book that is bought by all students who
major in both ‘CS’ and in ‘Math’.
10. Find the sid and name of each student who, if he or she bought a book
that cost at least $70 then he also bought a book that cost less than $30.
11. Find each pair (s1, s2) where s1 and s2 are the sids of students who have
a common major but who did not buy the same books.
12. Find the tuple (s1, b1, s2, sb) such that if the student with sid s1 bought
book with bookno b1 then the student with sid s2 did not buy the book
with bookno b2.
13. Define a view bookAtLeast30 that defines the books whose price is at
least $30.
Consider the query “Find the sid and name of each student who bought
fewer than two books that cost less than $30.”
Write a SQL that uses the view bookAtLeast30 to solve this query.
After solving this problem drop the view bookAtLeast30
14. Reconsider the query in Problem 13. Redo this problem but this time by
using temporary views (i.e., use the WITH statement).
3
15. Write a parameterized view citesBooks (b integer) that returns the
relation of books that are cited by book b. (For each book returned by
citesBooks include all information, i.e., bookno, title, and price.)
(a) Use this parameterized view to write a SQL query that finds the
bookno and title of each book that cites the book with bookno 2001
as well as cites a book that cost less than $50.
(b) Use this parameterized view to write a SQL query that finds the
bookno and title of each book that cites at least two books.
4