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
What are the first names of customers who have not rented any films after '2005-08-23 02:06:01'?
assistant
SELECT first_name FROM customer WHERE customer_id NOT IN( SELECT customer_id FROM rental WHERE rental_date > '2005-08-23 02:06:01' )
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
What is the film title and inventory id of the item in the inventory which was rented most frequently?
assistant
SELECT T1.title , T2.inventory_id FROM film AS T1 JOIN inventory AS T2 ON T1.film_id = T2.film_id JOIN rental AS T3 ON T2.inventory_id = T3.inventory_id GROUP BY T2.inventory_id ORDER BY count(*) DESC LIMIT 1
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 the title and rental rate of the film with the highest rental rate?
assistant
SELECT title , rental_rate FROM film ORDER BY rental_rate DESC LIMIT 1 ;