WRITING POSTGRESQL QUERIES

RELATIONS:

CREATE TABLE Movies( movieID INT, year INT UNIQUE, rating CHAR(1), length INT, totalEarned NUMERIC(7,2), PRIMARY KEY(movieID) );

CREATE TABLE Theaters( theaterID INT, address VARCHAR(40) UNIQUE, numSeats INT NOT NULL, PRIMARY KEY(theaterID) );

CREATE TABLE TheaterSeats( theaterID INT, seatNum INT, brokenSeat BOOLEAN NOT NULL, PRIMARY KEY(theaterID, seatNum), FOREIGN KEY(theaterID) REFERENCES Theaters );

CREATE TABLE Showings( theaterID INT, showingDate DATE, startTime TIME, movieID INT, priceCode CHAR(1), PRIMARY KEY(theaterID, showingDate, startTime), FOREIGN KEY(theaterID) REFERENCES Theaters, FOREIGN KEY(movieID) REFERENCES Movies );

CREATE TABLE Customers( customerID INT, name VARCHAR(30) UNIQUE, address VARCHAR(40) UNIQUE, joinDate DATE, status CHAR(1), PRIMARY KEY(customerID) );

CREATE TABLE Tickets( theaterID INT, seatNum INT, showingDate DATE, startTime TIME, customerID INT, ticketPrice NUMERIC(4,2), PRIMARY KEY(theaterID, seatNum, showingDate, startTime), FOREIGN KEY(customerID) REFERENCES Customers, FOREIGN KEY(theaterID, seatNum) REFERENCES TheaterSeats, FOREIGN KEY(theaterID, showingDate, startTime) REFERENCES Showings );

QUERIES TO WRITE:

Query 1 Find all the theaters that have a broken seat. No duplicates should appear in your answer.

Query 2 Find the name and year of all movies for which a customer named Donald Duck bought a ticket. No duplicates should appear in your answer.

Query 3 Find the ID, name, year and length for every movie which was longer than the 2011 movie Avengers. In your result, movies with the largest year should appear first; within each year, movies should be in alphabetized by name. No duplicates should appear in your answer.

Query 4 Find the ID and name of each customer whose name has the letter a or A anywhere in it, and who bought tickets to at least 2 different movies. Careful; a customer who bought 2 or more tickets to the same movie doesn't qualify. No duplicates should appear in your answer

Query 5 For each ticket for which all of the following are true:

a) the ticket was bought by a customer whose name starts with D (capital D),

b) the ticket is for a showing whose price code isn't NULL,

and c) the ticket is on a date between June 1, 2019 and June 30, 2019 (including those dates),

and d) the ticket is for a theater that has more than 5 seats,

Output the ID, name and address of the customer, the address and number of seats of the theater, and the price code for the showing. The 6 attributes in your result should appear as custID, custName, custAddress, theaterAddress, theaterSeats and priceCode. No duplicates should appear in your result.