SQL Exercises/Movie theatres

Relational Schema

Exercises

1. Select the title of all movies.

Click to see solution

 SELECT Title 
   FROM Movies;

2. Show all the distinct ratings in the database.

Click to see solution

 SELECT DISTINCT Rating 
   FROM Movies;

3. Show all unrated movies.

Click to see solution

 SELECT * 
   FROM Movies 
  WHERE Rating IS NULL;

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

Click to see solution

 SELECT * 
   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 (if one is being shown).

Click to see solution

 SELECT *
   FROM MovieTheaters 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.

Click to see solution

 SELECT *
   FROM MovieTheaters RIGHT JOIN Movies
   ON MovieTheaters.Movie = Movies.Code;

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

Click to see solution

 /* With JOIN */
 SELECT Movies.Title
   FROM MovieTheaters RIGHT JOIN Movies
   ON MovieTheaters.Movie = Movies.Code
   WHERE MovieTheaters.Movie IS NULL;

 /* With subquery */
 SELECT Title FROM Movies
   WHERE Code NOT IN
   (
     SELECT Movie FROM MovieTheaters
     WHERE Movie IS NOT NULL
   );

8. Add the unrated movie "One, Two, Three".

Click to see solution

 INSERT INTO Movies(Title,Rating) VALUES('One, Two, Three',NULL);

9. Set the rating of all unrated movies to "G".

Click to see solution

 UPDATE Movies SET Rating='G' WHERE Rating IS NULL;

10. Remove movie theaters projecting movies rated "NC-17".

Click to see solution

 DELETE FROM MovieTheaters WHERE Movie IN
   (SELECT Code FROM Movies WHERE Rating = 'NC-17');

Table creation code

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

Click to see MySQL syntax.

 CREATE TABLE Movies (
   Code INTEGER PRIMARY KEY,
   Title VARCHAR(255) NOT NULL,
   Rating VARCHAR(255) 
 );
 
 CREATE TABLE MovieTheaters (
   Code INTEGER PRIMARY KEY,
   Name VARCHAR(255) NOT NULL,
   Movie INTEGER,  
     FOREIGN KEY (Movie) REFERENCES Movies(Code)
 ) ENGINE=INNODB;

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);