CREATE TABLE Problem_Category_Codes (
    problem_category_code TEXT PRIMARY KEY,
    problem_category_description TEXT
);
CREATE TABLE Problem_Log (
    problem_log_id NUMBER PRIMARY KEY,
    assigned_to_staff_id NUMBER,
    problem_id NUMBER,
    problem_category_code TEXT,
    problem_status_code TEXT,
    log_entry_date TIME,
    log_entry_description TEXT,
    log_entry_fix TEXT,
    other_log_details TEXT,
    FOREIGN KEY ("problem_status_code") REFERENCES Problem_Status_Codes("problem_status_code"),
    FOREIGN KEY ("problem_id") REFERENCES Problems("problem_id"),
    FOREIGN KEY ("assigned_to_staff_id") REFERENCES Staff("staff_id"),
    FOREIGN KEY ("problem_category_code") REFERENCES Problem_Category_Codes("problem_category_code")
);
CREATE TABLE Problem_Status_Codes (
    problem_status_code TEXT PRIMARY KEY,
    problem_status_description TEXT
);
CREATE TABLE Product (
    product_id NUMBER PRIMARY KEY,
    product_name TEXT,
    product_details TEXT
);
CREATE TABLE Staff (
    staff_id NUMBER PRIMARY KEY,
    staff_first_name TEXT,
    staff_last_name TEXT,
    other_staff_details TEXT
);
CREATE TABLE Problems (
    problem_id NUMBER PRIMARY KEY,
    product_id NUMBER,
    closure_authorised_by_staff_id NUMBER,
    reported_by_staff_id NUMBER,
    date_problem_reported TIME,
    date_problem_closed TIME,
    problem_description TEXT,
    other_problem_details TEXT,
    FOREIGN KEY ("reported_by_staff_id") REFERENCES Staff("staff_id"),
    FOREIGN KEY ("product_id") REFERENCES Product("product_id"),
    FOREIGN KEY ("closure_authorised_by_staff_id") REFERENCES Staff("staff_id")
);

Question: What is the id of the problem log that is created most recently?
SQL: SELECT problem_log_id FROM problem_log ORDER BY log_entry_date DESC LIMIT 1

Question: Which problem log was created most recently? Give me the log id.
SQL: SELECT problem_log_id FROM problem_log ORDER BY log_entry_date DESC LIMIT 1

Question: What is the oldest log id and its corresponding problem id?
SQL: SELECT problem_log_id ,  problem_id FROM problem_log ORDER BY log_entry_date LIMIT 1

Question: Find the oldest log id and its corresponding problem id.
SQL: SELECT problem_log_id ,  problem_id FROM problem_log ORDER BY log_entry_date LIMIT 1

Question: Find all the ids and dates of the logs for the problem whose id is 10.
SQL: SELECT problem_log_id ,  log_entry_date FROM problem_log WHERE problem_id = 10

Question: For the problem with id 10, return the ids and dates of its problem logs.
SQL: SELECT problem_log_id ,  log_entry_date FROM problem_log WHERE problem_id = 10

Question: List all the log ids and their descriptions from the problem logs.
SQL: SELECT problem_log_id ,  log_entry_description FROM problem_log

Question: What are the log id and entry description of each problem?
SQL: SELECT problem_log_id ,  log_entry_description FROM problem_log

Question: List the first and last names of all distinct staff members who are assigned to the problem whose id is 1.
SQL: SELECT DISTINCT staff_first_name ,  staff_last_name FROM staff AS T1 JOIN problem_log AS T2 ON T1.staff_id = T2.assigned_to_staff_id WHERE T2.problem_id = 1

Question: Which staff members are assigned to the problem with id 1? Give me their first and last names.
SQL: SELECT DISTINCT staff_first_name ,  staff_last_name FROM staff AS T1 JOIN problem_log AS T2 ON T1.staff_id = T2.assigned_to_staff_id WHERE T2.problem_id = 1

