Write an SQL query that returns the studentid and name of students who have over
ID: 3596266 • Letter: W
Question
Write an SQL query that returns the studentid and name of students who have overdue books (assume a book is due after 30 days), use construct CURRENT_DATE to access the current date in your query. Do not return any duplicate tuples.
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-07Explanation / Answer
# This query will find the name of students who have overdue books. Relation 'student' and 'book_checkout' are joined on 'studentid' attribute. In where clause difference between the 'CURRENT_DATE' and 'date' attribute of 'book_checkout' relation is calculated.
SELECT DISTINCT s.studentid, s.name
FROM student AS s
INNER JOIN book_checkout AS bc
ON s.studentid = bc.studentid
WHERE DATEDIFF(DAY, CURRENT_DATE, bc.date) > 30
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.