DBMS Lab Excercise-1

  Table Name: Course_Master

Field Name	Data types	Constraints
Course_ID	Varchar2(6)	PK
Course_Name	Varchar2(45)
Start_Date	Date
Strength	Number(2)

Table Creation
SQL>create table course_master(course_id varchar2(6) constraints pk_courseid primary key,
2        course_name varchar2(45),
3        start_date date,
4        Strength number(2));

Table created .

Insert Operation

SQL>  insert into course_master values(111,'MCA','24-sep-2010',60);

1 row inserted Successfully.

Update Operation

SQL>update course_master set course_id=1 where course_name='MCA';

1 row updated Successfully.

Delete Operation 

SQL>  delete from course_master where course_id=1;

no row selected.

Alter Operation

SQL> alter table course_master modify(course_id number(5));

Table Altered successfully. 

Table  Name: Staff_academic

Table Creation
SQL>create table staff_academic(staff_id number(6) constraints pk_staffid primary key,
2        name varchar2(30),department varchar2(30),designation varchar2(30),
3        app_date date,head char(5),
4        basic number(5));

Insert Operation

SQL>  insert into staff_academic values(101,'Master','MCA','Lecturer','24-sep-2010','XZY',20000);

1 row inserted Successfully.

Update Operation

SQL>update staff_academic set staff_id=1 where staff_id='101';

1 row updated Successfully.

Table Name: Student_academic

Table Creation:
SQL> create table student_academic(rollno number(4) constraints cst_no primary key, name varchar2(30), courseid varchar2(6) constraints cst_id references course_master(course_id) on delete cascade, admn_no number(4), adm_date date);

Insertion:: 

SQL>insert into student_academic(roll_no,name,course_id,admn_no,admn_date)values(1,raj,201,133,13-may-11);
Deletion::
SQL>delete from studentacadamic where name='muruga';
Update::
SQL>alter table studentacadamic modify name='balu' where roll_no=3;
 
Table Name:Student_personal

Creation::

SQL> create table student_personal(roll_no number(4) constraints ran_roll_no primary key,

name   varchar2(30) constraints nan_name references studentacadamic(name) on delete        cascade,sex varchar2(6),dob date,guardian varchar2(30),address1 varchar2(30),address2 varchar2(30),town varchar2(25),district varchar2(30),pin char(6));
 

Insertion::
SQL>  

insert into Student_personal (roll_no,name,sex,dob,guardian,address1,address2,town,district,pin) values(1,'raj','male','21-may-89','raja','mudalnagar','pondy','tamilnadu','pondy',605007);

 Deletion::
SQL>delete from student_personal where name='RGCET';

Update::
SQL>alter table student_personal modify name='raja' where roll_no=1;