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: Count the number of items store 1 has in stock.
SQL: SELECT count(*) FROM inventory WHERE store_id  =  1

Question: When did the first payment happen?
SQL: SELECT payment_date FROM payment ORDER BY payment_date ASC LIMIT 1

Question: What was the date of the earliest payment?
SQL: SELECT payment_date FROM payment ORDER BY payment_date ASC LIMIT 1

Question: Where does the customer with the first name Linda live? And what is her email?
SQL: SELECT T2.address ,  T1.email FROM customer AS T1 JOIN address AS T2 ON T2.address_id  =  T1.address_id WHERE T1.first_name  =  'LINDA'

Question: Return the address and email of the customer with the first name Linda.
SQL: SELECT T2.address ,  T1.email FROM customer AS T1 JOIN address AS T2 ON T2.address_id  =  T1.address_id WHERE T1.first_name  =  'LINDA'

Question: Find all the films longer than 100 minutes, or rated PG, except those who cost more than 200 for replacement. List the titles.
SQL: SELECT title FROM film WHERE LENGTH  >  100 OR rating  =  'PG' EXCEPT SELECT title FROM film WHERE replacement_cost  >  200

Question: What are the titles of films that are either longer than 100 minutes or rated PG other than those that cost more than 200 to replace?
SQL: SELECT title FROM film WHERE LENGTH  >  100 OR rating  =  'PG' EXCEPT SELECT title FROM film WHERE replacement_cost  >  200

Question: What is the first name and the last name of the customer who made the earliest rental?
SQL: 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

Question: Return the full name of the customer who made the first rental.
SQL: 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

Question: What is the full name of the staff member who has rented a film to a customer with the first name April and the last name Burns?
SQL: 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'

Question: 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.
SQL: 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'

Question: Which store has most the customers?
SQL: SELECT store_id FROM customer GROUP BY store_id ORDER BY count(*) DESC LIMIT 1

Question: Return the id of the store with the most customers.
SQL: SELECT store_id FROM customer GROUP BY store_id ORDER BY count(*) DESC LIMIT 1

Question: What is the largest payment amount?
SQL: SELECT amount FROM payment ORDER BY amount DESC LIMIT 1

Question: Return the amount of the largest payment.
SQL: SELECT amount FROM payment ORDER BY amount DESC LIMIT 1

Question: Where does the staff member with the first name Elsa live?
SQL: SELECT T2.address FROM staff AS T1 JOIN address AS T2 ON T1.address_id  =  T2.address_id WHERE T1.first_name  =  'Elsa'

Question: Give the address of the staff member who has the first name Elsa.
SQL: SELECT T2.address FROM staff AS T1 JOIN address AS T2 ON T1.address_id  =  T2.address_id WHERE T1.first_name  =  'Elsa'

Question: What are the first names of customers who have not rented any films after '2005-08-23 02:06:01'?
SQL: SELECT first_name FROM customer WHERE customer_id NOT IN( SELECT customer_id FROM rental WHERE rental_date  >  '2005-08-23 02:06:01' )

Question: Return the first names of customers who did not rented a film after the date '2005-08-23 02:06:01'.
SQL: SELECT first_name FROM customer WHERE customer_id NOT IN( SELECT customer_id FROM rental WHERE rental_date  >  '2005-08-23 02:06:01' )