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 movies have 'Deleted Scenes' as a substring in the special feature?
assistant
SELECT title FROM film WHERE special_features LIKE '%Deleted Scenes%'
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
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
Which film is rented at a fee of 0.99 and has less than 3 in the inventory? List the film title and id.
assistant
SELECT title , film_id FROM film WHERE rental_rate = 0.99 INTERSECT 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 HAVING count(*) < 3
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 ;