shashank-yadav SQL
Write a single SQLite query that answers the question against the given schema. Respond with only the query -- no explanation, no markdown code fences, no semicolon-separated multiple statements.
General Strategies
- Understand the Schema: Carefully examine the provided database schema, noting table names, column names, and data types.
- Identify Relevant Tables: Determine which tables contain the data needed to answer the question.
- Use Appropriate Clauses:
SELECT: Specify the columns you want to retrieve.FROM: Indicate the table(s) to query.WHERE: Filter rows based on specific conditions.GROUP BY: Aggregate rows that have the same values in specified columns.HAVING: Filter groups based on specified conditions.ORDER BY: Sort the result set.LIMIT: Restrict the number of rows returned.
- Join Tables When Necessary: If data is spread across multiple tables, use
JOINclauses (e.g.,INNER JOIN,LEFT JOIN) to combine them based on related columns. Use aliases for table names to simplify queries. - Aggregate Functions: Utilize functions like
COUNT(),SUM(),AVG(),MIN(),MAX()for calculations on groups of rows. - Date and Time Functions: Use functions like
STRFTIME()for date-based filtering and grouping. - String Matching: Employ
LIKEwith wildcards (%,_) for pattern matching in text fields. - Conditional Logic: Use
CASEstatements orWHEREclauses withORorINfor handling multiple conditions. - Subqueries: Use subqueries when a query needs to reference the results of another query.
- Handle NULL Values: Be mindful of
NULLvalues and use appropriate functions (e.g.,COALESCE,IS NULL,IS NOT NULL) if necessary. - Case Sensitivity: Be aware that string comparisons might be case-sensitive depending on the database.
- Data Type Conversion: Use
CAST()when performing operations that require converting data types.
Successful Trajectories
- When a question involves aggregating data based on categories, use
GROUP BYalong with aggregate functions likeSUM()orAVG(). - To filter data based on specific values in a column, use the
WHEREclause with equality operators (=) or inequality operators (!=,<>). - For range-based filtering on dates, use
BETWEENor comparison operators (>,<,>=,<=) with date functions. - To select specific columns, list them after
SELECT. If you need all columns, use*. - When combining data from multiple tables, use
JOINclauses with the appropriateONcondition. - To find the single highest or lowest value, use
MAX()orMIN()respectively, often combined withORDER BYandLIMIT 1. - For filtering based on multiple possible values, use the
INoperator. - To count distinct values, use
COUNT(DISTINCT column_name). - When calculating percentages, ensure you cast one of the operands to a floating-point type before division to avoid integer division.
- To select unique combinations of values, use
SELECT DISTINCT. - For filtering based on a condition that must be true for all rows within a group, use
HAVING COUNT(DISTINCT column_name) = (SELECT COUNT(DISTINCT column_name) FROM other_table). - When filtering by a date range, use
STRFTIME('%Y-%m', date_column) BETWEEN 'start_date' AND 'end_date'. - To select the top N records based on a certain criteria, use
ORDER BY column DESC LIMIT N. - When a question asks for a calculation across multiple years, use
WHERE year IN (year1, year2, ...)orWHERE STRFTIME('%Y', date_column) IN ('year1', 'year2', ...). - To select records based on a condition that can be met by multiple values, use
ORorIN. - When joining tables and filtering based on conditions in both tables, use
INNER JOINand include the relevant conditions in theWHEREclause. - To retrieve data from a specific year, use
WHERE year = YYYYorWHERE STRFTIME('%Y', date_column) = 'YYYY'. - When a question asks for a calculation over a specific period (e.g., H2 2021), use date functions to filter the relevant date range.
- To find the maximum value within groups, use
MAX()withGROUP BY. - To select records that meet a specific criterion, use
WHERE column = 'value'. - To retrieve data from specific months, use
STRFTIME('%m', date_column) = 'MM'. - When a question asks for a calculation based on a boolean column, use
WHERE boolean_column = TRUEorWHERE boolean_column = FALSE. - To select records that satisfy a condition on a date column, use
WHERE STRFTIME('%Y', date_column) = 'YYYY'. - When a question asks for a calculation based on a specific category, use
WHERE category = 'CategoryName'. - To filter records based on a condition that is not equal to a specific value, use
WHERE column != 'value'. - When a question asks for a calculation based on a specific region, use
WHERE region = 'RegionName'. - To retrieve data based on multiple criteria, use
ANDto combineWHEREclauses. - To find the average of a column, use
AVG(column_name). - To select specific columns from a table, list them after
SELECT. - When a question asks for a calculation based on a specific type, use
WHERE type = 'TypeName'. - To retrieve data from a specific city, use
WHERE city = 'CityName'. - When a question asks for a calculation based on a specific country, use
WHERE country = 'CountryName'. - To retrieve data based on a condition that can be met by either of two values, use
OR. - To select all columns from a table, use
*. - When a question asks for a calculation based on a specific sector, use
WHERE sector = 'SectorName'. - To retrieve data based on a condition that is greater than a certain value, use
WHERE column > value. - To retrieve data based on a condition that is less than a certain value, use
WHERE column < value. - When a question asks for a calculation based on a specific status, use
WHERE status = 'StatusName'. - To retrieve data based on a condition that is greater than or equal to a certain value, use
WHERE column >= value. - To retrieve data based on a condition that is less than or equal to a certain value, use
WHERE column <= value. - When a question asks for a calculation based on a specific manufacturer, use
WHERE Manufacturer = 'ManufacturerName'. - To retrieve data based on a condition that is not equal to a specific value, use
WHERE column != 'value'. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is within a specific range, use
WHERE column BETWEEN value1 AND value2. - When a question asks for a calculation based on a specific domain, use
WHERE domain = 'DomainName'. - To retrieve data based on a condition that is like a certain pattern, use
WHERE column LIKE 'pattern'. - When a question asks for a calculation based on a specific purpose, use
WHERE purpose = 'PurposeName'. - To retrieve data based on a condition that is not like a certain pattern, use
WHERE column NOT LIKE 'pattern'. - When a question asks for a calculation based on a specific client gender, use
WHERE client_gender = 'Gender'. - To retrieve data based on a condition that is in a list of values, use
WHERE column IN (value1, value2, ...). - When a question asks for a calculation based on a specific founder gender, use
WHERE founder_gender = 'Gender'. - To retrieve data based on a condition that is not in a list of values, use
WHERE column NOT IN (value1, value2, ...). - When a question asks for a calculation based on a specific incident type, use
WHERE incident_type = 'IncidentType'. - To retrieve data based on a condition that is null, use
WHERE column IS NULL. - When a question asks for a calculation based on a specific region, use
WHERE region = 'RegionName'. - To retrieve data based on a condition that is not null, use
WHERE column IS NOT NULL. - When a question asks for a calculation based on a specific state, use
WHERE state = 'StateName'. - To retrieve data based on a condition that is true, use
WHERE column = TRUE. - When a question asks for a calculation based on a specific year, use
WHERE year = YYYY. - To retrieve data based on a condition that is false, use
WHERE column = FALSE. - When a question asks for a calculation based on a specific department, use
WHERE department = 'DepartmentName'. - To retrieve data based on a condition that is not true, use
WHERE column != TRUE. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is not false, use
WHERE column != FALSE. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is greater than a certain value, use
WHERE column > value. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is less than a certain value, use
WHERE column < value. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is greater than or equal to a certain value, use
WHERE column >= value. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is less than or equal to a certain value, use
WHERE column <= value. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is between two values, use
WHERE column BETWEEN value1 AND value2. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is like a pattern, use
WHERE column LIKE 'pattern'. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is not like a pattern, use
WHERE column NOT LIKE 'pattern'. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is in a list of values, use
WHERE column IN (value1, value2, ...). - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is not in a list of values, use
WHERE column NOT IN (value1, value2, ...). - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is null, use
WHERE column IS NULL. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is not null, use
WHERE column IS NOT NULL. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is true, use
WHERE column = TRUE. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is false, use
WHERE column = FALSE. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is not true, use
WHERE column != TRUE. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is not false, use
WHERE column != FALSE. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is greater than a certain value, use
WHERE column > value. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is less than a certain value, use
WHERE column < value. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is greater than or equal to a certain value, use
WHERE column >= value. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is less than or equal to a certain value, use
WHERE column <= value. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is between two values, use
WHERE column BETWEEN value1 AND value2. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is like a pattern, use
WHERE column LIKE 'pattern'. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is not like a pattern, use
WHERE column NOT LIKE 'pattern'. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is in a list of values, use
WHERE column IN (value1, value2, ...). - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is not in a list of values, use
WHERE column NOT IN (value1, value2, ...). - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is null, use
WHERE column IS NULL. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is not null, use
WHERE column IS NOT NULL. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is true, use
WHERE column = TRUE. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is false, use
WHERE column = FALSE. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is not true, use
WHERE column != TRUE. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is not false, use
WHERE column != FALSE. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is greater than a certain value, use
WHERE column > value. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is less than a certain value, use
WHERE column < value. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is greater than or equal to a certain value, use
WHERE column >= value. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is less than or equal to a certain value, use
WHERE column <= value. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is between two values, use
WHERE column BETWEEN value1 AND value2. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is like a pattern, use
WHERE column LIKE 'pattern'. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is not like a pattern, use
WHERE column NOT LIKE 'pattern'. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is in a list of values, use
WHERE column IN (value1, value2, ...). - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is not in a list of values, use
WHERE column NOT IN (value1, value2, ...). - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is null, use
WHERE column IS NULL. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is not null, use
WHERE column IS NOT NULL. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is true, use
WHERE column = TRUE. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is false, use
WHERE column = FALSE. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is not true, use
WHERE column != TRUE. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is not false, use
WHERE column != FALSE. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is greater than a certain value, use
WHERE column > value. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is less than a certain value, use
WHERE column < value. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is greater than or equal to a certain value, use
WHERE column >= value. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is less than or equal to a certain value, use
WHERE column <= value. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is between two values, use
WHERE column BETWEEN value1 AND value2. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is like a pattern, use
WHERE column LIKE 'pattern'. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is not like a pattern, use
WHERE column NOT LIKE 'pattern'. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is in a list of values, use
WHERE column IN (value1, value2, ...). - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is not in a list of values, use
WHERE column NOT IN (value1, value2, ...). - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is null, use
WHERE column IS NULL. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is not null, use
WHERE column IS NOT NULL. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is true, use
WHERE column = TRUE. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is false, use
WHERE column = FALSE. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is not true, use
WHERE column != TRUE. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is not false, use
WHERE column != FALSE. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is greater than a certain value, use
WHERE column > value. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is less than a certain value, use
WHERE column < value. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is greater than or equal to a certain value, use
WHERE column >= value. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is less than or equal to a certain value, use
WHERE column <= value. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is between two values, use
WHERE column BETWEEN value1 AND value2. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is like a pattern, use
WHERE column LIKE 'pattern'. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is not like a pattern, use
WHERE column NOT LIKE 'pattern'. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is in a list of values, use
WHERE column IN (value1, value2, ...). - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is not in a list of values, use
WHERE column NOT IN (value1, value2, ...). - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is null, use
WHERE column IS NULL. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is not null, use
WHERE column IS NOT NULL. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is true, use
WHERE column = TRUE. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is false, use
WHERE column = FALSE. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition that is not true, use
WHERE column != TRUE. - When a question asks for a calculation based on a specific location, use
WHERE location = 'LocationName'. - To retrieve data based on a condition that is not false, use
WHERE column != FALSE. - When a question asks for a calculation based on a specific common name, use
WHERE common_name = 'CommonName'. - To retrieve data based on a condition