DBMS PRACTICAL 3 VIEW DEFINATION LANGUAGE

PRACTICAL 3:- VIEW DEFINITION LANGUAGE

SQL> create table emp57(emp_id varchar(10) primary key , emp_name varchar(20) not

null , address var

char(20) , desig varchar(20) , d_o_b date , d_o_j date , salary decimal(10,2));

Table created.

SQL> select * from emp57;

no rows selected

SQL> insert into emp57 values('01','MAYANK

SHARMA','BHAYANDAR','ENGINEER','04-AUG-89','20-AUG-08',20

000);

1 row created.

SQL> insert into emp57 values('02','ADITYA

LOHAI','BHAYANDAR','ENGINEER','04-AUG-89','20-AUG-08',150

00);

1 row created.

SQL> insert into emp57 values('03','BHUSHAN

TALEKAR','BHAYANDAR','ENGINEER','27-MAR-89','2-MAY-08',1

5000);

1 row created.

SQL> insert into emp57 values('04','SAURAB

SHETTY','DAHISAR','MANAGER','27-JAN-89','02-MAY-08',15000

);

1 row created.

SQL> insert into emp57 values('05','KHYATI

VASHI','BHAYANDAR','MANAGER','29-JAN-89','02-MAY-08',1500

0);

1 row created.

SQL> select * from emp57;

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

01 MAYANK SHARMA BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 20000

02 ADITYA LOHAI BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 15000

03 BHUSHAN TALEKAR BHAYANDAR ENGINEER

27-MAR-89 02-MAY-08 15000

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

04 SAURAB SHETTY DAHISAR MANAGER

27-JAN-89 02-MAY-08 15000

05 KHYATI VASHI BHAYANDAR MANAGER

29-JAN-89 02-MAY-08 15000

SQL> create view manager57 as(select * from emp57);

View created.

SQL> select * from manager57;

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

01 MAYANK SHARMA BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 20000

02 ADITYA LOHAI BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 15000

03 BHUSHAN TALEKAR BHAYANDAR ENGINEER

27-MAR-89 02-MAY-08 15000

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

04 SAURAB SHETTY DAHISAR MANAGER

27-JAN-89 02-MAY-08 15000

05 KHYATI VASHI BHAYANDAR MANAGER

29-JAN-89 02-MAY-08 15000

SQL> create view peon57 as(select emp_id,emp_name from emp57);

View created.

SQL> select * from peon57;

EMP_ID EMP_NAME

---------- --------------------

01 MAYANK SHARMA

02 ADITYA LOHAI

03 BHUSHAN TALEKAR

04 SAURAB SHETTY

05 KHYATI VASHI

