if any one want answers of following questions send messege on
Use the following Schema to perform the
given set of assignment. Tables-
Member –
It contains information about the members
|
Column Name |
Data Type |
Description |
|
Member_Id |
Number(5) |
Unique Member ID |
|
Member_Name |
Varchar2(30) |
Name of the Library member |
|
Member_address |
Varchar2(50) |
Address of the member |
|
Acc_Open_Date |
Date |
Date of membership |
|
Membership_type |
Varchar2(20) |
Type of the membership such as ‘Lifetime’,’
Annual’, ‘Half Yearly’,’ Quarterly’ |
|
Fees_paid |
Number(4) |
Membership fees paid |
|
Max_Books_Allowed |
Number(2) |
Total
Number of books that can be issued to the member. |
|
Penalty_Amount |
Number(7,2) |
Penalty amount due |
Books- It contains
information about the books belongs to the library
|
Column Name |
Data Type |
Description |
|
Book_No |
Number(6) |
Book identification number |
|
Book_Name |
VarChar2(30) |
Name of the book |
|
Author_name |
Varchar2(30) |
Author of the book |
|
Cost |
Number(7,2) |
Cost of the book |
|
Category |
Char(10) |
Category like Science , Fiction etc. |
Issue –
It contains the information about issue of the books
|
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 table without
constraints and with
constraints, Alter,
truncate, and Drop DML commands Insert, Update, Delete,
Transaction Control Commit, Rollback,
Savepoint. Create sequence command
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, Books and Issue without any constraints as mentioned in the
schema description above.
2) View
the structure of the tables.
3)
Drop the Member
table
4)
Create the table Member again as per the schema
description with the following constraints.
a.
Member_Id – Primary Key
b.
Membership_type - ‘Lifetime’,’ Annual’, ‘Half Yearly’,’
Quarterly’
5)
Modify the table Member increase the width of
the member name to 30 characters.
6)
Add a column named as Reference of Char(30) to
Issue table.
7) Delete/Drop
the column Reference from Issue.
8)
Rename the table Issue to Lib_Issue.
9)
Insert following data in table Member
|
Member
ID |
Member
Name |
Member
Address |
Acc_Open_Date |
Membership_type |
Fees_Paid |
Max_Books _Allowed |
Penalty_
Amount |
|
1 |
Richa Sharma |
Pune |
10-Dec-05 |
Lifetime |
25000 |
5 |
50 |
|
2 |
Garima
Sen |
Pune |
current
date |
Annual |
1000 |
3 |
Null |
10)
Insert at least 5 records with suitable data and
save it.
11)
Modify the column Member_name. Decrease the width of the member name to 20
characters. (If it does not allow state the reason for that)
12) Try
to insert a record with Max_Books_Allowed = 110, Observe the error that comes. Report the reason
for this error.
13) Generate
another table named Member101 using
a Create command along with a simple SQL query on member table.
14) Add
the constraints on columns max_books_allowed and penalty_amt as follows
a. max_books_allowed
< 100
b. penalty_amt
maximum 1000 Also give names to the constraints.
15) Drop
the table books.
16)
Create table Books again as per the schema
description with the following constraints.
a.
Book_No – Primary Key
b.
Book_Name – Not
Null
c.
Category – Science, Fiction, Database, RDBMS, Others.
17) Insert
data in Book table as follows:
|
Book_N
o |
Book Name |
Author |
Cost |
Category |
|
101 |
Let us
C |
Denis Ritchie |
450 |
System |
|
102 |
Oracle
– Complete Ref |
Loni |
550 |
Database |
|
103 |
Mastering
SQL |
Loni |
250 |
Database |
|
104 |
PL
SQL-Ref |
Scott Urman |
750 |
Database |
18) Insert
more records in Book table using & operator in the insert statement.
19) Create
table Book101 similar to Book in structure with no data in it.
20) Insert
into Book101 all the data in Book table using
Select Statement.
21) Save
all the data so far inserted in the tables.
22) View
the data in the tables using simple SQL query.
23) Insert
into Book following data.
105, National Geographic, Adis Scott, 1000, Science
24) Undo
the last changes.
25) Modify
the price of book with id 103 to Rs 300 and category to RDBMS.
26) Rename
the table Lib_Issue to Issue.
27) Drop
table Issue.
28) As
per the given structure Create table Issue again with following constraints.
§ Lib_Issue_Id-Primary key
§ Book_No-
foreign key
§ Member_id
- foreign key
§
Issue_date <= system date.
§ Issue_date
< Return_date
29) Insert
following data into Issue table. Note leave the column Return_Date blank.
|
Lib_Issu
e_Id |
Book No |
Member
ID |
Issue Date |
Return
Date |
|
7001 |
101 |
1 |
10-Dec-06 |
|
|
7002 |
102 |
2 |
25-Dec-06 |
|
|
7003 |
104 |
1 |
15-Jan-06 |
|
|
7004 |
101 |
1 |
04-Jul-06 |
|
|
7005 |
104 |
2 |
15-Nov-06 |
|
|
7006 |
101 |
3 |
18-Feb-06 |
|
30) Save
the data.
31) Disable
the constraints on Issue table
32)
Insert a record in Issue table. The member_id
should not exist in member table.
33) Now
enable the constraints of Issue table. Observe the error
34) Delete
the record inserted at Q-32) and enable the constraints.
35) Try
to delete the record of member id 1 from member table and observe the error .
36) Modify
the Return_Date of 7004,7005 to 15 days after
the Issue_date.
37) Modify
the Penalty_Amount for Garima Sen to Rs 100.
38) Perform a save point X here.
39) Remove
all the records from Issue table where member_ID is 1 and Issue date in before 10-Dec-06.
40) Remove
all the records from Book table with category other than RDBMS and Database.
41) Undo the changes done after savepoint X.
42) Save
all the changes done before X.
43) Remove
the table Member101.
44) Remove
the table Book101.
45) View
the data and structure of all the three tables Member, Issue, Book.
46) Create
a sequence no_seq of even numbers
starting with 100 and ending with 200.
47) Drop
the above created sequence.
48) Create
a sequence book_seq starting with 101 and ending with 1000 And increamented by 1 without cycle.
49) Create
a sequence member_seq starting with 1 and ending with 100 And increamented by 1 without cycle.
50)
Drop the above created sequences.
