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 different last names do the actors and actresses have?
SQL: SELECT count(DISTINCT last_name) FROM actor

Question: Count the number of different last names actors have.
SQL: SELECT count(DISTINCT last_name) FROM actor

Question: What is the most popular first name of the actors?
SQL: SELECT first_name FROM actor GROUP BY first_name ORDER BY count(*) DESC LIMIT 1

Question: Return the most common first name among all actors.
SQL: SELECT first_name FROM actor GROUP BY first_name ORDER BY count(*) DESC LIMIT 1

Question: What is the most popular full name of the actors?
SQL: SELECT first_name ,  last_name FROM actor GROUP BY first_name ,  last_name ORDER BY count(*) DESC LIMIT 1

Question: Return the most common full name among all actors.
SQL: SELECT first_name ,  last_name FROM actor GROUP BY first_name ,  last_name ORDER BY count(*) DESC LIMIT 1

Question: Which districts have at least two addresses?
SQL: SELECT district FROM address GROUP BY district HAVING count(*)  >=  2

Question: Give the districts which have two or more addresses.
SQL: SELECT district FROM address GROUP BY district HAVING count(*)  >=  2

Question: What is the phone number and postal code of the address 1031 Daugavpils Parkway?
SQL: SELECT phone ,  postal_code FROM address WHERE address  =  '1031 Daugavpils Parkway'

Question: Give the phone and postal code corresponding to the address '1031 Daugavpils Parkway'.
SQL: SELECT phone ,  postal_code FROM address WHERE address  =  '1031 Daugavpils Parkway'

Question: Which city has the most addresses? List the city name, number of addresses, and city id.
SQL: SELECT T2.city ,  count(*) ,  T1.city_id FROM address AS T1 JOIN city AS T2 ON T1.city_id  =  T2.city_id GROUP BY T1.city_id ORDER BY count(*) DESC LIMIT 1

Question: What are the city name, id, and number of addresses corresponding to the city with the most addressed?
SQL: SELECT T2.city ,  count(*) ,  T1.city_id FROM address AS T1 JOIN city AS T2 ON T1.city_id  =  T2.city_id GROUP BY T1.city_id ORDER BY count(*) DESC LIMIT 1

Question: How many addresses are in the district of California?
SQL: SELECT count(*) FROM address WHERE district  =  'California'

Question: Count the number of addressed in the California district.
SQL: SELECT count(*) FROM address WHERE district  =  'California'

Question: Which film is rented at a fee of 0.99 and has less than 3 in the inventory? List the film title and id.
SQL: 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

Question: What are the title and id of the film which has a rental rate of 0.99 and an inventory of below 3?
SQL: 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

Question: How many cities are in Australia?
SQL: SELECT count(*) FROM city AS T1 JOIN country AS T2 ON T1.country_id  =  T2.country_id WHERE T2.country  =  'Australia'

Question: Count the number of cities in Australia.
SQL: SELECT count(*) FROM city AS T1 JOIN country AS T2 ON T1.country_id  =  T2.country_id WHERE T2.country  =  'Australia'

Question: Which countries have at least 3 cities?
SQL: SELECT T2.country FROM city AS T1 JOIN country AS T2 ON T1.country_id  =  T2.country_id GROUP BY T2.country_id HAVING count(*)  >=  3

Question: What are the countries that contain 3 or more cities?
SQL: SELECT T2.country FROM city AS T1 JOIN country AS T2 ON T1.country_id  =  T2.country_id GROUP BY T2.country_id HAVING count(*)  >=  3

Question: Find all the payment dates for the payments with an amount larger than 10 and the payments handled by a staff person with the first name Elsa.
SQL: SELECT payment_date FROM payment WHERE amount  >  10 UNION SELECT T1.payment_date FROM payment AS T1 JOIN staff AS T2 ON T1.staff_id  =  T2.staff_id WHERE T2.first_name  =  'Elsa'