Question: List the problem id and log id which are assigned to the staff named Rylan Homenick.
SQL: SELECT DISTINCT T2.problem_id ,  T2.problem_log_id FROM staff AS T1 JOIN problem_log AS T2 ON T1.staff_id = T2.assigned_to_staff_id WHERE T1.staff_first_name = "Rylan" AND T1.staff_last_name = "Homenick"

Question: Which problem id and log id are assigned to the staff named Rylan Homenick?
SQL: SELECT DISTINCT T2.problem_id ,  T2.problem_log_id FROM staff AS T1 JOIN problem_log AS T2 ON T1.staff_id = T2.assigned_to_staff_id WHERE T1.staff_first_name = "Rylan" AND T1.staff_last_name = "Homenick"

Question: How many problems are there for product voluptatem?
SQL: SELECT count(*) FROM product AS T1 JOIN problems AS T2 ON T1.product_id = T2.product_id WHERE T1.product_name = "voluptatem"

Question: How many problems did the product called "voluptatem" have in record?
SQL: SELECT count(*) FROM product AS T1 JOIN problems AS T2 ON T1.product_id = T2.product_id WHERE T1.product_name = "voluptatem"

Question: How many problems does the product with the most problems have? List the number of the problems and product name.
SQL: SELECT count(*) ,  T1.product_name FROM product AS T1 JOIN problems AS T2 ON T1.product_id = T2.product_id GROUP BY T1.product_name ORDER BY count(*) DESC LIMIT 1

Question: Which product has the most problems? Give me the number of problems and the product name.
SQL: SELECT count(*) ,  T1.product_name FROM product AS T1 JOIN problems AS T2 ON T1.product_id = T2.product_id GROUP BY T1.product_name ORDER BY count(*) DESC LIMIT 1

Question: Give me a list of descriptions of the problems that are reported by the staff whose first name is Christop.
SQL: SELECT T1.problem_description FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Christop"

Question: Which problems are reported by the staff with first name "Christop"? Show the descriptions of the problems.
SQL: SELECT T1.problem_description FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Christop"

Question: Find the ids of the problems that are reported by the staff whose last name is Bosco.
SQL: SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_last_name = "Bosco"

Question: Which problems are reported by the staff with last name "Bosco"? Show the ids of the problems.
SQL: SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_last_name = "Bosco"

Question: What are the ids of the problems which are reported after 1978-06-26?
SQL: SELECT problem_id FROM problems WHERE date_problem_reported > "1978-06-26"

Question: Find the ids of the problems reported after 1978-06-26.
SQL: SELECT problem_id FROM problems WHERE date_problem_reported > "1978-06-26"

Question: What are the ids of the problems which are reported before 1978-06-26?
SQL: SELECT problem_id FROM problems WHERE date_problem_reported < "1978-06-26"

Question: Which problems are reported before 1978-06-26? Give me the ids of the problems.
SQL: SELECT problem_id FROM problems WHERE date_problem_reported < "1978-06-26"

Question: For each product which has problems, what are the number of problems and the product id?
SQL: SELECT count(*) ,  T2.product_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id GROUP BY T2.product_id

Question: For each product with some problems, list the count of problems and the product id.
SQL: SELECT count(*) ,  T2.product_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id GROUP BY T2.product_id

Question: For each product that has problems, find the number of problems reported after 1986-11-13 and the product id?
SQL: SELECT count(*) ,  T2.product_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id WHERE T1.date_problem_reported > "1986-11-13" GROUP BY T2.product_id

Question: What are the products that have problems reported after 1986-11-13? Give me the product id and the count of problems reported after 1986-11-13.
SQL: SELECT count(*) ,  T2.product_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id WHERE T1.date_problem_reported > "1986-11-13" GROUP BY T2.product_id

Question: List the names of all the distinct product names in alphabetical order?
SQL: SELECT DISTINCT product_name FROM product ORDER BY product_name