1. create a table with the below specification:
Table name: Accounts
columns names :
account_id number(5), ( no duplicates or null values allowed)
account_name varchar(20)
account_type varchar(20) ,It can have only 2 types(SAVINGS) , create a check constraint name as chk_act_type to do that
join_date date , sysdate should be automatically inserted if not specified( use a constraint)
inital_deposit number(10), mininum of rs.10000 should be deposited,( use a constraint) constraint name as chk_intdepo
address varchar(50),
contactno number(10),
location varchar(20))
After creating the table, do the following
|
2. Add a not null constraint to contactno.
|
3. Add a check constraint to location and it should be (NORTH,EAST,WEST,SOUTH).
|
4. Modify the Account_type to have two options (SAVINGS and CURRENT).
|
5.Insert 5 records with the below values.
1,CHERRY,SAVINGS,10000,CHENNAI,9980499232,SOUTH
2,SAKTHI,CURRENT,12000,BANGALORE,9980493232,SOUTH
3,NEHA,SAVINGS,15000,PUNE,9980499242,NORTH
4,SHIVANI,CURRENT,10000,PUNE,9985499232,SOUTH
5,TEJA,SAVINGS,14000,ASSAM,9980499232,NORTH
6,KAVIYA,SAVINGS,20000,CHENNAI,9980439232,EAST
7,HASINI,CURRENT,17000,CHENNAI,9980239232,WEST
|
6. Check how many constraints are there in ACCOUNTs table. Also, specify its type.
|
7. Check which columns have constraints
|
8. How many indexes are there for this table.
|
9.Create a table TXN with following columns
column name :
account_id number(5)
account_name varchar2(20)
txn_date date, without null
txn_type varchar2(10)(DEPOSIT/WITHDRAWL) -> use check constraint
amount number(10)
after creating the table.
Add a foreign key for the TXN table referencing accounts(account_id)
|
10. Check how many constraints are there in ACCOUNTs/TXN table. Also, specify its type.
|
11. Check which columns have constraints on both tables
|
12. How many indexes are there for this table on both tables
|
13.Insert below records. If you get any errors, specify the reason why you were not able to do it and
fix it.
1,CHERRY,sysdate,DEPOSIT,10000
8,SAI,sysdate,DEPOSIT,20000
4,SHIVANI,sysdate,WITHDRAWL,30000
4,SHIVANI,sysdate,DEPOSIT,40000
2,SAKTHI,sysdate,WITHDRAWL,2000
|
14. delete a record from accounts with accountid=1
|
15. delete account_id=2 from accounts , and make sure that the records from TXN also deletes
automatically. (use on delete cascade)
|
16. Disable all the constraints for accounts and txn( create a alter script for that)
|
17.insert below records
4,SHIVANI,sysdate,WITHDRAWL,10000
11,SHIVANI,sysdate,DEPOSIT,40000
10,SAKTHI,sysdate,WITHDRAWL,2000
|
18. Enable all the constraints for accounts and txn (without validating it)(create a alter script for that)
|
19. Check how many constraints are there in ACCOUNTs/TXN table. Also, specify its type
|
20. Check which columns have constraints on both tables
|
