target-benchmark/spider-queries huggingface dataset:
Database: loan_1 (tables: loan, customer)
Question: What are the names and account balances for customers who have taken a total amount of more than 5000 in loans?
SQL: SELECT T1.cust_name ,  T1.acc_type FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id GROUP BY T1.cust_name HAVING sum(T2.amount)  >  5000
Database: loan_1 (tables: bank, loan)
Question: Find the name of bank branch that provided the greatest total amount of loans.
SQL: SELECT T1.bname FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id GROUP BY T1.bname ORDER BY sum(T2.amount) DESC LIMIT 1
Database: loan_1 (tables: bank, loan)
Question: What is the name of the bank branch that has lent the greatest amount?
SQL: SELECT T1.bname FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id GROUP BY T1.bname ORDER BY sum(T2.amount) DESC LIMIT 1
Database: loan_1 (tables: customer, bank, loan)
Question: Find the name of bank branch that provided the greatest total amount of loans to customers with credit score is less than 100.
SQL: SELECT T2.bname FROM loan AS T1 JOIN bank AS T2 ON T1.branch_id  =  T2.branch_id JOIN customer AS T3 ON T1.cust_id  =  T3.cust_id WHERE T3.credit_score  <  100 GROUP BY T2.bname ORDER BY sum(T1.amount) DESC LIMIT 1
Database: loan_1 (tables: customer, bank, loan)
Question: What is the name of the bank branch that has lended the largest total amount in loans, specifically to customers with credit scores below 100?
SQL: SELECT T2.bname FROM loan AS T1 JOIN bank AS T2 ON T1.branch_id  =  T2.branch_id JOIN customer AS T3 ON T1.cust_id  =  T3.cust_id WHERE T3.credit_score  <  100 GROUP BY T2.bname ORDER BY sum(T1.amount) DESC LIMIT 1
Database: loan_1 (tables: bank, loan)
Question: Find the name of bank branches that provided some loans.
SQL: SELECT DISTINCT T1.bname FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id
Database: loan_1 (tables: bank, loan)
Question: What are the names of the different banks that have provided loans?
SQL: SELECT DISTINCT T1.bname FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id
Database: loan_1 (tables: loan, customer)
Question: Find the name and credit score of the customers who have some loans.
SQL: SELECT DISTINCT T1.cust_name ,  T1.credit_score FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id
Database: loan_1 (tables: loan, customer)
Question: What are the different names and credit scores of customers who have taken a loan?
SQL: SELECT DISTINCT T1.cust_name ,  T1.credit_score FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id
Database: loan_1 (tables: loan, customer)
Question: Find the the name of the customers who have a loan with amount more than 3000.
SQL: SELECT T1.cust_name FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id WHERE amount  >  3000
Database: loan_1 (tables: loan, customer)
Question: What are the names of customers who have a loan of more than 3000 in amount?
SQL: SELECT T1.cust_name FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id WHERE amount  >  3000
Database: loan_1 (tables: bank, loan)
Question: Find the city and name of bank branches that provide business loans.
SQL: SELECT T1.bname ,  T1.city FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id WHERE T2.loan_type  =  'Business'
Database: loan_1 (tables: bank, loan)
Question: What are the names and cities of bank branches that offer loans for business?
SQL: SELECT T1.bname ,  T1.city FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id WHERE T2.loan_type  =  'Business'
Database: loan_1 (tables: customer, bank, loan)
Question: Find the names of bank branches that have provided a loan to any customer whose credit score is below 100.
SQL: SELECT T2.bname FROM loan AS T1 JOIN bank AS T2 ON T1.branch_id  =  T2.branch_id JOIN customer AS T3 ON T1.cust_id  =  T3.cust_id WHERE T3.credit_score  <  100
Database: loan_1 (tables: customer, bank, loan)
Question: What are the names of banks that have loaned money to customers with credit scores below 100?
SQL: SELECT T2.bname FROM loan AS T1 JOIN bank AS T2 ON T1.branch_id  =  T2.branch_id JOIN customer AS T3 ON T1.cust_id  =  T3.cust_id WHERE T3.credit_score  <  100
Database: loan_1 (tables: bank, loan)
Question: Find the total amount of loans provided by bank branches in the state of New York.
SQL: SELECT sum(T2.amount) FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.state  =  'New York'
Database: loan_1 (tables: bank, loan)
Question: What is the total amount of money loaned by banks in New York state?
SQL: SELECT sum(T2.amount) FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.state  =  'New York'
Database: loan_1 (tables: customer, loan)
Question: Find the average credit score of the customers who have some loan.
SQL: SELECT avg(credit_score) FROM customer WHERE cust_id IN (SELECT cust_id FROM loan)
Database: loan_1 (tables: customer, loan)
Question: What is the average credit score for customers who have taken a loan?
SQL: SELECT avg(credit_score) FROM customer WHERE cust_id IN (SELECT cust_id FROM loan)
Database: loan_1 (tables: customer, loan)
Question: Find the average credit score of the customers who do not have any loan.
SQL: SELECT avg(credit_score) FROM customer WHERE cust_id NOT IN (SELECT cust_id FROM loan)
Database: loan_1 (tables: customer, loan)
Question: What is the average credit score for customers who have never taken a loan?
SQL: SELECT avg(credit_score) FROM customer WHERE cust_id NOT IN (SELECT cust_id FROM loan)
Database: behavior_monitoring (tables: assessment_notes)
Question: How many assessment notes are there in total?
SQL: SELECT count(*) FROM ASSESSMENT_NOTES
Database: behavior_monitoring (tables: assessment_notes)
Question: What are the dates of the assessment notes?
SQL: SELECT date_of_notes FROM Assessment_Notes
Database: behavior_monitoring (tables: addresses)
Question: How many addresses have zip code 197?
SQL: SELECT count(*) FROM ADDRESSES WHERE zip_postcode  =  "197"
Database: behavior_monitoring (tables: behavior_incident)
Question: How many distinct incident type codes are there?
SQL: SELECT count(DISTINCT incident_type_code) FROM Behavior_Incident
Database: behavior_monitoring (tables: detention)
Question: Return all distinct detention type codes.
SQL: SELECT DISTINCT detention_type_code FROM Detention
Database: behavior_monitoring (tables: behavior_incident)
Question: What are the start and end dates for incidents with incident type code "NOISE"?
SQL: SELECT date_incident_start ,  date_incident_end FROM Behavior_Incident WHERE incident_type_code  =  "NOISE"
Database: behavior_monitoring (tables: detention)
Question: Return all detention summaries.
SQL: SELECT detention_summary FROM Detention
Database: behavior_monitoring (tables: students)
Question: Return the cell phone number and email address for all students.
SQL: SELECT cell_mobile_number ,  email_address FROM STUDENTS
Database: behavior_monitoring (tables: students)
Question: What is the email of the student with first name "Emma" and last name "Rohan"?
SQL: SELECT email_address FROM Students WHERE first_name  =  "Emma" AND last_name  =  "Rohan"
Database: behavior_monitoring (tables: students_in_detention)
Question: How many distinct students have been in detention?
SQL: SELECT count(DISTINCT student_id) FROM Students_in_Detention
Database: behavior_monitoring (tables: teachers)
Question: What is the gender of the teacher with last name "Medhurst"?
SQL: SELECT gender FROM TEACHERS WHERE last_name  =  "Medhurst"
Database: behavior_monitoring (tables: ref_incident_type)
Question: What is the incident type description for the incident type with code "VIOLENCE"?
SQL: SELECT incident_type_description FROM Ref_Incident_Type WHERE incident_type_code  =  "VIOLENCE"
Database: behavior_monitoring (tables: student_addresses)
Question: Find the maximum and minimum monthly rental for all student addresses.
SQL: SELECT max(monthly_rental) ,  min(monthly_rental) FROM Student_Addresses
Database: behavior_monitoring (tables: teachers)
Question: Find the first names of teachers whose email address contains the word "man".
SQL: SELECT first_name FROM Teachers WHERE email_address LIKE '%man%'
Database: behavior_monitoring (tables: assessment_notes)
Question: List all information about the assessment notes sorted by date in ascending order.
SQL: SELECT * FROM Assessment_Notes ORDER BY date_of_notes ASC
Database: behavior_monitoring (tables: addresses)
Question: List all cities of addresses in alphabetical order.
SQL: SELECT city FROM Addresses ORDER BY city
Database: behavior_monitoring (tables: teachers)
Question: Find the first names and last names of teachers in alphabetical order of last name.
SQL: SELECT first_name ,  last_name FROM Teachers ORDER BY last_name
Database: behavior_monitoring (tables: student_addresses)
Question: Find all information about student addresses, and sort by monthly rental in descending order.
SQL: SELECT * FROM Student_Addresses ORDER BY monthly_rental DESC
Database: behavior_monitoring (tables: assessment_notes, students)
Question: Find the id and first name of the student that has the most number of assessment notes?
SQL: SELECT T1.student_id ,  T2.first_name FROM Assessment_Notes AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id ORDER BY count(*) DESC LIMIT 1
Database: behavior_monitoring (tables: teachers, assessment_notes)
Question: Find the ids and first names of the 3 teachers that have the most number of assessment notes?
SQL: SELECT T1.teacher_id ,  T2.first_name FROM Assessment_Notes AS T1 JOIN Teachers AS T2 ON T1.teacher_id  =  T2.teacher_id GROUP BY T1.teacher_id ORDER BY count(*) DESC LIMIT 3
Database: behavior_monitoring (tables: behavior_incident, students)
Question: Find the id and last name of the student that has the most behavior incidents?
SQL: SELECT T1.student_id ,  T2.last_name FROM Behavior_Incident AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id ORDER BY count(*) DESC LIMIT 1
Database: behavior_monitoring (tables: detention, teachers)
Question: Find the id and last name of the teacher that has the most detentions with detention type code "AFTER"?
SQL: SELECT T1.teacher_id ,  T2.last_name FROM Detention AS T1 JOIN Teachers AS T2 ON T1.teacher_id  =  T2.teacher_id WHERE T1.detention_type_code  =  "AFTER" GROUP BY T1.teacher_id ORDER BY count(*) DESC LIMIT 1
Database: behavior_monitoring (tables: student_addresses, students)
Question: What are the id and first name of the student whose addresses have the highest average monthly rental?
SQL: SELECT T1.student_id ,  T2.first_name FROM Student_Addresses AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id GROUP BY T1.student_id ORDER BY AVG(monthly_rental) DESC LIMIT 1
Database: behavior_monitoring (tables: addresses, student_addresses)
Question: Find the id and city of the student address with the highest average monthly rental.
SQL: SELECT T2.address_id ,  T1.city FROM Addresses AS T1 JOIN Student_Addresses AS T2 ON T1.address_id  =  T2.address_id GROUP BY T2.address_id ORDER BY AVG(monthly_rental) DESC LIMIT 1
Database: behavior_monitoring (tables: ref_incident_type, behavior_incident)
Question: What are the code and description of the most frequent behavior incident type?
SQL: SELECT T1.incident_type_code ,  T2.incident_type_description FROM Behavior_Incident AS T1 JOIN Ref_Incident_Type AS T2 ON T1.incident_type_code  =  T2.incident_type_code GROUP BY T1.incident_type_code ORDER BY count(*) DESC LIMIT 1
Database: behavior_monitoring (tables: ref_detention_type, detention)
Question: What are the code and description of the least frequent detention type ?
SQL: SELECT T1.detention_type_code ,  T2.detention_type_description FROM Detention AS T1 JOIN Ref_Detention_Type AS T2 ON T1.detention_type_code  =  T2.detention_type_code GROUP BY T1.detention_type_code ORDER BY count(*) ASC LIMIT 1
Database: behavior_monitoring (tables: assessment_notes, students)
Question: Find the dates of assessment notes for students with first name "Fanny".
SQL: SELECT T1.date_of_notes FROM Assessment_Notes AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id WHERE T2.first_name  =  "Fanny"
Database: behavior_monitoring (tables: teachers, assessment_notes)
Question: Find the texts of assessment notes for teachers with last name "Schuster".
SQL: SELECT T1.text_of_notes FROM Assessment_Notes AS T1 JOIN Teachers AS T2 ON T1.teacher_id  =  T2.teacher_id WHERE T2.last_name  =  "Schuster"
Database: behavior_monitoring (tables: behavior_incident, students)
Question: Find the start and end dates of behavior incidents of students with last name "Fahey".
SQL: SELECT T1.date_incident_start ,  date_incident_end FROM Behavior_Incident AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id WHERE T2.last_name  =  "Fahey"
Database: behavior_monitoring (tables: detention, teachers)
Question: Find the start and end dates of detentions of teachers with last name "Schultz".
SQL: SELECT T1.datetime_detention_start ,  datetime_detention_end FROM Detention AS T1 JOIN Teachers AS T2 ON T1.teacher_id  =  T2.teacher_id WHERE T2.last_name  =  "Schultz"
Database: behavior_monitoring (tables: addresses, student_addresses)
Question: What are the id and zip code of the address with the highest monthly rental?
SQL: SELECT T2.address_id ,  T1.zip_postcode FROM Addresses AS T1 JOIN Student_Addresses AS T2 ON T1.address_id  =  T2.address_id ORDER BY monthly_rental DESC LIMIT 1
Database: behavior_monitoring (tables: student_addresses, students)
Question: What is the cell phone number of the student whose address has the lowest monthly rental?
SQL: SELECT T2.cell_mobile_number FROM Student_Addresses AS T1 JOIN Students AS T2 ON T1.student_id  =  T2.student_id ORDER BY T1.monthly_rental ASC LIMIT 1
Database: behavior_monitoring (tables: addresses, student_addresses)
Question: What are the monthly rentals of student addresses in Texas state?
SQL: SELECT T2.monthly_rental FROM Addresses AS T1 JOIN Student_Addresses AS T2 ON T1.address_id  =  T2.address_id WHERE T1.state_province_county  =  "Texas"
Database: behavior_monitoring (tables: addresses, students)
Question: What are the first names and last names of students with address in Wisconsin state?
SQL: SELECT T2.first_name ,  T2.last_name FROM Addresses AS T1 JOIN Students AS T2 ON T1.address_id  =  T2.address_id WHERE T1.state_province_county  =  "Wisconsin"
Database: behavior_monitoring (tables: addresses, student_addresses)
Question: What are the line 1 and average monthly rentals of all student addresses?
SQL: SELECT T1.line_1 ,  avg(T2.monthly_rental) FROM Addresses AS T1 JOIN Student_Addresses AS T2 ON T1.address_id  =  T2.address_id GROUP BY T2.address_id
Database: behavior_monitoring (tables: addresses, teachers)
Question: What is the zip code of the address where the teacher with first name "Lyla" lives?
SQL: SELECT T1.zip_postcode FROM Addresses AS T1 JOIN Teachers AS T2 ON T1.address_id  =  T2.address_id WHERE T2.first_name  =  "Lyla"
Database: behavior_monitoring (tables: addresses, teachers)
Question: What are the email addresses of teachers whose address has zip code "918"?
SQL: SELECT T2.email_address FROM Addresses AS T1 JOIN Teachers AS T2 ON T1.address_id  =  T2.address_id WHERE T1.zip_postcode  =  "918"
Database: behavior_monitoring (tables: students, behavior_incident)
Question: How many students are not involved in any behavior incident?
SQL: SELECT count(*) FROM STUDENTS WHERE student_id NOT IN ( SELECT student_id FROM Behavior_Incident )
Database: behavior_monitoring (tables: detention, teachers)
Question: Find the last names of teachers who are not involved in any detention.
SQL: SELECT last_name FROM Teachers EXCEPT SELECT T1.last_name FROM Teachers AS T1 JOIN Detention AS T2 ON T1.teacher_id  =  T2.teacher_id
Database: behavior_monitoring (tables: addresses, teachers, students)
Question: What are the line 1 of addresses shared by some students and some teachers?
SQL: SELECT T1.line_1 FROM Addresses AS T1 JOIN Students AS T2 ON T1.address_id  =  T2.address_id INTERSECT SELECT T1.line_1 FROM Addresses AS T1 JOIN Teachers AS T2 ON T1.address_id  =  T2.address_id
Database: assets_maintenance (tables: assets, asset_parts, fault_log)
Question: Which assets have 2 parts and have less than 2 fault logs? List the asset id and detail.
SQL: SELECT T1.asset_id ,  T1.asset_details FROM Assets AS T1 JOIN Asset_Parts AS T2 ON T1.asset_id  =  T2.asset_id GROUP BY T1.asset_id HAVING count(*)  =  2 INTERSECT SELECT T1.asset_id ,  T1.asset_details FROM Assets AS T1 JOIN Fault_Log AS T2 ON T1.asset_id  =  T2.asset_id GROUP BY T1.asset_id HAVING count(*)  <  2
Database: assets_maintenance (tables: assets, maintenance_contracts)
Question: How many assets does each maintenance contract contain? List the number and the contract id.
SQL: SELECT count(*) ,  T1.maintenance_contract_id FROM Maintenance_Contracts AS T1 JOIN Assets AS T2 ON T1.maintenance_contract_id  =  T2.maintenance_contract_id GROUP BY T1.maintenance_contract_id
Database: assets_maintenance (tables: assets, third_party_companies)
Question: How many assets does each third party company supply? List the count and the company id.
SQL: SELECT count(*) ,  T1.company_id FROM Third_Party_Companies AS T1 JOIN Assets AS T2 ON T1.company_id  =  T2.supplier_company_id GROUP BY T1.company_id
Database: assets_maintenance (tables: maintenance_contracts, third_party_companies, maintenance_engineers)
Question: Which third party companies have at least 2 maintenance engineers or have at least 2 maintenance contracts? List the company id and name.
SQL: SELECT T1.company_id ,  T1.company_name FROM Third_Party_Companies AS T1 JOIN Maintenance_Engineers AS T2 ON T1.company_id  =  T2.company_id GROUP BY T1.company_id HAVING count(*)  >=  2 UNION SELECT T3.company_id ,  T3.company_name FROM Third_Party_Companies AS T3 JOIN Maintenance_Contracts AS T4 ON T3.company_id  =  T4.maintenance_contract_company_id GROUP BY T3.company_id HAVING count(*)  >=  2
Database: assets_maintenance (tables: staff, engineer_visits, fault_log)
Question: What is the name and id of the staff who recorded the fault log but has not contacted any visiting engineers?
SQL: SELECT T1.staff_name ,  T1.staff_id FROM Staff AS T1 JOIN Fault_Log AS T2 ON T1.staff_id  =  T2.recorded_by_staff_id EXCEPT SELECT T3.staff_name ,  T3.staff_id FROM Staff AS T3 JOIN Engineer_Visits AS T4 ON T3.staff_id  =  T4.contact_staff_id
Database: assets_maintenance (tables: engineer_visits, maintenance_engineers)
Question: Which engineer has visited the most times? Show the engineer id, first name and last name.
SQL: SELECT T1.engineer_id ,  T1.first_name ,  T1.last_name FROM Maintenance_Engineers AS T1 JOIN Engineer_Visits AS T2 GROUP BY T1.engineer_id ORDER BY count(*) DESC LIMIT 1
Database: assets_maintenance (tables: part_faults, parts)
Question: Which parts have more than 2 faults? Show the part name and id.
SQL: SELECT T1.part_name ,  T1.part_id FROM Parts AS T1 JOIN Part_Faults AS T2 ON T1.part_id  =  T2.part_id GROUP BY T1.part_id HAVING count(*)  >  2
Database: assets_maintenance (tables: skills, engineer_skills, maintenance_engineers)
Question: List all every engineer's first name, last name, details and coresponding skill description.
SQL: SELECT T1.first_name ,  T1.last_name ,  T1.other_details ,  T3.skill_description FROM Maintenance_Engineers AS T1 JOIN Engineer_Skills AS T2 ON T1.engineer_id  =  T2.engineer_id JOIN Skills AS T3 ON T2.skill_id  =  T3.skill_id
Database: assets_maintenance (tables: part_faults, skills, skills_required_to_fix)
Question: For all the faults of different parts, what are all the decriptions of the skills required to fix them? List the name of the faults and the skill description.
SQL: SELECT T1.fault_short_name ,  T3.skill_description FROM Part_Faults AS T1 JOIN Skills_Required_To_Fix AS T2 ON T1.part_fault_id  =  T2.part_fault_id JOIN Skills AS T3 ON T2.skill_id  =  T3.skill_id
Database: assets_maintenance (tables: asset_parts, parts)
Question: How many assets can each parts be used in? List the part name and the number.
SQL: SELECT T1.part_name ,  count(*) FROM Parts AS T1 JOIN Asset_Parts AS T2 ON T1.part_id  =  T2.part_id GROUP BY T1.part_name
Database: assets_maintenance (tables: fault_log_parts, fault_log)
Question: What are all the fault descriptions and the fault status of all the faults recoreded in the logs?
SQL: SELECT T1.fault_description ,  T2.fault_status FROM Fault_Log AS T1 JOIN Fault_Log_Parts AS T2 ON T1.fault_log_entry_id  =  T2.fault_log_entry_id
Database: assets_maintenance (tables: engineer_visits, fault_log)
Question: How many engineer visits are required at most for a single fault log? List the number and the log entry id.
SQL: SELECT count(*) ,  T1.fault_log_entry_id FROM Fault_Log AS T1 JOIN Engineer_Visits AS T2 ON T1.fault_log_entry_id  =  T2.fault_log_entry_id GROUP BY T1.fault_log_entry_id ORDER BY count(*) DESC LIMIT 1
Database: assets_maintenance (tables: maintenance_engineers)
Question: What are all the distinct last names of all the engineers?
SQL: SELECT DISTINCT last_name FROM Maintenance_Engineers
Database: assets_maintenance (tables: fault_log_parts)
Question: How many fault status codes are recorded in the fault log parts table?
SQL: SELECT DISTINCT fault_status FROM Fault_Log_Parts
Database: assets_maintenance (tables: engineer_visits, maintenance_engineers)
Question: Which engineers have never visited to maintain the assets? List the engineer first name and last name.
SQL: SELECT first_name ,  last_name FROM Maintenance_Engineers WHERE engineer_id NOT IN (SELECT engineer_id FROM Engineer_Visits)
Database: assets_maintenance (tables: assets)
Question: List the asset id, details, make and model for every asset.
SQL: SELECT asset_id ,  asset_details ,  asset_make ,  asset_model FROM Assets
Database: assets_maintenance (tables: assets)
Question: When was the first asset acquired?
SQL: SELECT asset_acquired_date FROM Assets ORDER BY asset_acquired_date ASC LIMIT 1
Database: assets_maintenance (tables: part_faults, skills_required_to_fix, parts)
Question: Which part fault requires the most number of skills to fix? List part id and name.
SQL: SELECT T1.part_id ,  T1.part_name FROM Parts AS T1 JOIN Part_Faults AS T2 ON T1.part_id  =  T2.part_id JOIN Skills_Required_To_Fix AS T3 ON T2.part_fault_id  =  T3.part_fault_id GROUP BY T1.part_id ORDER BY count(*) DESC LIMIT 1
Database: assets_maintenance (tables: part_faults, parts)
Question: Which kind of part has the least number of faults? List the part name.
SQL: SELECT T1.part_name FROM Parts AS T1 JOIN Part_Faults AS T2 ON T1.part_id  =  T2.part_id GROUP BY T1.part_name ORDER BY count(*) ASC LIMIT 1
Database: assets_maintenance (tables: engineer_visits, maintenance_engineers)
Question: Among those engineers who have visited, which engineer makes the least number of visits? List the engineer id, first name and last name.
SQL: SELECT T1.engineer_id ,  T1.first_name ,  T1.last_name FROM Maintenance_Engineers AS T1 JOIN Engineer_Visits AS T2 ON T1.engineer_id  =  T2.engineer_id GROUP BY T1.engineer_id ORDER BY count(*) ASC LIMIT 1
Database: assets_maintenance (tables: staff, engineer_visits, maintenance_engineers)
Question: Which staff have contacted which engineers? List the staff name and the engineer first name and last name.
SQL: SELECT T1.staff_name ,  T3.first_name ,  T3.last_name FROM Staff AS T1 JOIN Engineer_Visits AS T2 ON T1.staff_id  =  T2.contact_staff_id JOIN Maintenance_Engineers AS T3 ON T2.engineer_id  =  T3.engineer_id
Database: assets_maintenance (tables: fault_log_parts, fault_log)
Question: Which fault log included the most number of faulty parts? List the fault log id, description and record time.
SQL: SELECT T1.fault_log_entry_id ,  T1.fault_description ,  T1.fault_log_entry_datetime FROM Fault_Log AS T1 JOIN Fault_Log_Parts AS T2 ON T1.fault_log_entry_id  =  T2.fault_log_entry_id GROUP BY T1.fault_log_entry_id ORDER BY count(*) DESC LIMIT 1
Database: assets_maintenance (tables: skills, skills_required_to_fix)
Question: Which skill is used in fixing the most number of faults? List the skill id and description.
SQL: SELECT T1.skill_id ,  T1.skill_description FROM Skills AS T1 JOIN Skills_Required_To_Fix AS T2 ON T1.skill_id  =  T2.skill_id GROUP BY T1.skill_id ORDER BY count(*) DESC LIMIT 1