Scribd
Upload
35 views2 pages

Library SQL Queries

The document contains a series of SQL queries related to a Library Management System, focusing on retrieving information from the 'books' and 'book_issuers' tables. Key queries include selecting all books, counting total books, filtering by available copies, and joining tables to find issued books and their statuses. The queries also analyze data such as average available copies and the most issued books.

Uploaded by

Ojasva Gupta
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
35 views2 pages

Library SQL Queries

The document contains a series of SQL queries related to a Library Management System, focusing on retrieving information from the 'books' and 'book_issuers' tables. Key queries include selecting all books, counting total books, filtering by available copies, and joining tables to find issued books and their statuses. The queries also analyze data such as average available copies and the most issued books.

Uploaded by

Ojasva Gupta
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Library Management System - SQL Queries with Outputs

Q1: SELECT * FROM books;

Q2: SELECT * FROM books WHERE available_copies > 5;

Q3: SELECT COUNT(*) AS total_books FROM books;

Q4: SELECT title FROM books WHERE genre = 'Fiction';

Q5: SELECT title FROM books ORDER BY available_copies DESC LIMIT 1;

Q6: SELECT title, author, genre FROM books;

Q7: SELECT * FROM books WHERE title LIKE 'The%';

Q8: SELECT AVG(available_copies) AS avg_copies FROM books;

Q9: SELECT COUNT(DISTINCT author) FROM books;

Q10: SELECT * FROM books ORDER BY available_copies DESC;

Q11: SELECT [Link], [Link] FROM book_issuers i JOIN books b ON i.book_id = b.book_id;

Q12: SELECT [Link] FROM book_issuers i JOIN books b ON i.book_id = b.book_id WHERE [Link]

= 'The Alchemist';

Q13: SELECT [Link] FROM books b LEFT JOIN book_issuers i ON b.book_id = i.book_id WHERE

i.book_id IS NULL;

Q14: SELECT [Link], COUNT(i.book_id) AS books_issued FROM book_issuers i GROUP BY

[Link];

Q15: SELECT [Link] FROM books b JOIN book_issuers i ON b.book_id = i.book_id WHERE [Link]
= 'Eva Green' AND [Link] = 'returned';

Q16: SELECT [Link], COUNT(i.book_id) AS total_issues FROM book_issuers i GROUP BY [Link]

ORDER BY total_issues DESC LIMIT 1;

Q17: SELECT [Link], COUNT(i.book_id) AS total_issues FROM books b JOIN book_issuers i ON

b.book_id = i.book_id GROUP BY [Link] ORDER BY total_issues DESC LIMIT 1;

Q18: SELECT title FROM books WHERE available_copies = 0;

Q19: SELECT COUNT(*) FROM book_issuers WHERE issue_date BETWEEN '2024-01-01' AND

'2024-01-31';

Q20: SELECT [Link], [Link] FROM books b LEFT JOIN book_issuers i ON b.book_id = i.book_id;

You might also like