22
Chapter 3
SQL
b. Print the names of employees who have borrowed all books published by
McGraw-Hill.
c. For each publisher, print the names of employees who have borrowed more
than five books of that publisher.
Answer:
a. Print the names of employees who have borrowed any book published by
McGraw-Hill.
select name
from employee e, books b, loan l
where e.empno = l.empno
and l.isbn = b.isbn and
b.publisher = ’McGrawHill’
b. Print the names of employees who have borrowed all books published by
McGraw-Hill.
select distinct e.name
from employee e
where not exists
((select isbn
from books
where publisher = ’McGrawHill’)
except
(select isbn
from loan l
where l.empno = e.empno))
c. For each publisher, print the names of employees who have borrowed more
than five books of that publisher.
select publisher, name
from (select publisher, name, count isbn
from employee e, books b, loan l
where e.empno = l.empno
and l.isbn = b.isbn
group by publisher, name) as
emppub(publisher, name, count books)
where count books ¡ 5
3.22 Consider the relational schema
student(student id, student name)
registered(student id, course id)
Write an SQL query to list the student-id and name of each student along with
the total number of courses that the student is registered for. Students who are
not registered for any course must also be listed, with the number of registered
Exercises
23
courses shown as 0.
Answer:
select student id, student name, count courses
from (select student id, count course id
from registered group by student id) as
count(student name, count courses)), student
where count.student id = student.student id
union
select student id, student name, 0
from student where student id not in
(select student id from registered)
3.23 Suppose that we have a relation marks(student id, score). Write an SQL query to
find the dense rank of each student. That is, all students with the top mark get a
rank of 1, those with the next highest mark get a rank of 2, and so on. Hint: Split
the task into parts, using the with clause.
Answer:
with i