PL-SQL

Write a program in SQL command.

1.a) Create the table invoice with fields inv_no(char,primary key),mv_no(number),cust_id(char),issue-date(date),return_date(date).

SQL> create table invoice (inv_no varchar2(5),mv_no number(10),cust_id char(6),issue_date date,return_date date);

Table created.

SQL> desc invoice

Name Null? Type

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

INV_NO VARCHAR2(5)

MV_NO NUMBER(10)

CUST_ID CHAR(6)

ISSUE_DATE DATE

RETURN_DATE DATE

Insert data in the table invoice as follows:

SQL> insert into invoice values('&inv_no','&mv_no','&cust_id','&issue_date','&return_date');

Enter value for inv_no: i01

Enter value for mv_no: 1

Enter value for cust_id: a01

Enter value for issue_date: 21-dec-06

Enter value for return_date: 26-dec-06

old 1: insert into invoice values('&inv_no','&mv_no','&cust_id','&issue_date','&return_date')

new 1: insert into invoice values('i01','1','a01','21-dec-06','26-dec-06')

1 row created.

SQL> /

Enter value for inv_no: i02

Enter value for mv_no: 3

Enter value for cust_id: a02

Enter value for issue_date: 19-oct-06

Enter value for return_date: 30-oct-06

old 1: insert into invoice values('&inv_no','&mv_no','&cust_id','&issue_date','&return_date')

new 1: insert into invoice values('i02','3','a02','19-oct-06','30-oct-06')

1 row created.

SQL> /

Enter value for inv_no: i03

Enter value for mv_no: 5

Enter value for cust_id: a03

Enter value for issue_date: 14-jun-06

Enter value for return_date: 30-aug-06

old 1: insert into invoice values('&inv_no','&mv_no','&cust_id','&issue_date','&return_date')

new 1: insert into invoice values('i03','5','a03','14-jun-06','30-aug-06')

1 row created.

SQL> select * from invoice;

INV_N MV_NO CUST_ID ISSUE_DAT RETURN_DA

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

i01 1 a01 21-DEC-06 26-DEC-06

i02 3 a02 19-OCT-06 30-OCT-06

i03 5 a03 14-JUN-06 30-AUG-06

i04 5 a04 15-SEP-06 30-SEP-06

i05 6 a05 19-OCT-06 21-OCT-06

i06 7 a06 01-JAN-06 15-JAN-06

i07 8 a07 15-AUG-06 30-AUG-06

i08 2 a05 01-OCT-06 15-OCT-06

i09 4 a01 12-SEP-06 19-SEP-06

i10 9 a02 01-DEC-06 15-DEC-06

i11 6 a05 02-FEB-06 14-FEB-06

INV_N MV_NO CUST_ID ISSUE_DAT RETURN_DA

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

i12 3 a06 01-JAN-06 10-JAN-06

12 rows selected.

1.b) Modifying table structure by adding the new field price(number) and increasing the size of inv_no.

SQL> alter table invoice add (price number(10));

Table altered.

SQL> desc invoice;

Name Null? Type

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

INV_NO VARCHAR2(5)

MV_NO NUMBER(10)

CUST_ID VARCHAR2(10)

ISSUE_DATE DATE

RETURN_DATE DATE

PRICE NUMBER(10)

SQL> alter table invoice modify(inv_no varchar2(8));

Table altered.

SQL> desc invoice;

Name Null? Type

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

INV_NO VARCHAR2(8)

MV_NO NUMBER(10)

CUST_ID VARCHAR2(10)

ISSUE_DATE DATE

RETURN_DATE DATE

PRICE NUMBER(10)

SQL> select * from invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i01 1 a01 21-DEC-06 26-DEC-06

i02 3 a02 19-OCT-06 30-OCT-06

i03 5 a03 14-JUN-06 30-AUG-06

i04 5 a04 15-SEP-06 30-SEP-06

i05 6 a05 19-OCT-06 21-OCT-06

i06 7 a06 01-JAN-06 15-JAN-06

i07 8 a07 15-AUG-06 30-AUG-06

i08 2 a05 01-OCT-06 15-OCT-06

i09 4 a01 12-SEP-06 19-SEP-06

i10 9 a02 01-DEC-06 15-DEC-06

i11 6 a05 02-FEB-06 14-FEB-06

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i12 3 a06 01-JAN-06 10-JAN-06

12 rows selected.

1.c) Add and Drop constrint.

