# SQL Exercises/Movie theatres

## ExercisesEdit

Select the title of all movies.

Click to see solution
``` SELECT Title
FROM Movies;
```

Show all the distinct ratings in the database.

Click to see solution
``` SELECT DISTINCT Rating
FROM Movies;
```

Show all unrated movies.

Click to see solution
``` SELECT *
FROM Movies
WHERE Rating IS NULL;
```

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

Click to see solution
``` SELECT *
FROM MovieTheaters
WHERE Movie IS NULL;
```

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 LEFT JOIN Movies
ON MovieTheaters.Movie = Movies.Code;
```

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

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

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

Click to see solution
``` INSERT INTO Movies(Title,Rating) VALUES('One, Two, Three',NULL);
```

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

Click to see solution
``` UPDATE Movies SET Rating='G' WHERE Rating IS NULL;
```

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 codeEdit

``` 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 datasetEdit

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