SQL Assignments.doc - Set 2 SQL Assignment Library System Use the following Schema to perform the given set of assignment Tables Member Column Name

Set 2 SQL Assignment Library System Use the following Schema to perform the given set of assignment. Tables Member Column Name Data Type Description Member_Id Number(5) Unique Member ID Member_Name Char(25) Name of the Library member Acc_Open_Date Date Date of membership Max_Books_Allowed Number(2) Total Number of books that can be issued to the member. Penalty_Amount Number(7,2) Penalty amount due Book Column Name Data Type Description Book_No Number(6) Book identification number Book_Name VarChar2(30) Name of the book Author Char(30) Author of the book Cost Number(7,2) Cost of the book Category Char(10) Category like Science , Fiction etc. Issue Column Name Data Type Description Lib_Issue_Id Number(10) Library Book Issue No Book_No Number(6) Number of the book issued Member_Id Number(5) Member that issued the book Issue_Date Date Date of Issue Return_date Date Return date

Day # 1 Assignments (Estimated Time: 2 Hrs) Concept : SQL Basics, DDL commands Create , Alter , Drop DML commands Insert, Update , Delete , Transaction Control Commit , Rollback , Savepoint. Objective : At the end of the assignments, participants will be able to understand basic DDL/DML/Transaction Control statements. Task / Problems : 1) Create the table Member, Book and Issue without any constraints as mentioned in the schema description above. 2) View the structure of the tables. 3) Add column Comments to table Issue of width 100 characters. 4) Modify the table Member increase the width of the member name to 30 characters. 5) Add a column Reference Char(30) to Issue table. 6) Delete/Drop the column Reference from Issue. 7) Rename the table Issue to Lib_Issue. 8) Insert following data in table Member Member ID Member Name Acc_Ope n_Date Max_Books_All owed Penalty_Amo unt 1 Richa Sharma 10-Dec- 05 5 50 2 Garima Sen current date 3 Null Insert at least 5 records with suitable data, put null values in various columns other then member id and name in different records. 9) Modify the column Member Decrease the width of the member name to 20 characters. (If it does not allow state the reason for that) 10) Try to insert a record with Max_Books_Allowed = 110, Observe the error that comes. Report the reason for this error.