Select count as the total collection,
Sum (pricing) as the total amount of books,
Sum (actual quantity) as the total inventory photo album,
Max (pricing) as the highest price,
Min (pricing) as the lowest price.
From the library card
go to
2. List books with 10 or above (title, author, publisher, year).
Select the title, author's name, publishing house and publication date.
From the library card
Grouped by book number are (coung( 1)> 10).
Sort by title
go to
3. Which publishing houses have more than 100 books?
Select the publishing house as "a publishing house with 100 books".
From the library card
According to the publishing house, there are (count (category) >; 100)
Publishing house order
go to
4. How many books have actually been lent out?
Select the sum as the Lending Quantity.
From the library card
go to
5. The oldest book.
Select top 1 and ties book names from the library card.
Sort by publication date
go to
6. Course of Database System Principles, edited by Susan Wang, Tsinghua University Publishing House, 1998, how many books are there?
Select the count from the library card (1).
(Abstract, Course of Database System Principles, edited by Susan Wang, Tsinghua University Publishing House, 1998).
go to
7. Which year has the most books?
Select top 1, ties convert (substring (publication date, 1, 4)) as the year, and count( 1) as the number of books.
From the library card
Grouped by publication date
Sort desc by number of books
go to
8. Which library card has not been returned the most?
Select top 1 and ties a. the number of readers, and count (1) as "the number of books borrowed".
Borrow books from library card A and B.
Where a. ISBN = B. ISBN
Group by reader number
Sort by the number of books borrowed by desc.
go to
9. The average number of books borrowed per library card.
Select avg as "average number of books borrowed per library card"
Borrow from ...
go to
10. Which department students borrow the most books and photo albums on average?
Select top 1 and ties a. Work unit, avg (borrowed quantity) as "average borrowed quantity".
From reader A, borrow B.
Where a. reader number = b. reader number.
Group by work unit
Sort by average borrowed quantity' desc
go to
Books that have not been borrowed in the last two years.
Select a book name
From the library card
Where book number in (select the book number from borrowing where datediff(year, borrowing date, acquisition date ()) > 2).
go to
12. List the library card numbers and titles of overdue books.
Select a. the reader number is "library card number", and b. the name of the book.
From the reader as an inline book card as B. A. Book number = B. Book number
Where a. the date of return
go to
13. There is no library card for borrowing books this year.
Select the card reader number
From readers
Where the card reader number is not available (select the card reader number
From readers
Where datediff (year, borrowing date, acquisition date ()) =0)
go to
14. What kind of books did you lend the most this year?
Select the top 1 and a. categories, and the count (1) is "Lended Quantity".
Borrow books from library card A and B.
Where datediff (year, B. borrowing date, getdate())=0.
Group by category
Sort by lending quantity' desc
go to