只需要CREATE TABLE 就可以,不用INSERT
Relational Schema
Production(Title,BasicTicketPrice)
Performance(PerfDate,PerfTime,Title)
Zone(Name,PriceMultiplier)
Seat(RowNumber,Zone)
Booking(Email,PerfDate,PerfTime,RowNumber)
Constraints
Not Null:
Production: BasicTicketPrice
Booking: Email
Zone: PriceMultiplier
Data
We have provided table creation and insert statements for Zone and Seat. Use these to create and populate the two tables.
Zone
Seat
You will need to define your own table creation and insert commands for the following data: Production
Title BasicTicketPrice
Cats 15.00
Fame 15.00
Tosca 30.00
Performance
PerfDate PerfTime Title
2017-11-01 19:00:00 Cats
2017-11-02 19:00:00 Cats
2017-11-03 19:00:00 Cats
2017-11-03 13:00:00 Cats
2017-11-04 19:00:00 Fame
2017-11-05 13:00:00 Fame
2017-11-05 19:00:00 Tosca
2017-11-06 13:00:00 Tosca
2017-11-06 19:00:00 Tosca
Booking
Email PerfDate PerfTime RowNumber
ZP@email.com 2017-11-01 19:00:00 Z18
ZP@email.com 2017-11-01 19:00:00 Z19
Jane.Dot@live.com 2017-11-01 19:00:00 Z16
Jane.Dot@live.com 2017-11-05 13:00:00 U20
Jane.Dot@live.com 2017-11-05 13:00:00 U19
Mike.Stand@email.com 2017-11-05 13:00:00 X13
Mike.Stand@email.com 2017-11-05 13:00:00 X14
qvf3@live.com 2017-11-05 13:00:00 Z19
What you have to do
Write mySQL CREATE TABLE commands for Production, Performance and Booking, ensuring that they contain the correct columns. The commands must include primary key definitions. Pick sensible data types for the columns. You must include foreign keys and ensure that other constraints are enforced. These constraints include those explicitly listed as well as those implied by the models.
Write mySQL insertion commands to populate the tables with the data given above. Only the given data should be inserted.
Create the following mySQL queries.
All performances of Cats in the order they occur. Output all columns of the Performance table.
Booking and performance details where the person booking has a email with domain "@email.com". For each such booking, output the Email, PerfDate, PerfTime and Title.
The prices paid for of all booked seats. Output the RowNumber, Email of person booking and calculated price.
All the free seats for the performance at 2017-11-01, 19:00:00. Output the RowNumber only.
Count of all the seats in each zone for zones where the PriceMultiplier is greater than 2.3. Output the zone Name, PriceMultiplier and the count.