amount > 0; 7 Return the number of movies present in each category SELECT catego
ID: 3591343 • Letter: A
Question
amount > 0; 7 Return the number of movies present in each category SELECT category id, COUNT) FROM film. category GROUP BY film category.category id 8 Return the title of the movies that have a rental rate equal to the maximum rental rate for their category 9 Return a list of all the different first 3 numbers of a zipcode (something that can be used to determine the number of different cities, do not worry about the fact that they are in different countries) 10 Find the total revenue (the sum of all the rentals) for each store (characterized by store id) 11 Return a list of all the customers that performed more than 10 rentals 12 Using Union, Except, or Intersect provide the name of the customers that never performed a rental 13 Provide the titles of all the movies rented by Nicholas Barfield. 14 Provide a summary table using GROUPING SETS where the different movies are counted by rental ate, and by category (the first part must count how many movies exist with rental rate sg 4.99 and then how many movies exist for a category e.g Horror)
Explanation / Answer
8)
SELECT title
FROM film f, film_category fc
WHERE f.film_id = fc.film_id
AND rental_rate = (SELECT max(rental_rate)
FROM film, film_category
WHERE film.film_id = film_category.film_id
AND category_id = fc.category_id);
....
9)
SELECT DISTINCT SUBSTR(postal_code,1,3)
FROM address;
....
10)
SELECT sum(rental_rate*rental_duration)
FROM film f, inventory i, rental r, staff s, store st
WHERE f.film_id = i.film_id
AND i.inventor_id = r.inventory_id
AND r.staff_id = s.staff_id
AND s.store_id = st.store_id
GROUP BY st.store_id;
....
11)
SELECT customer_id
FROM rental
GROUP BY customer_id
HAVING count(rental_id)>=10;
....
12)
SELECT customer_id
FROM customer
EXCEPT SELECT customer_id FROM rental;
....
13)
SELECT DISTINCT title
FROM film f, inventory i, rental r, customer c
WHERE f.film_id = i.film_id
AND i.inventory_id = r.inventory_id
AND r.customer_id = c.customer_id
AND first_name = "Nicholas"
AND last_name = "Barfield" ;
....
14)
//Part1
SELECT rental_rate, count(*)
FROM film
GROUP BY rental_rate;
//Part2
SELECT name, count(*)
FROM film f, film_category fc, category c
WHERE f.film_id = fc.film_id
AND fc.category_id = c.category_id
GROUP BY name;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.