Zhoushi dormitory book management

Create database book management

go to

Use book management

-Library management system question bank.

All the questions in this question bank are based on the database defined here: book management. The database includes three tables, and the logical structure of each table is as follows:

-( 1) reader information:

-The library card number char(8) is not empty, and the name nvarchar( 10) is unique.

-sex nchar( 1), date of birth, occupation name nvarchar(20).

-(2) Book information:

-Book number: char(8) is not empty, and the title nvarchar(20) is unique.

-Author nvarchar( 10), press nvarchar(20),

-ISBN char (13), price decimal (5,2),

-Copy number tinyint, stock tinyint.

-③ Borrowing information:

-Refer to the "Library Card Number" field in the "Student Information" table to obtain the library card number.

-Obtain the book number by referring to the book number field in the book information table.

-borrow the time datetime,

-Returns the time, date and time.

-First, create a database table.

-1. Please define the reader information table with SQL data definition statement.

Create table reader information

(

The library number char(8) is not empty.

The name nvarchar( 10) is unique.

Gender nchar( 1),

Date and time of birth,

Occupational name nvarchar(20)

)

-2. Please define the book information table with SQL data definition statement.

Create table book information

(

Book number char(8) is not empty.

The title of the book nvarchar(20) unique,

Author nvarchar( 10),

Press nvarchar(20),

ISBN char( 13)，

Price decimal (5, 2),

Copy number tinyint,

Inventory tinyint

)

-3. Please define the information table with SQL data definition statement.

Create table borrowing information

(

The library number char(8) is not empty.

Book number char(8) is not empty.

Borrow time, date and time,

Return time date time

)

-Second, the creation of table constraints.

-1. Declare the primary key for the reader information table: set the library card number field as the primary key of the reader information table.

Change table reader information

Add Constraint PK _ Library Card Number PK (Library Card Number)

-2. Declare the master code for the borrowing information table: set the joint fields (library card number and book number) as the primary key of the borrowing information table.

Change table borrowing information

Add constraint primary key _ library card number _ book number primary key (library card number, book number)

-3. Declare the primary key for the book information table: set the book number field as the primary key of the book information table.

Change table book information

Add constraint primary key _ ISBN primary key (ISBN)

-4. Declare the foreign code of the borrowing information table: refer to the "Library Card Number" field of the "Reader Information" table for the library card number.

Change table borrowing information

Add constraint fk_ library card number foreign key references reader information (library card number)

-5. Declare the foreign code of the lending information table: refer to the "Book Number" field of the "Book Information" table for the book number.

Change table borrowing information

Add constraint fk_ book number foreign key references book information (book number).

-6. Set a constraint for the book information table: the inventory of books is less than or equal to the number of books.

Change table book information

Add constraint ck_ Inventory Check (Inventory

-7. Set constraints for the borrowing information table: the borrowing time of books is less than or equal to the return time.

Change table borrowing information

Add constraint ck_ Borrowing time check (Borrowing time

-8. Set a constraint for the book information table: the book price is greater than 0.

Change table book information

Add constraint ck_ book price check (price >；; 0)

-9. Set constraints for the reader information table: the gender can only be "male" and "female".

Change table reader information

Add constraint ck_ gender check (gender in ('male',' female'))

-10. Set a constraint for the reader information table: the date of birth is between1980-9-1~1995-9-1.

Change table reader information

Add constraint ck_ birth date check (birth date is between' 1980-9- 1' and' 1995-9- 1').

-1 1. Set constraints for the reader information table: set the default value of the gender field to "female".

Change table reader information

Add a constraint for gender dk_ gender Default "Female"

-12. Set constraints for the book information table: set the default value of the publishing house field as "Tsinghua University Publishing House".

Change table book information

Add constraints for publishing houses. dk_ press defaults to' Tsinghua University Publishing House'.

-13. Set constraints for the book information table: set the default value of the inventory field to 0.

Change table book information

Add constraints for inventory dk_ inventory Default value 0

-14. Set constraints for the book information table: set the default value of the duplicate number field to 0.

Change table book information

Adding constraints dk_ number of copies defaults to 0.

-Third, data query and view creation.

-In order to facilitate data query, the basic data of three tables involved in this test are as follows:

-Table 1: Reader Information Table

-Library card number, name, sex, date of birth, occupation name.

-XS00000 1 Wang Linlin 1990-03- 14 Department of Politics and Law.

-XS000002 Jiang 1989-04-2 1 Computer Department

-JS0000 1 Shen Junyi male1991-01-03 Computer Department

-JS 000002 Zhao Niunv 1990-05- 12 Computer Department

Insert reader information (library card number, name, gender, date of birth, professional name)

Value ('XS0000 1',' Wang Linlin',' female',' 1990-03- 14',' Department of Political Science and Law').

Insert reader information (library card number, name, gender, date of birth, professional name)

Value ('XS000002',' Jiang Jun',' Male',' 1989-04-2 1',' Computer Department')

Insert reader information (library card number, name, gender, date of birth, professional name)

Value ('JS0000 1',' Shen Junyi',' male','1991-03',' computer department')

Insert reader information (library card number, name, gender, date of birth, professional name)

Value ('JS000002',' Zhao Niu',' Female',' 1990-05- 12',' Computer Department')

-Table 2: Book Information Table

-Book number, title, ISBN price of author publishing house, number of copies, and inventory.

-A0800 1 C # Programming Tan Haoqiang Tsinghua University Publishing House 7-11-06359-728107.

-A08002 big database Li Kunlun Electronic Industry Press 7-113-04908-7 37 2019

-A08003 e-commerce Zheng Qiang Tsinghua University Press 7-115-07715-0201514.

Insert book information (book number, title, author, publisher, ISBN, price, number of copies, inventory).

Values ('A0800 1',' C# programming',' Tan Haoqiang',' Tsinghua University Publishing House',' 7-11-06359-7', 28,1.

Insert book information (book number, title, author, publisher, ISBN, price, number of copies, inventory).

Value ('A08002',' Large Database',' Li Kunlun',' Electronic Industry Press',' 7- 1 13-04908-7', 37,20, 19).

Insert book information (book number, title, author, publisher, ISBN, price, number of copies, inventory).

Value ('A08003',' E-commerce',' Zheng Qiang',' Tsinghua University Publishing House',' 7-115', 20,15,15.

-Table 3: Borrowing Information Table

-Library number, book number, borrowing time and return time.

-xs 00000 1 a 0800 1 2009-04- 13

-xs 00000 1 a 08002 2009-04- 13

-xs 00000 1 a 08003 2009-04- 13

-js 00000 1 a 0800 1 2009-05-0 1

-js 000002 a 0800 1 2009-04-23

Insert borrowing information (library card number, library number, borrowing time)

Value ('XS00000 1',' A0800 1',' 2009-04- 13')

Insert borrowing information (library card number, library number, borrowing time)

Value ('XS00000 1',' A08002',' 2009-04- 13')

Insert borrowing information (library card number, library number, borrowing time)

Value ('XS00000 1',' A08003',' 2009-04- 13')

Insert borrowing information (library card number, library number, borrowing time)

Value ('JS00000 1',' A0800 1',' 2009-05-0 1')

Insert borrowing information (library card number, library number, borrowing time)

Value ('JS000002',' A0800 1',' 2009-04-23')

-1. List the basic information of the first three readers in the reader information table.

Select the top 3 from reader information *

-2. List the reader's library card number, name, date of birth and other basic information in the reader information table.

Select the library card number, name and date of birth from the reader information.

-3. List the basic information of the reader's name and age fields in the reader information table.

Select name, datediff("yyyy "), date of birth and getdate () as age.

Information from readers

-4. List the basic information of female readers in the computer department in the reader information table.

Select * from reader information

In which professional name =' Computer Department' and gender =' female'

-5. List the information of readers with the surname "Zhao" in the reader information table.

Select *

Information from readers

Left where (name, 1)=' Zhao'

Select *

Information from readers

Where is the name like' Zhao%'

-6. List all readers' information with the word "Jun" in their names.

Select *

Information from readers

Van Charinders ('Jun', name)! = 0

-7. List the basic information of books published by Tsinghua University Publishing House.

Select *

From book information

Where Publishing House =' Tsinghua University Publishing House'

-8. List the information of all books in descending order of "Book Number".

Select *

From book information

Sort by desc book number

-9. List the borrowing information of all readers in ascending order of "borrowing time".

Select * from the borrowing information

Sort by borrowing time

-10 lists the book number, title, borrowing time, return time and other information of books borrowed by readers with library card number "XS0000 1".

-and in descending order of inventory.

Select book information. Book number, title, borrowing time and return time.

Add the borrowing information on the book information from the inside of the book information. Book number = borrowing information. book number

In which library card number = 'XS00000 1'

Desc sorted by inventory

-1 1 lists the library card number, reader's name, gender and date of birth of readers with book number' A0800 1'.

-In descending order of "Library ID number".

Select reader information. Library card number, name, sex, date of birth.

Add the borrowing information on the reader information from the inside of the reader information. Library card number = borrowing information. Library card number

In which book number = 'A0800 1'

Order by library card number desc

-12. List all book information in descending order of inventory.

Select * from the book information

Desc sorted by inventory

-13. Count and list the library card number, name and number of books borrowed by each reader.

Select reader information. The library card number, name, and the number of books lent out are indicated by count(*).

Add the borrowing information on the reader information from the inside of the reader information. Library card number = borrowing information. Library card number

Group by reader information. Library card number, name

-14. Count and list the total number of people and professional names of each major.

Select the specialty name, and count(*) is the total number of people.

Information from readers

Group by professional name

-15. Count and list the book number, book title and the number of borrowers.

Select book information. Book number, title, and count(*) are the number of borrowers.

Add the borrowing information on the book information from the inside of the book information. Book number = borrowing information. book number

Group by book information. Book number

-16. List the library card number, name, gender and date of birth of readers who were not born in 1990 in the reader information table.

Select the library card number, name, gender and date of birth.

Information from readers

Where the date of birth is not between "1990-1-kloc-0/"and "1990-12-31".

-17. List the titles, authors, publishers and stocks of books with prices between 20 and 30 and stocks not between 10-20 in the book information table.

Select the title, author, publisher and inventory.

From book information

Where the price is between 20 and 30, and the inventory is not between 10 and 20.

-18. List the titles, publishers and prices of the first two books in the book information table.

Select the top 2 book names, publishers and prices from the book information, and sort by price desc.

-19. List the book names, prices and inventory of Tsinghua University Publishing House in the book information table, in descending order of price, and in ascending order of inventory if the prices are equal.

Select the book name, price and inventory from the book information, where Press =' Tsinghua University Publishing House' order by price desc, inventory asc.

-20. Inquire about the average price of "Tsinghua University Publishing House" books in the book information table, and change the title to "average price".

Select avg (price) as the' average price' from the book information of publishing house =' Tsinghua University Publishing House'

-2 1. Lists the total number of books in the book information table and the total number of books in stock.

Select sum as the "total number of books" and sum as the "total number of books" from the book information.

-22. List the book names and prices with the highest price and the book names and prices with the lowest price in the book information table.

Select book name, price from book information where price = (select max (price from book information).

alliance

Select book name，price from book information where price =(select min(price from book information))

-23. Count the total number of readers in the reader information table.

Select the count (library card number) from the reader information.

-24. Count the number of students who borrow books in the borrowing information table.

Select count (unique library card number) from the borrowing information as "Number of Borrowers".

-25. Count the book number and the number of borrowers in the borrowing information table.

Select the book number, count (book card number) from the book number lending information group.

-26. List the average price and total number of books in each publishing house in the book information table, and display the publishing houses with average price less than 50, average price and total number of books in ascending order of total number of books.

Select press, avg (price) as "average price" and sum (number of copies) as "total number of copies".

From book information

Group by publishing house

Average price < 50

-27. List the library card number, name, gender and date of birth of readers with the same major as Jiang Jun in the reader information table.

Select the library card number, name, gender and date of birth.

Information from readers

Among them, the professional name is in (choose the professional name from the reader information, where the name is =' Jiang Jun')

-28. List the information of readers older than the average age of all students.

Select * from reader information where datediff ("yyyy", birthday, getdate ())

& gt (select avg(datediff("yyyy ",date of birth, getdate ()) from reader information).

-29. Create a view of book borrowing information, named as: TSQuery.

-View function: list information such as library card number, name, book number, title and borrowing time.

Create view TSQuery

be like

Select dbo. Reader information. Library card number. Reader information. Name, dbo. Book information. Book number,

Dbo。 Book information. Book title, dbo. Borrow information. Borrow time.

From dbo. Internal connection of reader information

Dbo。 Borrow information from dbo. Reader information. Library card number = dbo. Borrow information. The library card number is connected internally.

Dbo。 Book information on dbo. Borrow information. Book number = dbo. Book information. Book number.

-30. Create a view of book borrowing information, named TJQuery.

-View function: list information such as book number, book number, title, publishing house, price, etc.

Create view TJQuery

be like

Select dbo. Borrow information. Library card number. Borrow information. Book number, dbo. Book information. Book title,

Dbo。 Book information. News dbo Book information. Price.

From dbo. Borrowing information internal connection

Dbo。 Book information on dbo. Borrow information. Book number = dbo. Book information. Book number.

-Fourth, data update.

-1. Add a piece of data to the lending information table. The details are as follows:

-Book number: XS000002, book number: A08002, borrowing time: current system time.

Insert borrowing information (library card number, library number, borrowing time)

Value ('XS000002',' A08002', getdate ())

-2. Add a piece of data to the reader information table, and its specific information is as follows:

-Library number: XS000003, name: Li Bai, gender: male, date of birth:1989-1professional name: Department of Political Science and Law.

Insert reader information (library card number, name, gender, date of birth, professional name)

Values ('XS000003',' Li Bai',' male','1989-1','Department of Politics and Law')

-3. Update the number of books with book number A08003 to 30.

Update book information

Set number of copies =30

In which book number = 'A08003'

-4. Change the book return time of students who borrowed books from "large database" to the current system time.

Update loan information

Set book return time ='2009-6- 1'

Where book number = (select book number from book information, where book name =' large database')

-5. Update the number of e-commerce books to 20.

Update book information

Set number of copies = 20

Where Book Name = "e-commerce"

-6. Delete the record information of boys in the student information table.

Delete from reader information

Where gender =' male'

-7. Delete the record information that the book return time is later than May 2009-1 in the lending information table.

Delete from the borrowing information

Where can I return the book?' 2009-05-0 1'

-8. Delete the record information of "large database" books borrowed from the borrowing information table.

Delete from the borrowing information

Where book number = (select book number from book information, where book name =' large database')

-V. Stored procedures

-1. Create a stored procedure.

-Function: According to the library card number, query the information of books borrowed by readers represented by the card number.

-stored procedure name: ReaderBookQuery

-Input parameter: library card number.

-The returned query result set includes: book number, book title, ISBN, publishing house and borrowing time.

Creation process ReaderBookQuery

(

@ library card number varchar( 100)

)

be like

Select book information. Book number, title, ISBN, publishing house, borrowing time.

Add the borrowing information on the book information from the inside of the book information. Book number = borrowing information. book number

The library card number = @ library card number

-2. Create a stored procedure.

-Function: Inquire the basic information of readers according to the library card number.

-stored procedure name: ReaderInfoQuery

-Input parameter: library card number.

-The returned query result set includes: name, gender, date of birth and occupation name.

Creation process ReaderInfoQuery

(

@ library card number varchar( 100)

)

be like

Select the name, gender, date of birth and professional name from the reader information, where the library card number = @ library card number.

-3. Create a stored procedure

-Function: According to the book number, check the basic situation of the book represented by the book number.

-stored procedure name: BookInfoQuery

-Input parameter: Book number

-The returned result set includes: book title, author, publisher, ISBN, price, number of copies and inventory.

Create proc BookInfoQuery

(

@ book number varchar( 100)

)

be like

Select the title, author, publisher, ISBN, price, number of copies and inventory from the book information.

Where ISBN = @ ISBN

-4. Create a stored procedure.

-Function: According to the book number, query the information of readers who borrow books.

-stored procedure name: BookQuery

-Input parameter: Book number

-The returned query result set includes: library card number, name, gender and professional name.

Create a process reservation query

(

@ book number varchar( 100)

)

be like

Select reader information. Library ID number, name, gender and professional name.

Add the borrowing information on the reader information from the inside of the reader information. Library card number = borrowing information. Library card number

Where ISBN = @ ISBN

-5. Create a stored procedure.

-Function: According to the book publishing house, query the name of the book with the highest price in this publishing house.

-stored procedure name: BookPubQuery

-Input parameters: book publishing house

-The returned query result set includes: book name.

Create process book publishing query

(

@pub nvarchar(20)

)

be like

Select a book name from the book information.

Where price = (select where publishing house = @ pub from the book information)

-6. Create a stored procedure

-Function: Inquire about book borrowing according to the book number.

-If the number of copies-inventory

-If 2

-if number of copies-inventory >; 6. It says "The book is in sufficient stock" and returns to 3.

-stored procedure name: BookJYQuery

-Input parameter: Book number

Create proc BOOKJYQuery

(

@ book number varchar( 100)

)

be like

If (select copy number-inventory from book information, where book number = @ book number)

begin

Print' This book is out of stock, please continue to enter'

Return 1

end

other

If (select copy number-inventory from book information, where book number = @ book number)

begin

Print "This book is well stocked"

Return to 2

end

other

begin

Printing' This book is well stocked'

Return to 3

end

-VI. Creation of Triggers

-1. Create a trigger

-Function: When lending information is added to the lending information table, the corresponding inventory in the book information table will automatically decrease by 1.

-trigger name: BOOKTrigger

Create trigger book trigger

On information borrowing

After insertion

be like

Update book information set inventory = inventory-1 where ISBN = (select ISBN from the inserted ISBN)

-2. Create a trigger

-Function: When borrowing information is deleted from the borrowing information table, the corresponding inventory in the book information table will be automatically increased by 1.

-trigger name: BookDeleteTrigger

Create trigger BookDeleteTrigger

On information borrowing

After deletion

be like

Update book information set inventory = inventory+1 where ISBN = (select ISBN from deleted)

-3. Create a trigger

-Function: When inserting data into the borrowing information table, if the inventory in the book information table is equal to 0, it will prompt "Inventory is 0, and cannot be inserted".

-trigger name: JYinsert

Create trigger JYinsert

On information borrowing

Used to insert

be like

If (select inventory from book information) = 0

begin

Raiserror ('Inventory is 0, cannot insert', 16, 1)

Rollback transaction

end

-4. Create a trigger

-Function: When inserting data into the reader information table, if there are duplicate records, you will be prompted that "duplicate names are not allowed to be inserted".

-trigger name: DZinsert

Create trigger DZinsert

On reader information

After insertion

be like

If it exists (select name from reader information, where name = (select name from inserted information))

begin

Raiserror ('duplicate name, not inserted', 16, 1)

reverse

end

-5. Create a trigger

-Function: When a data record is deleted from the reader information table, the student record in the borrowing information table is also deleted.