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 kinds of different ratings are listed?
assistant
SELECT count(DISTINCT rating) FROM film
human
Which customer, who has made at least one payment, has spent the least money? List his or her first name, last name, and the id.
assistant
SELECT T1.first_name , T1.last_name , T1.customer_id FROM customer AS T1 JOIN payment AS T2 ON T1.customer_id = T2.customer_id GROUP BY T1.customer_id ORDER BY sum(amount) ASC LIMIT 1
human
How many stores are there?
assistant
SELECT count(*) FROM store
human
How many items in inventory does store 1 have?
assistant
SELECT count(*) FROM inventory WHERE store_id = 1
human
Which store has most the customers?
assistant
SELECT store_id FROM customer GROUP BY store_id ORDER BY count(*) DESC LIMIT 1
human
How many customers have an active value of 1?
assistant
SELECT COUNT( * ) FROM customer WHERE active = "1" ;