Using MySQL and sakila table, definitions, and download found here: https://dev.

Question

Using MySQL and sakila table, definitions, and download found here:

https://dev.mysql.com/doc/sakila/en

1. Using the Sakila schema, produce two queries that identify the films each actor has been in. List the film’s title, film’s release year, and actor’s last name Use implicit inner join syntax across three tables in the first query. Use two explicit inner join syntax in the second query.

Expected Result Set Size: 5462

2. Using the Sakila schema, select the rental_date and customer_id of the first rental made by each customer.

Expected Result Set Size: 599

3. Using the Sakila schema, return the number of rentals that were made between June 15, 2005 and June 18, 2005. Do not use the string comparison operation ‘like’ to compare dates. Hint: between

Expect count of 997

4. Using the Sakila schema, return the customer ID, first name, last name, and email of customers that have not returned their rentals Eliminate duplicate customers from the result set. Note the use of joins or nested selects is allowed in this question.

Expected Result Set Size: 159.

Explanation / Answer

1. SELECT a.title, a.release_year, c.last_name FROM `film` a inner JOIN `film_actor` b, `actor` c WHERE b.actor_id=c.actor_id and b.film_id=a.film_id;

2. SELECT rental_date, customer_id FROM `rental` GROUP BY customer_id ORDER BY `rental`.`rental_date` ASC

3. SELECT rental_date, customer_id FROM `rental` WHERE rental_date BETWEEN '2005-06-15' and '2005-06-18'

4. SELECT b.customer_id, b.first_name, b.last_name, b.email FROM `rental` a, customer b WHERE a.customer_id=b.customer_id and a.return_date is NULL GROUP BY a.customer_id;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.