SIT103 - Introduction to Database Design
Trimester 2 Assignment 1 2015
Background Due: 27th August 2015
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
The mark for this question will depend on the complexity of the query.
Plagiarism
Assignments that are found to be identical or extremely similar to others will be recorded as a fail. You are encouraged to work together and help each other but each student’s answers should be their own work.

Table Definitions
CINEMA
CINEMANO VARCHAR2(8)
NUMERICSEATS NUMERIC
SCREENSIZE CHAR(1)
MOVIE
MOVIENO VARCHAR2(8)
TITLE VARCHAR2(50)
LENGTH NUMERIC
CATEGORY VARCHAR2(20)
CLASSIFICATION CHAR(3)
YEAR NUMERIC
STUDIO VARCHAR2(20)
SCREENING
SCREENINGNO VARCHAR2(8)
CINEMANO VARCHAR2(8)
MOVIENO VARCHAR2(8)
SUPERVISORNO VARCHAR2(8)
CAMERAOPNO VARCHAR2(8)
HOURSTART NUMERIC
SCREENINGDATE DATE
STAFF
STAFFNO VARCHAR2(8)
SURNAME VARCHAR2(25)
GIVEN VARCHAR2(25)
SEX VARCHAR2(2)
DOB DATE
JOINED DATE
RESIGNED DATE
ADDRESS VARCHAR2(50)
SUBURB VARCHAR2(50)
POSTCODE VARCHAR2(6)
HOMEPHONE VARCHAR2(20)
WORKPHONE VARCHAR2(20)
RATEPERHOUR NUMERIC
SUPERVISORNO VARCHAR2(8)