Database Systems  Assignment 5  SOLUTION

1. SELECT   title,   length   FROM   movie   WHERE   genre="comedy";     2. SELECT   CONCAT(firstName,"   ",   lastName)   AS   fullname,   city   FROM   customer    WHERE   (lastName   LIKE   "B%");     3. SELECT   COUNT(*)   AS   numReserved   FROM   reservation,   customer   WHERE    customer.accountId=   reservation.accountId   AND   customer.firstName="Jane"    AND   customer.lastName="Block";     4. SELECT   CONCAT(firstName,"   ",lastName)AS   name,   dateDue   FROM   rental,    customer   WHERE   customer.accountId=rental.accountId   AND    MONTHNAME(rental.dateRented)="January"   AND    YEAR(rental.dateRented)="2002";     5. SELECT   DISTINCT   movie.title   FROM   movie,   video,   previousrental,   customer    WHERE   movie.movieId=video.movieId   AND    customer.accountId=previousrental.accountId   AND    previousrental.videoId=video.videoId   AND   customer.state="FL";     6. SELECT   title,   videoId,   CONCAT(MONTHNAME(dateAcquired),"    ",DAY(dateAcquired),",",YEAR(dateAcquired))   AS   Date   FROM   movie   LEFT    JOIN   video   ON   movie.movieId=video.movieId;
Powered by