[Solved] B561 Assignment 2-practice working with SQL

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,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.
   • Formulate this query in SQL without using subqueries and set predicates.
   • Formulate this query in SQL by only using the IN or NOT IN set predicates.
   • Formulate this query in SQL by only using the SOME or ALL set predicates.
   • 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.
   • Formulate this query in SQL without using subqueries and set predicates.
   • Formulate this query in SQL by only using the IN or NOT IN set predicates.
   • Formulate this query in SQL by only using the SOME or ALL set predicates.
   • 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.
   • Formulate this query in SQL without using subqueries and set predicates.
   • Formulate this query in SQL by only using the IN or NOT IN set predicates.
   • Formulate this query in SQL by only using the EXISTS or NOT EXISTS set predicates.

[1] Restrictions 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.