SQL for Cybersecurity Analysts: Query Security Data Step by Step

Learn the six SQL building blocks analysts use to investigate incidents: SELECT, WHERE, AND/OR/NOT, LIKE, JOIN and GROUP BY. Every example uses small sample security tables written right here, so you can run them yourself. Ends with 20 investigation-style practice queries and answers.

Independent study aid. Not affiliated with or endorsed by Google or Coursera. All explanations, sample data, examples and practice queries are original. Confirm current course content on the official Coursera page.

1. Why analysts use SQL

Security teams store logins, device inventories, alerts and access records in databases. SQL (Structured Query Language) lets you ask precise questions of that data: who failed to log in, from where, how often, and on whose device.

Core idea

Pick the columns you want, from which table, keeping only the rows that match your conditions.

Read-only first

Everything on this page uses SELECT, which only reads data. It never changes or deletes anything.

Transferable

The thinking carries over to SIEM and log search tools, which use their own query languages but ask the same questions.

2. The sample tables

All data is invented. IP addresses come from ranges reserved for documentation. Times are in one day, 1 October 2026.

Table: employees (8 rows)

employee_id	username	name	department	office
1	arao	Asha Rao	Finance	Bengaluru
2	bcarter	Ben Carter	IT	London
3	cwei	Chen Wei	Sales	Singapore
4	dsmith	Dana Smith	HR	London
5	ebrown	Eli Brown	Finance	Bengaluru
6	fkhan	Fatima Khan	IT	Bengaluru
7	gmiller	Gus Miller	Sales	London
8	hsato	Hana Sato	Engineering	Tokyo

Table: devices (9 rows)

device_id	employee_id	device_name	os	status
1	1	FIN-LT-01	Windows	active
2	2	IT-LT-02	Linux	active
3	3	SAL-LT-03	macOS	active
4	4	HR-LT-04	Windows	active
5	5	FIN-LT-05	Windows	retired
6	6	IT-LT-06	Linux	active
7	7	SAL-LT-07	Windows	active
8	NULL	SRV-WEB-01	Linux	active
9	8	ENG-LT-09	Linux	active

Device 8 is a shared server, so it has no owner. NULL means “no value”.

Table: login_attempts (20 rows)

attempt_id	username	source_ip	attempt_time	result	country
1	arao	198.51.100.10	2026-10-01 08:55:02	success	IN
2	bcarter	203.0.113.20	2026-10-01 09:01:15	success	GB
3	admin	192.0.2.50	2026-10-01 02:10:01	failure	RU
4	admin	192.0.2.50	2026-10-01 02:10:05	failure	RU
5	root	192.0.2.50	2026-10-01 02:10:09	failure	RU
6	ebrown	192.0.2.77	2026-10-01 03:30:12	failure	BR
7	ebrown	192.0.2.77	2026-10-01 03:30:20	failure	BR
8	ebrown	192.0.2.77	2026-10-01 03:30:28	failure	BR
9	ebrown	192.0.2.77	2026-10-01 03:30:40	success	BR
10	cwei	198.51.100.31	2026-10-01 09:15:44	success	SG
11	dsmith	203.0.113.21	2026-10-01 09:20:03	failure	GB
12	dsmith	203.0.113.21	2026-10-01 09:20:30	success	GB
13	fkhan	198.51.100.12	2026-10-01 10:05:10	success	IN
14	gmiller	203.0.113.44	2026-10-01 10:40:00	success	GB
15	test	192.0.2.50	2026-10-01 02:10:13	failure	RU
16	hsato	198.51.100.90	2026-10-01 11:00:21	success	JP
17	arao	198.51.100.10	2026-10-01 14:22:09	success	IN
18	gmiller	203.0.113.44	2026-10-01 23:48:50	failure	GB
19	gmiller	203.0.113.44	2026-10-01 23:49:02	failure	GB
20	bcarter	203.0.113.20	2026-10-01 17:30:41	success	GB

How the tables connect: login_attempts.username matches employees.username, and devices.employee_id matches employees.employee_id. These shared columns are what a JOIN uses.

3. Run the examples yourself

