New entry about database Problem 2 Consider the following schema of a video-rent

Question

New entry about database

Problem 2

Consider the following schema of a video-rental enterprise:

Member (m-ID, m-Name, m-city, age);

Movie ( movie-Title, director-ID, movie-Year, movieGenre );

Director( director-ID, dir-Name, dir-city);

Rents( movie-Title, m-ID, dateBorrowed, dateReturned);

Write the respective SQL statement for processing each of the following queries:

Q1: What is the average age of the members in each city;

Q2: Retrieve the names of all the customers who are older than 25 years and have borrowed movies directed by Spielberg.

Q3: Find the total number of movies directed by each director who lives in New York.

Q4: Retrieve the names of the members from Chicago who have rented all the movies directed by Coppola after 1970.

Explanation / Answer

Answer for the Query as follows:
Table structure as follows:

CREATE TABLE Member(m-ID CHAR(10),
     m-Name CHAR(20),
     m-city CHAR(20),
     age NUMBER(*,0))
     
     
CREATE TABLE Movie( movie-Title CHAR(20),
     director-ID CHAR(10),
     movie-Year NUMBER(*,0),
     movieGenre CHAR(10))
     
CREATE TABLE Director (director-ID CHAR(10),
        dir-Name CHAR(20),
        dir-city CHAR(20))
       
CREATE TABLE Rents (movie-Title CHAR(20),
     m-ID CHAR(10),
     dateBorrowed DATE,
     dateReturned DATE)

Q1: What is the average age of the members in each city;

SELECT avg(age) FROM Member group by m-city

Q2: Retrieve the names of all the customers who are older than 25 years and have borrowed movies directed by Spielberg.

SELECT mem.m-Name from Member mem , Director dir where mem.age > 25 && dir.dir-Name='Spielberg'

Q3: Find the total number of movies directed by each director who lives in New York.

SELECT dir.dir-Name count(*) from Director dir , Movie movie where (SELECT FROM Movie mv where where mv.director-ID==dir.director-ID && dir.dir-city='New York')

Q4: Retrieve the names of the members from Chicago who have rented all the movies directed by Coppola after 1970.

SELECT mem.m-Name from Member member WHERE (SELECT from Movie mv, Director dir, Member mem,Rents rent WHERE (dir.dir-Name =='Coppola' && mem.m-city='Chicago' && rent.dateBorrowed > (1970-01-01)))

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects