SQL Server Exercises 3

Server

Topic 3

Problem Description:
The following three relational tables are used:
Card library cards. CNO card number, name, class
Books. BNO ISBN, bname title, AUTHOR author, price, QUANTITY inventory number
Borrow Library records. CNO Library card number, BNO ISBN, Rdate book date
Note: Limit each type of books to only one copy; the number of inventory books is changed as the books are borrowed and returned.
The following 15 processing requirements are achieved:
1. Write out the SQL statement that establishes the borrow table, requiring the definition of the Master Code integrity constraint and referential integrity constraint.
2. Find readers who borrow more than 5 books, output the library card number and the number of books borrowed.
3. Check out the "margin" a book readers, the output name and class.
4. Query expired book, output borrower (card number), ISBN and return date.
5. Query title includes "network" key words of the book, Output ISBN, title, author.
6. Check the book with the highest price in the existing book, output the title and author.
7. The query currently borrows the "calculation method" but does not borrow the "calculation method problem" reader, prints out its library card number and sorts the output in descending order of the card number.
8. The "C01" class students borrowed books to extend the period of a week.
9. Remove the currently unattended book record from the books table.
10. If you frequently query book information by book title, create an appropriate index.
11. Set up a trigger on the borrow table to complete the following functions: If the reader's title is "Database technology and application", keep the reader's lending record in the Borrow_save table (orrow_save table structure with borrow table).
12. Create a view that displays the library information for the "Force 01" class (only the names and titles are required).
13. Query current readers with both "computational methods" and "Combinatorial math", output their library card number and sort the output by the card number in ascending order.
14. Assuming that no main code is defined when the books table is being built, write a statement that appends the main code to the Books table.
15. Make the following modifications to the card table:
A. Increase the name maximum column width to 10 characters (assuming the original is 6 characters).
B. Add 1 Column name (name) to the table, variable length, maximum 20 characters.

1. Write out the SQL statement to establish the borrow table, which requires the definition of Master Code integrity constraints and referential integrity constraints
--Implementation code:
CREATE TABLE Borrow (
CNO int FOREIGN KEY REFERENCES Card (CNO),
BNO int FOREIGN KEY REFERENCES Books (BNO),
Rdate datetime,
PRIMARY KEY (cno,bno))

2. Find readers who borrow more than 5 books, output the library card number and the number of books borrowed
--Implementation code:
SELECT CNO, borrow book Number =count (*)
From borrow
GROUP by CNO
Having COUNT (*) >5

3. Check and borrow the "margin" of the reader, the output name and class
--Implementation code:
SELECT * FROM card C
WHERE EXISTS (
SELECT * from borrow A,books b
WHERE A.bno=b.bno
and B.bname=n ' the margin '
and A.CNO=C.CNO)

4. Query overdue book, output borrower (card number), ISBN and return date
--Implementation code:
SELECT * from Borrow
WHERE Rdate<getdate ()

5. Query title includes "network" key words of the book, Output ISBN, title, author
--Implementation code:
SELECT Bno,bname,author from books
WHERE bname like N '% network% '

6. Check the book with the highest price in the existing book, output the title and author
--Implementation code:
SELECT Bno,bname,author from books
WHERE price= (
SELECT MAX (price) from books)

7. The query currently borrows the "calculation method" but does not borrow the "calculation method problem set" reader, output its library card number, and in descending order the number of cards sorted output
--Implementation code:
SELECT A.cno
From borrow A,books b
WHERE a.bno=b.bno and B.bname=n ' calculation method '
And not EXISTS (
SELECT * from Borrow aa,books BB
WHERE AA. Bno=bb. BNO
and BB. Bname=n ' Computational method problem set '
and AA. CNO=A.CNO)
ORDER BY A.cno DESC

8. The "C01" class students borrowed books to extend the period of a week
--Implementation code:
UPDATE b SET Rdate=dateadd (day,7,b.rdate)
From card A,borrow b
WHERE A.cno=b.cno
and A.class=n ' C01 '

9. Delete current unattended book records from the books table
--Implementation code:
DELETE A From books a
WHERE not EXISTS (
SELECT * from Borrow
WHERE bno=a.bno)

10. If you often search for book information by book title, please establish an appropriate index
--Implementation code:
CREATE CLUSTERED INDEX idx_books_bname on books (bname)

11. Set up a trigger on the borrow table to complete the following functions: If the reader borrows the title is "Database technology and Application", the reader's lending record is kept in the Borrow_save table (orrow_save table structure with borrow table)
--Implementation code:
CREATE TRIGGER Tr_save on borrow
For Insert,update
As
IF @ @ROWCOUNT >0
INSERT Borrow_save SELECT i.*
From INSERTED I,books b
WHERE I.bno=b.bno
and B.bname=n ' database technology and Applications '

12. Set up a view to show the "Force 01" class students of the library information (only required to display names and titles)
--Implementation code:
CREATE VIEW V_view
As
SELECT A.name,b.bname
From Borrow Ab,card A,books b
WHERE AB. Cno=a.cno
and AB. Bno=b.bno
and a.class=n ' Force 01 '

13. Query current readers with both "computational methods" and "Combinatorial math", output their library card number and sort the output by number of card numbers in ascending order
--Implementation code:
SELECT A.cno
From borrow A,books b
WHERE A.bno=b.bno
and B.bname in (n ' calculation method ', n ' combinatorial mathematics ')
GROUP by A.cno
Having COUNT (*) =2
ORDER BY A.cno DESC

14. Assuming that no main code is defined when the books table is being built, write a statement that appends the main code to the Books table
--Implementation code:
ALTER TABLE Books ADD PRIMARY KEY (BNO)

15.1 Increase the name maximum column width to 10 characters (assumed to be 6 characters)
--Implementation code:
Alter TABLE card alter COLUMN NAME varchar (10)

15.2 Add 1 Column name (name) to the table, variable length, maximum 20 characters
--Implementation code:
ALTER TABLE card ADD series name varchar (20)