You can use the free DB Browser for SQLite or the sqlite3 command-line tool from sqlite.org. Paste the script below into an empty database, run it, then try the queries.

Setup script (creates and fills all three tables)

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  username TEXT, name TEXT, department TEXT, office TEXT
);
INSERT INTO employees VALUES
(1,'arao','Asha Rao','Finance','Bengaluru'),
(2,'bcarter','Ben Carter','IT','London'),
(3,'cwei','Chen Wei','Sales','Singapore'),
(4,'dsmith','Dana Smith','HR','London'),
(5,'ebrown','Eli Brown','Finance','Bengaluru'),
(6,'fkhan','Fatima Khan','IT','Bengaluru'),
(7,'gmiller','Gus Miller','Sales','London'),
(8,'hsato','Hana Sato','Engineering','Tokyo');

CREATE TABLE devices (
  device_id INTEGER PRIMARY KEY,
  employee_id INTEGER, device_name TEXT, os TEXT, status TEXT
);
INSERT INTO devices VALUES
(1,1,'FIN-LT-01','Windows','active'),
(2,2,'IT-LT-02','Linux','active'),
(3,3,'SAL-LT-03','macOS','active'),
(4,4,'HR-LT-04','Windows','active'),
(5,5,'FIN-LT-05','Windows','retired'),
(6,6,'IT-LT-06','Linux','active'),
(7,7,'SAL-LT-07','Windows','active'),
(8,NULL,'SRV-WEB-01','Linux','active'),
(9,8,'ENG-LT-09','Linux','active');

CREATE TABLE login_attempts (
  attempt_id INTEGER PRIMARY KEY,
  username TEXT, source_ip TEXT, attempt_time TEXT, result TEXT, country TEXT
);
INSERT INTO login_attempts VALUES
(1,'arao','198.51.100.10','2026-10-01 08:55:02','success','IN'),
(2,'bcarter','203.0.113.20','2026-10-01 09:01:15','success','GB'),
(3,'admin','192.0.2.50','2026-10-01 02:10:01','failure','RU'),
(4,'admin','192.0.2.50','2026-10-01 02:10:05','failure','RU'),
(5,'root','192.0.2.50','2026-10-01 02:10:09','failure','RU'),
(6,'ebrown','192.0.2.77','2026-10-01 03:30:12','failure','BR'),
(7,'ebrown','192.0.2.77','2026-10-01 03:30:20','failure','BR'),
(8,'ebrown','192.0.2.77','2026-10-01 03:30:28','failure','BR'),
(9,'ebrown','192.0.2.77','2026-10-01 03:30:40','success','BR'),
(10,'cwei','198.51.100.31','2026-10-01 09:15:44','success','SG'),
(11,'dsmith','203.0.113.21','2026-10-01 09:20:03','failure','GB'),
(12,'dsmith','203.0.113.21','2026-10-01 09:20:30','success','GB'),
(13,'fkhan','198.51.100.12','2026-10-01 10:05:10','success','IN'),
(14,'gmiller','203.0.113.44','2026-10-01 10:40:00','success','GB'),
(15,'test','192.0.2.50','2026-10-01 02:10:13','failure','RU'),
(16,'hsato','198.51.100.90','2026-10-01 11:00:21','success','JP'),
(17,'arao','198.51.100.10','2026-10-01 14:22:09','success','IN'),
(18,'gmiller','203.0.113.44','2026-10-01 23:48:50','failure','GB'),
(19,'gmiller','203.0.113.44','2026-10-01 23:49:02','failure','GB'),
(20,'bcarter','203.0.113.20','2026-10-01 17:30:41','success','GB');

Dialect note: this page uses standard SQL that works in SQLite, MySQL, PostgreSQL and BigQuery with small differences. The main one is LIKE: it ignores upper and lower case in SQLite by default, depends on collation in MySQL, and is case-sensitive in PostgreSQL (which offers ILIKE). Quote rules also vary slightly, so check your database’s documentation.

Practise only on your own sample data. Real security databases hold personal and sensitive information. Query real systems only when your role allows it, and never copy real user data into notes, portfolios or public pages.

