Consider the customer - movie database
1. Customer Table
Name Null? Type
CUST_ID NOT NULL VARCHAR2(3)
LAST_NAME VARCHAR2(15)
FNAME VARCHAR2(15)
AREA VARCHAR2(3)
PNONE_NO NUMBER(8)
2. movie table
Name Null? Type
MVE_NO NOT NULL NUMBER(3)
TITLE VARCHAR2(25)
TYPE VARCHAR2(10)
STAR VARCHAR2(25)
PRICE NUMBER(5,2)
3. invoice table
Name Null? Type
INV_NO NOT NULL VARCHAR2(3)
MVE_NO NUMBER(3)
CUST_ID VARCHAR2(3)
ISSUE_DATE DATE
RETURN_DATE DATE
Create Customer table
CREATE TABLE MCUSTOMER
(
CUSTID NUMBER(3) PRIMARY KEY,
LASTNAME VARCHAR2(15),
FIRSTNAME VARCHAR2(15),
AREA VARCHAR2(3),
PHONE NUMBER(8)
);
Create movie table
CREATE TABLE MOVIE
(
MVENO NUMBER(3) PRIMARY KEY,
TITLE VARCHAR2(25),
TYPE VARCHAR2(10),
STAR VARCHAR2(25),
PRICE NUMBER(5,2)
);
Create invoice table
CREATE TABLE INVOICE
(
INVNO NUMBER(3),
MVENO NUMBER(3),
CUSTID NUMBER(3),
IDATE DATE,
RDATE DATE,
PRIMARY KEY (INVNO,MVENO,CUSTID)
);
Description / definition of customer table
DESC MCUSTOMER;
Output :
Name Null? Type
CUSTID NOT NULL VARCHAR2(3)
LASTNAME VARCHAR2(15)
FIRSTNAME VARCHAR2(15)
AREA VARCHAR2(3)
PHONE NUMBER(8)
Description / definition of customer table
DESC MOVIE;
Output :
Name Null? Type
MVENO NOT NULL NUMBER(3)
TITLE VARCHAR2(25)
TYPE VARCHAR2(10)
STAR VARCHAR2(25)
PRICE NUMBER(5,2)
Description / definition of invoice table
DESC INVOICE;
Output :
Name Null? Type
INVNO NOT NULL VARCHAR2(3)
MVENO NOT NULL NUMBER(3)
CUSTID NOT NULL VARCHAR2(3)
IDATE DATE
RDATE DATE
Insert statements
INSERT STATEMENT FOR CUSTOMER TABLE
INSERT INTO MCUSTOMER VALUES
(1,'DERASARI','DARSHAN','HMT',231182);
INSERT INTO MCUSTOMER VALUES
(2,'PATEL','TUSHAR','BOMBAY',233964);
INSERT INTO MCUSTOMER VALUES
(3,'SHAH','MEHUL','AHMD',235862);
INSERT INTO MCUSTOMER VALUES
(4,'DOSHI','NIRMAL','DELHI',233879);
INSERT INTO MCUSTOMER VALUES
(5,'CHECHANI','LOKESH','BOMBAY',231547);
INSERT INTO MCUSTOMER VALUES
(6,'SOMANI','PRITESH','AHMD',233504);
INSERT INTO MCUSTOMER VALUES
(7,'MODI','BHAVESH','AHMD',27550507);
INSERT INTO MCUSTOMER
(CUSTID,LASTNAME,FIRSTNAME,AREA)
VALUES (9,'DHANANI','DHAVAL','DELHI');
INSERT STATEMENT FOR MOVIE TABLE
INSERT INTO MOVIE (MVENO,TITLE,TYPE,STAR,PRICE)
VALUES (1,'KAHO NA PYAR HAI','ROMANTIC','HRITHIK',160);
INSERT INTO MOVIE (MVENO,TITLE,TYPE,STAR,PRICE)
VALUES (2,'KHONNI PANJA','HORROR','MUKESH',200);
INSERT INTO MOVIE (MVENO,TITLE,TYPE,STAR,PRICE)
VALUES (3,'ANARI NO 1','COMEDY','GOVINDA',170);
INSERT INTO MOVIE (MVENO,TITLE,TYPE,STAR,PRICE)
VALUES (4,'DDL','ROMANTIC','SHAHARUK',190);
INSERT INTO MOVIE (MVENO,TITLE,TYPE,STAR,PRICE)
VALUES (5,'DIL TO PAGAL HAI','ROMANTIC','SHAHRUK',20);
INSERT INTO MOVIE (MVENO,TITLE,TYPE,STAR,PRICE)
VALUES (6,'CHALTE CHALTE','DRAMA','SHAHRUK',120);
INSERT INTO MOVIE (MVENO,TITLE,TYPE,STAR,PRICE)
VALUES (7,'MAIN HOON NA','ROMANTIC','HRITHIK',250);
INSERT INTO MOVIE (MVENO,TITLE,TYPE,STAR,PRICE)
VALUES (8,'PURANI HAVELI','HORROR','RAJESH',150);
INSERT INTO MOVIE (MVENO,TITLE,TYPE,STAR,PRICE)
VALUES (9,'MURDER','ROMANTIC','MALAIKA',90);
INSERT STATEMENT FOR INVOICE TABLE
INSERT INTO INVOICE (INVNO,MVENO,CUSTID,IDATE,RDATE)
VALUES(1,1,1,'15-MAR-2004','20-MAR-2004');
INSERT INTO INVOICE (INVNO,MVENO,CUSTID,IDATE,RDATE)
VALUES(2,2,1,'18-MAR-2004','22-MAR-2004');
INSERT INTO INVOICE (INVNO,MVENO,CUSTID,IDATE,RDATE)
VALUES(3,5,1,'20-APR-2004','25-APR-2004');
INSERT INTO INVOICE (INVNO,MVENO,CUSTID,IDATE,RDATE)
VALUES(4,3,2,'18-SEP-2004','22-SEP-2004');
INSERT INTO INVOICE (INVNO,MVENO,CUSTID,IDATE,RDATE)
VALUES(5,4,3,'18-AUG-2004','22-AUG-2004');
INSERT INTO INVOICE (INVNO,MVENO,CUSTID,IDATE,RDATE)
VALUES(6,6,4,'18-NOV-2004','22-NOV-2004');
INSERT INTO INVOICE (INVNO,MVENO,CUSTID,IDATE,RDATE)
VALUES(7,7,8,'18-FEB-2004','22-FEB-2004');
INSERT INTO INVOICE (INVNO,MVENO,CUSTID,IDATE,RDATE)
VALUES(8,8,8,'18-MAR-2004','22-MAR-2004');
INSERT INTO INVOICE (INVNO,MVENO,CUSTID,IDATE,RDATE)
VALUES(9,6,5,'18-JUN-2004','22-JUN-2004');
DATA OF THE TABLES
CUSTOMER TABLE
CUSTID LASTNAME FIRSTNAME AREA PHONE
1 DERASARI DARSHAN HMT 231182
2 PATEL TUSHAR BOMBAY 233964
3 SHAH MEHUL AHMD 235862
4 DOSHI NIRMAL DELHI 233879
5 CHECHANI LOKESH BOMBAY 231547
6 SOMANI PRITESH AHMD 233504
7 MODI BHAVESH AHMD 27550507
8 PATEL KRUNAL DELHI 25435471
MOVIE TABLE
MVENO TITLE TYPE STAR PRICE
1 KAHO NA PYAR HAI ROMANTIC HRITHIK 160
2 KHONNI PANJA HORROR MUKESH 200
3 ANARI NO 1 COMEDY GOVINDA 170
4 DDL ROMANTIC SHAHARUK 190
5 DIL TO PAGAL HAI ROMANTIC SHAHRUK 20
6 CHALTE CHALTE DRAMA SHAHRUK 120
7 MAIN HOON NA ROMANTIC HRITHIK 250
8 PURANI HAVELI HORROR RAJESH 150
9 MURDER ROMANTIC MALAIKA 90
INVOICE TABLE
INVNO MVENO CUSTID IDATE RDATE
1 1 1 15-MAR-04 20-MAR-04
2 2 1 18-MAR-04 22-MAR-04
3 5 1 20-MAR-04 25-MAR-04
4 3 2 18-MAR-04 22-MAR-04
5 4 3 18-MAR-04 22-MAR-04
6 6 4 18-MAR-04 22-MAR-04
7 7 8 18-MAR-04 22-MAR-04
8 8 8 18-MAR-04 22-MAR-04
9 6 5 18-MAR-04 22-MAR-04
Retrieve fname and area of all customers.
SELECT FIRSTNAME,AREA FROM MCUSTOMER;
Output :
FIRSTNAME AREA
DARSHAN HMT
TUSHAR BOMBAY
MEHUL AHMD
NIRMAL DELHI
LOKESH BOMBAY
PRITESH AHMD
BHAVESH AHMD
KRUNAL DELHI
List various movie type.
SELECT TYPE FROM MOVIE;
Output :
TYPE
ROMANTIC
HORROR
COMEDY
ROMANTIC
ROMANTIC
DRAMA
ROMANTIC
HORROR
ROMANTIC
Find name of customer having ‘b’ as a starting letter in their fnames.
SELECT * FROM MCUSTOMER WHERE FIRSTNAME LIKE 'B%';
Output :
CUSTID LASTNAME FIRSTNAME AREA PHONE
7 MODI BHAVESH AHMD 27550507
Find the name of customer having ‘D’ as a start letter in their last name.
SELECT * FROM MCUSTOMER WHERE LASTNAME LIKE 'D%';
Output :
CUSTID LASTNAME FIRSTNAME AREA PHONE
1 DERASARI DARSHAN HMT 231182
4 DOSHI NIRMAL DELHI 233879
Find the name of customer who has area ‘AHMD’
SELECT * FROM MCUSTOMER WHERE AREA = 'AHMD';
Output :
CUSTID LASTNAME FIRSTNAME AREA PHONE
3 SHAH MEHUL AHMD 235862
6 SOMANI PRITESH AHMD 233504
7 MODI BHAVESH AHMD 27550507’
Find the name of customer having ‘D’ as a starting letter and ‘N’ as a ending letter in their fnames.
SELECT * FROM MCUSTOMER WHERE FIRSTNAME LIKE 'D%N';
Output :
CUSTID LASTNAME FIRSTNAME AREA PHONE
1 DERASARI DARSHAN HMT 231182
Find name of customer having ‘n’ as any letter in their fnames.
SELECT * FROM MCUSTOMER WHERE FIRSTNAME LIKE '%N%';
Output :
CUSTID LASTNAME FIRSTNAME AREA PHONE
1 DERASARI DARSHAN HMT 231182
4 DOSHI NIRMAL DELHI 233879
8 PATEL KRUNAL DELHI 25435471
Display the customer who has no phone.
SELECT * FROM MCUSTOMER WHERE PHONE IS NULL;
Output :
CUSTID LASTNAME FIRSTNAME AREA PHONE
9 DHANANI DHAVAL DELHI
Display the information from the invoice of customers who have issued movie for the month of september.
SELECT * FROM INVOICE;
Output :
INVNO MVENO CUSTID IDATE RDATE
1 1 1 15-MAR-04 20-MAR-04
2 2 1 18-MAR-04 22-MAR-04
3 5 1 20-APR-04 25-APR-04
4 3 2 18-SEP-04 22-SEP-04
5 4 3 18-AUG-04 22-AUG-04
6 6 4 18-NOV-04 22-NOV-04
7 7 8 18-FEB-04 22-FEB-04
8 8 8 18-MAR-04 22-MAR-04
9 6 5 18-JUN-04 22-JUN-04
SELECT INVNO,MVENO,CUSTID FROM INVOICE WHERE
TO_CHAR(IDATE,'MON') = 'SEP';
Output :
INVNO MVENO CUSTID
4 3 2
Display the movie of the type ‘action’ and ‘comedy’
SELECT * FROM MOVIE WHERE TYPE = 'COMEDY' OR TYPE = 'ACTION';
Output :
MVENO TITLE TYPE STAR PRICE
3 ANARI NO 1 COMEDY GOVINDA 170
Find the movies whose price is greater than 160 and less than equal than 200.
SELECT * FROM MOVIE WHERE PRICE BETWEEN 160 AND 200;
Output :
MVENO TITLE TYPE STAR PRICE
1 KAHO NA PYAR HAI ROMANTIC HRITHIK 160
2 KHONNI PANJA HORROR MUKESH 200
3 ANARI NO 1 COMEDY GOVINDA 170
4 DDL ROMANTIC SHAHARUK 190
Find the movies whose price is greater than 160 and also find the new cost as original * 2.
SELECT MVENO,TITLE,TYPE,STAR,PRICE * 2 FROM MOVIE WHERE PRICE > 160;
Output :
MVENO TITLE TYPE STAR PRICE*2
2 KHONNI PANJA HORROR MUKESH 400
3 ANARI NO 1 COMEDY GOVINDA 340
4 DDL ROMANTIC SHAHARUK 380
7 MAIN HOON NA ROMANTIC HRITHIK 500
Rename the new field in the above query as new price.
SELECT MVENO,TITLE,TYPE,STAR, PRICE * 2 AS "NEW PRICE" FROM MOVIE WHERE PRICE > 160;
Output :
MVENO TITLE TYPE STAR NEW PRICE
2 KHONNI PANJA HORROR MUKESH 400
3 ANARI NO 1 COMEDY GOVINDA 340
4 DDL ROMANTIC SHAHARUK 380
7 MAIN HOON NA ROMANTIC HRITHIK 500
Display the movies in the sorted order of their titles
SELECT * FROM MOVIE ORDER BY TITLE;
Output :
MVENO TITLE TYPE STAR PRICE
3 ANARI NO 1 COMEDY GOVINDA 170
6 CHALTE CHALTE DRAMA SHAHRUK 120
4 DDL ROMANTIC SHAHARUK 190
5 DIL TO PAGAL HAI ROMANTIC SHAHRUK 20
1 KAHO NA PYAR HAI ROMANTIC HRITHIK 160
2 KHONNI PANJA HORROR MUKESH 200
7 MAIN HOON NA ROMANTIC HRITHIK 250
9 MURDER ROMANTIC MALAIKA 90
8 PURANI HAVELI HORROR RAJESH 150
Display the names and the type except horror .
SELECT * FROM MOVIE WHERE TYPE <> 'HORROR';
Output :
MVENO TITLE TYPE STAR PRICE
1 KAHO NA PYAR HAI ROMANTIC HRITHIK 160
3 ANARI NO 1 COMEDY GOVINDA 170
4 DDL ROMANTIC SHAHARUK 190
5 DIL TO PAGAL HAI ROMANTIC SHAHRUK 20
6 CHALTE CHALTE DRAMA SHAHRUK 120
7 MAIN HOON NA ROMANTIC HRITHIK 250
9 MURDER ROMANTIC MALAIKA 90
Calculate the square root of the price of the movies.
SELECT TITLE,SQRT(PRICE) FROM MOVIE;
Output :
TITLE SQRT(PRICE)
KAHO NA PYAR HAI 12.649111
KHONNI PANJA 14.142136
ANARI NO 1 13.038405
DDL 13.784049
DIL TO PAGAL HAI 4.472136
CHALTE CHALTE 10.954451
MAIN HOON NA 15.811388
PURANI HAVELI 12.247449
MURDER 9.486833
Divide the cost of the movie ‘ ‘ by the difference between its price and 10.
SELECT TITLE,(PRICE / (PRICE-10) ) AS "NEW PRICE" FROM MOVIE;
Output :
TITLE NEW PRICE
KAHO NA PYAR HAI 1.0666667
KHONNI PANJA 1.0526316
ANARI NO 1 1.0625
DDL 1.0555556
DIL TO PAGAL HAI 2
CHALTE CHALTE 1.0909091
MAIN HOON NA 1.0416667
PURANI HAVELI 1.0714286
MURDER 1.125
Display the information of the invoice for the customer with id < ‘A05’
SELECT * FROM INVOICE WHERE CUSTID < 5;
Output :
INVNO MVENO CUSTID IDATE RDATE
1 1 1 15-MAR-04 20-MAR-04
2 2 1 18-MAR-04 22-MAR-04
3 5 1 20-APR-04 25-APR-04
4 3 2 18-SEP-04 22-SEP-04
5 4 3 18-AUG-04 22-AUG-04
6 6 4 18-NOV-04 22-NOV-04
Calculate the total price of all the movies
SELECT SUM(PRICE) FROM MOVIE;
SUM(PRICE)
1350
Determine the max and the min movie price.
SELECT MAX(PRICE),MIN(PRICE) FROM MOVIE;
Output :
MAX(PRICE) MIN(PRICE)
250 20
Display the type and the average price of each movie.
SELECT TYPE ,AVG(PRICE) FROM MOVIE GROUP BY TYPE;
Output :
TYPE AVG(PRICE)
COMEDY 170
DRAMA 120
HORROR 175
ROMANTIC 142
Calculate the average price of each type that had max price.
SELECT TYPE ,AVG(PRICE) FROM MOVIE WHERE PRICE = (SELECT MAX(PRICE) FROM MOVIE) GROUP BY TYPE;
Output :
TYPE AVG(PRICE)
ROMANTIC 250
Calculate the type which has the max price.
SELECT TYPE FROM MOVIE WHERE PRICE = (SELECT MAX(PRICE) FROM MOVIE);
Output :
TYPE
ROMANTIC
Display the info containing max price for the type horror.
SELECT * FROM MOVIE WHERE PRICE = (SELECT MAX(PRICE) FROM MOVIE WHERE TYPE = 'HORROR');
Output :
MVENO TITLE TYPE STAR PRICE
2 KHONNI PANJA HORROR MUKESH 200
Find the name and movie of the customer who have issued a movie.
SELECT A.FIRSTNAME,B.TITLE FROM MCUSTOMER A,MOVIE B,INVOICE C WHERE A.CUSTID = C.CUSTID AND B.MVENO=C.MVENO;
Output :
FIRSTNAME TITLE
DARSHAN KAHO NA PYAR HAI
DARSHAN KHONNI PANJA
DARSHAN DIL TO PAGAL HAI
TUSHAR ANARI NO 1
MEHUL DDL
NIRMAL CHALTE CHALTE
KRUNAL MAIN HOON NA
KRUNAL PURANI HAVELI
LOKESH CHALTE CHALTE
Find out the title and the types of the movie that have issued to ‘krunal’.
SELECT B.TITLE,B.TYPE FROM MCUSTOMER A,MOVIE B,INVOICE C WHERE A.CUSTID = C.CUSTID AND B.MVENO = C.MVENO AND A.FIRSTNAME = 'KRUNAL';
Output :
TITLE TYPE
MAIN HOON NA ROMANTIC
PURANI HAVELI HORROR
Find the name of customer who have issued movie of the type ‘drama’
SELECT * FROM MCUSTOMER WHERE CUSTID IN
(SELECT CUSTID FROM INVOICE WHERE MVENO IN
(SELECT MVENO FROM MOVIE WHERE TYPE = 'DRAMA'));
Output :
CUSTID LASTNAME FIRSTNAME AREA PHONE
4 DOSHI NIRMAL DELHI 233879
5 CHECHANI LOKESH BOMBAY 231547
Display the info of the customer who have issued the max movie.
SELECT * FROM MCUSTOMER WHERE CUSTID IN
{
SELECT CUSTID FROM INVOICE GROUP BY (CUSTID)
HAVING COUNT (CUSTID) IN
(SELECT MAX(COUNT(CUSTID)) FROM INVOICE
GROUP BY CUSTID)
);
Output :
CUSTID LASTNAME FIRSTNAME AREA PHONE
1 DERASARI DARSHAN HMT 231182
Find out which customer have issued the movie no 4.
SELECT * FROM MCUSTOMER WHERE CUSTID IN (SELECT CUSTID FROM INVOICE WHERE MVENO =4);
Output :
CUSTID LASTNAME FIRSTNAME AREA PHONE
3 SHAH MEHUL AHMD 235862
Display the info of the customer have issued the movie for august
SELECT * FROM MCUSTOMER WHERE CUSTID IN (SELECT CUSTID FROM INVOICE WHERE TO_CHAR(IDATE,'MON') = 'AUG');
Output :
CUSTID LASTNAME FIRSTNAME AREA PHONE
3 SHAH MEHUL AHMD 235862
Find the name of movie that is issue to 'KRUNAL’ and ‘DARSHAN’
SELECT B.TITLE FROM MCUSTOMER A,MOVIE B,INVOICE C WHERE A.CUSTID = C.CUSTID AND B.MVENO = C.MVENO AND(A.FIRSTNAME = 'KRUNAL' OR A.FIRSTNAME = 'DARSHAN');
Output :
TITLE
KAHO NA PYAR HAI
KHONNI PANJA
DIL TO PAGAL HAI
MAIN HOON NA
PURANI HAVELI
Find out the type and the movie no that is issued to custid ‘1’ and ‘3’ Display information of the customer along with the movie no and name.
SELECT A.CUSTID,A.FIRSTNAME,B.MVENO,B.TYPE FROM MCUSTOMER A,MOVIE B,INVOICE C WHERE A.CUSTID = C.CUSTID AND B.MVENO = C.MVENO AND A.CUSTID IN (1,3);
Output :
CUSTID FIRSTNAME MVENO TYPE
3 MEHUL 4 ROMANTIC
1 DARSHAN 1 ROMANTIC
1 DARSHAN 2 HORROR
1 DARSHAN 5 ROMANTIC
Display the invoice no and day on which customer were issued movie.
SELECT TO_CHAR(IDATE,'DAY') AS DAY ,INVNO FROM MCUSTOMER A,INVOICE B,MOVIE C
WHERE A.CUSTID = B.CUSTID AND C.MVENO = B.MVENO;
Output :
DAY INVNO
MONDAY 1
THURSDAY 2
TUESDAY 3
SATURDAY 4
WEDNESDAY 5
THURSDAY 6
WEDNESDAY 7
THURSDAY 8
FRIDAY 9
Display the month for which customer are supposed to return the movie.
SELECT CUSTID,TO_CHAR(RDATE,'MON') AS MONTH FROM INVOICE;
Output :
CUSTID MON
1 MAR
1 MAR
1 APR
2 SEP
3 AUG
4 NOV
8 FEB
8 MAR
5 JUN
Display the date in the format dd-month-yy.
SELECT INVNO,TO_CHAR(RDATE,'DD-MON-YY') FROM INVOICE;
Output :
INVNO TO_CHAR(R
1 20-MAR-04
2 22-MAR-04
3 25-APR-04
4 22-SEP-04
5 22-AUG-04
6 22-NOV-04
7 22-FEB-04
8 22-MAR-04
9 22-JUN-04
Find the date ,for any days after the current date.
select sysdate+5 from dual;
Output :
SYSDATE+5
05-MAY-97
Find the no of days elapsed bet the current date and the return date of the movie for customer.
SELECT MVENO,TO_CHAR(RDATE-IDATE) FROM INVOICE;
Output :
MVENO TO_CHAR(RDATE-IDATE)
1 238
2 240
5 274
3 4
4 4
6 4
7 4
8 4
6 4
Change the issued date of customer id ‘a01’ to 26/07/03.
SELECT * FROM INVOICE;
Output :
INVNO MVENO CUSTID IDATE RDATE
1 1 1 15-MAR-04 20-MAR-04
2 2 1 18-MAR-04 22-MAR-04
3 5 1 20-APR-04 25-APR-04
4 3 2 18-SEP-04 22-SEP-04
5 4 3 18-AUG-04 22-AUG-04
6 6 4 18-NOV-04 22-NOV-04
7 7 8 18-FEB-04 22-FEB-04
8 8 8 18-MAR-04 22-MAR-04
9 6 5 18-JUN-04 22-JUN-04
UPDATE INVOICE SET IDATE = '26-JUL-2003' WHERE CUSTID= 1;
SELECT * FROM INVOICE;
Output :
INVNO MVENO CUSTID IDATE RDATE
1 1 1 26-JUL-03 20-MAR-04
2 2 1 26-JUL-03 22-MAR-04
3 5 1 26-JUL-03 25-APR-04
4 3 2 18-SEP-04 22-SEP-04
5 4 3 18-AUG-04 22-AUG-04
6 6 4 18-NOV-04 22-NOV-04
7 7 8 18-FEB-04 22-FEB-04
8 8 8 18-MAR-04 22-MAR-04
9 6 5 18-JUN-04 22-JUN-04