SQL Movie Rating Database (CET 321 HW)

 

 SQL  Movie Rating Database 

(CET 321 HW)

The following article is about how to create a Movie Rating database.

Note: This is my CET 321 homework.

create database MovieRatingHW3

use MovieRatingHW3

create table tblDirector(

dID int primary key not null,

dName nvarchar(100) not null,

dateofBirth date not null,

)

insert into tblDirector values ('1','Frank Darabont','1959-01-28')

insert into tblDirector values ('2','Kartal Tibet','1939-03-27')

insert into tblDirector values ('3','Barry Sonnenfeld','1953-04-01')

create table tblReviewer(

rID int primary key not null,

rname nvarchar(100) not null,

gender char(1) not null,

)

insert into tblReviewer values ('101','Iperson','M')

insert into tblReviewer values ('102','Fperson','F')

insert into tblReviewer values ('104','Aperson','F')

insert into tblReviewer values ('103','AEperson','M')

 

UPDATE tblReviewer

SET rname = 'Zperson'

WHERE rID = 103;

create table tblMovie(

mID int primary key not null,

title nvarchar(100) not null,

Movieyear int not null,

coverimage image null,

)

insert into tblMovie(mID,title,Movieyear) values ('1','The Green Mile',1999)

insert into tblMovie(mID,title,Movieyear) values ('2','Tosun Paşa',1976)

insert into tblMovie(mID,title,Movieyear) values ('3','The Addams Family',1992)

create table tblMovieandDirector(

mID int foreign key (mID) references tblMovie(mID),

dID int foreign key (dID) references tblDirector(dID)

)

insert into tblMovieandDirector values ('1','1')

insert into tblMovieandDirector values ('2','2')

insert into tblMovieandDirector values ('3','3')

create table tblRatingAndReview(

rID int foreign key (rID) references tblReviewer(rID),

stars char(5) not null,

review nvarchar(100) not null,

ratingDate date not null,

)

insert into tblRatingAndReview  values ('101','5','Very good','2021-12-27')

insert into tblRatingAndReview  values ('102','4','Super','2021-03-27')

insert into tblRatingAndReview  values ('103','5','Super','2021-05-27')

DELETE FROM tblRatingAndReview WHERE rID='103';

create table tblRatingAndMovie(

mID int foreign key (mID) references tblMovie(mID),

rID int foreign key (rID) references tblReviewer(rID),

)

insert into tblRatingAndMovie  values ('1','101')

insert into tblRatingAndMovie  values ('2','102')

insert into tblRatingAndMovie  values ('3','103')

select * from tblMovie

select * from tblMovieandDirector

select * from tblDirector

select * from tblReviewer

select * from tblRatingAndReview

select * from tblRatingAndMovie

select * from tblReviewer where gender= 'F'

select title,dName from tblMovie, tblMovieandDirector, tblDirector where tblMovie.mID = tblMovieandDirector.mID and tblMovieandDirector.dID = tblDirector.dID

-- In the below, I wrote the 5b's code by using "inner join"

--select title,dName from tblMovie inner join tblMovieandDirector on (tblMovie.mID = tblMovieandDirector.mID) inner join tblDirector on (tblMovieandDirector.dID = tblDirector.dID)

select title,stars from tblMovie, tblRatingAndMovie, tblRatingAndReview where tblMovie.mID = tblRatingAndMovie.mID and tblRatingAndMovie.rID = tblRatingAndReview.rID ORDER BY tblRatingAndReview.stars DESC