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;