i. Add primary key constraint.

SQL> alter table invoice add primary key(inv_no);

Table altered.

SQL> desc invoice;

Name Null? Type

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

INV_NO NOT NULL VARCHAR2(8)

MV_NO NUMBER(10)

CUST_ID VARCHAR2(10)

ISSUE_DATE DATE

RETURN_DATE DATE

PRICE NUMBER(10)

ii.Drop primary key constraint.

SQL> alter table invoice drop primary key;

Table altered.

SQL> desc invoice;

Name Null? Type

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

INV_NO VARCHAR2(8)

MV_NO NUMBER(10)

CUST_ID VARCHAR2(10)

ISSUE_DATE DATE

RETURN_DATE DATE

PRICE NUMBER(10)

iii.Add unique key constraint.

SQL> alter table invoice add unique (inv_no);

Table altered.

SQL> desc invoice ;

Name Null? Type

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

INV_NO VARCHAR2(8)

MV_NO NUMBER(10)

CUST_ID VARCHAR2(10)

ISSUE_DATE DATE

RETURN_DATE DATE

PRICE NUMBER(10)

4.Drop unique key constraint.

SQL> alter table invoice drop unique (inv_no);

Table altered.

SQL> desc invoice;

Name Null? Type

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

INV_NO VARCHAR2(8)

MV_NO NUMBER(10)

CUST_ID VARCHAR2(10)

ISSUE_DATE DATE

RETURN_DATE DATE

PRICE NUMBER(10)

i. Add check constraint.

SQL> alter table invoice add check(mv_no<10);

Table altered.

SQL> desc invoice;

Name Null? Type

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

INV_NO VARCHAR2(8)

MV_NO NUMBER(10)

CUST_ID VARCHAR2(10)

ISSUE_DATE DATE

RETURN_DATE DATE

PRICE NUMBER(10)

2) DML Statement:

a) Add a records in above table

SQL> select*from invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i01 1 a01 21-DEC-06 26-DEC-06

i02 3 a02 19-OCT-06 30-OCT-06

i03 5 a03 14-JUN-06 30-AUG-06

i04 5 a04 15-SEP-06 30-SEP-06

i05 6 a05 19-OCT-06 21-OCT-06

i06 7 a06 01-JAN-06 15-JAN-06

i07 8 a07 15-AUG-06 30-AUG-06

i08 2 a05 01-OCT-06 15-OCT-06

i09 4 a01 12-SEP-06 19-SEP-06

i10 9 a02 01-DEC-06 15-DEC-06

i11 6 a05 02-FEB-06 14-FEB-06

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i12 3 a06 01-JAN-06 10-JAN-06

12 rows selected.

SQL> insert into invoice values('&inv_no','&mv_no','&cust_id','&issue_date','&return_date','&price');

Enter value for inv_no: i13

Enter value for mv_no: 9

Enter value for cust_id: a01

Enter value for issue_date: 23-jan-06

Enter value for return_date: 27-JAN-06

Enter value for price: 786

old 1: insert into invoice values('&inv_no','&mv_no','&cust_id','&issue_date','&return_date','&price')

new 1: insert into invoice values('i13','9','a01','23-jan-06','27-JAN-06','786')

1 row created.

SQL> /

Enter value for inv_no: i14

Enter value for mv_no: 8

Enter value for cust_id: a07

Enter value for issue_date: 14-feb-06

Enter value for return_date: 20-feb-06

Enter value for price: 780

old 1: insert into invoice values('&inv_no','&mv_no','&cust_id','&issue_date','&return_date','&price')

new 1: insert into invoice values('i14','8','a07','14-feb-06','20-feb-06','780')

1 row created.

SQL> /

Enter value for inv_no: i15

Enter value for mv_no: 7

Enter value for cust_id: a06

Enter value for issue_date: 22-nov-06

Enter value for return_date: 22-dec-06

Enter value for price: 999

old 1: insert into invoice values('&inv_no','&mv_no','&cust_id','&issue_date','&return_date','&price')

new 1: insert into invoice values('i15','7','a06','22-nov-06','22-dec-06','999')

1 row created.

SQL> /

Enter value for inv_no: i16

Enter value for mv_no: 5

Enter value for cust_id: a05

Enter value for issue_date: 23-oct-06

Enter value for return_date: 28-oct-06

Enter value for price: 699

old 1: insert into invoice values('&inv_no','&mv_no','&cust_id','&issue_date','&return_date','&price')

