CREATE TABLE actor (
    actor_id NUMBER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    last_update TIME
);
CREATE TABLE address (
    address_id NUMBER PRIMARY KEY,
    address TEXT,
    address2 TEXT,
    district TEXT,
    city_id NUMBER,
    postal_code TEXT,
    phone TEXT,
    last_update TIME,
    FOREIGN KEY ("city_id") REFERENCES city("city_id")
);
CREATE TABLE category (
    category_id NUMBER PRIMARY KEY,
    name TEXT,
    last_update TIME
);
CREATE TABLE city (
    city_id NUMBER PRIMARY KEY,
    city TEXT,
    country_id NUMBER,
    last_update TIME,
    FOREIGN KEY ("country_id") REFERENCES country("country_id")
);
CREATE TABLE country (
    country_id NUMBER PRIMARY KEY,
    country TEXT,
    last_update TIME
);
CREATE TABLE customer (
    customer_id NUMBER PRIMARY KEY,
    store_id NUMBER,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    address_id NUMBER,
    active BOOLEAN,
    create_date TIME,
    last_update TIME,
    FOREIGN KEY ("store_id") REFERENCES store("store_id"),
    FOREIGN KEY ("address_id") REFERENCES address("address_id")
);
CREATE TABLE film (
    film_id NUMBER PRIMARY KEY,
    title TEXT,
    description TEXT,
    release_year TIME,
    language_id NUMBER,
    original_language_id NUMBER,
    rental_duration NUMBER,
    rental_rate NUMBER,
    length NUMBER,
    replacement_cost NUMBER,
    rating TEXT,
    special_features TEXT,
    last_update TIME,
    FOREIGN KEY ("original_language_id") REFERENCES language("language_id"),
    FOREIGN KEY ("language_id") REFERENCES language("language_id")
);
CREATE TABLE film_actor (
    actor_id NUMBER PRIMARY KEY,
    film_id NUMBER,
    last_update TIME,
    FOREIGN KEY ("film_id") REFERENCES film("film_id"),
    FOREIGN KEY ("actor_id") REFERENCES actor("actor_id")
);
CREATE TABLE film_category (
    film_id NUMBER PRIMARY KEY,
    category_id NUMBER,
    last_update TIME,
    FOREIGN KEY ("category_id") REFERENCES category("category_id"),
    FOREIGN KEY ("film_id") REFERENCES film("film_id")
);
CREATE TABLE film_text (
    film_id NUMBER PRIMARY KEY,
    title TEXT,
    description TEXT
);
CREATE TABLE inventory (
    inventory_id NUMBER PRIMARY KEY,
    film_id NUMBER,
    store_id NUMBER,
    last_update TIME,
    FOREIGN KEY ("film_id") REFERENCES film("film_id"),
    FOREIGN KEY ("store_id") REFERENCES store("store_id")
);
CREATE TABLE language (
    language_id NUMBER PRIMARY KEY,
    name TEXT,
    last_update TIME
);
CREATE TABLE payment (
    payment_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    staff_id NUMBER,
    rental_id NUMBER,
    amount NUMBER,
    payment_date TIME,
    last_update TIME,
    FOREIGN KEY ("staff_id") REFERENCES staff("staff_id"),
    FOREIGN KEY ("customer_id") REFERENCES customer("customer_id"),
    FOREIGN KEY ("rental_id") REFERENCES rental("rental_id")
);
CREATE TABLE rental (
    rental_id NUMBER PRIMARY KEY,
    rental_date TIME,
    inventory_id NUMBER,
    customer_id NUMBER,
    return_date TIME,
    staff_id NUMBER,
    last_update TIME,
    FOREIGN KEY ("customer_id") REFERENCES customer("customer_id"),
    FOREIGN KEY ("inventory_id") REFERENCES inventory("inventory_id"),
    FOREIGN KEY ("staff_id") REFERENCES staff("staff_id")
);
CREATE TABLE staff (
    staff_id NUMBER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    address_id NUMBER,
    picture OTHERS,
    email TEXT,
    store_id NUMBER,
    active BOOLEAN,
    username TEXT,
    password TEXT,
    last_update TIME,
    FOREIGN KEY ("address_id") REFERENCES address("address_id")
);
CREATE TABLE store (
    store_id NUMBER PRIMARY KEY,
    manager_staff_id NUMBER,
    address_id NUMBER,
    last_update TIME,
    FOREIGN KEY ("address_id") REFERENCES address("address_id"),
    FOREIGN KEY ("manager_staff_id") REFERENCES staff("staff_id")
);

