CREATE TABLE Products (
    product_id NUMBER PRIMARY KEY,
    product_name TEXT,
    product_price NUMBER,
    product_description TEXT
);
CREATE TABLE Addresses (
    address_id NUMBER PRIMARY KEY,
    address_details TEXT,
    city TEXT,
    zip_postcode TEXT,
    state_province_county TEXT,
    country TEXT
);
CREATE TABLE Customers (
    customer_id NUMBER PRIMARY KEY,
    payment_method TEXT,
    customer_name TEXT,
    customer_phone TEXT,
    customer_email TEXT,
    date_became_customer TIME
);
CREATE TABLE Regular_Orders (
    regular_order_id NUMBER PRIMARY KEY,
    distributer_id NUMBER,
    FOREIGN KEY ("distributer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Regular_Order_Products (
    regular_order_id NUMBER,
    product_id NUMBER,
    FOREIGN KEY ("regular_order_id") REFERENCES Regular_Orders("regular_order_id"),
    FOREIGN KEY ("product_id") REFERENCES Products("product_id")
);
CREATE TABLE Actual_Orders (
    actual_order_id NUMBER PRIMARY KEY,
    order_status_code TEXT,
    regular_order_id NUMBER,
    actual_order_date TIME,
    FOREIGN KEY ("regular_order_id") REFERENCES Regular_Orders("regular_order_id")
);
CREATE TABLE Actual_Order_Products (
    actual_order_id NUMBER,
    product_id NUMBER,
    FOREIGN KEY ("actual_order_id") REFERENCES Actual_Orders("actual_order_id"),
    FOREIGN KEY ("product_id") REFERENCES Products("product_id")
);
CREATE TABLE Customer_Addresses (
    customer_id NUMBER,
    address_id NUMBER,
    date_from TIME,
    address_type TEXT,
    date_to TIME,
    FOREIGN KEY ("address_id") REFERENCES Addresses("address_id"),
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Delivery_Routes (
    route_id NUMBER PRIMARY KEY,
    route_name TEXT,
    other_route_details TEXT
);
CREATE TABLE Delivery_Route_Locations (
    location_code TEXT PRIMARY KEY,
    route_id NUMBER,
    location_address_id NUMBER,
    location_name TEXT,
    FOREIGN KEY ("route_id") REFERENCES Delivery_Routes("route_id"),
    FOREIGN KEY ("location_address_id") REFERENCES Addresses("address_id")
);
CREATE TABLE Trucks (
    truck_id NUMBER PRIMARY KEY,
    truck_licence_number TEXT,
    truck_details TEXT
);
CREATE TABLE Employees (
    employee_id NUMBER PRIMARY KEY,
    employee_address_id NUMBER,
    employee_name TEXT,
    employee_phone TEXT,
    FOREIGN KEY ("employee_address_id") REFERENCES Addresses("address_id")
);
CREATE TABLE Order_Deliveries (
    location_code TEXT,
    actual_order_id NUMBER,
    delivery_status_code TEXT,
    driver_employee_id NUMBER,
    truck_id NUMBER,
    delivery_date TIME,
    FOREIGN KEY ("driver_employee_id") REFERENCES Employees("employee_id"),
    FOREIGN KEY ("location_code") REFERENCES Delivery_Route_Locations("location_code"),
    FOREIGN KEY ("actual_order_id") REFERENCES Actual_Orders("actual_order_id"),
    FOREIGN KEY ("truck_id") REFERENCES Trucks("truck_id")
);

Question: Find the ids of orders whose status is 'Success'.
SQL: SELECT actual_order_id FROM actual_orders WHERE order_status_code  =  'Success'

Question: Find the name and price of the product that has been ordered the greatest number of times.
SQL: SELECT t1.product_name ,   t1.product_price FROM products AS t1 JOIN regular_order_products AS t2 ON t1.product_id  =  t2.product_id GROUP BY t2.product_id ORDER BY count(*) DESC LIMIT 1

Question: Find the number of customers in total.
SQL: SELECT count(*) FROM customers

Question: How many different payment methods are there?
SQL: SELECT count(DISTINCT payment_method) FROM customers

Question: Show the details of all trucks in the order of their license number.
SQL: SELECT truck_details FROM trucks ORDER BY truck_licence_number

Question: Find the name of the most expensive product.
SQL: SELECT product_name FROM products ORDER BY product_price DESC LIMIT 1

Question: Find the names of customers who are not living in the state of California.
SQL: SELECT customer_name FROM customers EXCEPT SELECT t1.customer_name FROM customers AS t1 JOIN customer_addresses AS t2 ON t1.customer_id  =  t2.customer_id JOIN addresses AS t3 ON t2.address_id  =  t3.address_id WHERE t3.state_province_county  =  'California'

Question: List the names and emails of customers who payed by Visa card.
SQL: SELECT customer_email ,  customer_name FROM customers WHERE payment_method  =  'Visa'

Question: Find the names and phone numbers of customers living in California state.
SQL: SELECT t1.customer_name ,  t1.customer_phone FROM customers AS t1 JOIN customer_addresses AS t2 ON t1.customer_id  =  t2.customer_id JOIN addresses AS t3 ON t2.address_id  =  t3.address_id WHERE t3.state_province_county  =  'California'

Question: Find the states which do not have any employee in their record.
SQL: SELECT state_province_county FROM addresses WHERE address_id NOT IN (SELECT employee_address_id FROM Employees)

Question: List the names, phone numbers, and emails of all customers sorted by their dates of becoming customers.
SQL: SELECT customer_name ,  customer_phone ,  customer_email FROM Customers ORDER BY date_became_customer

Question: Find the name of the first 5 customers.
SQL: SELECT customer_name FROM Customers ORDER BY date_became_customer LIMIT 5

Question: Find the payment method that is used most frequently.
SQL: SELECT payment_method FROM Customers GROUP BY payment_method ORDER BY count(*) DESC LIMIT 1

Question: List the names of all routes in alphabetic order.
SQL: SELECT route_name FROM Delivery_Routes ORDER BY route_name

Question: Find the name of route that has the highest number of deliveries.
SQL: SELECT t1.route_name FROM Delivery_Routes AS t1 JOIN Delivery_Route_Locations AS t2 ON t1.route_id  =  t2.route_id GROUP BY t1.route_id ORDER BY count(*) DESC LIMIT 1

Question: List the state names and the number of customers living in each state.
SQL: SELECT t2.state_province_county ,  count(*) FROM customer_addresses AS t1 JOIN addresses AS t2 ON t1.address_id  =  t2.address_id GROUP BY t2.state_province_county