SQL> insert into emp57 values('06','SANKET

TANDEL','VASAI','ENGINEER','29-MAY-89','02-AUG-09',15000)

;

1 row created.

SQL> select * from emp57;

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

01 MAYANK SHARMA BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 20000

02 ADITYA LOHAI BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 15000

03 BHUSHAN TALEKAR BHAYANDAR ENGINEER

27-MAR-89 02-MAY-08 15000

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

04 SAURAB SHETTY DAHISAR MANAGER

27-JAN-89 02-MAY-08 15000

05 KHYATI VASHI BHAYANDAR MANAGER

29-JAN-89 02-MAY-08 15000

06 SANKET TANDEL VASAI ENGINEER

29-MAY-89 02-AUG-09 15000

6 rows selected.

SQL> select * from manager57;

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

01 MAYANK SHARMA BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 20000

02 ADITYA LOHAI BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 15000

03 BHUSHAN TALEKAR BHAYANDAR ENGINEER

27-MAR-89 02-MAY-08 15000

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

04 SAURAB SHETTY DAHISAR MANAGER

27-JAN-89 02-MAY-08 15000

05 KHYATI VASHI BHAYANDAR MANAGER

29-JAN-89 02-MAY-08 15000

06 SANKET TANDEL VASAI ENGINEER

29-MAY-89 02-AUG-09 15000

6 rows selected.

SQL> select * from peon57;

EMP_ID EMP_NAME

---------- --------------------

01 MAYANK SHARMA

02 ADITYA LOHAI

03 BHUSHAN TALEKAR

04 SAURAB SHETTY

05 KHYATI VASHI

06 SANKET TANDEL

SQL> create view manager57 as(select * from emp57);

View created.

SQL> select * from manager57;

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

01 MAYANK SHARMA BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 20000

02 ADITYA LOHAI BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 15000

03 BHUSHAN TALEKAR BHAYANDAR ENGINEER

27-MAR-89 02-MAY-08 15000

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

04 SAURAB SHETTY DAHISAR MANAGER

27-JAN-89 02-MAY-08 15000

05 KHYATI VASHI BHAYANDAR MANAGER

29-JAN-89 02-MAY-08 15000

SQL> create view peon57 as(select emp_id,emp_name from emp57);

View created.

SQL> select * from peon57;

EMP_ID EMP_NAME

---------- --------------------

01 MAYANK SHARMA

02 ADITYA LOHAI

03 BHUSHAN TALEKAR

04 SAURAB SHETTY

05 KHYATI VASHI

SQL> insert into emp57 values('06','SANKET TANDEL','VASAI','ENGINEER','29-MAY-89','02-AUG-09',15000)

;

1 row created.

SQL> select * from emp57;

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

01 MAYANK SHARMA BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 20000

02 ADITYA LOHAI BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 15000

03 BHUSHAN TALEKAR BHAYANDAR ENGINEER

27-MAR-89 02-MAY-08 15000

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

04 SAURAB SHETTY DAHISAR MANAGER

27-JAN-89 02-MAY-08 15000

05 KHYATI VASHI BHAYANDAR MANAGER

29-JAN-89 02-MAY-08 15000

06 SANKET TANDEL VASAI ENGINEER

29-MAY-89 02-AUG-09 15000

6 rows selected.

SQL> select * from manager57;

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

01 MAYANK SHARMA BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 20000

02 ADITYA LOHAI BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 15000

03 BHUSHAN TALEKAR BHAYANDAR ENGINEER

27-MAR-89 02-MAY-08 15000

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

04 SAURAB SHETTY DAHISAR MANAGER

27-JAN-89 02-MAY-08 15000

05 KHYATI VASHI BHAYANDAR MANAGER

29-JAN-89 02-MAY-08 15000

06 SANKET TANDEL VASAI ENGINEER

29-MAY-89 02-AUG-09 15000

6 rows selected.

SQL> select * from peon57;

EMP_ID EMP_NAME

---------- --------------------

01 MAYANK SHARMA

02 ADITYA LOHAI

03 BHUSHAN TALEKAR

04 SAURAB SHETTY

05 KHYATI VASHI

06 SANKET TANDEL

6 rows selected.

SQL> SQL> select * from peon57;

SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.

SQL>

SQL> EMP_ID EMP_NAME

SP2-0734: unknown command beginning "EMP_ID ..." - rest of line ignored.

SQL> ---------- --------------------

SQL> 01 MAYANK SHARMA

SQL> 02 ADITYA LOHAI

SQL> 03 BHUSHAN TALEKAR

SQL> 04 SAURAB SHETTY

SQL> 05 KHYATI VASHI

SQL> 06 SANKET TANDEL

SQL>

SQL>

SQL>

SQL>

SQL> insert into manager57 values('07','ASHOK JANGID','BHAYANDAR','ENGINEER','01-MAY-89','22-AUG-09'

,15000);

1 row created.

SQL> select * from manager57;

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

01 MAYANK SHARMA BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 20000

02 ADITYA LOHAI BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 15000

03 BHUSHAN TALEKAR BHAYANDAR ENGINEER

27-MAR-89 02-MAY-08 15000

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

04 SAURAB SHETTY DAHISAR MANAGER

27-JAN-89 02-MAY-08 15000

05 KHYATI VASHI BHAYANDAR MANAGER

29-JAN-89 02-MAY-08 15000

06 SANKET TANDEL VASAI ENGINEER

29-MAY-89 02-AUG-09 15000

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

07 ASHOK JANGID BHAYANDAR ENGINEER

01-MAY-89 22-AUG-09 15000

7 rows selected.

SQL> select * from emp57;

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

01 MAYANK SHARMA BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 20000

02 ADITYA LOHAI BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 15000

03 BHUSHAN TALEKAR BHAYANDAR ENGINEER

27-MAR-89 02-MAY-08 15000

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

04 SAURAB SHETTY DAHISAR MANAGER

27-JAN-89 02-MAY-08 15000

05 KHYATI VASHI BHAYANDAR MANAGER

29-JAN-89 02-MAY-08 15000

06 SANKET TANDEL VASAI ENGINEER

29-MAY-89 02-AUG-09 15000

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

07 ASHOK JANGID BHAYANDAR ENGINEER

01-MAY-89 22-AUG-09 15000

7 rows selected.

SQL> select * from peon57;

EMP_ID EMP_NAME

---------- --------------------

01 MAYANK SHARMA

02 ADITYA LOHAI

03 BHUSHAN TALEKAR

04 SAURAB SHETTY

05 KHYATI VASHI

06 SANKET TANDEL

07 ASHOK JANGID

7 rows selected.

SQL> insert into peon57 values('08','ISHA VERMA');

1 row created.

SQL> select * from peon57;

EMP_ID EMP_NAME

---------- --------------------

01 MAYANK SHARMA

02 ADITYA LOHAI

03 BHUSHAN TALEKAR

04 SAURAB SHETTY

05 KHYATI VASHI

06 SANKET TANDEL

07 ASHOK JANGID

08 ISHA VERMA

8 rows selected.

SQL> select * from emp57;

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

01 MAYANK SHARMA BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 20000

02 ADITYA LOHAI BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 15000

03 BHUSHAN TALEKAR BHAYANDAR ENGINEER

27-MAR-89 02-MAY-08 15000

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

04 SAURAB SHETTY DAHISAR MANAGER

27-JAN-89 02-MAY-08 15000

05 KHYATI VASHI BHAYANDAR MANAGER

29-JAN-89 02-MAY-08 15000

06 SANKET TANDEL VASAI ENGINEER

29-MAY-89 02-AUG-09 15000

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

07 ASHOK JANGID BHAYANDAR ENGINEER

01-MAY-89 22-AUG-09 15000

08 ISHA VERMA

8 rows selected.

SQL> select * from manager57;

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

01 MAYANK SHARMA BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 20000

02 ADITYA LOHAI BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 15000

03 BHUSHAN TALEKAR BHAYANDAR ENGINEER

27-MAR-89 02-MAY-08 15000

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

04 SAURAB SHETTY DAHISAR MANAGER

27-JAN-89 02-MAY-08 15000

05 KHYATI VASHI BHAYANDAR MANAGER

29-JAN-89 02-MAY-08 15000

06 SANKET TANDEL VASAI ENGINEER

29-MAY-89 02-AUG-09 15000

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

07 ASHOK JANGID BHAYANDAR ENGINEER

01-MAY-89 22-AUG-09 15000

08 ISHA VERMA

8 rows selected.

SQL> create view secretary57 as(select emp_name,address from emp57);

View created.

SQL> select * from secretary57

2

SQL> select * from secretary57;

EMP_NAME ADDRESS

-------------------- --------------------

MAYANK SHARMA BHAYANDAR

ADITYA LOHAI BHAYANDAR

BHUSHAN TALEKAR BHAYANDAR

SAURAB SHETTY DAHISAR

KHYATI VASHI BHAYANDAR

SANKET TANDEL VASAI

ASHOK JANGID BHAYANDAR

ISHA VERMA

8 rows selected.

SQL> select * from emp57;

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

01 MAYANK SHARMA BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 20000

02 ADITYA LOHAI BHAYANDAR ENGINEER

04-AUG-89 20-AUG-08 15000

03 BHUSHAN TALEKAR BHAYANDAR ENGINEER

27-MAR-89 02-MAY-08 15000

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

04 SAURAB SHETTY DAHISAR MANAGER

27-JAN-89 02-MAY-08 15000

05 KHYATI VASHI BHAYANDAR MANAGER

29-JAN-89 02-MAY-08 15000

06 SANKET TANDEL VASAI ENGINEER

29-MAY-89 02-AUG-09 15000

EMP_ID EMP_NAME ADDRESS DESIG

---------- -------------------- -------------------- --------------------

D_O_B D_O_J SALARY

--------- --------- ----------

07 ASHOK JANGID BHAYANDAR ENGINEER

01-MAY-89 22-AUG-09 15000

08 ISHA VERMA

8 rows selected.

SQL> insert into secretary57 values('kusum sharma','miraroad');

insert into secretary57 values('kusum sharma','miraroad')

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("SYSTEM"."EMP57"."EMP_ID")