top of page

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.

Comments


REALCODE4YOU

Realcode4you is the one of the best website where you can get all computer science and mathematics related help, we are offering python project help, java project help, Machine learning project help, and other programming language help i.e., C, C++, Data Structure, PHP, ReactJs, NodeJs, React Native and also providing all databases related help.

Hire Us to get Instant help from realcode4you expert with an affordable price.

USEFUL LINKS

Discount

ADDRESS

Noida, Sector 63, India 201301

Follows Us!

  • Facebook
  • Twitter
  • Instagram
  • LinkedIn

OUR CLIENTS BELONGS TO

  • india
  • australia
  • canada
  • hong-kong
  • ireland
  • jordan
  • malaysia
  • new-zealand
  • oman
  • qatar
  • saudi-arabia
  • singapore
  • south-africa
  • uae
  • uk
  • usa

© 2023 IT Services provided by Realcode4you.com

bottom of page