1) DDL Statements :

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).
Insert data in the table invoice as follows:
Inv_no mv_no cust_id issue_date return_date
I01 1 a01 21-Dec-24 25-Dec-24
I02 3 a02 19-Oct-24 30-Oct-24
I03 5 a03 14-Jul-24 1-Aug-24
I04 5 a04 15-Sep-24 30-Sept-24
I05 6 a05 19-Oct-24 21-Oct-24
I06 7 a06 1-Jan-24 15-Jan-24
I07 8 a07 15-Aug-24 30-Aug-24
I08 2 a05 1-Oct-24 15-Oct-24
I09 4 a01 12-sep-24 19-Sep-24
I10 9 a02 1-Dec-24 15-Dec-24
I11 6 a05 2-feb-24 14-feb-24
I12 3 a06 1-Jan-24 10-Jan-24
Ans:-

SQL> CREATE TABLE Invoice(inv_no char(10) Primary key, mv_no

number(10),cust_id char(10), issue_date date, return_date date);

Table created.
SQL> INSERT INTO Invoice values('I01','1','a01','21-Dec-24','25-Dec-24');

1 row created.
SQL> INSERT INTO Invoice values('I02','3','a02','19-Oct-24','30-Oct-24');

1 row created.

SQL> INSERT INTO Invoice values('I03','5','a03','14-Jul-24','01-Aug-24');

1 row created.

SQL> INSERT INTO Invoice values('I04','5','a04','15-Sep-24','30-Sep-24');

1 row created.

SQL> INSERT INTO Invoice values('I05','6','a05','19-Oct-24','21-Oct-24');

1 row created.

SQL> INSERT INTO Invoice values('I06','7','a06','1-Jan-24','15-Jan-24');

1 row created.
SQL> INSERT INTO Invoice values('I07','8','a07','15-Aug-24','30-Aug-24');

1 row created.

SQL> INSERT INTO Invoice values('I08','2','a05','1-Oct-24','15-Oct-24');

1 row created.

SQL> INSERT INTO Invoice values('I09','4','a01','12-Sep-24','19-Sep-24');

1 row created.

SQL> INSERT INTO Invoice values('I10','9','a02','1-Dec-24','15-Dec-24');

1 row created.

SQL> INSERT INTO Invoice values('I11','6','a05','2-Feb-24','14-Feb-24');

1 row created.

SQL> INSERT INTO Invoice values('I12','3','a06','1-Jan-24','10-Jan-24');

1 row created.
SQL> SELECT * FROM Invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA

---------- ---------- ---------- --------- ---------
I01 1 a01 21-DEC-24 25-DEC-24
I02 3 a02 19-OCT-24 30-OCT-24
I03 5 a03 14-JUL-24 01-AUG-24
I04 5 a04 15-SEP-24 30-SEP-24
I05 6 a05 19-OCT-24 21-OCT-24
I06 7 a06 01-JAN-24 15-JAN-24
I07 8 a07 15-AUG-24 30-AUG-24
I08 2 a05 01-OCT-24 15-OCT-24
I09 4 a01 12-SEP-24 19-SEP-24
I10 9 a02 01-DEC-24 15-DEC-24
I11 6 a05 02-FEB-24 14-FEB-24

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA

---------- ---------- ---------- --------- ---------
I12 3 a06 01-JAN-24 10-JAN-24

12 rows selected.
1.b) Modifying Table Structure by adding the new field price (number) and
increasing the size of inv_no.

Ans:-

SQL> ALTER TABLE Invoice ADD(price number(10));

Table altered.

SQL> ALTER TABLE Invoice MODIFY (inv_no char(15));

Table altered.

SQL> DESC Invoice;

Name Null? Type
----------------------------------------- -------- ----------------------------
INV_NO NOT NULL CHAR(15)
MV_NO NUMBER(10)
CUST_ID CHAR(10)
ISSUE_DATE DATE
RETURN_DATE DATE
PRICE NUMBER(10)
1.c) Add and Drop Constraints
i) Add primary key constraint
ii) Drop primary key constraint
iii) Add Unique key constraint
iv) Drop Unique key constraint
v) Add check constraint

