Write an SQL query that returns the studentid and name of students who have chec

Question

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.

book bookid title Introduction of Algorithms rice total copies 84.66 Database System Concepts74.99 Stochastic Calculus for Financ41.02 Stochastic Calculus for Finance II 55.22 course title Algorithms DB Organization Advanced DB Organization Math Finance I Math Finance II faculty enroll studentid courseid student studentid facultyid name gpa Tom 3.3 John3.8 Mary 3.0 Kris 3.6 Alex3.5 James 70000 Sarah 60000 ay 80000 Rache 70000 Pau85000 book_ checkout bookid studentid 2017-08-29 2017-09-02 2017-09-07

Explanation / Answer

----------
Answer:
----------

SELECT
   s.studentid,s.name,SUM(b.price)
   FROM
       book_checkout bc,student s,book b
   WHERE
       (bc.bookid = b.bookid AND bc.studentid = s.studentid)
   GROUP BY
       bc.studentid
   HAVING
       SUM(b.price)>100;
      

--------------
Explanation:
--------------
   JOIN book_checkout and student, book tables
   ON bookid for book_checkout and book tables
   and studentid for book_checkout and student tables.
  
   GROUP BY studentid   groups the records by student id
   and final a having condition will check if the sum of
   checked-out books sum above 100

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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