Database: tracking_software_problems

CREATE TABLE `Problem_Category_Codes` (
`problem_category_code` VARCHAR(20) PRIMARY KEY,
`problem_category_description` VARCHAR(80)
);
CREATE TABLE `Problem_Log` (
`problem_log_id` INTEGER PRIMARY KEY,
`assigned_to_staff_id` INTEGER NOT NULL,
`problem_id` INTEGER NOT NULL,
`problem_category_code` VARCHAR(20) NOT NULL,
`problem_status_code` VARCHAR(20) NOT NULL,
`log_entry_date` DATETIME,
`log_entry_description` VARCHAR(255),
`log_entry_fix` VARCHAR(255),
`other_log_details` VARCHAR(255),
FOREIGN KEY (`problem_category_code` ) REFERENCES `Problem_Category_Codes`(`problem_category_code` ),FOREIGN KEY (`assigned_to_staff_id` ) REFERENCES `Staff`(`staff_id` ),FOREIGN KEY (`problem_id` ) REFERENCES `Problems`(`problem_id` ),FOREIGN KEY (`problem_status_code` ) REFERENCES `Problem_Status_Codes`(`problem_status_code` )
);
CREATE TABLE `Problem_Status_Codes` (
`problem_status_code` VARCHAR(20) PRIMARY KEY,
`problem_status_description` VARCHAR(80)
);
CREATE TABLE `Product` (
`product_id` INTEGER PRIMARY KEY,
`product_name` VARCHAR(80),
`product_details` VARCHAR(255)
);
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_first_name` VARCHAR(80),
`staff_last_name` VARCHAR(80),
`other_staff_details` VARCHAR(255)
);
CREATE TABLE `Problems` (
`problem_id` INTEGER PRIMARY KEY,
`product_id` INTEGER NOT NULL,
`closure_authorised_by_staff_id` INTEGER NOT NULL,
`reported_by_staff_id` INTEGER NOT NULL,
`date_problem_reported` DATETIME NOT NULL,
`date_problem_closed` DATETIME,
`problem_description` VARCHAR(255),
`other_problem_details` VARCHAR(255),
FOREIGN KEY (`closure_authorised_by_staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Product`(`product_id` ),
FOREIGN KEY (`reported_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

Question: Sort all the distinct product names in alphabetical order.
SQL: SELECT DISTINCT product_name FROM product ORDER BY product_name

Question: List all the distinct product names ordered by product id?
SQL: SELECT DISTINCT product_name FROM product ORDER BY product_id

Question: What is the list of distinct product names sorted by product id?
SQL: SELECT DISTINCT product_name FROM product ORDER BY product_id

Question: What are the id of problems reported by the staff named Dameon Frami or Jolie Weber?
SQL: SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Dameon" AND T2.staff_last_name = "Frami" UNION SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Jolie" AND T2.staff_last_name = "Weber"

Question: Which problems were reported by the staff named Dameon Frami or Jolie Weber? Give me the ids of the problems.
SQL: SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Dameon" AND T2.staff_last_name = "Frami" UNION SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Jolie" AND T2.staff_last_name = "Weber"

Question: What are the product ids for the problems reported by Christop Berge with closure authorised by Ashley Medhurst?
SQL: SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Christop" AND T2.staff_last_name = "Berge" INTERSECT SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.closure_authorised_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Ashley" AND T2.staff_last_name = "Medhurst"

Question: For which product was there a problem reported by Christop Berge, with closure authorised by Ashley Medhurst? Return the product ids.
SQL: SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Christop" AND T2.staff_last_name = "Berge" INTERSECT SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.closure_authorised_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Ashley" AND T2.staff_last_name = "Medhurst"

Question: What are the ids of the problems reported before the date of any problem reported by Lysanne Turcotte?
SQL: SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE date_problem_reported  <  ( SELECT min(date_problem_reported) FROM problems AS T3 JOIN staff AS T4 ON T3.reported_by_staff_id = T4.staff_id WHERE T4.staff_first_name = "Lysanne" AND T4.staff_last_name = "Turcotte" )

Question: Which problems were reported before the date of any problem reported by the staff Lysanne Turcotte? Give me 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 date_problem_reported  <  ( SELECT min(date_problem_reported) FROM problems AS T3 JOIN staff AS T4 ON T3.reported_by_staff_id = T4.staff_id WHERE T4.staff_first_name = "Lysanne" AND T4.staff_last_name = "Turcotte" )

Question: What are the ids of the problems reported after the date of any problems reported by Rylan Homenick?
SQL: SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE date_problem_reported  >  ( SELECT max(date_problem_reported) FROM problems AS T3 JOIN staff AS T4 ON T3.reported_by_staff_id = T4.staff_id WHERE T4.staff_first_name = "Rylan" AND T4.staff_last_name = "Homenick" )

Question: Find the ids of the problems reported after the date of any problems reported by the staff Rylan Homenick.
SQL: SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE date_problem_reported  >  ( SELECT max(date_problem_reported) FROM problems AS T3 JOIN staff AS T4 ON T3.reported_by_staff_id = T4.staff_id WHERE T4.staff_first_name = "Rylan" AND T4.staff_last_name = "Homenick" )

Question: Find the top 3 products which have the largest number of problems?
SQL: SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id GROUP BY T2.product_name ORDER BY count(*) DESC LIMIT 3

Question: What are the three products that have the most problems?s
SQL: SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id GROUP BY T2.product_name ORDER BY count(*) DESC LIMIT 3

Question: List the ids of the problems from the product "voluptatem" that are reported after 1995?
SQL: SELECT T1.problem_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id WHERE T2.product_name = "voluptatem" AND T1.date_problem_reported > "1995"

Question: What are the ids of the problems that are from the product "voluptatem" and are reported after 1995?
SQL: SELECT T1.problem_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id WHERE T2.product_name = "voluptatem" AND T1.date_problem_reported > "1995"

Question: Find the first and last name of the staff members who reported problems from the product "rem" but not "aut"?
SQL: SELECT T3.staff_first_name ,  T3.staff_last_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T2.product_name = "rem" EXCEPT SELECT T3.staff_first_name ,  T3.staff_last_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T2.product_name = "aut"

Question: Which staff members who reported problems from the product "rem" but not "aut"? Give me their first and last names.
SQL: SELECT T3.staff_first_name ,  T3.staff_last_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T2.product_name = "rem" EXCEPT SELECT T3.staff_first_name ,  T3.staff_last_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T2.product_name = "aut"

Question: Find the products which have problems reported by both Lacey Bosco and Kenton Champlin?
SQL: SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T3.staff_first_name = "Lacey" AND T3.staff_last_name = "Bosco" INTERSECT SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T3.staff_first_name = "Kenton" AND T3.staff_last_name = "Champlin"

Question: Which products have problems reported by both the staff named Lacey Bosco and the staff named Kenton Champlin?
SQL: SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T3.staff_first_name = "Lacey" AND T3.staff_last_name = "Bosco" INTERSECT SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T3.staff_first_name = "Kenton" AND T3.staff_last_name = "Champlin"