Ans:-
i) Add primary key constraint

SQL> ALTER TABLE Invoice DROP PRIMARY KEY;

Table altered.

SQL> ALTER TABLE Invoice ADD PRIMARY KEY(inv_no);

Table altered.

SQL> DESC Invoice;

Name Null? Type
----------------------------------------- -------- ----------------------------
INV_NO NOT NULL CHAR(15)
MV_NO NUMBER(10)
CUST_ID CHAR(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 CHAR(15)
MV_NO NUMBER(10)
CUST_ID CHAR(10)
ISSUE_DATE DATE
RETURN_DATE DATE
PRICE NUMBER(10)
 iii) Add Unique key constraint
ALTER TABLE Invoice ADD CONSTRAINT U UNIQUE(inv_no);

Table altered.

SQL> DESC Invoice;

Name Null? Type
----------------------------------------- -------- ----------------------------
INV_NO CHAR(15)
MV_NO NUMBER(10)
CUST_ID CHAR(10)
ISSUE_DATE DATE
RETURN_DATE DATE
PRICE NUMBER(10)

iv) Drop Unique key constraint

SQL> ALTER TABLE Invoice DROP UNIQUE(inv_no);

Table altered.

SQL> DESC Invoice;

Name Null? Type
----------------------------------------- -------- ----------------------------
INV_NO CHAR(15)
MV_NO NUMBER(10)
CUST_ID CHAR(10)
ISSUE_DATE DATE
RETURN_DATE DATE
PRICE NUMBER(10)

v) Add check constraint

SQL> ALTER TABLE Invoice ADD CONSTRAINT C CHECK(mv_no<10);

Table altered.

SQL> INSERT INTO Invoice values('I13','10','a03','7-Jun-24','20-Jun-24','500');

INSERT INTO Invoice values('I13','10','a03','7-Jun-24','20-Jun-24','500')
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.C) violated
2) DML Statements:
2.a) Add records in above table
SQL> SELECT * from Invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I01 1 a01 21-DEC-24 25-DEC-24
I02 3 a02 19-OCT-24 30-OCT-24
I03 5 a03 14-JUL-24 01-AUG-24
I04 5 a04 15-SEP-24 30-SEP-24
I05 6 a05 19-OCT-24 21-OCT-24
I06 7 a06 01-JAN-24 15-JAN-24
I07 8 a07 15-AUG-24 30-AUG-24
I08 2 a05 01-OCT-24 15-OCT-24
I09 4 a01 12-SEP-24 19-SEP-24
I10 9 a02 01-DEC-24 15-DEC-24
I11 6 a05 02-FEB-24 14-FEB-24

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I12 3 a06 01-JAN-24 10-JAN-24

12 rows selected.

SQL> INSERT INTO Invoice values('I13','1','a10','24-Aug-24','28-Aug-24','500');

1 row created.

SQL> INSERT INTO Invoice values('I13','2','a11','21-Aug-24','30-Aug-24','700');

1 row created.

SQL> INSERT INTO Invoice values('I15','3','a17','17-Aug-24','30-Aug-24','900');

1 row created.

SQL> INSERT INTO Invoice values('I15','7','a11','21-Aug-24','30-Aug-24','1700');

1 row created.

SQL> SELECT * FROM Invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I01 1 a01 21-DEC-24 25-DEC-24
I02 3 a02 19-OCT-24 30-OCT-24
I03 5 a03 14-JUL-24 01-AUG-24
I04 5 a04 15-SEP-24 30-SEP-24
I05 6 a05 19-OCT-24 21-OCT-24
I06 7 a06 01-JAN-24 15-JAN-24
I07 8 a07 15-AUG-24 30-AUG-24
I08 2 a05 01-OCT-24 15-OCT-24
I09 4 a01 12-SEP-24 19-SEP-24
I10 9 a02 01-DEC-24 15-DEC-24
I11 6 a05 02-FEB-24 14-FEB-24

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I12 3 a06 01-JAN-24 10-JAN-24
I13 1 a10 24-AUG-24 28-AUG-24 500
I13 2 a11 21-AUG-24 30-AUG-24 700
I15 3 a17 17-AUG-24 30-AUG-24 900
I15 7 a11 21-AUG-24 30-AUG-24 1700

