1. Write an SQL query that returns the studentid and name of students who have c

Question

1. Write an SQL query that returns the studentid and name of students who have checked out books that are worth more than $100 in total.
2. Write an SQL query that returns the studentid and name of students who checked out the textbook of a course that they did not enroll in.

Consider the following library database schema and example instance storing book bookid price total_copies 84.66 uction of Algorit Database System Concepts74.99 Stochastic Calulus for Finance 41.02 Stochastic Calculus for Finance II 55.22 course courseid titl structor text DB Organization Advanced DB Organization Math Finance I Math Finance II student faculty enroll studentid courseid studentid name gpa Tom 3.3 John 38 Mary 3.0 Kris 3.6 Alex 3.5 facultyiod James70000 Sarah60000 Jay80000 Rache 70000 Pau85000 book checkout bookid studentid date 2017-08-29 2017-09-02 2017-09-07 Hints: All the attributes that have integer values are of type INT; numbers with decimal point are of type NUMERIC; the attribute date of book checkout relation is of type DATE; others are of type VARCHAR Attributes with black background form the primary key of an relation The attribute instructorid of relation course is a foreign key to relation facuity, and tertbookid is a foreign key to relation book. The attribute studentid of relation enroll is a foreign key to relation student, and courseid is a foreign key to relation course. The attribute bookid of relation book_checkout is a foreign key to relation book, and studentid is a foreign key to relation student.

Explanation / Answer

The below SQL statements satisfies the given requirements as follows:

---------------------------------------------------------------------------------------------------

1. Write an SQL query that returns the studentid and name of students who have checked out books that are worth more than $100 in total.

--Here we are joining the three tables and calculating the sum.

select studentid,name from

(select studentid,name,sum(price) as sum from

(SELECT student.studentid,student.name,book_checkout.bookid,book.price

FROM book_checkout

INNER JOIN student ON student.studentid=book_checkout.studentid

INNER JOIN book ON book.bookid=book_checkout.bookid) group by name)where sum>=100

Output:-

---------------

studentid name

4 Kris

2. Write an SQL query that returns the studentid and name of students who checked out the textbook of a course that they did not enroll in.

--Here we are joining the three tables and calculating the ids which are not matching with the course ids.

SELECT distinct

studentid,name from

(SELECT student.studentid,student.name,book_checkout.bookid,enroll.courseid,enroll.courseid as cour

FROM book_checkout

INNER JOIN student ON student.studentid=book_checkout.studentid

INNER JOIN enroll ON student.studentid=enroll.studentid

INNER JOIN enroll ON textbookid=book_checkout.bookid) where bookid<>cour

Output:-

-------------

studentid name

4 Kris

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.