Question: How many films are there in each category? List the genre name, genre id and the count.
SQL: 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

Question: What are the names and ids of the different categories, and how many films are in each?
SQL: 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

Question: Which film has the most copies in the inventory? List both title and id.
SQL: 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

Question: What is the title and id of the film that has the greatest number of copies in inventory?
SQL: 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

Question: What is the film title and inventory id of the item in the inventory which was rented most frequently?
SQL: 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

Question: Return the title and inventory id of the film that is rented most often.
SQL: 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

Question: How many languages are in these films?
SQL: SELECT count(DISTINCT language_id) FROM film

Question: Count the number of different languages in these films.
SQL: SELECT count(DISTINCT language_id) FROM film

Question: What are all the movies rated as R? List the titles.
SQL: SELECT title FROM film WHERE rating  =  'R'

Question: Return the titles of any movies with an R rating.
SQL: SELECT title FROM film WHERE rating  =  'R'

Question: Where is store 1 located?
SQL: SELECT T2.address FROM store AS T1 JOIN address AS T2 ON T1.address_id  =  T2.address_id WHERE store_id  =  1

Question: Return the address of store 1.
SQL: SELECT T2.address FROM store AS T1 JOIN address AS T2 ON T1.address_id  =  T2.address_id WHERE store_id  =  1

Question: Which staff handled least number of payments? List the full name and the id.
SQL: SELECT T1.first_name ,  T1.last_name ,  T1.staff_id FROM staff AS T1 JOIN payment AS T2 ON T1.staff_id  =  T2.staff_id GROUP BY T1.staff_id ORDER BY count(*) ASC LIMIT 1

Question: Give the full name and staff id of the staff who has handled the fewest payments.
SQL: SELECT T1.first_name ,  T1.last_name ,  T1.staff_id FROM staff AS T1 JOIN payment AS T2 ON T1.staff_id  =  T2.staff_id GROUP BY T1.staff_id ORDER BY count(*) ASC LIMIT 1

Question: Which language does the film AIRPORT POLLOCK use? List the language name.
SQL: SELECT T2.name FROM film AS T1 JOIN LANGUAGE AS T2 ON T1.language_id  =  T2.language_id WHERE T1.title  =  'AIRPORT POLLOCK'

Question: What is the name of the language that the film 'AIRPORT POLLOCK' is in?
SQL: SELECT T2.name FROM film AS T1 JOIN LANGUAGE AS T2 ON T1.language_id  =  T2.language_id WHERE T1.title  =  'AIRPORT POLLOCK'

Question: How many stores are there?
SQL: SELECT count(*) FROM store

Question: Count the number of stores.
SQL: SELECT count(*) FROM store

Question: How many kinds of different ratings are listed?
SQL: SELECT count(DISTINCT rating) FROM film

Question: Count the number of different film ratings.
SQL: SELECT count(DISTINCT rating) FROM film

Question: Which movies have 'Deleted Scenes' as a substring in the special feature?
SQL: SELECT title FROM film WHERE special_features LIKE '%Deleted Scenes%'

Question: Return the titles of films that include 'Deleted Scenes' in their special feature section.
SQL: SELECT title FROM film WHERE special_features LIKE '%Deleted Scenes%'

Question: How many items in inventory does store 1 have?
SQL: SELECT count(*) FROM inventory WHERE store_id  =  1