sample queries for a simple database
Haiqing Hua
I share news from Chinese website (you can use google translate please also subscribe my YouTube Channel) | Ideologist | Poet | Futurist | Educator | Technologist | Business Analyst | Data Analyst | Realtor |
CREATE TABLE tbl_publisher ( publisher_publisherName VARCHAR(100) PRIMARY KEY, publisher_PublisherAddress VARCHAR(200), publisher_publisherPhone VARCHAR(50) );
CREATE TABLE tbl_book ( book_BookID INT PRIMARY KEY IDENTITY, book_Title VARCHAR(100), book_publisherName VARCHAR(100), CONSTRAINT fk_publisher_name FOREIGN KEY (book_publisherName) REFERENCES tbl_publisher(publisher_publisherName) );
CREATE TABLE tbl_book_authors ( book_authors_AuthorID int PRIMARY KEY IDENTITY, book_authors_BookID INT, book_authors_AuthorName VARCHAR(100), CONSTRAINT fk_book_authors_BookID FOREIGN KEY (book_authors_BookID) REFERENCES tbl_book(book_bookID) );
CREATE TABLE tbl_library_branch ( library_branch_branchID INT PRIMARY KEY IDENTITY , library_branch_BranchName VARCHAR(100), library_branch_BranchAddress VARCHAR(200) );
CREATE TABLE tbl_borrower ( borrower_CardNo INT PRIMARY KEY IDENTITY, borrower_borrowerName VARCHAR(100), borrower_BorrowerAddress VARCHAR(200), borrower_borrowerPhone VARCHAR(50) );
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
CREATE TABLE tbl_book_loans ( book_loansID INT PRIMARY KEY IDENTITY, book_loans_bookID INT, book_loans_BranchID INT, book_loans_CardNo INT, book_lOans_DateOut DATE, book_loans_DueDate DATE, CONSTRAINT fk_book_loans_BranchID FOREIGN KEY (book_loans_BranchID) REFERENCES tbl_library_branch(library_branch_BranchID), /*CONSTRAINT fk_book_loans_CardNo FOREIGN KEY (book_loans_CardNo) REFERENCES tbl_borrower(borrower_CardNo)*/ );
CREATE TABLE tbl_book_copies( book_copies_CopiesID INT PRIMARY KEY IDENTITY, book_copies_BookID int, book_copies_BranchID int, book_copies_No_Of_Copies int, CONSTRAINT fk_book_copies_BranchID FOREIGN KEY (book_copies_BranchID) REFERENCES tbl_library_branch(library_branch_branchID) )
Sure, here are 100 example queries using the provided tables:
1. Select all publishers:
```sql
SELECT * FROM tbl_publisher;
```
2. Select all books:
```sql
SELECT * FROM tbl_book;
```
3. Select all authors:
```sql
SELECT * FROM tbl_book_authors;
```
4. Select all library branches:
```sql
SELECT * FROM tbl_library_branch;
```
5. Select all borrowers:
```sql
SELECT * FROM tbl_borrower;
```
6. Select all book loans:
```sql
SELECT * FROM tbl_book_loans;
```
7. Select all book copies:
```sql
SELECT * FROM tbl_book_copies;
```
8. Select books published by a specific publisher:
```sql
SELECT * FROM tbl_book WHERE book_publisherName = 'PublisherName';
```
9. Select books authored by a specific author:
```sql
SELECT * FROM tbl_book WHERE book_BookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'AuthorName');
```
10. Select books available in a specific library branch:
```sql
SELECT * FROM tbl_book WHERE book_BookID IN (SELECT book_copies_BookID FROM tbl_book_copies WHERE book_copies_BranchID = BranchID);
```
11. Select books borrowed by a specific borrower:
```sql
SELECT * FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_CardNo = BorrowerCardNo);
```
12. Select books with due dates after a specific date:
```sql
SELECT * FROM tbl_book_loans WHERE book_loans_DueDate > '2024-01-01';
```
13. Select books with due dates before a specific date:
```sql
SELECT * FROM tbl_book_loans WHERE book_loans_DueDate < '2024-01-01';
```
14. Select books loaned out from a specific branch:
```sql
SELECT * FROM tbl_book_loans WHERE book_loans_BranchID = BranchID;
```
15. Select books loaned out by a specific borrower:
```sql
SELECT * FROM tbl_book_loans WHERE book_loans_CardNo = BorrowerCardNo;
```
16. Select books with overdue loans:
```sql
SELECT * FROM tbl_book_loans WHERE book_loans_DueDate < GETDATE();
```
17. Select borrowers with overdue loans:
```sql
SELECT * FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_DueDate < GETDATE());
```
18. Select books published in a specific year:
```sql
SELECT * FROM tbl_book WHERE YEAR(book_publishDate) = 2024;
```
19. Select books borrowed by a borrower in a specific year:
```sql
SELECT * FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_CardNo = BorrowerCardNo AND YEAR(book_loans_DateOut) = 2024);
```
20. Select books with more than 10 copies available:
```sql
SELECT * FROM tbl_book WHERE book_BookID IN (SELECT book_copies_BookID FROM tbl_book_copies WHERE book_copies_No_Of_Copies > 10);
```
21. Select books borrowed by a specific borrower from a specific branch:
```sql
SELECT * FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_CardNo = BorrowerCardNo AND book_loans_BranchID = BranchID);
```
22. Select books with the earliest due date:
```sql
SELECT * FROM tbl_book_loans WHERE book_loans_DueDate = (SELECT MIN(book_loans_DueDate) FROM tbl_book_loans);
```
23. Select books borrowed by borrowers located in New York:
```sql
SELECT * FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_CardNo IN (SELECT borrower_CardNo FROM tbl_borrower WHERE borrower_BorrowerAddress LIKE '%New York%'));
```
24. Select books with a specific title:
```sql
SELECT * FROM tbl_book WHERE book_Title = 'BookTitle';
```
25. Select books borrowed by a specific borrower with a specific title:
```sql
SELECT * FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_CardNo = Borrow
erCardNo) AND book_Title = 'BookTitle';
```
26. Select the number of books available in each library branch:
```sql
SELECT library_branch_BranchName, COUNT(*) AS TotalBooksAvailable FROM tbl_book_copies JOIN tbl_library_branch ON tbl_book_copies.book_copies_BranchID = tbl_library_branch.library_branch_branchID GROUP BY library_branch_BranchName;
```
27. Select borrowers who have borrowed books from all branches:
```sql
SELECT FROM tbl_borrower WHERE NOT EXISTS (SELECT FROM tbl_library_branch WHERE NOT EXISTS (SELECT * FROM tbl_book_loans WHERE tbl_borrower.borrower_CardNo = tbl_book_loans.book_loans_CardNo AND tbl_book_loans.book_loans_BranchID = tbl_library_branch.library_branch_branchID));
```
28. Select borrowers who have borrowed books authored by both "Author1" and "Author2":
```sql
SELECT * FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_bookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'Author1') INTERSECT SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_bookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'Author2'));
```
29. Select the average number of copies available per book across all branches:
```sql
SELECT AVG(book_copies_No_Of_Copies) FROM tbl_book_copies;
```
30. Select the branch with the highest number of books loaned out:
```sql
SELECT library_branch_BranchName FROM tbl_library_branch WHERE library_branch_branchID = (SELECT TOP 1 book_loans_BranchID FROM tbl_book_loans GROUP BY book_loans_BranchID ORDER BY COUNT(*) DESC);
```
31. Select the title of the book with the earliest due date:
```sql
SELECT book_Title FROM tbl_book WHERE book_BookID = (SELECT TOP 1 book_loans_bookID FROM tbl_book_loans WHERE book_loans_DueDate = (SELECT MIN(book_loans_DueDate) FROM tbl_book_loans));
```
32. Select the names of borrowers who have borrowed books published by "Bloomsbury":
```sql
SELECT DISTINCT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_bookID IN (SELECT book_BookID FROM tbl_book WHERE book_publisherName = 'Bloomsbury'));
```
33. Select the names of borrowers located in New York:
```sql
SELECT * FROM tbl_borrower WHERE borrower_BorrowerAddress LIKE '%New York%';
```
34. Select the book title borrowed by borrowers:
```sql
SELECT b.book_Title, br.borrower_borrowerName FROM tbl_book b JOIN tbl_book_loans bl ON b.book_BookID = bl.book_loans_bookID JOIN tbl_borrower br ON bl.book_loans_CardNo = br.borrower_CardNo;
```
35. Select the author of the books loaned out:
```sql
SELECT ba.book_authors_AuthorName, b.book_Title FROM tbl_book_authors ba JOIN tbl_book b ON ba.book_authors_BookID = b.book_BookID JOIN tbl_book_loans bl ON b.book_BookID = bl.book_loans_bookID;
```
36. Select the bookID by "Stephen King" or "J.K. Rowling" that have been loaned out:
```sql
SELECT b.book_BookID, b.book_Title FROM tbl_book b JOIN tbl_book_authors ba ON b.book_BookID = ba.book_authors_BookID WHERE ba.book_authors_AuthorName IN ('Stephen King', 'J.K. Rowling') AND b.book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans);
```
37. Select the borrower who borrowed bookID 2:
```sql
SELECT br.borrower_borrowerName FROM tbl_borrower br JOIN tbl_book_loans bl ON br.borrower_CardNo = bl.book_loans_CardNo WHERE bl.book_loans_bookID = 2;
```
38. Select the borrower and the books they borrowed:
```sql
SELECT br.borrower_borrowerName, b.book_Title FROM tbl_borrower br JOIN tbl_book_loans bl ON br.borrower_CardNo = bl.book_loans_CardNo JOIN tbl_book b ON bl.book_loans_bookID = b.book_BookID;
```
39. Select all publishers and their associated books:
```sql
SELECT p.*, b.* FROM tbl_publisher p JOIN tbl_book b ON p.publisher_publisherName = b.book_publisherName;
```
40. Select all borrowers and their associated loans:
```sql
SELECT br.*, bl.* FROM tbl_borrower br JOIN tbl_book_loans bl ON br.borrower_CardNo = bl.book_loans_CardNo;
```
41. Select all books and their associated authors:
```sql
SELECT b.*, ba.* FROM tbl_book b JOIN tbl_book_authors ba ON b.book_BookID = ba.book_authors_BookID;
```
42. Select all library branches and their associated book copies:
```sql
SELECT lb.*, bc.* FROM tbl_library_branch lb JOIN tbl_book_copies bc ON lb.library_branch_branchID = bc.book_copies_BranchID;
```
43. Select all books with their associated copies and library branches:
```sql
SELECT b.*, bc.*, lb.* FROM tbl_book b JOIN tbl_book_copies bc ON b.book_BookID = bc.book_copies_BookID JOIN tbl_library_branch lb ON bc.book_copies_BranchID = lb.library_branch_branchID;
```
44. **Select all borrowers with their associated loans and borrowed books
:**
```sql
SELECT br.*, bl.*, b.* FROM tbl_borrower br JOIN tbl_book_loans bl ON br.borrower_CardNo = bl.book_loans_CardNo JOIN tbl_book b ON bl.book_loans_bookID = b.book_BookID;
```
45. Select all books with their associated authors and publishers:
```sql
SELECT b.*, ba.*, p.* FROM tbl_book b JOIN tbl_book_authors ba ON b.book_BookID = ba.book_authors_BookID JOIN tbl_publisher p ON b.book_publisherName = p.publisher_publisherName;
```
46. Select all publishers with their associated books and authors:
```sql
SELECT p.*, b.*, ba.* FROM tbl_publisher p JOIN tbl_book b ON p.publisher_publisherName = b.book_publisherName JOIN tbl_book_authors ba ON b.book_BookID = ba.book_authors_BookID;
```
47. Select all library branches with their associated book copies and borrowed books:
```sql
SELECT lb.*, bc.*, bl.* FROM tbl_library_branch lb JOIN tbl_book_copies bc ON lb.library_branch_branchID = bc.book_copies_BranchID JOIN tbl_book_loans bl ON bc.book_copies_BookID = bl.book_loans_bookID;
```
48. Select all borrowers with their associated loans and library branches:
```sql
SELECT br.*, bl.*, lb.* FROM tbl_borrower br JOIN tbl_book_loans bl ON br.borrower_CardNo = bl.book_loans_CardNo JOIN tbl_library_branch lb ON bl.book_loans_BranchID = lb.library_branch_branchID;
```
49. Select all authors with their associated books and borrowers:
```sql
SELECT ba.*, b.*, br.* FROM tbl_book_authors ba JOIN tbl_book b ON ba.book_authors_BookID = b.book_BookID JOIN tbl_book_loans bl ON b.book_BookID = bl.book_loans_bookID JOIN tbl_borrower br ON bl.book_loans_CardNo = br.borrower_CardNo;
```
50. Select all borrowers with their associated loans and publishers:
```sql
SELECT br.*, bl.*, p.* FROM tbl_borrower br JOIN tbl_book_loans bl ON br.borrower_CardNo = bl.book_loans_CardNo JOIN tbl_book b ON bl.book_loans_bookID = b.book_BookID JOIN tbl_publisher p ON b.book_publisherName = p.publisher_publisherName;
```
51. Select all books with their associated authors and library branches:
```sql
SELECT b.*, ba.*, lb.* FROM tbl_book b JOIN tbl_book_authors ba ON b.book_BookID = ba.book_authors_BookID JOIN tbl_book_loans bl ON b.book_BookID = bl.book_loans_bookID JOIN tbl_library_branch lb ON bl.book_loans_BranchID = lb.library_branch_branchID;
```
52. Select all borrowers with their associated loans and book copies:
```sql
SELECT br.*, bl.*, bc.* FROM tbl_borrower br JOIN tbl_book_loans bl ON br.borrower_CardNo = bl.book_loans_CardNo JOIN tbl_book_copies bc ON bl.book_loans_BookID = bc.book_copies_BookID;
```
53. Select all publishers with their associated books, authors, and borrowers:
```sql
SELECT p.*, b.*, ba.*, br.* FROM tbl_publisher p JOIN tbl_book b ON p.publisher_publisherName = b.book_publisherName JOIN tbl_book_authors ba ON b.book_BookID = ba.book_authors_BookID JOIN tbl_book_loans bl ON b.book_BookID = bl.book_loans_bookID JOIN tbl_borrower br ON bl.book_loans_CardNo = br.borrower_CardNo;
```
54. Select all library branches with their associated book copies, borrowed books, and borrowers:
```sql
SELECT lb.*, bc.*, bl.*, br.* FROM tbl_library_branch lb JOIN tbl_book_copies bc ON lb.library_branch_branchID = bc.book_copies_BranchID JOIN tbl_book_loans bl ON bc.book_copies_BookID = bl.book_loans_bookID JOIN tbl_borrower br ON bl.book_loans_CardNo = br.borrower_CardNo;
```
55. Select all authors with their associated books, publishers, and library branches:
```sql
SELECT ba.*, b.*, p.*, lb.* FROM tbl_book_authors ba JOIN tbl_book b ON ba.book_authors_BookID = b.book_BookID JOIN tbl_publisher p ON b.book_publisherName = p.publisher_publisherName JOIN tbl_library_branch lb ON b.book_BookID = lb.library_branch_branchID;
```
56. Select all borrowers with their associated loans, books, and authors:
```sql
SELECT br.*, bl.*, b.*, ba.* FROM tbl_borrower br JOIN tbl_book_loans bl ON br.borrower_CardNo = bl.book_loans_CardNo JOIN tbl_book b ON bl.book_loans_bookID = b.book_BookID JOIN tbl_book_authors ba ON b.book_BookID = ba.book_authors_BookID;
```
57. Select all publishers with their associated books, authors, and library branches:
```sql
SELECT p.*, b.*, ba.*, lb.* FROM tbl_publisher p JOIN tbl_book b ON p.publisher_publisherName = b.book_publisherName JOIN tbl_book_authors ba ON b
.book_BookID = ba.book_authors_BookID JOIN tbl_library_branch lb ON b.book_BookID = lb.library_branch
Here are some specific queries based on the provided data:
1. Select all books published by DAW Books:
```sql
SELECT * FROM tbl_book WHERE book_PublisherName = 'DAW Books';
```
2. Select all books authored by Patrick Rothfuss:
```sql
SELECT * FROM tbl_book WHERE book_BookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'Patrick Rothfuss');
```
3. Select all books borrowed from the Central library branch:
```sql
SELECT * FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Central'));
```
4. Select all books borrowed by Joe Smith:
领英推荐
```sql
SELECT * FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_CardNo = (SELECT borrower_CardNo FROM tbl_borrower WHERE borrower_BorrowerName = 'Joe Smith'));
```
5. Select all books with due dates after February 1, 2018:
```sql
SELECT * FROM tbl_book_loans WHERE book_loans_DueDate > '2018-02-01';
```
6. Select the number of copies of each book available at the Sharpstown branch:
```sql
SELECT book_copies_BookID, COUNT(*) AS NumOfCopies FROM tbl_book_copies WHERE book_copies_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Sharpstown') GROUP BY book_copies_BookID;
```
7. Select all books borrowed by Joe Smith from the Central branch:
```sql
SELECT * FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_CardNo = (SELECT borrower_CardNo FROM tbl_borrower WHERE borrower_BorrowerName = 'Joe Smith') AND book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Central'));
```
8. Select all authors who have written books borrowed from the Saline branch:
```sql
SELECT DISTINCT book_authors_AuthorName FROM tbl_book_authors WHERE book_authors_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Saline'));
```
9. Select the total number of books published by Bloomsbury:
```sql
SELECT COUNT(*) AS TotalBooks FROM tbl_book WHERE book_PublisherName = 'Bloomsbury';
```
10. Select all borrowers who have borrowed books authored by J.K. Rowling:
```sql
SELECT * FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_bookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'J.K. Rowling'));
```
Certainly! Here are 100 queries based on the provided schema and inserted values:
1. Select all books:
```sql
SELECT * FROM tbl_book;
```
2. Select all publishers:
```sql
SELECT * FROM tbl_publisher;
```
3. Select all authors:
```sql
SELECT * FROM tbl_book_authors;
```
4. Select all library branches:
```sql
SELECT * FROM tbl_library_branch;
```
5. Select all borrowers:
```sql
SELECT * FROM tbl_borrower;
```
6. Select all book loans:
```sql
SELECT * FROM tbl_book_loans;
```
7. Select all book copies:
```sql
SELECT * FROM tbl_book_copies;
```
8. Select all books published by DAW Books:
```sql
SELECT * FROM tbl_book WHERE book_publisherName = 'DAW Books';
```
9. Select all books authored by Stephen King:
```sql
SELECT * FROM tbl_book WHERE book_BookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'Stephen King');
```
10. Select all books borrowed from the Central library branch:
```sql
SELECT * FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Central'));
```
11. Select all books borrowed by Joe Smith:
```sql
SELECT * FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_CardNo = (SELECT borrower_CardNo FROM tbl_borrower WHERE borrower_borrowerName = 'Joe Smith'));
```
12. Select all books with due dates after February 1, 2018:
```sql
SELECT * FROM tbl_book_loans WHERE book_loans_DueDate > '2018-02-01';
```
13. Select the number of copies of each book available at the Sharpstown branch:
```sql
SELECT book_copies_BookID, COUNT(*) AS NumOfCopies FROM tbl_book_copies WHERE book_copies_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Sharpstown') GROUP BY book_copies_BookID;
```
14. Select all books borrowed by Joe Smith from the Central branch:
```sql
SELECT * FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_CardNo = (SELECT borrower_CardNo FROM tbl_borrower WHERE borrower_borrowerName = 'Joe Smith') AND book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Central'));
```
15. Select all authors who have written books borrowed from the Saline branch:
```sql
SELECT DISTINCT book_authors_AuthorName FROM tbl_book_authors WHERE book_authors_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Saline'));
```
16. Select the total number of books published by Bloomsbury:
```sql
SELECT COUNT(*) AS TotalBooks FROM tbl_book WHERE book_publisherName = 'Bloomsbury';
```
17. Select all borrowers who have borrowed books authored by J.K. Rowling:
```sql
SELECT * FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_bookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'J.K. Rowling'));
```
18. Select the titles of all books published by Alfred A. Knopf:
```sql
SELECT book_Title FROM tbl_book WHERE book_publisherName = 'Alfred A. Knopf';
```
19. Select the names of all borrowers who have borrowed books from the Sharpstown branch:
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Sharpstown'));
```
20. Select all books with more than 5 copies available at any branch:
```sql
SELECT FROM tbl_book WHERE book_BookID IN (SELECT book_copies_BookID FROM tbl_book_copies GROUP BY book_copies_BookID HAVING COUNT() > 5);
```
21. Select the names of all borrowers who have borrowed more than 3 books:
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE
borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans GROUP BY book_loans_CardNo HAVING COUNT(*) > 3);
```
22. Select the titles of all books borrowed from the Ann Arbor branch:
```sql
SELECT book_Title FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Ann Arbor'));
```
23. Select the names of all borrowers who have borrowed books published by Penguin Books:
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_bookID IN (SELECT book_BookID FROM tbl_book WHERE book_publisherName = 'Penguin Books'));
```
24. Select the titles of all books authored by Stephen King and borrowed from the Central branch:
```sql
SELECT book_Title FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Central')) AND book_BookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'Stephen King');
```
25. Select the names of all borrowers who have borrowed books with due dates between '2018-01-01' and '2018-02-01':
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_DueDate BETWEEN '2018-01-01' AND '2018-02-01');
```
26. Select the titles of all books borrowed by Joe Smith with due dates after '2018-02-15':
```sql
SELECT book_Title FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_CardNo = (SELECT borrower_CardNo FROM tbl_borrower WHERE borrower_borrowerName = 'Joe Smith') AND book_loans_DueDate > '2018-02-15');
```
27. Select the names of all borrowers who have borrowed books authored by J.K. Rowling and have due dates after '2018-03-01':
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_bookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'J.K. Rowling') AND book_loans_DueDate > '2018-03-01');
```
28. Select the names of all borrowers who have borrowed books from the Sharpstown branch and have due dates before '2018-01-31':
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Sharpstown') AND book_loans_DueDate < '2018-01-31');
```
29. Select the titles of all books published by DAW Books and borrowed from the Central branch:
```sql
SELECT book_Title FROM tbl_book WHERE book_publisherName = 'DAW Books' AND book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Central'));
```
30. Select the names of all borrowers who have borrowed books authored by J.K. Rowling and have due dates after '2018-03-15' from the Central branch:
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_bookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'J.K. Rowling') AND book_loans_DueDate > '2018-03-15' AND book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Central'));
```
31. **Select the titles of all books borrowed from the Ann Arbor branch with due dates between '2018-02
-01' and '2018-02-28':**
```sql
SELECT book_Title FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Ann Arbor') AND book_loans_DueDate BETWEEN '2018-02-01' AND '2018-02-28');
```
32. Select the names of all borrowers who have borrowed books published by Bloomsbury and authored by J.K. Rowling:
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_bookID IN (SELECT book_BookID FROM tbl_book WHERE book_publisherName = 'Bloomsbury') AND book_loans_bookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'J.K. Rowling'));
```
33. Select the titles of all books borrowed by Tom Li from the Saline branch:
```sql
SELECT book_Title FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Saline') AND book_loans_CardNo = (SELECT borrower_CardNo FROM tbl_borrower WHERE borrower_borrowerName = 'Tom Li'));
```
34. Select the names of all borrowers who have borrowed books authored by J.K. Rowling and have due dates before '2018-01-15' from the Sharpstown branch:
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_bookID IN (SELECT book_BookID FROM tbl_book WHERE book_publisherName = 'Bloomsbury') AND book_loans_DueDate < '2018-01-15' AND book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Sharpstown'));
```
35. Select the titles of all books authored by Stephen King borrowed from the Central branch with due dates after '2018-02-01':
```sql
SELECT book_Title FROM tbl_book WHERE book_BookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'Stephen King') AND book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Central') AND book_loans_DueDate > '2018-02-01');
```
36. Select the names of all borrowers who have borrowed books authored by Stephen King and J.K. Rowling from the Central branch:
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Central') AND book_loans_bookID IN (SELECT book_BookID FROM tbl_book WHERE book_BookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'Stephen King') OR book_BookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'J.K. Rowling')));
```
37. Select the titles of all books borrowed by Jane Smith with due dates after '2018-02-28':
```sql
SELECT book_Title FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_CardNo = (SELECT borrower_CardNo FROM tbl_borrower WHERE borrower_borrowerName = 'Jane Smith') AND book_loans_DueDate > '2018-02-28');
```
38. Select the names of all borrowers who have borrowed books authored by Stephen King and have due dates after '2018-03-15':
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_bookID IN (SELECT book_BookID FROM tbl_book WHERE book_BookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'Stephen King')) AND book_loans_DueDate > '2018-03-15');
```
39. Select the titles of all books published by Bloomsbury and borrowed from the Central branch with due dates before '2018-02-01':
```sql
SELECT book
Title FROM tblbook WHERE book_publisherName = 'Bloomsbury' AND book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Central') AND book_loans_DueDate < '2018-02-01');
```
40. Select the names of all borrowers who have borrowed books authored by George R.R. Martin from the Ann Arbor branch:
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Ann Arbor') AND book_loans_bookID IN (SELECT book_BookID FROM tbl_book WHERE book_BookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'George R.R. Martin')));
```
41. Select the titles of all books authored by William Goldman borrowed from the Saline branch with due dates after '2018-02-15':
```sql
SELECT book_Title FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Saline') AND book_loans_DueDate > '2018-02-15' AND book_loans_bookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'William Goldman'));
```
42. Select the names of all borrowers who have borrowed books published by Harper and Row from the Central branch:
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Central') AND book_loans_bookID IN (SELECT book_BookID FROM tbl_book WHERE book_publisherName = 'Harper and Row'));
```
43. Select the titles of all books borrowed by Tom Haverford from the Sharpstown branch with due dates after '2018-02-28':
```sql
SELECT book_Title FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Sharpstown') AND book_loans_CardNo = (SELECT borrower_CardNo FROM tbl_borrower WHERE borrower_borrowerName = 'Tom Haverford') AND book_loans_DueDate > '2018-02-28');
```
44. Select the names of all borrowers who have borrowed books authored by Douglas Adams and have due dates before '2018-01-15' from the Sharpstown branch:
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Sharpstown') AND book_loans_bookID IN (SELECT book_BookID FROM tbl_book WHERE book_BookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'Douglas Adams')) AND book_loans_DueDate < '2018-01-15');
```
45. Select the titles of all books authored by J.K. Rowling borrowed from the Central branch with due dates after '2018-03-01':
```sql
SELECT book_Title FROM tbl_book WHERE book_BookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'J.K. Rowling') AND book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Central') AND book_loans_DueDate > '2018-03-01');
```
46. Select the names of all borrowers who have borrowed books published by Signet Books from the Central branch with due dates before '2018-02-15':
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Central') AND book_loans_bookID IN (SELECT book_BookID FROM tbl_book WHERE book_publisherName = 'Signet Books') AND book_loans_DueDate < '2018-02-15');
```
47. Select the titles of all books borrowed from the Saline branch with due dates after '2018-02-28' and before '2018-03-15':
```sql
SELECT book_Title FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Saline') AND book_loans_DueDate > '2018-02-28' AND book_loans_DueDate < '2018-03-15');
```
48. Select the names of all borrowers who have borrowed books authored by J.K. Rowling and Stephen King from the Central branch with due dates after '2018-03-15':
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Central') AND book_loans_bookID IN (SELECT book_BookID FROM tbl_book WHERE book_BookID IN (SELECT book_authors_BookID FROM tbl_book_authors
WHERE book_authors_AuthorName = 'J.K. Rowling') OR book_BookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'Stephen King')) AND book_loans_DueDate > '2018-03-15');
```
49. Select the titles of all books published by Penguin Books and borrowed from the Ann Arbor branch with due dates after '2018-03-01':
```sql
SELECT book_Title FROM tbl_book WHERE book_publisherName = 'Penguin Books' AND book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Ann Arbor') AND book_loans_DueDate > '2018-03-01');
```
50. Select the names of all borrowers who have borrowed books authored by George Orwell and have due dates before '2018-01-15' from the Central branch:
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Central') AND book_loans_bookID IN (SELECT book_BookID FROM tbl_book WHERE book_BookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'George Orwell')) AND book_loans_DueDate < '2018-01-15');
```
51. Select the titles of all books authored by J.R.R. Tolkien borrowed from the Sharpstown branch with due dates after '2018-02-15':
```sql
SELECT book_Title FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Sharpstown') AND book_loans_DueDate > '2018-02-15' AND book_loans_bookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'J.R.R. Tolkien'));
```
52. Select the names of all borrowers who have borrowed books published by Ace Books and authored by Philip K. Dick from the Ann Arbor branch:
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Ann Arbor') AND book_loans_bookID IN (SELECT book_BookID FROM tbl_book WHERE book_publisherName = 'Ace Books') AND book_loans_bookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'Philip K. Dick'));
```
53. Select the titles of all books borrowed from the Central branch with due dates after '2018-03-01' and before '2018-03-15':
```sql
SELECT book_Title FROM tbl_book WHERE book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Central') AND book_loans_DueDate > '2018-03-01' AND book_loans_DueDate < '2018-03-15');
```
54. Select the names of all borrowers who have borrowed books authored by Ursula K. Le Guin from the Central branch with due dates after '2018-02-28':
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Central') AND book_loans_DueDate > '2018-02-28' AND book_loans_bookID IN (SELECT book_BookID FROM tbl_book WHERE book_BookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'Ursula K. Le Guin')));
```
55. Select the titles of all books published by HarperCollins and borrowed from the Saline branch with due dates before '2018-01-31':
```sql
SELECT book_Title FROM tbl_book WHERE book_publisherName = 'HarperCollins' AND book_BookID IN (SELECT book_loans_bookID FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Saline') AND book_loans_DueDate < '2018-01-31');
```
56. Select the names of all borrowers who have borrowed books authored by Arthur C. Clarke and have due dates after '2018-02-01' from the Ann Arbor branch:
```sql
SELECT borrower_borrowerName FROM tbl_borrower WHERE borrower_CardNo IN (SELECT book_loans_CardNo FROM tbl_book_loans WHERE book_loans_BranchID = (SELECT library_branch_branchID FROM tbl_library_branch WHERE library_branch_BranchName = 'Ann Arbor') AND book_loans_bookID IN (SELECT book_BookID FROM tbl_book WHERE book_BookID IN (SELECT book_authors_BookID FROM tbl_book_authors WHERE book_authors_AuthorName = 'Arthur C. Clarke')) AND book_loans_DueDate > '2018-02-01');
```
57. **Select the titles of all books authored by Margaret Atwood and