new 1: insert into invoice values('i16','5','a05','23-oct-06','28-oct-06','699')

1 row created.

SQL> select*from invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i01 1 a01 21-DEC-06 26-DEC-06

i02 3 a02 19-OCT-06 30-OCT-06

i03 5 a03 14-JUN-06 30-AUG-06

i04 5 a04 15-SEP-06 30-SEP-06

i05 6 a05 19-OCT-06 21-OCT-06

i06 7 a06 01-JAN-06 15-JAN-06

i07 8 a07 15-AUG-06 30-AUG-06

i08 2 a05 01-OCT-06 15-OCT-06

i09 4 a01 12-SEP-06 19-SEP-06

i10 9 a02 01-DEC-06 15-DEC-06

i11 6 a05 02-FEB-06 14-FEB-06

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i12 3 a06 01-JAN-06 10-JAN-06

i13 9 a01 23-JAN-06 27-JAN-06 786

i14 8 a07 14-FEB-06 20-FEB-06 780

i15 7 a06 22-NOV-06 22-DEC-06 999

i16 5 a05 23-OCT-06 28-OCT-06 699

16 rows selected.

SQL> select*from invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i01 1 a01 21-DEC-06 26-DEC-06

i02 3 a02 19-OCT-06 30-OCT-06

i03 5 a03 14-JUN-06 30-AUG-06

i04 5 a04 15-SEP-06 30-SEP-06

i05 6 a05 19-OCT-06 21-OCT-06

i06 7 a06 01-JAN-06 15-JAN-06

i07 8 a07 15-AUG-06 30-AUG-06

i08 2 a05 01-OCT-06 15-OCT-06

i09 4 a01 12-SEP-06 19-SEP-06

i10 9 a02 01-DEC-06 15-DEC-06

i11 6 a05 02-FEB-06 14-FEB-06

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i12 3 a06 01-JAN-06 10-JAN-06

i13 9 a01 23-JAN-06 27-JAN-06 786

i14 8 a07 14-FEB-06 20-FEB-06 780

i15 7 a06 22-NOV-06 22-DEC-06 999

i16 5 a05 23-OCT-06 28-OCT-06 699

16 rows selected.

b.1) change inv_no of ‘i12’with ‘i111’

SQL> update invoice set inv_no='i111' where inv_no='i12';

1 row updated.

SQL> select*from invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i01 1 a01 21-DEC-06 26-DEC-06

i02 3 a02 19-OCT-06 30-OCT-06

i03 5 a03 14-JUN-06 30-AUG-06

i04 5 a04 15-SEP-06 30-SEP-06

i05 6 a05 19-OCT-06 21-OCT-06

i06 7 a06 01-JAN-06 15-JAN-06

i07 8 a07 15-AUG-06 30-AUG-06

i08 2 a05 01-OCT-06 15-OCT-06

i09 4 a01 12-SEP-06 19-SEP-06

i10 9 a02 01-DEC-06 15-DEC-06

i11 6 a05 02-FEB-06 14-FEB-06

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i111 3 a06 01-JAN-06 10-JAN-06

i13 9 a01 23-JAN-06 27-JAN-06 786

i14 8 a07 14-FEB-06 20-FEB-06 780

i15 7 a06 22-NOV-06 22-DEC-06 999

i16 5 a05 23-OCT-06 28-OCT-06 699

16 rows selected.

b.2) change issue_date of customer of cust_id ‘01’to ’22-dec-06’.

SQL> update invoice set issue_date='22-dec-06' where cust_id='a01';

2 rows updated.

SQL> select*from invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i01 1 a01 21-DEC-06 26-DEC-06

i02 3 a02 19-OCT-06 30-OCT-06

i03 5 a03 14-JUN-06 30-AUG-06

i04 5 a04 15-SEP-06 30-SEP-06

i05 6 a05 19-OCT-06 21-OCT-06

i06 7 a06 01-JAN-06 15-JAN-06

i07 8 a07 15-AUG-06 30-AUG-06

i08 2 a05 01-OCT-06 15-OCT-06

i09 4 a01 22-DEC-06 19-SEP-06

i10 9 a02 01-DEC-06 15-DEC-06

i11 6 a05 02-FEB-06 14-FEB-06

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i111 3 a06 01-JAN-06 10-JAN-06

i13 9 a01 22-DEC-06 27-JAN-06 786