16 rows selected.
2.b)
i)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-24 25-DEC-24
I02 3 a02 19-OCT-24 30-OCT-24
I03 5 a03 14-JUL-24 01-AUG-24
I04 5 a04 15-SEP-24 30-SEP-24
I05 6 a05 19-OCT-24 21-OCT-24
I06 7 a06 01-JAN-24 15-JAN-24
I07 8 a07 15-AUG-24 30-AUG-24
I08 2 a05 01-OCT-24 15-OCT-24
I09 4 a01 12-SEP-24 19-SEP-24
I10 9 a02 01-DEC-24 15-DEC-24
I11 6 a05 02-FEB-24 14-FEB-24

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I111 3 a06 01-JAN-24 10-JAN-24
I13 1 a10 24-AUG-24 28-AUG-24 500
I13 2 a11 21-AUG-24 30-AUG-24 700
I15 3 a17 17-AUG-24 30-AUG-24 900
I15 7 a11 21-AUG-24 30-AUG-24 1700

16 rows selected.

ii) Change issue_date of customer of cust_id ‘a01’ to ’22-Dec-24’.

SQL> UPDATE Invoice SET issue_date = '22-Dec-24' WHERE inv_no='I01';

1 row updated.

SQL> SELECT * FROM Invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I01 1 a01 22-DEC-24 25-DEC-24
I02 3 a02 19-OCT-24 30-OCT-24
I03 5 a03 14-JUL-24 01-AUG-24
I04 5 a04 15-SEP-24 30-SEP-24
 I05 6 a05 19-OCT-24 21-OCT-24
I06 7 a06 01-JAN-24 15-JAN-24
I07 8 a07 15-AUG-24 30-AUG-24
I08 2 a05 01-OCT-24 15-OCT-24
I09 4 a01 12-SEP-24 19-SEP-24
I10 9 a02 01-DEC-24 15-DEC-24
I11 6 a05 02-FEB-24 14-FEB-24

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I111 3 a06 01-JAN-24 10-JAN-24
I13 1 a10 24-AUG-24 28-AUG-24 500
I13 2 a11 21-AUG-24 30-AUG-24 700
I15 3 a17 17-AUG-24 30-AUG-24 900
I15 7 a11 21-AUG-24 30-AUG-24 1700

16 rows selected.

iii) Change movie no of cust_id ‘a05’ to 11.

SQL> ALTER TABLE Invoice DROP CONSTRAINT C;

Table altered.

SQL> UPDATE Invoice SET mv_no = '11' WHERE cust_id = 'a05';

3 rows updated.

SQL> SELECT * FROM Invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I01 1 a01 22-DEC-24 25-DEC-24
I02 3 a02 19-OCT-24 30-OCT-24
I03 5 a03 14-JUL-24 01-AUG-24
I04 5 a04 15-SEP-24 30-SEP-24
I05 11 a05 19-OCT-24 21-OCT-24
I06 7 a06 01-JAN-24 15-JAN-24
I07 8 a07 15-AUG-24 30-AUG-24
I08 11 a05 01-OCT-24 15-OCT-24
I09 4 a01 12-SEP-24 19-SEP-24
I10 9 a02 01-DEC-24 15-DEC-24
I11 11 a05 02-FEB-24 14-FEB-24

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I111 3 a06 01-JAN-24 10-JAN-24
 I13 1 a10 24-AUG-24 28-AUG-24 500
