B561-Assignment 2 SQL Solved

In this assignment, you will practice working with SQL

In this assignment, we will use the following relation schemas about students and books.

Student(Sid,Sname)

Major(Sid,Major)

Book(BookNo,Title,Price)

 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
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 boughta 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 anyMath 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 booksthat 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.

4.      Find the sid and name of each student along with the title and price ofthe 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 thatcost 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 isnot among the most expensive books.

8.      Find the sid and name of each student who has a single major and suchthat 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 whomajor in both ‘CS’ and in ‘Math’.

10.   Find the sid and name of each student who, if he or she bought a bookthat 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 byusing temporary views (i.e., use the WITH statement).

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 thebookno 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 thebookno and title of each book that cites at least two books.