i14 8 a07 14-FEB-06 20-FEB-06 780

i15 7 a06 22-NOV-06 22-DEC-06 999

i16 5 a05 23-OCT-06 28-OCT-06 699

16 rows selected.

b.3) change mv_no of where cust_id ‘a05’to ‘11’.

SQL> update invoice set mv_no='9' where cust_id='a05';

4 rows updated.

SQL> select*from invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i01 1 a01 21-DEC-06 26-DEC-06

i02 3 a02 19-OCT-06 30-OCT-06

i03 5 a03 14-JUN-06 30-AUG-06

i04 5 a04 15-SEP-06 30-SEP-06

i05 9 a05 19-OCT-06 21-OCT-06

i06 7 a06 01-JAN-06 15-JAN-06

i07 8 a07 15-AUG-06 30-AUG-06

i08 9 a05 01-OCT-06 15-OCT-06

i09 4 a01 22-DEC-06 19-SEP-06

i10 9 a02 01-DEC-06 15-DEC-06

i11 9 a05 02-FEB-06 14-FEB-06

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i111 3 a06 01-JAN-06 10-JAN-06

i13 9 a01 22-DEC-06 27-JAN-06 786

i14 8 a07 14-FEB-06 20-FEB-06 780

i15 7 a06 22-NOV-06 22-DEC-06 999

i16 9 a05 23-OCT-06 28-OCT-06 699

16 rows selected.

b.4) display all movies with issue_date between 1-jan-06 and 1-sep-06.

SQL> select mv_no from invoice where issue_date between '01-jan-06'and '01-sep-06';

MV_NO

----------

6 rows selected.

b.5) display the information of cust_id= ‘a01’ or cust_id= ‘a02’.

SQL> select*from invoice where cust_id='a01'or cust_id='a02';

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i09 4 a01 22-DEC-06 19-SEP-06

i10 9 a02 01-DEC-06 15-DEC-06

i13 9 a01 22-DEC-06 27-JAN-06 786

c.1) deleting the record from invoice where in _no= ‘i11’.

SQL> delete from invoice where inv_no='i11';

1 row deleted.

SQL> select*from invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i01 1 a01 21-DEC-06 26-DEC-06

i02 3 a02 19-OCT-06 30-OCT-06

i03 5 a03 14-JUN-06 30-AUG-06

i04 5 a04 15-SEP-06 30-SEP-06

i05 9 a05 19-OCT-06 21-OCT-06

i06 7 a06 01-JAN-06 15-JAN-06

i07 8 a07 15-AUG-06 30-AUG-06

i08 9 a05 01-OCT-06 15-OCT-06

i09 4 a01 22-DEC-06 19-SEP-06

i10 9 a02 01-DEC-06 15-DEC-06

i111 3 a06 01-JAN-06 10-JAN-06

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i13 9 a01 22-DEC-06 27-JAN-06 786

i14 8 a07 14-FEB-06 20-FEB-06 780

i15 7 a06 22-NOV-06 22-DEC-06 999

i16 9 a05 23-OCT-06 28-OCT-06 699

15 rows selected.

c.2) deleting the record having return_date<= ’20-jan-06’;

SQL> delete from invoice where return_date<='20-jan-06';

2 rows deleted.

SQL> select*from invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i01 1 a01 21-DEC-06 26-DEC-06

i02 3 a02 19-OCT-06 30-OCT-06

i03 5 a03 14-JUN-06 30-AUG-06

i04 5 a04 15-SEP-06 30-SEP-06

i05 9 a05 19-OCT-06 21-OCT-06

i07 8 a07 15-AUG-06 30-AUG-06

i08 9 a05 01-OCT-06 15-OCT-06

i09 4 a01 22-DEC-06 19-SEP-06

i10 9 a02 01-DEC-06 15-DEC-06

i13 9 a01 22-DEC-06 27-JAN-06 786

i14 8 a07 14-FEB-06 20-FEB-06 780

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i15 7 a06 22-NOV-06 22-DEC-06 999

i16 9 a05 23-OCT-06 28-OCT-06 699

13 rows selected.

3) DCL and TCL Statement

3.1) grant insetion and deletion privileges on invoice table to another user xyz.

SQL> create user xyz identified by abc ;

User created.

SQL> grant connect to xyz;

Grant succeeded.

SQL> grant insert,delete,select on invoice to xyz;

Grant succeeded.

SQL> connect xyz;

