SQL (Structured Query Language) is how crime analysts ask questions about a database and this guide teaches users how to use SQL to analyze a fictional offender and incident-report data. This guide includes six mini lessons, then practice problems with an answer key. Every query below was run against the fictional database and the results shown are the real output.
- A free tool that connects to SQL Server, such as SQL Server Management Studio (SSMS) or Azure Data Studio, also a SQL Server instance you can write to.
- The practice script
cj_practice_sqlserver.sql. Open the file in a query window and run it once. Doing so will create a database calledcj_practiceand builds and fills all four tables. - After that, open a new query window. Make sure that
cj_practiceis the selected database (or start with USEcj_practice;), paste a query and press execute (F5).
This guide uses SQL Server's dialect, T-SQL. PostgreSQL, MySQL and SQLite read almost the same. The differences are small and flagged where they matter (mainly limiting rows with TOP versus LIMIT, and date functions).
Everything in this database is randomly generated. No row describes a real person, case or agency.
| Table | One row is | Rows | Columns |
|---|---|---|---|
| Offenders | One person | 60 | offender_id, first_name, last_name, birth_date, sex, supervision_type (Parole, Probation, None), risk_level (Low, Medium, High, or blank), prior_convictions, home_district |
| incidents | One reported incident | 200 | incident_id, report_date, offense_type, severity, district, location_type, weapon_involved (1 = yes, 0 = no), status, officer_id |
| arrests | One person arrested for one incident | 103 | arrest_id, incident_id, offender_id, arrest_date, charge, disposition |
| officers | one officer | 15 | officer_id, last_name, rank, district |
The tables connect through ID columns: arrests.incident_id points to incidents, arrests.offender_id points to offenders, and incidents.officer_id points to officers. The arrests table is the bridge between people and incidents, which matters in Lesson 5.
- End each query with a semicolon. SQL Server does not always require it, but it is good practice and it needed before a
WITHclause. - Text values go in single quotes:
Burglary. Numbers do not1. - Keywords are not case sensitive, but writing them in capitals makes queries easier to read.
--starts a comment that SQL ignores.- Clauses always appear in this order:
SELECT(withTOPif you want to limit rows),FROM,WHERE,GROUP BY,HAVING, ORDER BY```.
Every query starts with two questions: which columns do you want (SELECT), and from which table (FROM)?
SELECT TOP 5 first_name, last_name, risk_level
FROM offenders;| first_name | last_name | risk_level |
|---|---|---|
| Kevin | Ibarra | Medium |
| Lena | Turner | Low |
| Carlos | Reyes | NULL |
| Brianna | Vega | High |
| Omar | Underwood | Medium |
TOP 5 stops the output at five rows, a good habit when exploring a table with thousand of records. Without an ORDER BY (Lesson 3), SQL Server does not promise which five rows come back, so yours may differ from the table. NULL means the value is missing (here, no risk level was assigned). NULL is not the same as zero or an empty word, and Lesson 2 shows how to handle it.
SELECT TOP 5 *
FROM uncidents;The star returns every column. It is handy for a first look at a table, but in real work name the columns you need.
An alias gives a column a cleaner name in your results, which matters when the output goes into a report or dashboard. Giving columns an alias is also done for organizational purposes.
SELECT TOP 3 first_name AS [First Name],
prior_convictions AS priors
FROM offenders;DISTINCT returns each value once. Use it to see what categories a column holds.
SELECT DISTINCT district
FROM incidents;| district |
|---|
| South |
| Central |
| East |
| West |
| North |
To count the categories instead of listing them, wrap the column:
SELECT COUNT(DISTINCT offense_type)
FROM incidents;This returns a count of 10.
- Show
offense_type,report_date, anddistrictfor the first 10 incidents. - Lis every distinct
location_typein incidents. - Show all columns for the first 3 officers.
Answers are in the answer key.
WHERE keeps only the rows that meet a condition, which is how you narrow 200 incidents down to the ones you care about.
SELECT TOP 5 incident_id, report_date, district
FROM incidents
WHERE offense_type = 'Burglary'
ORDER BY incident_id;| incident_id | report_date | district |
|---|---|---|
| 13 | 2025-10-01 | West |
| 17 | 2025-11-30 | North |
| 39 | 2025-09-02 | North |
| 55 | 2025-08-21 | East |
| 81 | 2025-05-21 | North |
Swap TOP 5 and the column list for COUNT(*) and the same filter shows there are 18 burglaries in the data. Spelling and spaces must match; capitals usually do not matter in SQL Server's default setup.
AND requires all conditions to be true for a record to be included. OR requires at least one condition to be true.
SELECT incident_id, report_date, offense_type
FROM incidents
WHERE district = 'North'
AND weapon_involved = 1;This returns 6 incidents; the first rows are:
| incident_id | report_date | offense_type |
|---|---|---|
| 20 | 2025-03-25 | Weapons Violation |
| 124 | 2025-07-22 | Weapons Violation |
| 125 | 2025-05-11 | Simple Assault |
| 139 | 2025-10-11 | Weapons Violation |
| 161 | 2025-08-18 | Larceny |
When you mix AND with OR, SQL evaluates AND before OR. Always use parentheses to explicitly group your conditions so the database executes your intended logic:
WHERE district = 'North' AND (offense_type = 'Robbery' OR offense_type = 'Burglary')is a different question from the same line without the parentheses.
| Tool | What it does | Example | Rows |
|---|---|---|---|
IN |
Matches any value in a list | WHERE offense_type IN ('Robbery', 'Aggravated Assault') |
23 |
BETWEEN |
Matches a range, ends included | WHERE report_date BETWEEN '2025-06-01' AND '2025-08-31' |
51 |
| ```LIKE`` | Matches a text pattern; % means any characters |
WHERE offense_type LIKE '%Assault% |
36 |
!= or <> |
Not equal | WHERE status != 'Open' |
The BETWEEN example is the summer 2025 incidents. In the practice database report_date is a DATE column; write date values as 'YYYY-MM-DD' and SQL Server reads them correctly.
IS NULL and IS NOT NULL are operators used to test whether a column or expression contains a missing or unknown value.
SELECT offender_id, last_name, supervision_type
FROM offenders
WHERE risk_level IS NULL;This returns 8 offenders who were never assigned a risk level, such as Reyes (id 3, Parole) and Turner (id 16, no supervision). In real records, checking for NULLs before you count anything is basic data hygiene.
- Find all incidents with
severity = 'Felony'in theEastdistrict. - Find offenders on Parole with a
risk_levelof High. - Count incidents whose
location_typeis Residence or Vehicle. - List arrests where
dispositionis Dismissed or Pending.
ORDER BY sorts your query results and TOP limits the number of rows returned, which together answer, "who has the most?" or "what happened most recently?" Together, they let you retrieve the highest, lowest, or most recent records from a table.
SELECT TOP 5 offender_id, first_name, last_name,
supervision_type, prior_convictions
FROM offenders
ORDER BY prior_convictions DESC, last_name ASC;| offender_id | first_name | last_name | supervision_type | prior_convictions |
|---|---|---|---|---|
| 49 | Kevin | Ibarra | Parole | 7 |
| 12 | Omar | Quinn | Parole | 7 |
| 14 | Aaron | Young | Parole | 7 |
| 38 | Quentin | Alvarez | Probation | 6 |
| 29 | Aaron | Mason | None | 6 |
DESCsorts high to low;ASC(the default) sorts low to high.- Add a second column after a comma to break ties. Three offenders tie at 7 priors, so
last_nameputs them in alphabetical order. - Sorting dates newest first (
ORDER BY report_date DESC) is the quickest way to see the latest incidents.
In SQL Server, TOP 5 goes right after SELECT. PostgreSQL, MySQL and SQLite put LIMIT 5 at the end of the query instead; the idea is identical.
- Show the 10 most recent incidents with their
offense_typeanddistrict. - List offenders from oldest to youngest by birth_date, showing name and birth date, 5 rows.
- Show the 5 earliest arrests by
arrest_date, withchargeanddisposition.
Most crime-analysis questions are counts and rates ("how many, where, when?"), and aggregate functions answer them by collapsing many rows into one number.
| Function | Returns |
|---|---|
COUNT(*) |
Number of rows |
COUNT(column) |
Total of non-NULL values in the column |
SUM(column) |
Total |
AVG(column) |
Average |
MIN(column), MAX(column) |
Smallest and largest value |
SELECT COUNT(*) AS total_incidents
FROM incidents;The result is one row: 200.
GROUP BY splits the rows into groups and runs the aggregate on each group. This is the "incidents by offense type" table every analyst builds.
SELECT offense_type, COUNT(*) AS incident_count
FROM incidents
GROUP BY offense_type
ORDER BY incident_count DESC;| offense_type | incident_count |
|---|---|
| Larceny | 34 |
| Vandalism | 28 |
| Simple Assault | 25 |
| Drug Possession | 25 |
| Motor Vehicle Theft | 22 |
| Burglary | 18 |
| Domestic Violence | 16 |
| Robbery | 12 |
| Aggravated Assault | 11 |
| Weapons Violation | 9 |
The rule to remember: every column in SELECT must either be inside an aggregate function or appear in GROUP BY. Change the grouping column to district and the same pattern gives West 48, Central 45, South 38, East 38 and North 31.