34. Stocked up movies

easy

Instruction
  • Write a query to return the titles of movies with more than >7 dvd copies in the inventory.
  • The film titles are unique, i.e., no 2 films share the same titles.

Table 1: film

       col_name       |  col_type
----------------------+--------------------------
 film_id              | integer
 title                | text
 description          | text
 release_year         | integer
 language_id          | smallint
 original_language_id | smallint
 rental_duration      | smallint
 rental_rate          | numeric
 length               | smallint
 replacement_cost     | numeric
 rating               | text

Table 2: inventory

Each row is unique, inventoy_id is the primary key of this table.

   col_name   | col_type
--------------+--------------------------
 inventory_id | integer
 film_id      | smallint
 store_id     | smallint

Sample results

         title
------------------------
 ACADEMY DINOSAUR
 APACHE DIVINE

Expected results

Solution postgres

SELECT title
FROM film
WHERE film_id IN (
	SELECT 
	    film_id
	FROM inventory
	GROUP BY film_id
	HAVING COUNT(*) >=8
);
    

Explanation

This query is selecting the title of all films from the "film" table where the film_id is present in a subquery.

The subquery is selecting all film_ids from the "inventory" table where there are at least 8 records for that film_id.

In other words, the main query is returning the titles of films that have at least 8 copies available in the inventory.



More IN, BETWEEN, LIKE, CASE WHEN questions

ID Title Level FTPR
178 Members who worked at both Microsoft and Google linkedin medium
34%
177 Purchases by platform report amazon medium
10%
176 Employees' annual bonus amazon easy
12%
160 Sellers with no sales by day ebay hard
11%
156 Cancellation rate by unbanned users lyft hard
15%
155 Driver with the highest cancellation rate lyft easy
24%
151 Salary report dropbox easy
18%
138 Happy restaurants doordash easy
14%
136 Extremely late orders doordash easy
11%
131 Churned accounts affirm hard
10%
102 Histogram by visit session duration mobile hard
17%
35 Film length report easy
27%
33 Returning customers medium
8%
32 Unpopular movies hard
17%
31 Movies that have not been returned easy
26%
30 Inactive customers in May easy
22%
29 Second highest spend customer medium
14%
28 Film with the second largest cast medium
26%
27 Film with the largest cast easy
27%
26 Second shortest film easy
30%
25 Shortest film easy
46%
24 Films with more than 10 actors medium
19%
22 Average cost per rental transaction easy
42%
15 Fast movie watchers vs slow watchers hard
6%
14 Good days and bad days hard
4%
13 Actors' first name medium
14%
12 Actors' last name ending in 'EN' or 'RY' easy
27%
11 Actors' last name easy
21%