Writing Multiple SQL 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 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.

IMPORTANT NOTES

* DO NOT USE INNER JOIN

* FOR EACH QUERY USE THE FORMAT

SELECT

FROM

WHERE

* DO NOT POST THE SAME ANSWER AS THE OTHER POST.