In this blog, we will learn about the movie management system and how to create the database schema and how to perform queries on these schemas -
First creating the database :
DROP DATABASE mt_db;CREATE DATABASE mt_db;USE mt_db;Now we will be creating database tables
Creating Customer_master table
Create table CUSTOMER_MASTER
(
CUSTOMER_ID Varchar(10),
CUSTOMER_NAME Varchar(30) NOT NULL,
CONTACT_NO BIGINT(10),
CONTACT_ADD Varchar(20),
DATE_OF_REGISTRATION Date NOT NULL,
AGE Varchar(15)NOT NULL,
Constraint MT_cts1 PRIMARY KEY(CUSTOMER_ID)
);Creating LIBRARY_CARD_MASTER table
Create table LIBRARY_CARD_MASTER
(
CARD_ID Varchar(10),
DESCRIPTION Varchar(30) NOT NULL,
AMOUNT BIGINT(50),
NUMBER_OF_YEARS bigint(10) NOT NULL,
Constraint MT_cts2 PRIMARY KEY(CARD_ID)
);Creating MOVIES_MASTER table
Create table MOVIES_MASTER
(
MOVIE_ID Varchar(10),
MOVIE_NAME Varchar(50) NOT NULL,
RELEASE_DATE Varchar(30) NOT NULL,
LANGUAGE Varchar(30),
RATING int(2),
DURATION VARCHAR(10) NOT NULL,
MOVIE_TYPE Varchar(3),
MOVIE_CATEGORY VARCHAR(20) NOT NULL,
DIRECTOR VARCHAR(20) NOT NULL,
LEAD_ROLE_1 Varchar(3) NOT NULL,
LEAD_ROLE_2 VARCHAR(4) NOT NULL,
RENT_COST BIGINT(10),
Constraint MT_cts4 PRIMARY KEY(MOVIE_ID)
);Creating CUSTOMER_CARD_DETAILS table
Create table CUSTOMER_CARD_DETAILS
(
CUSTOMER_ID Varchar(10),
CARD_ID VARCHAR(10),
ISSUE_DATE DATE NOT NULL,
Constraint MT_cts3 PRIMARY KEY(CUSTOMER_ID),
Constraint MT_CTS41 FOREIGN KEY(CUSTOMER_ID) References CUSTOMER_MASTER(CUSTOMER_ID),
Constraint MT_CTS42 FOREIGN KEY(CARD_ID) References LIBRARY_CARD_MASTER(CARD_ID)
);Creating CUSTOMER_ISSUE_DETAILS table
Create table CUSTOMER_ISSUE_DETAILS
(
ISSUE_ID Varchar(10) NOT NULL,
CUSTOMER_ID Varchar(10) NOT NULL,
MOVIE_ID VARCHAR(10),
ISSUE_DATE Date NOT NULL,
RETURN_DATE Date NOT NULL,
ACTUAL_DATE_RETURN Date NOT NULL,
Constraint MT_cts5 PRIMARY KEY(ISSUE_ID),
Constraint MT_Mem FOREIGN KEY(CUSTOMER_ID) References CUSTOMER_MASTER(CUSTOMER_ID),
Constraint MT_Mem1 FOREIGN KEY(MOVIE_ID) References MOVIES_MASTER(MOVIE_ID)
);Now we have insert value into the tables
Insert value into the CUSTOMER_MASTER
Insert into CUSTOMER_MASTER Values('CUS001', 'AMIT', 9876543210,'ADD1', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS002', 'ABDHUL', 8765432109,'ADD2', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS003', 'GAYAN', 7654321098,'ADD3', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS004', 'RADHA', 6543210987,'ADD4', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS005', 'GURU', NULL,'ADD5', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS006', 'MOHAN', 4321098765,'ADD6', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS007', 'NAME7', 3210987654,'ADD7', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS008', 'NAME8', 2109876543,'ADD8', '2013-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS009', 'NAME9', NULL,'ADD9', '2013-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS010', 'NAM10', 9934567890,'ADD10', '2013-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS011', 'NAM11', 9875678910,'ADD11', '2013-02-12', '21');Insert value into the LIBRARY_CARD_MASTER
Insert into LIBRARY_CARD_MASTER Values('CR001', 'DES1', 200, 5);
Insert into LIBRARY_CARD_MASTER Values('CR002', 'DES2', 400, 9);
Insert into LIBRARY_CARD_MASTER Values('CR003', 'DES3', 600, 8);
Insert into LIBRARY_CARD_MASTER Values('CR004', 'DES4', 800, 7);
Insert into LIBRARY_CARD_MASTER Values('CR005', 'DES5', 1200, 6);Insert value into the MOVIES_MASTER
Insert into MOVIES_MASTER Values('MV001', 'DIEHARD', '2012-05-13','ENGLISH', 4 , '2HRS', 'U/A','ACTION','DIR1','L1','L2',100);
Insert into MOVIES_MASTER Values('MV002', 'THE MATRIX', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ACTION','DIR2','L1','L2',100);
Insert into MOVIES_MASTER Values('MV003', 'INCEPTION', '2012-05-13','ENGLISH', 4 , '2HRS', 'U/A','ACTION','DIR3','L1','L2',100);
Insert into MOVIES_MASTER Values('MV004', 'DARK KNIGHT', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ACTION','DIR4','L1','L2',100);
Insert into MOVIES_MASTER Values('MV005', 'OFFICE S', '2012-05-13','ENGLISH', 4 , '2HRS', 'U/A','COMEDY','DIR5','L1','L2',100);
Insert into MOVIES_MASTER Values('MV006', 'SHAWN OF DEAD', '2012-05-13','ENGLISH', 4 , '2HRS', 'U/A','COMEDY','DIR6','L1','L2',100);
Insert into MOVIES_MASTER Values('MV007', 'YOUNG FRANKEN', '2012-05-13','ENGLISH', 4 , '2HRS', 'U/A','COMEDY','DIR7','L1','L2',100);
Insert into MOVIES_MASTER Values('MV008', 'CAS', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ROMANCE','DIR8','L1','L2',100);
Insert into MOVIES_MASTER Values('MV009', 'GWW', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ROMANCE','DIR9','L1','L2',100);
Insert into MOVIES_MASTER Values('MV010', 'TITANIC', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ROMANCE','DIR10','L1','L2',100);
Insert into MOVIES_MASTER Values('MV011', 'THE NOTE BOOK', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ROMANCE','DIR11','L1','L2',100);Insert value into the CUSTOMER_CARD_DETAILS
Insert into CUSTOMER_CARD_DETAILS Values('CUS001', 'CR001', '2012-05-13');
Insert into CUSTOMER_CARD_DETAILS Values('CUS002', 'CR002', '2012-05-13');
Insert into CUSTOMER_CARD_DETAILS Values('CUS003', 'CR002', '2013-05-13');
Insert into CUSTOMER_CARD_DETAILS Values('CUS004', 'CR003', '2013-05-13');
Insert into CUSTOMER_CARD_DETAILS Values('CUS005', 'CR003', '2012-05-13');Insert value into the CUSTOMER_ISSUE_DETAILS
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS001', 'CUS001', 'MV001', '2012-05-13', '2012-05-13','2012-05-13');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS002', 'CUS001', 'MV001', '2012-05-01', '2012-05-16','2012-05-16');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS003', 'CUS002', 'MV004', '2012-05-02', '2012-05-06','2012-05-16');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS004', 'CUS002', 'MV004', '2012-04-03', '2012-04-16','2012-04-20');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS005', 'CUS002', 'MV009', '2012-04-04', '2012-04-16','2012-04-20');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS006', 'CUS003', 'MV002', '2012-03-30', '2012-04-15','2012-04-20');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS007', 'CUS003', 'MV003', '2012-04-20', '2012-05-05','2012-05-05');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS008', 'CUS003', 'MV005', '2012-04-21', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS009', 'CUS003', 'MV001', '2012-04-22', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS010', 'CUS003', 'MV009', '2012-04-22', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS011', 'CUS003', 'MV010', '2012-04-23', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS012', 'CUS003', 'MV010', '2012-04-24', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS013', 'CUS003', 'MV008', '2012-04-25', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS014', 'CUS004', 'MV007', '2012-04-26', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS015', 'CUS004', 'MV006', '2012-04-27', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS016', 'CUS004', 'MV006', '2012-04-28', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS017', 'CUS004', 'MV001', '2012-04-29', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS018', 'CUS010', 'MV008', '2012-04-24', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS019', 'CUS011', 'MV009', '2012-04-27', '2012-05-07','2012-05-25');After this, we will be writing queries to perform the given task, here in the below section different types of queries, which is most important for any developer or professionals:
Query#1
Count the members who have gold cards
Solution
select count(customer_id) from customer_card_details where card_id in
(select card_id from library_card_master where description='gold card');Query#2
Display the name of the member who issued movie and the count of the movies issued and display 0 for the member who has not issued any movie
Solution
select customer_name,count(movie_id) count from customer_issue_details a,customer_master b where a.customer_id=b.customer_id group by b.customer_id union
select customer_name,0 as count from customer_master where customer_id not in (select customer_id from customer_issue_details);Query#3
Display the name of the person starting with letter 'r' and category is 'comedy'
Solution
select distinct a.customer_name from customer_master a,customer_issue_details b,movies_master c where a.customer_id=b.customer_id and b.movie_id=c.movie_id and
c.movie_category='comedy' and a.customer_name like 'r%';Query#4
Display id, name & total rent of customers for movie issued
Solution
select a.customer_id,customer_name,count(a.movie_id)*c.rent_cost rent from customer_issue_details a,customer_master b ,(select movie_id,rent_cost from movies_master) c
where
a.customer_id=b.customer_id and a.movie_id=c.movie_id group by b.customer_id;Query#5
Display id,name,card id,amount in $(amount/54.42) upto 0 decimals
Solution
select a.customer_id,customer_name,b.card_id,round(amount/54.42)
amount from customer_master a,customer_card_details b,library_card_master c
where
a.customer_id=b.customer_id and b.card_id=c.card_id;Query#6
Display id, name of customers who don't have a library card but still have issued the movie
Solution
select distinct b.customer_id,customer_name from customer_issue_details a,customer_master b
where
a.customer_id=b.customer_id and a.customer_id not in (select customer_id from customer_card_details);Query#7
Display the no.of customers with first letter 'r' and have paid fine i.e actual return date is greater than the return date
Solution
select count(b.customer_id) count from (select customer_id from customer_issue_details
where return_date>actual_date_return group by customer_id) b,customer_master c
where
b.customer_id=c.customer_id and c.customer_name like 'r%';Query#8
Display customer name, customer id who have issued max and min no.of movies issued
movies issued
Solution
select customer_name,a.customer_id from customer_issue_details a,customer_master b
where
a.customer_id=b.customer_id group by a.customer_id having count(a.movie_id)=(select min(a.count) min from (select customer_id,count(movie_id) count from customer_issue_details group by customer_id) a) or count(a.movie_id)=(select max(b.count) min from (select customer_id,count(movie_id) count from customer_issue_details group by customer_id) b);For more other queries which are want to ask related to movie Shema or other schemas, you can free to ask.