Use SQL Query Analyzer to work with BookStore database and create a SQL script file follow those steps:
- Create 4 tables and insert data as follows: Students (Student list)
StudentID (int) Name (varchar (50)) Age (tinyint) Gender (bit) 1 Nguyen Thi Huyen 19 0 2 Mai Thanh Minh 33 1 3 Dao Thien Hai 26 1 4 Trinh Chan Tran 24 0 5 Diem Diem Quynh 30 Null
Books (Book list)
BookID (int) Name (Varchar (50)) TotalPage (int) Type (Varchar(10)) Quantity (int) 1 Word 50 Null 10 2 Excel 60 Null 20 3 Access 71 Null 7 4 LGC 42 Null 1 5 HTML 71 Null 2
Borrows (Describe borrowing. Example: the first row of following table denote “Nguyen Thi Huyen has borrowed one Excel book on 10/29/2004)
BorrowID (int) StudentID (int) BookID (int) BorrowDate (datetime) 1 1 1 10/29/2004 2 4 4 10/26/2004
DropOuts (Describe DropOut list)
DrpID (int) StudentID (int) Date (datetime)
– Apply the Not Null Constraint to the Name column of Books table.
– Apply the Primary Key Constraint for the StudentID, BookID, BorrowID and DrpID columns of 4 above tables.
– Apply the Identity(1,1) Constraint for the DrpID column of DropOuts table.
- Display the Books that have TotalPage more than 50, this list must order by TotalPage and then Name as following:
BookID Name TotalPage Type Quantity 2 Excel 60 NULL 20 3 Access 71 NULL 7 5 HTML 71 NULL 2
- Insert appropriate data to Borrows to represent following information:
– One Access book was borrowed by Trinh Chan Tran on 10/30/04.
– One HTML book was borrowed by Mai Thanh Minh on 10/31/04
– 2 Word books were borrowed by Trinh Chan Tran on today (Hint: use GetDate())
- Display book names were borrowed and borrower name as following:
Student Name Book Name Nguyen Thi Huyen Word Trinh Chan Tran LGC Trinh Chan Tran Access Mai Thanh Minh HTML Trinh Chan Tran Word Trinh Chan Tran Word
- Display all student names and total books were borrowed by them as following
Student Name Total borrowed book Dao Thien Hai 0 Diem Diem Quynh 0 Mai Thanh Minh 1 Nguyen Thi Huyen 1 Trinh Chan Tran 4
- Display name of the students that are null in gender column.
- Display Name of the best borrowed book (the book that its total borrower is highest) and total borrower of this book as following:
Book Name Total Borrower Word 3
- Display number of total available books on BookStore (Hint: Total available=Total Books – Total Borrowed Books)
- Create a view named ‘vwBookList’ that list all information of Books table as following:
Book Name TotalPage Type Quantity Word 50 Normal 10 Excel 60 Normal 20 Access 71 Thick 7 LGC 42 Thin 1 HTML 71 Thick 2
- Modify the view named ‘vwBookList‘ such that you can create an index on it.
- Create an index named ‘indBookName’ on the [Book Name] and Type column of the ‘vwBookList’ view.
- Create an Insert Trigger named ‘tgNoInsertBook’ on the Borrows table. This trigger must check data inserting, if number of borrower greater than book’ quantity it will be rollback and display a message “Out of stock”.
Example: if user insert a row (using: Insert into Borrows Values(10,1,4,getdate()) to Borrows table, no row will be inserted and message “Out of stock” will be displayed
- Create a stored procedure named ‘spDropOut’ that accept the Student Name as parameter. If the student name available on Students table and if he/she is not borrow any books it will drop all information of the student from BookStore database and add StudentID into DropOuts table.
Example: if Nguyen Thi Huyen is dropped out, there is no change in database because she still is borrowing 1 book.
If Dao Thien Hai is dropped out all his information in all tables will be deleted and a row will be added in DropOuts table as following:
DrpID StudentID Date 1 3 <Current Date time>
Notes: You must ensure that this action will completely or rollback.
EXAMPLE SOURCE: Aptech-SQL-2-PE-2.zip