human
Schema:
actor (actor_id, first_name, last_name, last_update)
address (address_id, address, address2, district, city_id, postal_code, phone, last_update)
category (category_id, name, last_update)
city (city_id, city, country_id, last_update)
country (country_id, country, last_update)
customer (customer_id, store_id, first_name, last_name, email, address_id, active, create_date, last_update)
film (film_id, title, description, release_year, language_id, original_language_id, rental_duration, rental_rate, length, replacement_cost, rating, special_features, last_update)
film_actor (actor_id, film_id, last_update)
film_category (film_id, category_id, last_update)
film_text (film_id, title, description)
inventory (inventory_id, film_id, store_id, last_update)
language (language_id, name, last_update)
payment (payment_id, customer_id, staff_id, rental_id, amount, payment_date, last_update)
rental (rental_id, rental_date, inventory_id, customer_id, return_date, staff_id, last_update)
staff (staff_id, first_name, last_name, address_id, picture, email, store_id, active, username, password, last_update)
store (store_id, manager_staff_id, address_id, last_update)
Please answer with SQL.
assistant
Sure, I understand.
human
Which film has the most copies in the inventory? List both title and id.
assistant
SELECT T1.title , T1.film_id FROM film AS T1 JOIN inventory AS T2 ON T1.film_id = T2.film_id GROUP BY T1.film_id ORDER BY count(*) DESC LIMIT 1
human
How many languages are in these films?
assistant
SELECT count(DISTINCT language_id) FROM film
human
Find all the films longer than 100 minutes, or rated PG, except those who cost more than 200 for replacement. List the titles.
assistant
SELECT title FROM film WHERE LENGTH > 100 OR rating = 'PG' EXCEPT SELECT title FROM film WHERE replacement_cost > 200
human
What are all the movies rated as R? List the titles.
assistant
SELECT title FROM film WHERE rating = 'R'
human
Which film has the most number of actors or actresses? List the film name, film id and description.
assistant
SELECT T2.title , T2.film_id , T2.description FROM film_actor AS T1 JOIN film AS T2 ON T1.film_id = T2.film_id GROUP BY T2.film_id ORDER BY count(*) DESC LIMIT 1
human
How many films are there in each category? List the genre name, genre id and the count.
assistant
SELECT T2.name , T1.category_id , COUNT( * ) FROM film_category AS T1 JOIN category AS T2 ON T1.category_id = T2.category_id GROUP BY T1.category_id ;