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.