Enter password:

Connected.

SQL> connect xyz;

Enter password:

Connected.

SQL> insert into system.invoice values('&inv_no','&mv_no','&cust_id','&issue_date','&return_date','&price');

Enter value for inv_no: i17

Enter value for mv_no: 9

Enter value for cust_id: a09

Enter value for issue_date: 11-dec-06

Enter value for return_date: 25-dec-06

Enter value for price: 12

old 1: insert into system.invoice values('&inv_no','&mv_no','&cust_id','&issue_date','&return_date','&price')

new 1: insert into system.invoice values('i17','9','a09','11-dec-06','25-dec-06','12')

1 row created.

SQL> /

Enter value for inv_no: i18

Enter value for mv_no: 4

Enter value for cust_id: a08

Enter value for issue_date: 15-dec-06

Enter value for return_date: 20-dec-06

Enter value for price: 10

old 1: insert into system.invoice values('&inv_no','&mv_no','&cust_id','&issue_date','&return_date','&price')

new 1: insert into system.invoice values('i18','4','a08','15-dec-06','20-dec-06','10')

1 row created.

SQL> select*from system.invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i01 1 a01 21-DEC-06 26-DEC-06

i02 3 a02 19-OCT-06 30-OCT-06

i03 5 a03 14-JUN-06 30-AUG-06

i04 5 a04 15-SEP-06 30-SEP-06

i05 9 a05 19-OCT-06 21-OCT-06

i07 8 a07 15-AUG-06 30-AUG-06

i08 9 a05 01-OCT-06 15-OCT-06

i09 4 a01 22-DEC-06 19-SEP-06

i10 9 a02 01-DEC-06 15-DEC-06

i13 9 a01 22-DEC-06 27-JAN-06 786

i14 8 a07 14-FEB-06 20-FEB-06 780

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i15 7 a06 22-NOV-06 22-DEC-06 999

i16 9 a05 23-OCT-06 28-OCT-06 699

i17 9 a09 11-DEC-06 25-DEC-06 12

i18 4 a08 15-DEC-06 20-DEC-06 10

15 rows selected.

3.2) revoke the deletion privileges from user xyz

SQL> delete from system.invoice where inv_no='i07';

1 row deleted.

SQL> select*from system.invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i01 1 a01 21-DEC-06 26-DEC-06

i02 3 a02 19-OCT-06 30-OCT-06

i03 5 a03 14-JUN-06 30-AUG-06

i04 5 a04 15-SEP-06 30-SEP-06

i05 9 a05 19-OCT-06 21-OCT-06

i08 9 a05 01-OCT-06 15-OCT-06

i09 4 a01 22-DEC-06 19-SEP-06

i10 9 a02 01-DEC-06 15-DEC-06

i13 9 a01 22-DEC-06 27-JAN-06 786

i14 8 a07 14-FEB-06 20-FEB-06 780

i15 7 a06 22-NOV-06 22-DEC-06 999

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i16 9 a05 23-OCT-06 28-OCT-06 699

i17 9 a09 11-DEC-06 25-DEC-06 12

i18 4 a08 15-DEC-06 20-DEC-06 10

14 rows selected.

SQL> connect system

Enter password:

Connected.

SQL> revoke delete on invoice from xyz;

Revoke succeeded.

3.3) After deletion of 2 records in invoice table create savepoint A and B.

SQL> delete from invoice where inv_no='i13';

1 row deleted.

SQL> savepoint A;

Savepoint created.

SQL> delete from invoice where inv_no='i14';

1 row deleted.

SQL> savepoint B;

Savepoint created.

3.4) Rollback to A.

SQL> rollback to A;

Rollback complete.

SQL> select*from invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i01 1 a01 21-DEC-06 26-DEC-06

i02 3 a02 19-OCT-06 30-OCT-06

i03 5 a03 14-JUN-06 30-AUG-06

i04 5 a04 15-SEP-06 30-SEP-06

i05 9 a05 19-OCT-06 21-OCT-06

i08 9 a05 01-OCT-06 15-OCT-06

i09 4 a01 22-DEC-06 19-SEP-06

i10 9 a02 01-DEC-06 15-DEC-06

i14 8 a07 14-FEB-06 20-FEB-06 780

i15 7 a06 22-NOV-06 22-DEC-06 999

i16 9 a05 23-OCT-06 28-OCT-06 699

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

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

