PostgreSQL Assignment Help | Get Help In Database Designing And Querying Using Movie-rent Database

Requirement:

Creating Tables

CREATE TABLE customer(ID TEXT PRIMARY KEY NOT NULL,FNAME TEXT,ADDRESS CHAR(50),TELEPHONE TEXT,ACCOUNT_NO TEXT,LNAME TEXT);

CREATE TABLE movie(MOVIE_ID TEXT PRIMARY KEY NOT NULL,TOTAL_ACTOR INT,TOTAL_ACTRESS INT,TOTAL_DIRECTORS INT,TOTAL_ACADEMY_AWARDS INT,MOVIE_LENGTH INT,MOVIE_TYPE TEXT);

CREATE TABLE inventory(INVENTORY_ID TEXT PRIMARY KEY NOT NULL,DVD_ID TEXT,VIDEO_ID TEXT,DISTRIBUTOR_ID TEXT,TOTAL_DVD INT,TOTAL_VIDEO INT,MOV_ID TEXT REFERENCES movie(movie_id));

create table movie_details(MOVIE_LIST_NO INT PRIMARY KEY NOT NULL,ACTOR TEXT,ACTRESS TEXT,DIRECTOR TEXT,MOVIE_NO TEXT REFERENCES movie(movie_id));

create table movie_rent_video(RENT_VIDEO_LIST_NO TEXT PRIMARY KEY NOT NULL,VIDEO_ID TEXT,MOVIE_NAME TEXT,PRICE INT,RENT_DATE TIMESTAMP,RETURN_DATE TIMESTAMP,LATE_FEE INT,DAMAGED_FEE INT,TAX INT,REWIND_FEE INT,CUSTOMER_ID TEXT REFERENCES customer(id),MOV_ID TEXT REFERENCES movie(movie_id));

create table movie_rent_dvd(RENT_DVD_LIST_NO TEXT PRIMARY KEY NOT NULL,DVD_ID TEXT,MOVIE_NAME TEXT,PRICE INT,RENT_DATE TIMESTAMP,RETURN_DATE TIMESTAMP,LATE_FEE INT,DAMAGED_FEE INT,TAX INT,REWIND_FEE INT,CUSTOMER_ID TEXT REFERENCES customer(id),MOV_ID TEXT REFERENCES movie(movie_id));

create table distributor(ID TEXT PRIMARY KEY NOT NULL, DVD_ID TEXT,VIDEO_ID TEXT,VIDEO_QUANTITY INT,DVD_QUANTITY INT,VIDEO_PRICE INT,DVD_PRICE INT,CATALOGUE TEXT,MOV_ID TEXT REFERENCES movie(movie_id));

Insert Data Into Tables

INSERT INTO "customer"("id","name","address","telephone","account_no") VALUES('C01','ANKIT','Kali Badi 211003','8247666541','AB300001CD');

INSERT INTO "customer"("id","name","address","telephone","account_no") VALUES('C02','Alok','Mamura 506009','8247626541','AB300002CD');

INSERT INTO "customer"("id","name","address","telephone","account_no") VALUES('C03','Abhi','Noida 506109','8247626521','AB300003CD');

INSERT INTO "customer"("id","name","address","telephone","account_no") VALUES('C04','Abhijeet','Gurgaon 706109','9247626521','AB300004CD');

INSERT INTO "customer"("id","name","address","telephone","account_no") VALUES('C05','Abhishek','Delhi 206109','9227626521','AB300005CD');

INSERT INTO "customer"("id","name","address","telephone","account_no") VALUES('C06','Sanket','Saket 206108','9227626522','AB300006CD');

INSERT INTO "customer"("id","name","address","telephone","account_no") VALUES('C07','sushil','Rohini 606108','9227526522','AB300007CD');

INSERT INTO "customer"("id","name","address","telephone","account_no") VALUES('C08','agam','AshokNagar 3456721','9247526522','AB300008CD');

INSERT INTO "movie"("movie_id","total_actor","total_actress","total_directors","total_academy_awards","movie_length","movie_type") VALUES('MOV01',5,3,2,2,200,'COMIC');