I13 2 a11 21-AUG-24 30-AUG-24 700
I15 3 a17 17-AUG-24 30-AUG-24 900
I15 7 a11 21-AUG-24 30-AUG-24 1700

16 rows selected.

iv) Display all movies with issue_date between 01-jan-24 to 01-sep-24 .

SQL> SELECT mv_no,issue_date FROM Invoice WHERE issue_date
BETWEEN '1-Jan-24' AND '1-Sep-24';

MV_NO ISSUE_DAT
---------- ---------
5 14-JUL-24
7 01-JAN-24
8 15-AUG-24
11 02-FEB-24
3 01-JAN-24
1 24-AUG-24
2 21-AUG-24
3 17-AUG-24
7 21-AUG-24

9 rows selected.

vi) Display information for cust_id ‘a01’ & ‘a02’.

SQL> SELECT * FROM Invoice WHERE cust_id IN('a01','a02');

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA

PRICE
--------------- ---------- ---------- --------- --------- ----------
I01 1 a01 22-DEC-24 25-DEC-24
I02 3 a02 19-OCT-24 30-OCT-24
I09 4 a01 12-SEP-24 19-SEP-24
I10 9 a02 01-DEC-24 15-DEC-24
2.c)
i) Delete records with inv_no ‘I11’from invoice table.

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 22-DEC-24 25-DEC-24
I02 3 a02 19-OCT-24 30-OCT-24
I03 5 a03 14-JUL-24 01-AUG-24
I04 5 a04 15-SEP-24 30-SEP-24
I05 11 a05 19-OCT-24 21-OCT-24
I06 7 a06 01-JAN-24 15-JAN-24
I07 8 a07 15-AUG-24 30-AUG-24
I08 11 a05 01-OCT-24 15-OCT-24
I09 4 a01 12-SEP-24 19-SEP-24
I10 9 a02 01-DEC-24 15-DEC-24
I111 3 a06 01-JAN-24 10-JAN-24

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I13 1 a10 24-AUG-24 28-AUG-24 500
I13 2 a11 21-AUG-24 30-AUG-24 700
I15 3 a17 17-AUG-24 30-AUG-24 900
I15 7 a11 21-AUG-24 30-AUG-24 1700

15 rows selected.

ii)Delete records having return date <=’20-Jan-24’.

SQL> DELETE FROM Invoice WHERE return_date <='20-Jan-24';

2 rows deleted.

SQL> SELECT * FROM Invoice ;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I01 1 a01 22-DEC-24 25-DEC-24
I02 3 a02 19-OCT-24 30-OCT-24
I03 5 a03 14-JUL-24 01-AUG-24
I04 5 a04 15-SEP-24 30-SEP-24
 I05 11 a05 19-OCT-24 21-OCT-24
I07 8 a07 15-AUG-24 30-AUG-24
I08 11 a05 01-OCT-24 15-OCT-24
I09 4 a01 12-SEP-24 19-SEP-24
I10 9 a02 01-DEC-24 15-DEC-24
I13 1 a10 24-AUG-24 28-AUG-24 500
I13 2 a11 21-AUG-24 30-AUG-24 700

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I15 3 a17 17-AUG-24 30-AUG-24 900
I15 7 a11 21-AUG-24 30-AUG-25 1700

13 rows selected.
3) DCL and TCL Statements
i) Grant insertion and deletion privileges on invoice table to another user XYZ.
SQL> SELECT * FROM Invoice ;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I01 1 a01 22-DEC-24 25-DEC-24
I02 3 a02 19-OCT-24 30-OCT-24
I03 5 a03 14-JUL-24 01-AUG-24
I04 5 a04 15-SEP-24 30-SEP-24
I05 11 a05 19-OCT-24 21-OCT-24
I07 8 a07 15-AUG-24 30-AUG-24
I08 11 a05 01-OCT-24 15-OCT-24
I09 4 a01 12-SEP-24 19-SEP-24
I10 9 a02 01-DEC-24 15-DEC-24
I13 1 a10 24-AUG-24 28-AUG-24 500
I13 2 a11 21-AUG-24 30-AUG-24 700

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I15 3 a17 17-AUG-24 30-AUG-24 900
I15 7 a11 21-AUG-24 30-AUG-25 1700