i17 9 a09 11-DEC-06 25-DEC-06 12

i18 4 a08 15-DEC-06 20-DEC-06 10

13 rows selected.

4.1) Create the table movie with fileds mv_no(number,primary key),title(char),type(char),star(char),price(number),release_date(date).

SQL> Create table movie(mv_no number(4),title varchar2(10),type varchar2(10),star varchar2(10),price number(4),release_date date);

Table created.

SQL> desc movie;

Name Null? Type

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

MV_NO NUMBER(4)

TITLE VARCHAR2(10)

TYPE VARCHAR2(10)

STAR VARCHAR2(10)

PRICE NUMBER(4)

RELEASE_DATE DATE

SQL> insert into movie values('&mv_no','&title','&type','&star','&price','&release_date');

Enter value for mv_no: 1

Enter value for title: kabir singh

Enter value for type: love story

Enter value for star: shahid

Enter value for price: 300

Enter value for release_date: 21-sep-19

old 1: insert into movie values('&mv_no','&title','&type','&star','&price','&release_date')

new 1: insert into movie values('1','kabir singh','love story','shahid','300','21-sep-19')

1 row created.

SQL> insert into movie values('&mv_no','&title','&type','&star','&price','&release_date');

Enter value for mv_no: 2

Enter value for title: geeta govindam

Enter value for type: romance

Enter value for star: vijay

Enter value for price: 250

Enter value for release_date: 22-dec-19

old 1: insert into movie values('&mv_no','&title','&type','&star','&price','&release_date')

new 1: insert into movie values('2','geeta govindam','romance','vijay','250','22-dec-19')

1 row created.

SQL> insert into movie values('&mv_no','&title','&type','&star','&price','&release_date');

Enter value for mv_no: 3

Enter value for title: kiyara

Enter value for type: motivational

Enter value for star: sonakshi

Enter value for price: 200

Enter value for release_date: 23-jan-18

old 1: insert into movie values('&mv_no','&title','&type','&star','&price','&release_date')

new 1: insert into movie values('3','kiyara','motivational','sonakshi','200','23-jan-18')

1 row created.

SQL> select*from movie;

MV_NO TITLE TYPE STAR PRICE RELEASE_D

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

1 kabir singh love story shahid 300 21-SEP-19

2 geeta govindam romance vijay 250 22-DEC-19

3 kiyara motivational sonakshi 200 23-JAN-18

4 yariyaan friendship rakul 280 12-FEB-15

5 kaminey sahid shahid 320 22-SEP-16

6 krish action ritik 260 17-MAR-15

6 rows selected.

4.2) count totle number of customer.

SQL> select count(mv_no)from movie;

COUNT(MV_NO)

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

4.3) calculate totle price of all the movie.

SQL> select sum(price)from movie;

SUM(PRICE)

----------

1610

4.4) calculate average price of all the movies.

SQL> select avg(price)from movie;

AVG(PRICE)

----------

268.333333

4.5) determine the maximum and minimum movies price and rename the title as MAX-PRICE and MIN-PRICE.

SQL> select max(price)"max-price" ,min(price)"min-price"from movie;

max-price min-price

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

320 200

4.6)count the number of movies having price greater than or equel to 150.

SQL> select count(mv_no)from movie where price>=150;

COUNT(MV_NO)

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

4.7) display all the movie which start with letter ‘k’.

SQL> select*from movie where title like'k%';

MV_NO TITLE TYPE STAR PRICE RELEASE_D

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

1 kabir singh love story shahid 300 21-SEP-19

3 kiyara motivational sonakshi 200 23-JAN-18

5 kaminey sahid shahid 320 22-SEP-16

6 krish action ritik 260 17-MAR-15

5.1) Create view of table invoice with following coloumn

Inv_mo (char,primary key),mv_no (number),cust_id (char).

SQL> create view myvoice as select inv_no,mv_no,cust_id from invoice;

View created.

SQL> desc myvoice;

Name Null? Type

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

INV_NO VARCHAR2(8)

MV_NO NUMBER(10)

CUST_ID VARCHAR2(10)

SQL> insert into myvoice values('i13','5','a03');

1 row created.

5.2) Insert and delete records.

SQL> select*from myvoice;

INV_NO MV_NO CUST_ID

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

i01 1 a01

i02 3 a02

i03 5 a03

i04 5 a04

i05 9 a05

i13 5 a03

i08 9 a05

i09 4 a01

i10 9 a02

