Using MySQL and the included schema answer the following using queries with join

Question

Using MySQL and the included schema answer the following using queries with joins.

#1 Find all films with maximum length or minimum rental duration (compared to all other films).
#In other words let L be the maximum film length, and let R be the minimum rental duration in the table film. You need to find all films that have length L or duration R or both length L and duration R.
#You just need to return attribute film id for this query.

#2 We want to find out how many of each category of film ED CHASE has started in so return a table with category.name and the count
#of the number of films that ED was in which were in that category order by the category name ascending (Your query should return every category even if ED has been in no films in that category).

#3 Find the first name, last name and total combined film length of Sci-Fi films for every actor
#That is the result should list the names of all of the actors(even if an actor has not been in any Sci-Fi films)and the total length of Sci-Fi films they have been in.

#4 Find the first name and last name of all actors who have never been in a Sci-Fi film

#5 Find the film title of all films which feature both KIRSTEN PALTROW and WARREN NOLTE
#Order the results by title, descending (use ORDER BY title DESC at the end of the query)
#Warning, this is a tricky one and while the syntax is all things you know, you have to think oustide
#the box a bit to figure out how to get a table that shows pairs of actors in movies

MySOL Inwentory n SMALLINT category d Ny.. latupdate TIME.. title VARCHAR[253) language_id INYDU none CHAR (2U) rental-rate UKIMA- length 1ALLINI Indexe Indexes AHI UPLAIE upd film lastupdate AHI DeIE del fllm Movle datbane

Explanation / Answer

1.

SELECT film_id
FROM film
WHERE
rental_duration=(SELECT MIN(rental_duration) FROM film)
OR
length=(SELECT MAX(length) FROM film);

2.

SELECT category.category_id, category.name, COUNT(actor.actor_id)
FROM category
LEFT JOIN film_category ON category.category_id = film_category.category_id
LEFT JOIN film ON film_category.film_id = film.film_id
LEFT JOIN film_actor ON film.film_id = film_actor.film_id
LEFT JOIN actor ON film_actor.actor_id = actor.actor_id
AND actor.first_name = 'ED'
AND actor.last_name = 'CHASE'
GROUP BY category.category_id, category.name;

3.

SELECT actor.first_name, actor.last_name, SUM(film.length)FROM actor
INNER JOIN film_actor ON actor.actor_id = film_actor.actor_id
INNER JOIN film ON film_actor.film_id = film.film_id
INNER JOIN film_category ON film.film_id = film_category.film_id
INNER JOIN category ON film_category.category_id = category.category_id
WHERE category.name='Sci-Fi'
GROUP BY actor.first_name;

4.

SELECT actor.first_name, actor.last_name FROM actor
WHERE actor.actor_id NOT IN
(SELECT DISTINCT actor.actor_id FROM actor
INNER JOIN film_actor ON film_actor.actor_id = actor.actor_id
INNER JOIN film_category ON film_category.film_id = film_actor.film_id
INNER JOIN category ON category.category_id = film_category.category_id
WHERE category.name = 'Sci-Fi' );

or

SELECT actor.first_name, actor.last_name FROM actor
WHERE actor.actor_id NOT IN
(SELECT DISTINCT actor.actor_id FROM actor, film_actor, film_category,category
WHERE film_actor.actor_id = actor.actor_id
AND film_category.film_id = film_actor.film_id
AND category.category_id = film_category.category_id
AND category.name = 'Sci-Fi' );

5.

SELECT film.title FROM film
INNER JOIN film_actor ON film_actor.film_id = film.film_id
INNER JOIN actor ON actor.actor_id = film_actor.actor_id
WHERE actor.actor_id=(SELECT actor_id FROM actor
WHERE first_name='KIRSTEN' AND last_name='PALTROW')
|| actor.actor_id=(SELECT actor_id FROM actor
WHERE first_name='WARREN' AND last_name='NOLTE')
GROUP BY film.title having (count(*) >= 2)
ORDER BY film.title DESC;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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