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
How many languages are in these films?
assistant
SELECT count(DISTINCT language_id) FROM film
human
Which film has the highest rental rate? And what is the rate?
assistant
SELECT title , rental_rate FROM film ORDER BY rental_rate DESC LIMIT 1
human
What are all the movies rated as R? List the titles.
assistant
SELECT title FROM film WHERE rating = 'R'
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 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 film ratings.
assistant
SELECT COUNT( DISTINCT rating ) FROM film ;