i14 8 a07

i15 7 a06

INV_NO MV_NO CUST_ID

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

i16 9 a05

i17 9 a09

i18 4 a08

14 rows selected.

SQL> delete from myvoice where inv_no='i10';

1 row deleted.

SQL> select*from myvoice;

INV_NO MV_NO CUST_ID

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

i01 1 a01

i02 3 a02

i03 5 a03

i04 5 a04

i05 9 a05

i13 5 a03

i08 9 a05

i09 4 a01

i14 8 a07

i15 7 a06

i16 9 a05

INV_NO MV_NO CUST_ID

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

i17 9 a09

i18 4 a08

13 rows selected.

5.3) Drop view

SQL> drop view myvoice;

View dropped.

6)Write a PL/SQL block

6.1) to reverse the number

SQL> set serveroutput on

SQL> begin

2 for k in reverse 1..10

3 loop

4 dbms_output.put_line(k);

5 end loop;

6 end;

7 /

PL/SQL procedure successfully completed.

6.2) to largest number.

SQL> set serveroutput on

SQL> declare

2 A number(3):=&A;

3 B number(4):=&B;

4 begin

5 if(A>B)then

6 dbms_output.put_line('the largest number is ='||A);

7 else

8 dbms_output.put_line('the largest number is ='||B);

9 end if;

10 end;

11 /

Enter value for a: 12

old 2: A number(3):=&A;

new 2: A number(3):=12;

Enter value for b: 13

old 3: B number(4):=&B;

new 3: B number(4):=13;

the largest number is =13

PL/SQL procedure successfully completed.

7) cursor

7.1)Write a cursor to fetch all rows of table invoice.

7.2)Write a cursor to display all movies with issue_date between 01-jan-06 to 01-sep-06.

If no record found then use exception to display proper message.

SQL> set serveroutput on

SQL> declare

2 inv_no varchar2(8);

3 mv_no number(10);

4 cust_id varchar(10);

5 issue_date date;

6 return_date date;

7 price number(10);

8 cursor curl is select inv_no,mv_no,cust_id,issue_date,return_date,price from invoice;

9 begin

10 open curl;

11 dbms_output.put_line('the detail information of invoice table');

12 for i in 1..12

13 loop

14 fetch curl into inv_no,mv_no,cust_id,issue_date,return_date,price;

15 dbms_output.put_line(inv_no||''||mv_no||''||cust_id||''||issue_date||''||return_date||''||price);

16 end loop;

17 close curl;

18 end;

19 /

the detail information of invoice table

i011a01 21-DEC-0626-DEC-06

i023a02 19-OCT-0630-OCT-06

i035a03 14-JUN-0630-AUG-06

i045a0415-SEP-0630-SEP-06

i059a0519-OCT-0621-OCT-06

i135a03

i089a0501-OCT-0615-OCT-06

i094a0122-DEC-0619-SEP-06

i148a0714-FEB-0620-FEB-06780

i157a0622-NOV-0622-DEC-06999

i169a0523-OCT-0628-OCT-06699

i179a0911-DEC-0625-DEC-0612

PL/SQL procedure successfully completed.

8)Procedure

8.1) to swap two values

SQL> create or replace procedure swap_no is

2 a number;

3 b number;

4 tmp number;

5 begin

6 a:=2;

7 b:=4;

8 tmp:=a;

9 a:=b;

10 b:=tmp;

11 dbms_output.put_line('a='||a);

12 dbms_output.put_line('b='||b);

13 end;

14 /

Procedure created.

SQL> set serveroutput on;

SQL> execute swap_no;

a=4

b=2

PL/SQL procedure successfully completed.

8.2)to display the cust_id,mv_no and call the procedure through main program

SQL> set serveroutput on

SQL> declare

2 cust invoice.cust_id%type;

3 mv invoice.mv_no%type;

4 cursor c1 is select cust_id,mv_no from invoice;

5 begin

6 open c1;

7 dbms_output.put_line('cust_id'||'mv_no');

8 for i in 1..5

9 loop

10 fetch c1 into cust,mv;

11 p1(cust,mv);

12 end loop;

13 close c1;

14 end;

15 /

cust_idmv_no

a01 1

a02 3

a03 5

a04 5

a05 6

PL/SQL procedure successfully completed.

Search

Bachalor Of Computer Application

Write a program to find sum of 10 to 20 numbers.

PL-SQL

Comments

Post a Comment