1. Login
2.  
3. 1.create table user_type_master_table(user_type number(2)primary key,type_descr varchar(15));
4.  
5.  
6.  
7.  
8.  
9.  
10.  
11.  
12.  
13.  
14.  
15.  
16.  
17.  
18.  
19. 2.
20. insert into user_type_master_table values(1,'student');
21. insert into user_type_master_table values(2,'staffs');
22. insert into user_type_master_table values(3,'hod');
23. insert into user_type_master_table values(4,'principal');
24.  
25.  
26.  
27.  
28. 3.display table
29.  
30.  USER_TYPE TYPE_DESCR
31. ---------- ---------------
32.          1 student
33.          2 staffs
34.          3 hod
35.          4 principal
36.  
37. 4.create table dept_master_tbl(dept_code number(2)primary key,dept varchar(15));
38. 5.insert into dept_master_tbl values(1,'IT');
39. insert into dept_master_tbl values(2,'CS');
40. insert into dept_master_tbl values(3,'EC');
41.  
42.  
43.  
44. 6
45.  
46.  DEPT_CODE DEPT
47. ---------- ---------------
48.          1 IT
49.          2 CS
50.          3 EC
51. 7 create table user_dtls_tbl(user_id number(9)primary key,user_name varchar(30),dpt_code number(2)references dept_master_tbl);
52.  
53. insert into user_dtls_tbl values(01,'Arun',1);
54. insert into user_dtls_tbl values(02,'Abhi',2);
55. insert into user_dtls_tbl values(03,'Albert',3);
56. insert into user_dtls_tbl values(04,'Pranav',1);
57. insert into user_dtls_tbl values(05,'Herin',2);
58. insert into user_dtls_tbl values(06,'Amrith',3);
59. insert into user_dtls_tbl values(07,'Vishnu',2);
60. insert into user_dtls_tbl values(08,'Amal',1);
61. insert into user_dtls_tbl values(09,'Alan',3);
62. 8
63.  
64.  
65.   USER_ID USER_NAME                        DPT_CODE
66. --------- ------------------------------ ----------
67.         1 Arun                                    1
68.         2 Abhi                                    2
69.         3 Albert                                  3
70.         4 Pranav                                  1
71.         6 Amrith                                  3
72.         7 Vishnu                                  2
73.         8 Amal                                    1
74.         9 Alan                                    3
75.  
76. 9
77. 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);
78.  
79. 10
80.  
81. insert into user_login_tbl values(01,'aaa',1,'15-jan-04');
82. insert into user_login_tbl values(02,'bbb',1,'1-feb-06');
83. insert into user_login_tbl values(03,'ccc',2,'17-mar-05');
84. insert into user_login_tbl values(04,'ddd',1,'14-may-03');
85. insert into user_login_tbl values(05,'eee',3,'7-jun-04');
86. insert into user_login_tbl values(06,'fff',1,'8-jul-04');
87. insert into user_login_tbl values(07,'ggg',3,'5-aug-04');
88. insert into user_login_tbl values(08,'hhh',2,'2-jan-04');
89. insert into user_login_tbl values(09,'iii',1,'30-jan-04');
90.  
91.  
92.  
93.  
94.  
95. 11
96.    
97. USER_ID USER_PWD  USER_TYPE USER_CREA
98. ------- -------- ---------- ---------
99.       1 aaa               1 15-JAN-04
100.       2 bbb               1 01-FEB-06
101.       3 ccc               2 17-MAR-05
102.       4 ddd               1 14-MAY-03
103.       6 fff               1 08-JUL-04
104.       7 ggg               3 05-AUG-04
105.       8 hhh               2 02-JAN-04
106.       9 iii               1 30-JAN-04
107.  
108.  
109.  
110. 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'));
111.  
112.  
113.  
114. 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');
115.  
116.  
117.  
118.  
119.  
120.  
121. 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
122. type_descr='student'));
123.  
124.  
125.  
126. 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
127. type_descr='student'));
128.  
129.  
130. 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) ;
131.  
132.  
133.  
134. 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');
135.  
136.  
137.  
138.  
139.  
140. 18
141. 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);
142.  
143.  
144.  
145. Create a table student with attributes admn_no as primary
146. key,name,branch,sessional_mark,university_mark and total.
147. create table student0(admn_no number(5) primary key,name varchar2(10),branch
148. varchar2(5),sessional_mark number(4),university_mark number(5),total number(7));
149. Table created.
150. 2.Insert data into table
151. insert into student0 values(101,'ann','IT',45,82,127);
152. 1 row(s) inserted.
153. insert into student0 values(102,'resmi','CS',46,80,126);
154. 1 row(s) inserted.
155. 3.Display all details.
156. select * from student0;
157. Create a view with admn_no,sessional_mark,university_mark and total
158. create view students as select admn_no,sessional_mark,university_mark,total from student;
159. View created.
160. 5.Insert two new records into the view and display it
161. insert into students values(110,43,88,131);
162. 1 row(s) inserted.
163. insert into students values(111,46,80,126);
164. 1 row(s) inserted.
165. select * from students;
166. Update university_mark of a particular student using view
167. update student0 set university_mark=86 where admn_no=111;
168. 1 row(s) updated.
169. 7.Display the details
170. select * from student0;
171.  
172.  
173.