4. SELECT: choose columns and tables

SELECT says which columns you want. FROM says which table. Use * for all columns.

Syntax and examples

SELECT column1, column2
FROM table_name;

Example: pick two columns

SELECT name, department
FROM employees
LIMIT 3;

name	department
Asha Rao	Finance
Ben Carter	IT
Chen Wei	Sales

LIMIT 3 returns only the first three rows. (SQL Server uses TOP instead.)

ORDER BY sorts results: ORDER BY attempt_time, or add DESC for newest or largest first.
DISTINCT removes duplicates: SELECT DISTINCT country FROM login_attempts; returns RU, BR, GB, SG, IN and JP (each once).
AS renames a column in the output: COUNT(*) AS failures.
Prefer naming columns over * in real work. It is faster and shows only what you need.

5. WHERE: keep only matching rows

WHERE filters rows before they are returned. Text values go in single quotes. Numbers do not.

Comparison operators and examples

Operator	Meaning	Example
`=`	Equal to	`result = 'failure'`
`<>` or `!=`	Not equal to	`country <> 'GB'`
`>`, `<`, `>=`, `<=`	Greater or less than	`attempt_id > 15`
`BETWEEN a AND b`	Within a range, inclusive	`attempt_time BETWEEN '2026-10-01 02:00:00' AND '2026-10-01 03:59:59'`
`IN (...)`	Matches any value in a list	`country IN ('RU','BR')`
`IS NULL`	Value is missing	`employee_id IS NULL`

Example: every attempt from Brazil

SELECT attempt_id, username, result, country
FROM login_attempts
WHERE country = 'BR';

attempt_id	username	result	country
6	ebrown	failure	BR
7	ebrown	failure	BR
8	ebrown	failure	BR
9	ebrown	success	BR

Notice the pattern: three failures, then a success. That is worth a closer look.

NULL is special. Writing employee_id = NULL never matches anything. Always use IS NULL or IS NOT NULL.

6. AND, OR, NOT: combine conditions

Operator	Keeps a row when
`AND`	Both conditions are true
`OR`	At least one condition is true
`NOT`	The condition is false

Examples, including the parentheses trap

Example: failures from Russia or Brazil

SELECT attempt_id, username, country
FROM login_attempts
WHERE result = 'failure'
  AND (country = 'RU' OR country = 'BR');

Returns 7 rows: attempts 3, 4, 5, 15 (Russia) and 6, 7, 8 (Brazil).

The trap: missing parentheses

SELECT attempt_id, username, result, country
FROM login_attempts
WHERE result = 'failure' AND country = 'RU'
   OR country = 'BR';

AND is evaluated before OR, so this means “(failure AND RU) OR BR”. It returns 8 rows, including the successful Brazil login (attempt 9), which is not what was intended. When mixing AND with OR, always add parentheses.

Example: NOT

SELECT DISTINCT username
FROM login_attempts
WHERE NOT country = 'GB';

Returns every username with at least one attempt from outside the UK: arao, admin, root, ebrown, cwei, fkhan, test, hsato.

7. LIKE: match patterns in text

Use LIKE when you know part of a value. Two wildcards matter: % stands for any number of characters (including none), and _ stands for exactly one character.

Patterns and examples

Pattern	Matches
`'FIN%'`	Starts with FIN (FIN-LT-01)
`'%LT%'`	Contains LT anywhere
`'%-01'`	Ends with -01
`'_dmin'`	Any single character, then dmin (admin)
`'192.0.2.%'`	Any address starting 192.0.2.

Example: usernames starting with “a”

SELECT attempt_id, username
FROM login_attempts
WHERE username LIKE 'a%';

Returns 4 rows: attempts 1 and 17 (arao) and attempts 3 and 4 (admin). Real investigations often use LIKE to catch a whole network range or a family of device names.

Add NOT to invert: username NOT LIKE 'a%'. A pattern with a leading % can be slow on very large tables, so use it thoughtfully.

8. JOIN: combine tables

A JOIN lines up rows from two tables using a shared column, written after ON. Short aliases (e for employees, d for devices) keep queries readable and avoid confusion when two tables share a column name.

