Lab – Sql Assignments – Part 1
1. Constraints
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