router router - 3 months ago 15
SQL Question

Queries about database management system

i want to write these queries


  • show the name of students who never Loaned Poetry books.

  • show the Name of student who Loaned more than 5 books.

  • show student name, book Title, issue Date, and author name of all bookes issued books.



Query i wrote so far is not working for first task

SELECT
name
FROM
students
INNER JOIN issued ON students.rollno = issued.rollno
GROUP BY
issued.rollno
HAVING
COUNT( issued.rollno )> 2

Answer

Give these queries a try

select s.name from students s
left join issued i on (s.rollno = i.rollno)
left join bookcopy bc on (i.copyid = bc.copyid)
left join books b on (bc.bookid = b.bookid)
left join category c on (b.catid = c.catid and c.catname = 'Poetry')
where c.catid is null

select s.name, count(*) as from students s
left join issued i on (s.rollno = i.rollno)
group by s.name
having count(*) > 5

select s.name, b.title, a.authorname, i.issuedate from students s
left join issued i on (s.rollno = i.rollno)
left join bookcopy bc on (i.copyid = bc.copyid)
left join books b on (bc.bookid = b.bookid)
left join author a on (b.authorid = a.authorid);
Comments