SQLPad
Instruction
- Write a query to return the titles of movies with more than
>7dvd 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.
Copied
Your results