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 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 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
How many different last names do the actors and actresses have?
assistant
SELECT count(DISTINCT last_name) FROM actor
human
What is the first name and the last name of the customer who made the earliest rental?
assistant
SELECT T1.first_name , T1.last_name FROM customer AS T1 JOIN rental AS T2 ON T1.customer_id = T2.customer_id ORDER BY T2.rental_date ASC LIMIT 1
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
Return the full name of the staff who provided a customer with the first name April and the last name Burns with a film rental.
assistant
SELECT DISTINCT T1.first_name , T1.last_name FROM staff AS T1 JOIN rental AS T2 ON T1.staff_id = T2.staff_id JOIN customer AS T3 ON T2.customer_id = T3.customer_id WHERE T3.first_name = "APRIL" AND T3.last_name = "BURNS" ;