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
----------
5
7
8
9
3
8
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)
------------
6
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)
------------
6
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 /
10
9
8
7
6
5
4
3
2
1
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.
Comments
Post a Comment