Lab Objective

Google Cybersecurity Certificate activity: “Complete a join.” Practice INNER JOIN, LEFT JOIN, and RIGHT JOIN against the organization database’s machines, employees, and log_in_attempts tables while investigating a security incident that compromised some machines. Also covers the follow-up “Continuous learning in SQL” reading on aggregate functions, and a coach-dialogue exercise applying SQL to a hypothetical SOC scenario.

Result: 100% on the graded quiz for this section.

Lab Environment

  • Course: Google Cybersecurity Certificate
  • Database: organization (MariaDB shell, already open at lab start)
  • Tables used: machines, employees, log_in_attempts
  • Reconnect command if the shell disconnects: sudo mysql organization

Scenario

Investigating a security incident that compromised some machines. Task 1: identify which employees are using which machines via INNER JOIN. Task 2: find machines with no assigned employee and employees with no assigned machine, via LEFT JOIN and RIGHT JOIN. Task 3: retrieve all login attempts made by all employees via INNER JOIN on username.

Commands Practiced

Command Purpose
INNER JOIN table2 ON t1.col = t2.col; Return only rows with a match in both tables
LEFT JOIN table2 ON t1.col = t2.col; Return every row from the first (left) table, NULL where unmatched
RIGHT JOIN table2 ON t1.col = t2.col; Return every row from the second (right) table, NULL where unmatched

Step 1 - Match Employees to Their Machines (INNER JOIN)

SELECT *
FROM machines;

Lists hardware only — device_id, operating_system — with no employee names. Not enough on its own.

SELECT *
FROM machines
INNER JOIN employees
ON machines.device_id = employees.device_id;

Both tables share device_id, so that’s the bridge column. Dot notation (machines.device_id vs employees.device_id) avoids ambiguity between two identically-named columns.

How many rows did the inner join return? Answer: 185. Only matched rows survive — an INNER JOIN keeps nothing that doesn’t have a partner in both tables.

Step 2 - Return More Data (LEFT JOIN and RIGHT JOIN)

SELECT *
FROM machines
LEFT JOIN employees
ON machines.device_id = employees.device_id;

machines is the left table (first after FROM), so every machine survives, even one with no assigned employee.

What is the value in the username column for the last record returned? Answer: NULL. A machine sitting unassigned shows up with a NULL employee — in real security work, this is exactly how you’d spot an unassigned endpoint worth investigating.

SELECT *
FROM machines
RIGHT JOIN employees
ON machines.device_id = employees.device_id;

Now every employee survives, even one with no machine assigned.

What is the value in the username column for the last record returned? Answer: areyes. The last employee record, areyes, has no machine — a NULL on the machine side of the row instead.

Step 3 - Retrieve Login Attempt Data (INNER JOIN on a Different Key)

SELECT *
FROM employees
INNER JOIN log_in_attempts
ON employees.username = log_in_attempts.username;

This time the shared column is username, not device_id — the join key depends entirely on what the two tables actually have in common, not a fixed assumption.

How many records are returned? Answer: 200.

A Correction Worth Noting

The lab’s own explanatory text describes INNER JOIN as choosing a “master list so no data is lost from it.” That phrasing is misleading and I’m deliberately not internalizing it: INNER JOIN does not preserve either table’s unmatched rows — if a row on either side has no match, it disappears entirely. LEFT JOIN or RIGHT JOIN is what preserves one side; INNER JOIN preserves neither side’s orphans.

Beyond the Lab: Aggregate Functions

The follow-up reading introduced aggregate functions — calculations across multiple rows that return a single summary value instead of the raw rows:

SELECT COUNT(firstname)
FROM customers;

SELECT COUNT(firstname)
FROM customers
WHERE country = 'USA';

COUNT, SUM, and AVG all follow the same placement pattern: right after SELECT, with the target column in parentheses.

Beyond the Lab: Applying JOINs and Aggregates to a SOC Scenario

A coach-dialogue exercise walked through a hypothetical company, “SecureCorp,” and asked how SQL supports real security tasks. Two queries came out of that exercise:

Finding a brute-force pattern — filter failed logins, group by username, count failures, sort descending:

SELECT username, COUNT(*) AS failed_attempts
FROM log_in_attempts
WHERE success = FALSE
GROUP BY username
ORDER BY failed_attempts DESC;

Finding systems missing a recent patch — compare a date column against a rolling window:

SELECT hostname, os_version, last_patch_date
FROM system_inventory
WHERE last_patch_date < DATE_SUB(CURDATE(), INTERVAL 30 DAY);

Both combine a WHERE filter with either grouping/counting or a date comparison — the same building blocks as the JOIN lab, applied to a slightly different shape of question.

Security Takeaways

  1. INNER JOIN keeps only matches — nothing survives from either side without a partner. Don’t reach for it when you need to see what’s missing.
  2. LEFT JOIN + IS NULL is the anti-join pattern. This is the single most reusable JOIN shape for security questions: unassigned machines, unpatched systems, accounts without MFA.
  3. The join key has to represent the same real-world entity on both sides. device_id connects machines to employees; username connects employees to login attempts — using the wrong shared column produces a syntactically valid but meaningless result.
  4. RIGHT JOIN is rarely necessary. Reordering the tables and using LEFT JOIN produces the identical result with less mental effort.
  5. GROUP BY + COUNT is how a raw list of failed logins becomes a brute-force detection query. Aggregation turns row-level noise into an actionable, ranked signal.

Where This Applies Beyond the Lab

The brute-force query (GROUP BY username, COUNT failed attempts, ORDER BY DESC) and the missing-patch query (WHERE last_patch_date < 30 days ago) are both realistic shapes of queries a SOC analyst runs regularly — not lab exercises invented for teaching purposes. Combined with JOINs for enrichment (which user owns which device, which destination IP maps to known threat intel), this is the actual toolkit behind turning raw logs into an investigation lead.