Use the following five tables to answer all questions. The primary keys are underlined. The foreign keys have the same name as the primary key in another table:
Play(PlayID, Title, Language, DateWritten, AuthorID)
Author(AuthorID, Name, DOB, Nationality)
Theater(TheaterID, TheaterName, City, State, SeatingCapacity)
Performance(TheaterID, PlayID, StartDate, EndDate, Performances, DirectorID)
Director(DirectorID, Name, DOB, Nationality, MentorID)
Assume that a MentorID must match an existing DirectorID. The other field names are self-explanatory.
1. Write CREATE TABLE commands for all five tables. Be careful to include primary and foreign key constraints. Any reasonable choice for the data type of a field is acceptable.
2. Write a command to delete all theaters in the state of Alabama.
3. Write a command to increase by 25% (multiply by 1.25) the SeatingCapacity of all theaters that are in the state of Vermont.
4. Write a command to output the TheaterName and SeatingCapacity for all theaters in the state of Maine.
5. Write a command to output the Title, Language, and DateWritten for all plays that have the word “night” in the Title.
6. Write a command to output the TheaterName for all theaters that are either in New York State with a seating capacity under 200, or that are in Vermont with a seating capacity under 100.
7. Write a command to output the average seating capacity of all theaters in Vermont.
8. Write a command to output the author name and the latest date that author has written a play (the maximum value of DateWritten).
9. Write a command to output the name of all authors who wrote a play in 1683.
10. Write a command to output the theater name, play title, start date, and end date for all plays written by William Shakespeare.
11. Write a command to print the name of each author and the number of plays that author has written.
12. Write a command to print the name of each author and the number of plays that author has written. Only include authors that have written at least 5 plays.
13. Write a command that will print the name, nationality, and date of birth for all directors and authors.
14. Write a command that will print the names of all directors and the name of their mentor.