Join type	Returns	Use it to
`INNER JOIN` (or just `JOIN`)	Only rows that match in both tables	Link logins to known employees
`LEFT JOIN`	All rows from the left table, plus matches (NULL where none)	Find items with no match, such as unknown users or unowned devices

Examples

Example 1: INNER JOIN

SELECT e.name, d.device_name
FROM employees e
JOIN devices d ON e.employee_id = d.employee_id
WHERE e.department = 'IT';

name	device_name
Ben Carter	IT-LT-02
Fatima Khan	IT-LT-06

Example 2: LEFT JOIN keeps unmatched rows

SELECT d.device_name, e.name
FROM devices d
LEFT JOIN employees e ON d.employee_id = e.employee_id
WHERE d.os = 'Linux'
ORDER BY d.device_id;

device_name	name
IT-LT-02	Ben Carter
IT-LT-06	Fatima Khan
SRV-WEB-01	NULL
ENG-LT-09	Hana Sato

An INNER JOIN would silently drop SRV-WEB-01, hiding an unowned server. Choosing the right join can change your findings.

Spotting unmatched rows: after a LEFT JOIN, add WHERE e.employee_id IS NULL to list only left-table rows with no partner.

9. GROUP BY: count and summarise

GROUP BY collapses rows that share a value into one row, so you can count or total them. This is how you turn a pile of log lines into a pattern.

Function	Does
`COUNT(*)`	Counts rows in each group
`COUNT(DISTINCT col)`	Counts unique values
`MIN(col)` / `MAX(col)`	Earliest or latest, smallest or largest
`SUM(col)` / `AVG(col)`	Total or average of numbers

Examples, HAVING, and clause order

Example: successes and failures

SELECT result, COUNT(*) AS total
FROM login_attempts
GROUP BY result;

result	total
failure	10
success	10

Example: filter groups with HAVING

SELECT username, COUNT(*) AS failures
FROM login_attempts
WHERE result = 'failure'
GROUP BY username
HAVING COUNT(*) >= 2
ORDER BY failures DESC, username;

username	failures
ebrown	3
admin	2
gmiller	2

WHERE vs HAVING: WHERE filters individual rows before grouping. HAVING filters the groups after counting.
Rule: every column in SELECT that is not inside a function like COUNT must appear in GROUP BY.
Clause order: SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT.

10. Twenty investigation-style practice queries

Each task is a small question from a pretend investigation. Write your own query first, then open the answer. Results are shown after each query. Row order may differ in your tool unless an ORDER BY is given.

Q1. List everyone in the employees table.

SELECT * FROM employees;

Result: all 8 rows with all 5 columns.

Q2. Show only the username and department of each employee, sorted by department then username.

SELECT username, department
FROM employees
ORDER BY department, username;

Result (8 rows, in this order): hsato (Engineering), arao (Finance), ebrown (Finance), dsmith (HR), bcarter (IT), fkhan (IT), cwei (Sales), gmiller (Sales).

Q3. Find every failed login attempt.

SELECT *
FROM login_attempts
WHERE result = 'failure';

Result: 10 rows (attempt_id 3, 4, 5, 6, 7, 8, 11, 15, 18, 19).

Q4. Find failed attempts that came from Russia (country RU).

SELECT attempt_id, username, source_ip
FROM login_attempts
WHERE result = 'failure' AND country = 'RU';

Result: 4 rows: attempts 3 and 4 (admin), 5 (root) and 15 (test), all from 192.0.2.50.

Q5. Which failed attempts happened between midnight and 05:59:59 on 1 October 2026?

SELECT attempt_id, username, attempt_time
FROM login_attempts
WHERE result = 'failure'
  AND attempt_time BETWEEN '2026-10-01 00:00:00' AND '2026-10-01 05:59:59'
ORDER BY attempt_time;

Result: 7 rows: attempts 3, 4, 5, 15 (all about 02:10) and 6, 7, 8 (about 03:30). Overnight bursts are a classic sign of automated activity.

Q6. List failed attempts that did not come from the UK (GB).