13 rows selected.
SQL> CREATE USER XYZ IDENTIFIED BY xyz ;

User created.

SQL> GRANT INSERT , DELETE ON Invoice TO XYZ ;

Grant succeeded.

ii) Revoke the deletion privileges from user XYZ.

SQL> REVOKE DELETE ON Invoice FROM XYZ ;

Revoke succeeded.
iii) After insertion of 2 records in invoice table create savepoint A.

SQL> INSERT INTO Invoice VALUES('I16','6','a06','15-Nov-24','20-Nov-

24','3000');

1 row created.

SQL> INSERT INTO Invoice VALUES('I17','7','a08','4-Oct-24','20-Oct-

24','5000');

1 row created.

SQL> SELECT * FROM Invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I01 1 a01 22-DEC-24 25-DEC-24
I02 3 a02 19-OCT-24 30-OCT-24
I03 5 a03 14-JUL-24 01-AUG-24
I04 5 a04 15-SEP-24 30-SEP-24
I05 11 a05 19-OCT-24 21-OCT-24
I07 8 a07 15-AUG-24 30-AUG-24
I08 11 a05 01-OCT-24 15-OCT-24
I09 4 a01 12-SEP-24 19-SEP-24
I10 9 a02 01-DEC-24 15-DEC-24
I13 1 a10 24-AUG-24 28-AUG-24 500
I13 2 a11 21-AUG-24 30-AUG-24 700

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I15 3 a17 17-AUG-24 30-AUG-24 900
I15 7 a11 21-AUG-24 30-AUG-25 1700
I16 6 a06 15-NOV-24 20-NOV-24 3000
I17 7 a08 04-OCT-24 20-OCT-24 5000

15 rows selected.

SQL> SAVEPOINT A;

Savepoint created.
iv) Delete a record and create savepoint B.

SQL> DELETE FROM Invoice WHERE PRICE='1700';

1 row deleted.

SQL> SELECT * FROM Invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I01 1 a01 22-DEC-24 25-DEC-24
I02 3 a02 19-OCT-24 30-OCT-24
I03 5 a03 14-JUL-24 01-AUG-24
I04 5 a04 15-SEP-24 30-SEP-24
I05 11 a05 19-OCT-24 21-OCT-24
I07 8 a07 15-AUG-24 30-AUG-24
I08 11 a05 01-OCT-24 15-OCT-24
I09 4 a01 12-SEP-24 19-SEP-24
I10 9 a02 01-DEC-24 15-DEC-24
I13 1 a10 24-AUG-24 28-AUG-24 500
I13 2 a11 21-AUG-24 30-AUG-24 700

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I15 3 a17 17-AUG-24 30-AUG-24 900
I16 6 a06 15-NOV-24 20-NOV-24 3000
I17 7 a08 04-OCT-24 20-OCT-24 5000

14 rows selected.

SQL> SAVEPOINT B;

Savepoint created.

v) 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 22-DEC-24 25-DEC-24
 I02 3 a02 19-OCT-24 30-OCT-24
I03 5 a03 14-JUL-24 01-AUG-24
I04 5 a04 15-SEP-24 30-SEP-24
I05 11 a05 19-OCT-24 21-OCT-24
I07 8 a07 15-AUG-24 30-AUG-24
I08 11 a05 01-OCT-24 15-OCT-24
I09 4 a01 12-SEP-24 19-SEP-24
I10 9 a02 01-DEC-24 15-DEC-24
I13 1 a10 24-AUG-24 28-AUG-24 500
I13 2 a11 21-AUG-24 30-AUG-24 700

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

