Temple Naylor Temple Naylor - 2 months ago 11
SQL Question

SELECTing specific information with SQL

I created a database from the following flow-chart:

enter image description here

Populated according to this:

  1. There is a book called 'The Lost Tribe'.

  2. There is a library branch called 'Sharpstown' and one called 'Central'.

  3. There are at least 20 books in the BOOK table.

  4. There are at least 10 authors in the BOOK_AUTHORS table.

  5. Each library branch has at least 10 book titles, and at least two copies of each of those titles.

  6. There are at least 8 borrowers in the BORROWER table, and at least 2 of those borrowers have more than 5 books loaned to them.

  7. There are at least 4 branches in the LIBRARY_BRANCH table.

  8. There are at least 50 loans in the BOOK_LOANS table.

  9. There must be at least one book written by 'Stephen King'

I am trying to find how many copies of the book titled The Lost Tribe are owned by the library branch whose name
is "Sharpstown". Now I know that by finding out what the BranchId of Sharpstown is I could just run the following query:

SELECT No_Of_Copies FROM BOOK_COPIES WHERE BookId = 1 and BranchId = 1

But is there a way to search by using the actual name "Sharpstown"?

Thank you for your help, I am quite new to SQL SERVER and don't know how specific one user can get with queries.


For library branch, you could use IN

WHERE BookId = 1 
    AND BranchId IN (SELECT BranchId 
                     FROM library_branch
                     WHERE BranchName = 'Sharpstown');