Question

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.

Answer 1

Query 1:
Select distinct theaterID from TheaterSeats where brokenSeat='True';

Query 2:
Select distinct Movies.movieID,Movies.year from Tickets inner join Customers on Tickets.customerID=Customers.customerID inner join Showings on Showings.theaterID=Tickets.theaterID inner join Movies on Showings.movieID=Movies.movieID where Customers.name='Donald Duck'

Query 3:
Select * from Movies where length>(Select length from movies where year=2011) order by year;

Query 4:
Select Customers.customerID,Customers.name from Customers where customerID=(Select customerID from Tickets group by customerID having count(*)>=2) and customerID=(Select Customers.customerID from Customers where Customers.name like 'a%' or Customers.name like 'A%');

Query 5:

Select * From Tickets where customerID in(Select customerID from Customers where name like 'D%') and theaterID in(Select theaterID from Showings where priceCode is not null) and theaterID in(Select theaterID from Theaters where numSeats>5) and showingDate between '01-jun-2019' and '30-jun-2019';

if you still have any Problem regarding this question please comment and if you like my code please appreciate me by thumbs up thank you.........