SQL Ticket Exercise

CODE:

OffersTicket(event no,zonecat,zoneno,zonevenue,price,freeseats,commission,sales)

primary key (event no,zonecat,zoneno,zone venue)

foreign key (zone cat,zoneno,zone venue) references Zone(category,zoneno,venue)

foreign key event no references Event(event no)

QUESTION 1.

Write SQL code to define table OffersTicket according to the Relational Database Schema given above. Your code must execute without error assuming that all other tables have been set up by running script a2.sql.

• Price and Commission fee hold positive numbers with values like 23:99 or 59:00. The maximum price value is 999:99, the maximum commission fee is 99:99. Neither must be missing from rows.
• Free seats is a positive integer number that is greater than or equal 0 and smaller than 1; 000; 000.
• When an event is deleted from the database, all ticket offers for this event should be automatically deleted too. Similarly, when 2 the event number of an event is changed, all the entries in OffersTicketshould reflect this change automatically.
• It should not be possible to delete zone records or change their primary key values if there are tickets for this zone on offer for any event.
• Sales is an additional column which states how the tickets will be sold. The different ways of distribution are called online, shops, agents, and phone. Note that sales can be any combination of those at the same time, e.g. tickets for a zone may be offered both online and in shops.

Equip all FOREIGN KEY constraints with unique names.

QUESTION 2.

For each of the tasks specified below write one single SQL query, respectively, that solves the task. You must NOT use subqueries but you may use SELECT queries in the FROM clause. As column headings for result tables you must choose column names unless explicitly stated otherwise. It is important that your queries will work correctly with any data (according to the schema), not just the data as presented in file a2.sql.

• For event number 224 the date should be set to December 10th, 2011.
• List all events with a description that contains the word music but does neither contain Britain nor UK in the exact spelling given above. The result table must list all event attributes.
• List all future events scheduled within the next 10 days. The result table must contain event name, event date and the first 50 characters of the description. This last column should be headed What. The date in the result table should be formatted in the following way: , the ofan example being Thu, the 3rd of November 2011. The date column should have heading When. The result table needs to be listed chronologically (earliest event first).

• List all phone numbers for venues of type arena. The result table must contain venue name and phone number. Sort alphabetically by venue name.
• Compute the capacity of all venues according to the information in the Zone table. The result table should have three columns: postcode, name of venue and capacity. The last column should contain integers only and have heading total capacity. Sort the result table by capacity with the highest number listed first.
• List all those venues that have a capacity (according to the information in the Zone table) of 11,000 or more. The result table should have three columns: postcode & name of the venue and the venue’s capacity. The last column should contain integers only and have heading capacity. Order the result table by capacity with the highest number listed first.
• Find out how many zones (as given by table Zone) are not used as an alternative offer for another zone at the same venue. The result table thus will just contain a single number in a column with heading unused alternatives.
• Compute how many events there are on the database for the current year, the year before and the year before that, as well as the following year and the year after that. The result table should have two columns with headings Years and NumEvents. It should be ordered by year in chronological order. For instance, when run in the year 2011, the result table for the data as provided by a2.sql (before executing the answer to 2a) has to look like this: See image.