Create a table reader (library card number character (4), unit character (8), name character (6), gender character (2), title character (6), address character (20))
Create a borrowing table (library card number Char (4), total number Char (6), borrowing date Datetime).
1. For the library management database, add a new field in the lending table: expiration date. Please fill in the blanks in the following SQL statements:
Change table reader add due date date time
2. Insert a record in the book list, with the total number of 10 1002 and the classification number of 02, entitled Database Technology, written by Wang Dawei and published by Electronic Industry Press, with a unit price of 23.5.
Value inserted into books ('10 1002',' 02',' Database Technology',' Wang Dawei',' Electronic Industry Press', 23.5).
3. For the library management database, increase the unit price of books in the book list by 10%. Please fill in the blanks in the following SQL statements:
Unit price for updating books = unit price * 1. 1 where publishing unit =' Electronic Industry Press'
4. For the library management database, it is necessary to query the highest unit price and average unit price of books in various publishing houses.
Select the publishing unit, with MAX (unit price) as the highest unit price and AVG (unit price) as the average unit price of the account book group listed by publishing unit.
5. For the library management database, find the highest unit price and average unit price of books published by Electronic Industry Press.
Select the publishing unit, MAX (unit price) is the highest unit price, and AVG (unit price) is the average unit price of books, where the publishing unit =' Electronic Industry Press'.
6. Use SQL statements to query the information of all readers named Liu in the above table:
SELECT * FROM Reader WHERE Name Liu % '
7. Search the titles, authors, book prices and classification numbers of books whose prices are between 10 yuan -30 yuan (including 10 yuan -30 yuan), and the results are sorted by classification numbers in ascending order.
Choose the title, author, unit price and classification number from the books.
Where the unit price is between 10 and 30.
Sort by classification number
8. For the library management database, query the library card number and the number of books borrowed by each reader who borrows more than 3 books. Please fill in the blanks in the following SQL statements:
Select the library card number, and count(*) is the number of books borrowed.
Group by the counted library card number (*) > three
9. For the book management database, query the book information of Electronic Industry Press, and the retrieval results are arranged in descending order of book prices.
SELECT * FROM book WHERE Publishing Unit =' Electronic Industry Press' ORDER BY unit price DESC
10. For the library management database, query the highest unit price and average price of books published by two or more book publishers in the collection.
Select the publishing unit, with the maximum value (unit price) as the highest unit price, and AVG (unit price) as the average price of the account book group listed by publishing units with count (*) > =2.