Login 1.create table user_type_master_table(user_type number(2)primary key,type_descr varchar(15)); 2. insert into user_type_master_table values(1,'student'); insert into user_type_master_table values(2,'staffs'); insert into user_type_master_table values(3,'hod'); insert into user_type_master_table values(4,'principal'); 3.display table USER_TYPE TYPE_DESCR ---------- --------------- 1 student 2 staffs 3 hod 4 principal 4.create table dept_master_tbl(dept_code number(2)primary key,dept varchar(15)); 5.insert into dept_master_tbl values(1,'IT'); insert into dept_master_tbl values(2,'CS'); insert into dept_master_tbl values(3,'EC'); 6 DEPT_CODE DEPT ---------- --------------- 1 IT 2 CS 3 EC 7 create table user_dtls_tbl(user_id number(9)primary key,user_name varchar(30),dpt_code number(2)references dept_master_tbl); insert into user_dtls_tbl values(01,'Arun',1); insert into user_dtls_tbl values(02,'Abhi',2); insert into user_dtls_tbl values(03,'Albert',3); insert into user_dtls_tbl values(04,'Pranav',1); insert into user_dtls_tbl values(05,'Herin',2); insert into user_dtls_tbl values(06,'Amrith',3); insert into user_dtls_tbl values(07,'Vishnu',2); insert into user_dtls_tbl values(08,'Amal',1); insert into user_dtls_tbl values(09,'Alan',3); 8 USER_ID USER_NAME DPT_CODE --------- ------------------------------ ---------- 1 Arun 1 2 Abhi 2 3 Albert 3 4 Pranav 1 6 Amrith 3 7 Vishnu 2 8 Amal 1 9 Alan 3 9 create table user_login_tbl(user_id number(3)references user_dtls_tbl,user_pwd varchar2(8)primary key,user_type number(4)references user_type_master_table,user_created_dt date); 10 insert into user_login_tbl values(01,'aaa',1,'15-jan-04'); insert into user_login_tbl values(02,'bbb',1,'1-feb-06'); insert into user_login_tbl values(03,'ccc',2,'17-mar-05'); insert into user_login_tbl values(04,'ddd',1,'14-may-03'); insert into user_login_tbl values(05,'eee',3,'7-jun-04'); insert into user_login_tbl values(06,'fff',1,'8-jul-04'); insert into user_login_tbl values(07,'ggg',3,'5-aug-04'); insert into user_login_tbl values(08,'hhh',2,'2-jan-04'); insert into user_login_tbl values(09,'iii',1,'30-jan-04'); 11 USER_ID USER_PWD USER_TYPE USER_CREA ------- -------- ---------- --------- 1 aaa 1 15-JAN-04 2 bbb 1 01-FEB-06 3 ccc 2 17-MAR-05 4 ddd 1 14-MAY-03 6 fff 1 08-JUL-04 7 ggg 3 05-AUG-04 8 hhh 2 02-JAN-04 9 iii 1 30-JAN-04 12select user_name from user_dtls_tbl where user_id in(select user_id from user_login_tbl where user_created_dt>'12-jan-05'and user_type in(select user_type from user_type_master_table where type_descr='student')); 13select user_name from user_dtls_tbl,dept_master_tbl where(user_dtls_tbl.dpt_code=dept_master_tbl.dept_code and dept_master_tbl.dept='IT'); 14select user_name from user_dtls_tbl where user_id in(select user_id from user_login_tbl where user_created_dt>'12-jan-05' and user_type in(select user_type from user_type_master_table where type_descr='student')); 15select user_name from user_dtls_tbl where user_id in(select user_id from user_login_tbl where user_created_dt>'12-jan-05' and user_type in(select user_type from user_type_master_table where type_descr='student')); 16 select user_name from user_dtls_tbl,dept_master_tbl where(user_dtls_tbl.dpt_code=dept_master_tbl.dept_code and user_dtls_tbl.dpt_code>1) ; 17select user_name from user_type_master_table,user_login_tbl,user_dtls_tbl where(user_type_master_table.user_type=user_login_tbl.user_type and user_login_tbl.user_id=user_dtls_tbl.user_id and user_type_master_table.type_descr='hod'); 18 select to_char(user_created_dt,'dd/mm/yyyy'),user_name from user_dtls_tbl,user_login_tbl where(user_dtls_tbl.user_id=user_login_tbl.user_id); Create a table student with attributes admn_no as primary key,name,branch,sessional_mark,university_mark and total. create table student0(admn_no number(5) primary key,name varchar2(10),branch varchar2(5),sessional_mark number(4),university_mark number(5),total number(7)); Table created. 2.Insert data into table insert into student0 values(101,'ann','IT',45,82,127); 1 row(s) inserted. insert into student0 values(102,'resmi','CS',46,80,126); 1 row(s) inserted. 3.Display all details. select * from student0; Create a view with admn_no,sessional_mark,university_mark and total create view students as select admn_no,sessional_mark,university_mark,total from student; View created. 5.Insert two new records into the view and display it insert into students values(110,43,88,131); 1 row(s) inserted. insert into students values(111,46,80,126); 1 row(s) inserted. select * from students; Update university_mark of a particular student using view update student0 set university_mark=86 where admn_no=111; 1 row(s) updated. 7.Display the details select * from student0;