Movie Theatres SQL Server Case Study

Scenario :

In Movie Theatres case study,we will build a database step-by-step for a fictional movie theater. This theater needs to keep track of ticket sales, concession sales, and their own special loyalty program.

 

Relational Schema :

schema

 

 

 

 

 

 

 

 

 

 

Table creation code :

CREATE TABLE Movies (
   Code int PRIMARY KEY NOT NULL,
   Title nvarchar(50) NOT NULL,
   Rating nvarchar(50) 
 );
  
 CREATE TABLE MovieTheaters (
   Code int PRIMARY KEY NOT NULL,
   Name nvarchar(50) NOT NULL,
   Movie int  
     CONSTRAINT fk_Movies_Code REFERENCES Movies(Code)
 );

Sample Dataset :

 

 INSERT INTO Movies(Code,Title,Rating) VALUES(9,'Citizen King','G');
 INSERT INTO Movies(Code,Title,Rating) VALUES(1,'Citizen Kane','PG');
 INSERT INTO Movies(Code,Title,Rating) VALUES(2,'Singin'' in the Rain','G');
 INSERT INTO Movies(Code,Title,Rating) VALUES(3,'The Wizard of Oz','G');
 INSERT INTO Movies(Code,Title,Rating) VALUES(4,'The Quiet Man',NULL);
 INSERT INTO Movies(Code,Title,Rating) VALUES(5,'North by Northwest',NULL);
 INSERT INTO Movies(Code,Title,Rating) VALUES(6,'The Last Tango in Paris','NC-17');
 INSERT INTO Movies(Code,Title,Rating) VALUES(7,'Some Like it Hot','PG-13');
 INSERT INTO Movies(Code,Title,Rating) VALUES(8,'A Night at the Opera',NULL);
 
 INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(1,'Odeon',5);
 INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(2,'Imperial',1);
 INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(3,'Majestic',NULL);
 INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(4,'Royale',6);
 INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(5,'Paraiso',3);
 INSERT INTO MovieTheaters(Code,Name,Movie) VALUES(6,'Nickelodeon',NULL);

Exercises:

–1.Select the title of all movies.

select Title from Movies

–2.Show all the distinct ratings in the database.

select distinct(Rating) from Movies

–3.Show all unrated movies.

select * from Movies
where rating is null

–4.Select all movie theaters that are not currently showing a movie.

select Name from MovieTheaters
where Movie is null

–5.Select all data from all movie theaters and, additionally, the data from the movie that is being shown in the theater

select * from MovieTheaters
inner join Movies
on MovieTheaters.Movie=Movies.Code

–6.Select all data from all movies and, if that movie is being shown in a theater, show the data from the theater.

select * from MovieTheaters
right join Movies
on MovieTheaters.Movie=Movies.Code

–7.Show the titles of movies not currently being shown in any theaters.

select Movies.Title from Movies
right join MovieTheaters
on Movies.Code=MovieTheaters.Movie
where MovieTheaters.Movie is not null

–8.Add the unrated movie “One, Two, Three”.

insert into Movies(code,Title,Rating) values (10,’One,Two,Three’,NULL)

–9.Set the rating of all unrated movies to “G”.

update Movies
set Rating=’G’
where Rating is null

–10.Remove movie theaters projecting movies rated “NC-17”.

delete from MovieTheaters
where Movie in
(
select code from Movies
where rating=’NC-17′
)

Downloads :

Movie Theaters SQL Dataset and Commands

 

I hope this tutorial will surely help you. If you have any questions or problems please let me know.

Happy Hadooping with Patrick..

Leave a Reply

Your email address will not be published. Required fields are marked *