Introduction to Database Design

You will be working with a set of tables for a cinema.

To access these tables in sql create the following synonyms:

SQL CREATE SYNONYM CINEMA FOR ARTHURA.CINEMA; SQL CREATE SYNONYM STAFF FOR ARTHURA.STAFF; SQL CREATE SYNONYM MOVIE FOR ARTHURA.MOVIE;

SQL CREATE SYNONYM SCREENING FOR ARTHURA.SCREENING;

You are to prepare 15 SQL query statements that will provide answers to the following 15 requests for information. You must submit the syntax of the queries themselves, not all the output (expecting 2 or 3 pages only).

1. A list of all screenings that screened on the 18th of April, 2015.

Display the number of seats that were in the cinema and the name of the movie.

2. A list of all screenings of PG movies during the month of January, 2015.

Display the screening date, the name of the movie and the name of the supervisor.

3. List all the screenings where Mario Baratto (staff id: 106) was the supervisor. Include the movie name, classification, camera operator name and the cinema size.

4. Display a list of each staff name and their supervisor's name.

5. Give a breakdown of how many screenings there have been for each screen size (using the screensize field in cinema).

6. Show the number of screenings that were scheduled for each month of 2015.

7. Create a view that shows a breakdown of movies by classification, ie. How many movies are ‘PG', how many are ‘M' etc.

8. Using the view created for query 7, write a query to display which classification has the lowest number of movies.

9. Create a view that gives a breakdown of the number of screenings there have been for each movie. Display the movie name as well as the movie number. You do not need to show movies that have never been screened.

10. Using the view in create for question 9, write a query to display which movie has been screened the most times.

11. List details of the oldest and youngest members of staff, show the full name of staff and their sex.

12. Show movies that have never been screened.

13. Show the number of screenings there have been for each cinema.

Include any cinemas that have had no screenings, if any.

14. List staff that have never worked as supervisors or camera operators.

15. Create your own query. It must include a nested query. Submit the following:

a. question your query is answering

b. the SQL query.