SQL Queries For Modification In Database Assignment Sample

Introduction

Get free samples written by our Top-Notch subject experts for taking online Assignment Help services.

Dropping Tables ...
Create Tables ...
... A2_CINEMA
... A2_STAFF
... A2_CINEMA (FK Manager added)
... A2_SCREEN
... A2_FILM
... A2_SHOWING
... A2_PERFORMANCE

All tables created.

Populate Tables ...
... A2_Cinema
... A2_Staff
... A2_Film
... A2_Screen
... A2_Showing
... A2_Performance

All tables populated.

PL/SQL procedure successfully completed.

1 - Create a yourself as a member of staff which includes your name and where the Employee_No is your SRN.

The remaining attributes can be data you make up. There should be no duplication between your SRN and the data in the dataset, but if this does occur, add 1 to your SRN. You can choose which cinema you are employed at unsupervised! [ 4 marks ]

Ans-

INSERT INTO A2_Staff VALUES ( '19040856', 'Abdullah Al Fadhli', '42,Williumson street,Newcastle',01233456021, TO_DATE( '11/09/1992', 'DD/MM/YYYY'), TO_DATE( '15/06/2020', 'DD/MM/YYYY'), 14500, NULL, 'Marvale Rex');

2 - Create two showings at a cinema and screen of your choice from the database.
Showing 1 MUST contain a single performance of a new film you must also add to the film table.

Ans-

INSERT INTO A2_Film VALUES (12345678, 'The Shawshank Redemption', 'U', 120, 'Two imprisoned men bond over a number of years finding solace and eventual redemption through acts of common decency.', TO_DATE('1994', 'YYYY'));

INSERT INTO A2_Showing VALUES ('183566', 'Marvale Rex', 2, '12345678');
INSERT INTO A2_Showing VALUES ('183567', 'Masterton Multiplex', 5, '38874372');

INSERT INTO A2_Performance VALUES ('183566', TO_DATE ('27/11/2021', 'DD/MM/YYYY'), TO_DATE ( '18:30', 'HH24:MI'), 860, 174);

3 - Showing 2 MUST contain three performances of a film already in the database showing on consecutive days.

Ans-

INSERT INTO A2_Performance VALUES ('183567', TO_DATE ('25/12/2021', 'DD/MM/YYYY'), TO_DATE ( '11:00', 'HH24:MI'), 500, 130);
INSERT INTO A2_Performance VALUES ('183567', TO_DATE ('27/12/2021', 'DD/MM/YYYY'), TO_DATE ( '19:30', 'HH24:MI'), 1100, 170);
INSERT INTO A2_Performance VALUES ('183567', TO_DATE ('01/01/2022', 'DD/MM/YYYY'), TO_DATE ( '14:30', 'HH24:MI'), 1500, 174);

QUESTION 1: Produce a list of film names which have a date of release this century.

Select A2_Film.Film_Name, A2_Film.Year_Released from A2_Film
Where Year_Released > '01.01.00';

QUESTION 2: Use a nested select statement to provide the full name and address details of the cinemas managed by the staff called Claire Wilson and Coren O’Halloran.

(DO NOT manually lookup their Employee Numbers.)
SELECT Cinema_Name, Location FROM A2_Cinema WHERE Cinema_Name IN (SELECT Cinema FROM A2_Staff WHERE Name = 'Coren O''Halloran' or Name = 'Claire Wilson');

QUESTION 3: Lec Dombrovski has phoned in sick, Create a query to report the name and phone number of his supervisor.

SELECT Name as Supervisor_Name, phone_no FROM A2_Staff WHERE
Employee_No IN (SELECT Supervisor FROM A2_Staff WHERE Name = 'Lec Dombrovski');

QUESTION 4: Write a report of all films shown in August 2021 more than 16 times, give the films name, how many performances of these films there were and how much those films took in total over that period. List the films by the film that took the most money first, and provide meaningful headings to the columns in the output as shown in the Solution Test below.

QUESTION 5-- Report how much each cinema took on the 12th August, the report should include the name of the cinema and the value of the takings in the report.

Order by highest takings first. The output should also be formatted as shown below.
SELECT A2_Showing.Cinema, Concat('£', Sum(A2_Performance.Takings)) as Takings_on_Aug_12 From
A2_Showing, A2_Performance
Where A2_Showing.Showing_No = A2_Performance.Showing_No
And A2_Performance.Performance_Date = '12-08-21'
GROUP By a2_showing.cinema
ORDER By SUM(A2_Performance.Takings) DESC;

QUESTION 6-- List the age in years of the oldest employee at each cinema. Order the report by the cinema with the most employees first. Output should be formatted as below.

-- Hint: Use the examples in the SQL Sessions to determine the age in years of staff.
SELECT Cinema, Count(Employee_No) as Most_Employee From A2_Staff
Group by Cinema
Order by Most_Employee Desc;
SELECT (extract(year from current_date)-extract(year from DoB)) as Age
from A2_Staff
Order by Age Desc;

QUESTION 7-- Which were the showings with the most performances? In which cinema were, they shown, on which screen and how many performances were there starting on which date. Format the output as given below:

Select a2_showing.Cinema, A2_Showing.Screen, A2_performance.performance_date as from A2_performance, A2_Showing
Where A2_performance.Showing_No = A2_Showing.Showing_No
And a2_performance.performance_date = '13-08-21';

QUESTION 8: Produce a report for all showings of "Casablanca", providing the film name, in which cinema each showing took place and the takings per seat available and takings per person attending.

Select A2_Film.Film_Name, A2_Showing.Cinema, A2_Performance.Takings
From A2_Film, A2_Showing, A2_Performance
Where A2_Showing.Showing_No = a2_performance.showing_no
And A2_Film.Film_No = A2_Showing.Film_No
And A2_Film.Film_Name = 'Casablanca';

QUESTION 9: Write a query to list the cinema names of all cinemas which employ more than 12 employees

Select Cinema, count(Employee_No) from A2_Staff
Group by Cinema
Having Count(Employee_No)> 12;

Question 10:
Select * from A2_Staff
Where Employee_No= 19040856;

Question 11:
Select * from A2_cinema;

Question 12:
Select * From A2_performance;