--------------- ---------- ---------- --------- --------- ----------
I15 3 a17 17-AUG-24 30-AUG-24 900
I15 7 a11 21-AUG-24 30-AUG-25 1700
I16 6 a06 15-NOV-24 20-NOV-24 3000
I17 7 a08 04-OCT-24 20-OCT-24 5000

15 rows selected.
4) Create the table Movie with fields mv_no(number, Primary Key), title(Char),
Type(char), star(char), price(number), releasedate(date)

SQL> CREATE TABLE Movie (mv_no NUMBER(5) PRIMARY KEY,title

CHAR(20),Type CHAR(5),star CHAR(20),price NUMBER(5),releasedate DATE);

Table created.

SQL> DESC Movie ;

Name Null? Type
----------------------------------------- -------- ----------------------------
MV_NO NOT NULL NUMBER(5)
TITLE CHAR(20)
TYPE CHAR(5)
STAR CHAR(20)
PRICE NUMBER(5)
RELEASEDATE DATE

i) Add few records in above table

SQL> INSERT INTO Movie VALUES('100','SAIYYARA','LOVE','KRISH
KAPOOR','1000','15-Aug-25');

1 row created.

SQL> INSERT INTO Movie VALUES('101','BAGHI','LOVE','SHRADHA

KAPOOR','15000','2-Nov-25');

1 row created.
SQL> INSERT INTO Movie VALUES('102','DON','ACT','SRK','20000','2-
Nov-25');

1 row created.

SQL> INSERT INTO Movie VALUES('103','KESARI','ACT','NTR','2000','4-

Oct-25');

1 row created.

SQL> INSERT INTO Movie

VALUES('104','KRISH','ACT','HRITIK','25000','15-Nov-25');

1 row created.

SQL> SELECT * FROM Movie;

MV_NO TITLE TYPE STAR PRICE RELEASEDA
---------- -------------------- ----- -------------------- ---------- ---------
100 SAIYYARA LOVE KRISH KAPOOR 1000 15-AUG-25
101 BAGHI LOVE SHRADHA KAPOOR 15000 02-NOV-25
102 DON ACT SRK 20000 02-NOV-25
103 KESARI ACT NTR 2000 04-OCT-25
104 KRISH ACT HRITIK 25000 15-NOV-25

ii) Count total number of star

SQL> SELECT COUNT(star) FROM Movie;

COUNT(STAR)
-----------
5

iii) Calculate total price of all the movies.

SQL> SELECT SUM(price) FROM Movie;

SUM(PRICE)
----------
63000

iv) Calculate average price of all the movies.

SQL> SELECT AVG(price) FROM Movie;

AVG(PRICE)
----------
12600

v) Determine the maximum & minimum movies prices and rename the title as
MAX PRICE and MIN-PRICE.
SQL> SELECT MAX(price) FROM Movie;

MAX(PRICE)
----------
25000

SQL> SELECT MIN(price) FROM Movie;

MIN(PRICE)
----------
1000

SQL> UPDATE Movie SET title='MAX PRICE' WHERE price='25000';

1 row updated.

SQL> UPDATE Movie SET title='MIN-PRICE' WHERE price='1000';

1 row updated.

SQL> SELECT * FROM Movie;

MV_NO TITLE TYPE STAR PRICE RELEASEDA

---------- -------------------- ----- ------------------- ---------- ---------
100 MIN-PRICE LOVE KRISH KAPOOR 1000 15-AUG-25
101 BAGHI LOVE SHRADHA KAPOOR 15000 02-NOV-25
102 DON ACT SRK 20000 02-NOV-25
103 KESARI ACT NTR 2000 04-OCT-25
104 MAX PRICE ACT HRITIK 25000 15-NOV-25

vii) Count the number of movies having price greater than or equal to 150.
SQL> SELECT COUNT(title) FROM Movie WHERE price >=150;

COUNT(TITLE)
------------
5
viii) Display total number of movies released in the current year.

SQL> SELECT * FROM Movie WHERE releasedate >'1-Jan-25';

