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
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
How many different last names do the actors and actresses have?
assistant
SELECT count(DISTINCT last_name) FROM actor
human
Which language does the film AIRPORT POLLOCK use? List the language name.
assistant
SELECT T2.name FROM film AS T1 JOIN LANGUAGE AS T2 ON T1.language_id = T2.language_id WHERE T1.title = 'AIRPORT POLLOCK'
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
human
Count the number of different languages in these films.
assistant
SELECT COUNT( DISTINCT language_id ) FROM film ;