INSERT INTO "movie"("movie_id","total_actor","total_actress","total_directors","total_academy_awards","movie_length","movie_type") VALUES('MOV02',2,8,1,3,150,'SUSPENSE');

INSERT INTO "movie"("movie_id","total_actor","total_actress","total_directors","total_academy_awards","movie_length","movie_type") VALUES('MOV03',4,4,2,10,170,'MYSTERY');

INSERT INTO "movie"("movie_id","total_actor","total_actress","total_directors","total_academy_awards","movie_length","movie_type") VALUES('MOV04',25,10,1,1,200,'HORROR');

INSERT INTO "movie"("movie_id","total_actor","total_actress","total_directors","total_academy_awards","movie_length","movie_type") VALUES('MOV05',6,12,3,2,220,'SCIENCE');

INSERT INTO "inventory"("inventory_id","dvd_id","video_id","distributor_id","total_dvd","total_video","mov_id") VALUES('IN01','DV01','VI01','DI01',2000,1200,'MOV01');

INSERT INTO "inventory"("inventory_id","dvd_id","video_id","distributor_id","total_dvd","total_video","mov_id") VALUES('IN02','DV02','VI02','DI02',2100,1300,'MOV02');

INSERT INTO "inventory"("inventory_id","dvd_id","video_id","distributor_id","total_dvd","total_video","mov_id") VALUES('IN03','DV03','VI03','DI03',1900,1400,'MOV03');

INSERT INTO "inventory"("inventory_id","dvd_id","video_id","distributor_id","total_dvd","total_video","mov_id") VALUES('IN04','DV04','VI04','DI04',1800,1500,'MOV04');

INSERT INTO "inventory"("inventory_id","dvd_id","video_id","distributor_id","total_dvd","total_video","mov_id") VALUES('IN05','DV05','VI05','DI05',1500,1100,'MOV05');

INSERT INTO "distributor"("id","dvd_id","video_id","video_quantity","dvd_quantity","video_price","dvd_price","catalogue","mov_id") VALUES('DI01','DV01','VI01',1000,2000,100,200,'Hungama 2','MOV01');

INSERT INTO "distributor"("id","dvd_id","video_id","video_quantity","dvd_quantity","video_price","dvd_price","catalogue","mov_id") VALUES('DI02','DV02','VI02',2000,1500,150,300,'Kahani 2','MOV02');

INSERT INTO "distributor"("id","dvd_id","video_id","video_quantity","dvd_quantity","video_price","dvd_price","catalogue","mov_id") VALUES('DI03','DV03','VI03',200,500,50,100,'HatimTai','MOV03');

INSERT INTO "distributor"("id","dvd_id","video_id","video_quantity","dvd_quantity","video_price","dvd_price","catalogue","mov_id") VALUES('DI04','DV04','VI04',400,700,70,120,'Bhool Bhulaiya','MOV04');

INSERT INTO "distributor"("id","dvd_id","video_id","video_quantity","dvd_quantity","video_price","dvd_price","catalogue","mov_id") VALUES('DI05','DV05','VI05',500,900,70,140,'Mission Mangal','MOV05');

INSERT INTO "movie_details"("movie_list_no","actor","actress","director","movie_no") VALUES (1,'Sharukh','kajol','karan johar','MOV01');

INSERT INTO "movie_details"("movie_list_no","actor","actress","director","movie_no") VALUES (2,'Rampal','kajol','karan johar','MOV01');

INSERT INTO "movie_details"("movie_list_no","actor","actress","director","movie_no") VALUES (3,'Boman','Kriti ','karan johar','MOV01');

INSERT INTO "movie_details"("movie_list_no","actor","actress","director","movie_no") VALUES (4,'Rajpal','Kriti ','karan johar','MOV01');

INSERT INTO "movie_details"("movie_list_no","actor","actress","director","movie_no") VALUES (5,'Varun','Kriti ','karan johar','MOV01');

INSERT INTO "movie_rent_dvd"("rent_dvd_list_no","dvd_id","movie_name","price","rent_date","return_date","late_fee","damaged_fee","tax","rewind_fee","customer_id","mov_id") VALUES('1','DV01','Hungama 2','100','2020-01-01','2020-01-02',20,100,5,50,'C01','MOV01');