MV_NO TITLE TYPE STAR PRICE RELEASEDA

---------- -------------------- ----- -------------------- ---------- ---------
100 MIN-PRICE LOVE KRISH KAPOOR 1000 15-AUG-25
101 BAGHI LOVE SHRADHA KAPOOR 15000 02-NOV-25
102 DON ACT SRK 20000 02-NOV-25
103 KESARI ACT NTR 2000 04-OCT-25
104 MAX PRICE ACT HRITIK 25000 15-NOV-25

ix) Display all the movie which start with letter ‘K’.
SQL> SELECT * FROM Movie WHERE title LIKE 'K%';

MV_NO TITLE TYPE STAR PRICE RELEASEDA

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

103 KESARI ACT NTR 2000 04-OCT-25

5) i) Create a View of table Invoice with following column inv_no(char, Primary
key), mv_no(number), cust_id(char)
SQL> CREATE VIEW v_Invoice AS SELECT inv_no,mv_no,cust_id FROM
Invoice;

View created.

SQL> SELECT * FROM Invoice;

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

------------- ---------- ---------- --------- --------- ----------
I01 1 a01 22-DEC-24 25-DEC-24
102 3 a02 19-OCT-24 30-OCT-24
I03 5 a03 14-JUL-24 01-AUG-24
I04 5 a04 15-SEP-24 30-SEP-24
I05 11 a05 19-OCT-24 21-OCT-24
I07 8 a07 15-AUG-24 30-AUG-24
I08 11 a05 01-OCT-24 15-OCT-24
I09 4 a01 12-SEP-24 19-SEP-24
I10 9 a02 01-DEC-24 15-DEC-24
I13 1 a10 24-AUG-24 28-AUG-24 500
I13 2 a11 21-AUG-24 30-AUG-24 700

INV_NO MV_NO CUST_ID ISSUE_DAT RETURN_DA PRICE

------------ ---------- ---------- --------- --------- ----------
I15 3 a17 17-AUG-24 30-AUG-24 900
I15 7 a11 21-AUG-24 30-AUG-25 1700
I16 6 a06 15-NOV-24 20-NOV-24 3000
I17 7 a08 04-OCT-24 20-OCT-24 5000

15 rows selected.

iii) Insert and delete records

SQL> INSERT INTO v_Invoice VALUES('I18','3','a06');

1 row created.

SQL> INSERT INTO v_Invoice VALUES('I19','7','a08');

1 row created.

SQL> INSERT INTO v_Invoice VALUES('I20','9','a09');

1 row created.

SQL> SELECT * FROM v_Invoice;

INV_NO MV_NO CUST_ID
--------------- ---------- ----------
I01 1 a01
I02 3 a02
I03 5 a03
I04 5 a04
I05 11 a05
I18 3 a06
I07 8 a07
I08 11 a05
I09 4 a01
I10 9 a02
I19 7 a08

INV_NO MV_NO CUST_ID

--------------- ---------- ----------
I20 9 a09
I13 1 a10
I13 2 a11
I15 3 a17
I15 7 a11
I16 6 a06
I17 7 a08

18 rows selected.

SQL> DELETE FROM v_Invoice WHERE mv_no=11;

2 rows deleted.

SQL> SELECT * FROM v_Invoice;

INV_NO MV_NO CUST_ID

--------------- ---------- ----------
I01 1 a01
I02 3 a02
I03 5 a03
I04 5 a04
I18 3 a06
I07 8 a07
I09 4 a01
I10 9 a02
I19 7 a08
I20 9 a09
I13 1 a10

INV_NO MV_NO CUST_ID

--------------- ---------- ----------
I13 2 a11
I15 3 a17
I15 7 a11
I16 6 a06
I17 7 a08

16 rows selected.

iv) Drop view

SQL> DROP VIEW v_Invoice;

View dropped.

SQL> SELECT * FROM v_Invoice;

SELECT * FROM v_Invoice
*
ERROR at line 1:
ORA-00942: table or view does not exist