SELECT attempt_id, username, country
FROM login_attempts
WHERE result = 'failure' AND NOT country = 'GB';

Result: 7 rows: attempts 3, 4, 5, 6, 7, 8 and 15 (four from RU and three from BR). country <> 'GB' gives the same result.

Q7. Find all attempts that targeted the generic account names admin or root.

SELECT attempt_id, username, source_ip, result
FROM login_attempts
WHERE username = 'admin' OR username = 'root';

Result: 3 rows: attempts 3, 4 (admin) and 5 (root), all failures from 192.0.2.50. Shorter form: WHERE username IN ('admin','root').

Q8. List devices whose name starts with FIN.

SELECT device_name, status
FROM devices
WHERE device_name LIKE 'FIN%';

Result: 2 rows: FIN-LT-01 (active) and FIN-LT-05 (retired).

Q9. Find every login attempt from the 192.0.2.x address range.

SELECT attempt_id, username, source_ip, result
FROM login_attempts
WHERE source_ip LIKE '192.0.2.%';

Result: 8 rows: attempts 3, 4, 5, 6, 7, 8, 9 and 15. Seven are failures and one (attempt 9) is a success.

Q10. How many failed attempts are there in total?

SELECT COUNT(*) AS total_failures
FROM login_attempts
WHERE result = 'failure';

Result: 10.

Q11. Count failed attempts per source IP, highest first.

SELECT source_ip, COUNT(*) AS failures
FROM login_attempts
WHERE result = 'failure'
GROUP BY source_ip
ORDER BY failures DESC;

source_ip	failures
192.0.2.50	4
192.0.2.77	3
203.0.113.44	2
203.0.113.21	1

Q12. Count failed attempts per country, highest first (break ties alphabetically).

SELECT country, COUNT(*) AS failures
FROM login_attempts
WHERE result = 'failure'
GROUP BY country
ORDER BY failures DESC, country;

country	failures
RU	4
BR	3
GB	3

Q13. Which source IPs have three or more failed attempts?

SELECT source_ip, COUNT(*) AS failures
FROM login_attempts
WHERE result = 'failure'
GROUP BY source_ip
HAVING COUNT(*) >= 3
ORDER BY failures DESC;

source_ip	failures
192.0.2.50	4
192.0.2.77	3

A threshold like this is a simple version of a brute-force detection rule.

Q14. Find login attempts made with usernames that do not exist in the employees table.

SELECT l.attempt_id, l.username, l.source_ip, l.result
FROM login_attempts l
LEFT JOIN employees e ON l.username = e.username
WHERE e.employee_id IS NULL
ORDER BY l.attempt_id;

Result: 4 rows: attempts 3 and 4 (admin), 5 (root) and 15 (test), all failures from 192.0.2.50. The attacker was guessing common account names.

Q15. List failed attempts by known employees, showing their name and department.

SELECT l.attempt_id, e.name, e.department, l.country
FROM login_attempts l
JOIN employees e ON l.username = e.username
WHERE l.result = 'failure'
ORDER BY l.attempt_id;

attempt_id	name	department	country
6	Eli Brown	Finance	BR
7	Eli Brown	Finance	BR
8	Eli Brown	Finance	BR
11	Dana Smith	HR	GB
18	Gus Miller	Sales	GB
19	Gus Miller	Sales	GB

Q16. Count failed attempts per department.

SELECT e.department, COUNT(*) AS failures
FROM login_attempts l
JOIN employees e ON l.username = e.username
WHERE l.result = 'failure'
GROUP BY e.department
ORDER BY failures DESC;

department	failures
Finance	3
Sales	2
HR	1

Q17. (Stretch) Which user and IP combinations had a failed attempt followed later by a success?

Idea: join the attempts table to itself, once for failures and once for later successes with the same username and IP.

SELECT DISTINCT f.username, f.source_ip
FROM login_attempts f
JOIN login_attempts s
  ON f.username = s.username
 AND f.source_ip = s.source_ip
 AND s.result = 'success'
 AND s.attempt_time > f.attempt_time
WHERE f.result = 'failure'
ORDER BY f.username;

username	source_ip
dsmith	203.0.113.21
ebrown	192.0.2.77