INSERT INTO "movie_rent_dvd"("rent_dvd_list_no","dvd_id","movie_name","price","rent_date","return_date","late_fee","damaged_fee","tax","rewind_fee","customer_id","mov_id") VALUES('2','DV01','Kahani 2','200','2020-01-02','2020-01-03',30,110,15,60,'C02','MOV02');

INSERT INTO "movie_rent_dvd"("rent_dvd_list_no","dvd_id","movie_name","price","rent_date","return_date","late_fee","damaged_fee","tax","rewind_fee","customer_id","mov_id") VALUES('3','DV03','HatimTai','300','2020-01-03','2020-01-04',10,90,25,70,'C03','MOV03');

INSERT INTO "movie_rent_dvd"("rent_dvd_list_no","dvd_id","movie_name","price","rent_date","return_date","late_fee","damaged_fee","tax","rewind_fee","customer_id","mov_id") VALUES('4','DV04','Bhool Bhulaiya','250','2020-01-04','2020-01-05',12,80,22,80,'C04','MOV04');

INSERT INTO "movie_rent_dvd"("rent_dvd_list_no","dvd_id","movie_name","price","rent_date","return_date","late_fee","damaged_fee","tax","rewind_fee","customer_id","mov_id") VALUES('5','DV05','Mission Mangal','150','2020-01-05','2020-01-06',18,81,20,85,'C05','MOV05');

INSERT INTO "movie_rent_video"("rent_video_list_no","video_id","movie_name","price","rent_date","return_date","late_fee","damaged_fee","tax","rewind_fee","customer_id","mov_id") VALUES('1','VI05','Mission Mangal','50','2020-01-06','2020-01-07',28,91,30,95,'C01','MOV05');

INSERT INTO "movie_rent_video"("rent_video_list_no","video_id","movie_name","price","rent_date","return_date","late_fee","damaged_fee","tax","rewind_fee","customer_id","mov_id") VALUES('2','VI04','Bhool Bhulaiya','150','2020-01-07','2020-01-08',32,70,42,60,'C02','MOV04');

INSERT INTO "movie_rent_video"("rent_video_list_no","video_id","movie_name","price","rent_date","return_date","late_fee","damaged_fee","tax","rewind_fee","customer_id","mov_id") VALUES('3','VI03','HatimTai','320','2020-01-08','2020-01-09',20,95,15,40,'C03','MOV03');

INSERT INTO "movie_rent_video"("rent_video_list_no","video_id","movie_name","price","rent_date","return_date","late_fee","damaged_fee","tax","rewind_fee","customer_id","mov_id") VALUES('4','VI02','Kahani 2','220','2020-01-09','2020-01-10',40,120,45,30,'C04','MOV02');

INSERT INTO "movie_rent_video"("rent_video_list_no","video_id","movie_name","price","rent_date","return_date","late_fee","damaged_fee","tax","rewind_fee","customer_id","mov_id") VALUES('5','VI01','Hungama 2','200','2020-01-10','2020-01-11',90,10,35,100,'C05','MOV01');

Testing Queries Over Database Tables

1) SELECT * FROM customer LIMIT 5;

SELECT * FROM movie LIMIT 5;

SELECT * FROM inventory LIMIT 5;

SELECT * FROM movie_details LIMIT 5;

SELECT * FROM movie_rent_video LIMIT 5;

SELECT * FROM movie_rent_dvd LIMIT 5;

SELECT * FROM distributor LIMIT 5;

2) SELECT fname,lname,account_no,address FROM customer ORDER BY account_no;

3) SELECT dvd_id FROM movie_rent_dvd WHERE rent_date >= current_timestamp -interval '30 day';

4) UPDATE customer SET LNAME ='Kumar' WHERE ID ='C02';

5) DELETE FROM customer WHERE ID='C01';

Contact Us!

Hire expert to get help in any other database related help like MySQL, Oracle, SQL Server, MongoDB, MariaDB and Others.

Send Your requirement details at realcode4you@gmail.com and get instant helo with an affordable price.