Lab Objective

Google Cybersecurity Certificate activity: “Filter a SQL query.” Practice narrowing SQL results to exactly the records a security task needs, using:

  • WHERE to filter rows by an exact value
  • LIKE with the % wildcard to filter rows by a pattern
  • reading DESCRIBE output before querying a table you don’t fully know

Scored 3/4 on first pass.

Lab Environment

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

Scenario

I needed specific information about employees, their machines, and their departments for four real tasks: which machines need an update, which employees sit in departments getting a privacy notice, and which employee’s machine in a specific office is having an issue — that then turned out to affect a whole building.

Commands Practiced

Command Purpose
DESCRIBE table; See column names and types before querying
SELECT col1, col2 FROM table; Return only the columns needed
WHERE column = 'value'; Filter rows to an exact match
WHERE column LIKE 'value%'; Filter rows to a pattern (starts-with)

Step 1 - Know the Data First

DESCRIBE machines;
DESCRIBE employees;

DESCRIBE is a table of contents, not the data itself — it shows field names and types (device_id, operating_system, varchar, int) so the next queries use the exact right column names.

Step 2 - List All Machines and Their Operating Systems

SELECT device_id, operating_system
FROM machines;

Result: 200 rows — every machine in the organization.

Step 3 - Filter to a Single Operating System

SELECT device_id, operating_system
FROM machines
WHERE operating_system = 'OS 2';

Result: 80 machines running OS 2 — the population that needs the update.

Three things SQL is picky about, learned the hard way: every command needs its semicolon or the shell just waits; string values need single quotes ('OS 2', not OS 2); column names never get quoted.

Step 4 - Filter Employees by Department

SELECT *
FROM employees
WHERE department = 'Finance';

Result: first row’s employee_id is 1003.

SELECT *
FROM employees
WHERE department = 'Sales';

Result: 33 employees in Sales.

Both results define exactly which office numbers get the confidential-handling notice — no more, no less.

Step 5 - Trace a Single Report to One Employee, Then to a Whole Building

SELECT *
FROM employees
WHERE office = 'South-109';

Result: jlansky — the employee whose machine has the reported issue, ready for a direct alert.

Then the scope widened: every machine in the South building, not just one office, needed the update.

SELECT *
FROM employees
WHERE office LIKE 'South%';

Result: the first employee listed in the South building is in the Finance department.

The % wildcard is doing real work here: 'South%' matches anything starting with “South” (South-109, South-210…), '%South' would match anything ending with it, and '%South%' would match “South” anywhere in the string. Picking the right position turned one office report into full building coverage.

Security Takeaways

  1. DESCRIBE before you query. Guessing column names wastes time and produces silent “0 rows” failures.
  2. WHERE is vulnerability triage. Filtering machines by operating_system is exactly how a real patch campaign gets scoped to the machines that actually need it.
  3. LIKE turns one incident into its full scope. A single office report (South-109) became a building-wide check (South%) with one wildcard.
  4. SQL syntax errors are almost always one of three things: a missing semicolon, missing quotes around a string, or quotes around a column name that shouldn’t have them.
  5. Filtering by department is how a privacy or compliance action gets scoped correctly — Finance and Sales got different notices because the query asked for exactly the right population, no more.

Where This Applies Beyond the Lab

This is the exact shape of a real patch-management or incident-scoping query: “which assets share this exposure” (WHERE), and “does this incident actually extend further than the one report I got” (LIKE). The syntax is introductory; the reasoning is not.