Interpretation: Dana Smith failed once and succeeded 27 seconds later from a UK address, which looks like a typo. Eli Brown failed three times in 16 seconds and then succeeded from a Brazilian address at 03:30, which is much more suspicious. Gus Miller’s late failures do not appear because no success followed them.

Q18. Which employees own a retired device?

SELECT e.name, d.device_name, d.status
FROM employees e
JOIN devices d ON e.employee_id = d.employee_id
WHERE d.status = 'retired';

Result: 1 row: Eli Brown, FIN-LT-05, retired. Combined with Q17, an account owner with a retired device and a suspicious overnight login deserves follow-up.

Q19. Which devices have no assigned employee?

SELECT device_name, os
FROM devices
WHERE employee_id IS NULL;

Result: 1 row: SRV-WEB-01 (Linux). Shared or unowned assets are worth documenting, since nobody feels responsible for them.

Q20. For employees who had at least one failed login, show their name, device and operating system.

SELECT DISTINCT e.name, d.device_name, d.os
FROM login_attempts l
JOIN employees e ON l.username = e.username
JOIN devices d ON d.employee_id = e.employee_id
WHERE l.result = 'failure'
ORDER BY e.name;

name	device_name	os
Dana Smith	HR-LT-04	Windows
Eli Brown	FIN-LT-05	Windows
Gus Miller	SAL-LT-07	Windows

Three tables joined in one query: attempts → employees → devices. DISTINCT stops repeated failures from creating duplicate rows.

11. Common mistakes

Using = NULL. It matches nothing. Use IS NULL.
Mixing AND with OR without parentheses. You can silently pull in the wrong rows.
Forgetting quotes around text, or using double quotes where your database expects single quotes.
Putting an aggregate in WHERE. Conditions on COUNT(*) belong in HAVING.
Selecting a column that is not in GROUP BY. Add it to the group or wrap it in an aggregate function.
Joining without an ON condition. This can multiply rows and give wildly wrong counts.
Using INNER JOIN when you need to find missing matches. Use LEFT JOIN ... IS NULL.
Comparing times stored as text carelessly. ISO format (YYYY-MM-DD HH:MM:SS) sorts correctly. Other formats may not.
Treating a count as a verdict. A query shows a pattern. You still need context before calling something an attack.
Running unknown queries on production data. Start with SELECT only, and use LIMIT while exploring.

Defensive tie-in: the same language is what attackers abuse in SQL injection, where unchecked user input becomes part of a query. Developers prevent this with parameterised queries. The OWASP project explains this at owasp.org.

12. YouTube search links

13. One-screen revision summary

SELECT columns FROM a table. DISTINCT removes duplicates, ORDER BY sorts, LIMIT trims.
WHERE filters rows: =, <>, >, BETWEEN, IN, IS NULL.
AND needs both, OR needs one, NOT flips. Use parentheses when mixing.
LIKE: % is any number of characters, _ is exactly one.
JOIN links tables through shared columns. INNER keeps matches, LEFT keeps everything on the left and reveals gaps.
GROUP BY with COUNT(*) turns logs into patterns. HAVING filters groups, WHERE filters rows.
Clause order: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.
Investigation pattern: count failures, rank sources, check for a success after failures, then enrich with employee and device data.
Handle data responsibly: read-only first, protect personal data, report findings with context.

14. What you should be able to do

Write a SELECT that returns chosen columns from one table
Filter rows with WHERE, including BETWEEN, IN and IS NULL
Combine conditions with AND, OR and NOT using correct parentheses
Use LIKE with % and _ to match partial values
Join two or three tables and explain INNER vs LEFT JOIN
Count and rank events with GROUP BY, ORDER BY and HAVING
Spot a brute-force pattern in login data
Explain the findings from a query in plain language
Complete at least 15 of the 20 practice queries without looking at answers

← Back to hub

Educational summary for learners; not affiliated with Google or Coursera. All data is invented for practice. SQL syntax varies slightly between database systems, so verify details in your database’s documentation. Last reviewed: October 2026.

Subscribe to Updates

What's Hot