Today's Topic

SQL Basics

The database is where every transaction is stored permanently. SQL is the language you use to ask the database questions — and as L2, you'll be asking a lot of them.

SELECT JOIN COUNT GROUP BY

01 The Simple Idea First

Real-life Analogy

Think of a database like a massive Excel file with multiple sheets. One sheet has all transactions. Another has all client accounts. Another has all error logs.

SQL is how you ask questions to that Excel file. Instead of scrolling through 2 million rows manually, you type a question like: "Show me all failed transactions from Alpha Bank today" — and the database gives you exactly that in seconds.

As an L2 engineer, you use SQL every time a client asks "did my transaction go through?" or your manager asks "how many transactions failed today?"

What is SQL and why do you need it?

SQL stands for Structured Query Language. It's the standard way to talk to a database. You write a query (a question), the database runs it, and sends you back the data you asked for.

In fintech, the database holds everything — every transaction, every account, every status update. Without SQL you're blind. With SQL you can find any transaction, check its status, count how many failed, and generate a full report — all in seconds.

02 Understanding the Tables First

Before writing any SQL — know your tables

A database has multiple tables. Each table is like a sheet in Excel. For today's examples we'll use two common fintech tables. Understanding these helps you follow all the queries below.

📋 transactions table

txn_id	client_id	amount	status	created_at
TXN-001	C01	5000	SUCCESS	2024-03-15
TXN-002	C02	2000	FAILED	2024-03-15
TXN-003	C01	8500	FAILED	2024-03-15
TXN-004	C03	1200	SUCCESS	2024-03-15
TXN-005	C02	3300	PENDING	2024-03-15

🏦 clients table

client_id	client_name	vpn	type
C01	Alpha Bank	Multinet	Bank
C02	Beta Wallet	Jazz PSL	Wallet
C03	Gamma Finance	Jazz PSL	MFB
C04	Delta Pay	Multinet	Processor

03 The 4 Commands — Syntax, Use & Examples

SELECT

Retrieve

"Ask the database to show you data"

What it does: SELECT is the most basic SQL command. It says "go into this table and show me these columns." Every SQL query starts with SELECT. You pick which columns you want to see and which table to get them from.

Syntax

SELECT column1, column2 -- choose which columns to show
FROM table_name -- which table to look in
WHERE column = 'value'; -- filter rows (optional)

-- Use * to select ALL columns
SELECT * FROM transactions;

Real examples

-- Show all columns for every transaction
SELECT * FROM transactions;

-- Show only txn_id, amount and status
SELECT txn_id, amount, status
FROM transactions;

-- Find a specific transaction by ID
SELECT * FROM transactions
WHERE txn_id = 'TXN-9823';

-- Find all FAILED transactions today
SELECT * FROM transactions
WHERE status = 'FAILED'
AND created_at = '2024-03-15';

Result of last query

txn_id	client_id	amount	status	created_at
TXN-002	C02	2000	FAILED	2024-03-15
TXN-003	C01	8500	FAILED	2024-03-15

💡 L2 use case: Client says "transaction TXN-9823 is missing." You run SELECT * FROM transactions WHERE txn_id = 'TXN-9823' — instantly see if it exists, what its status is, and when it was created.

JOIN

Combine

"Connect two tables together like merging two Excel sheets"

What it does: JOIN combines rows from two tables based on a matching column. The transactions table has a client_id but not the client name. The clients table has the name. JOIN brings them together so you see both in one result — the transaction details AND the client name side by side.

Syntax

SELECT columns
FROM table1
JOIN table2
ON table1.matching_column = table2.matching_column;

Real examples

-- Show transaction ID, amount, status AND the client name together
SELECT t.txn_id, t.amount, t.status, c.client_name
FROM transactions t
JOIN clients c
ON t.client_id = c.client_id;

-- Show only FAILED transactions with client names
SELECT t.txn_id, c.client_name, t.amount, t.status
FROM transactions t
JOIN clients c
ON t.client_id = c.client_id
WHERE t.status = 'FAILED';

Result of last query

txn_id	client_name	amount	status
TXN-002	Beta Wallet	2000	FAILED
TXN-003	Alpha Bank	8500	FAILED

💡 L2 use case: Your manager says "show me all failed transactions with client names." Without JOIN you'd have just IDs. With JOIN you get the full picture — client name, amount, status — all in one result.

COUNT

Aggregate

"Count how many rows match your question"

What it does: COUNT counts the number of rows that match your query. Instead of returning all the rows, it just tells you the number. Used constantly in fintech reports — "how many transactions failed today?", "how many clients have PENDING transactions?"

Syntax

SELECT COUNT(*) -- count all rows
FROM table_name
WHERE condition; -- optional filter

Real examples

-- How many total transactions are there?
SELECT COUNT(*) FROM transactions;

-- How many transactions FAILED today?
SELECT COUNT(*) FROM transactions
WHERE status = 'FAILED'
AND created_at = '2024-03-15';

-- How many transactions are still PENDING for client C02?
SELECT COUNT(*) FROM transactions
WHERE status = 'PENDING'
AND client_id = 'C02';

Result of COUNT(*) for FAILED today

COUNT(*)
2

💡 L2 use case: Manager asks "how bad was the outage? How many transactions failed between 2 PM and 3 PM?" You run COUNT with a time range filter and give a precise number instantly — no manual counting.

GROUP BY

Summarise

"Count or summarise data by category"

What it does: GROUP BY groups rows that share the same value in a column, then lets you COUNT or SUM each group. It's how you build summary reports — "show me how many transactions each client had" or "break down transactions by status." This is your most powerful reporting tool.

Syntax

SELECT group_column, COUNT(*)
FROM table_name
GROUP BY group_column; -- group rows by this column

Real examples

-- Count transactions by status (SUCCESS / FAILED / PENDING)
SELECT status, COUNT(*) AS total
FROM transactions
GROUP BY status;

-- Count transactions per client — who has the most?
SELECT c.client_name, COUNT(*) AS txn_count
FROM transactions t
JOIN clients c ON t.client_id = c.client_id
GROUP BY c.client_name;

-- Count FAILED transactions per client — the full failure report
SELECT c.client_name, COUNT(*) AS failed_txns
FROM transactions t
JOIN clients c ON t.client_id = c.client_id
WHERE t.status = 'FAILED'
GROUP BY c.client_name;

Result — transactions by status

status	total
SUCCESS	2
FAILED	2
PENDING	1

💡 L2 use case: After an incident, your manager asks for a breakdown. You run GROUP BY status and instantly have a clean summary — 2 success, 2 failed, 1 pending. That's your report done in one query.

04 Hands-on Lab — Extract Transaction Stats

Can you do this on your own machine?

Yes — fully performable on Kali Linux. SQLite is built into Kali Linux — no installation needed. You create a database, insert sample data, and run all 4 queries yourself. Here is every step exactly.

🔬 Lab: Build a Transaction DB and Generate a Report

Kali Linux · SQLite3 · No install needed

Open terminal and launch SQLite

SQLite is already on Kali. This command creates a new database file and opens it.

terminal

sqlite3 fintech_lab.db

✅ You'll see sqlite> prompt — you are now inside the database.

Create the two tables

Copy and paste this to create the clients and transactions tables.

sqlite>

CREATE TABLE clients (
  client_id TEXT PRIMARY KEY,
  client_name TEXT,
  vpn TEXT,
  type TEXT
);

CREATE TABLE transactions (
  txn_id TEXT PRIMARY KEY,
  client_id TEXT,
  amount INTEGER,
  status TEXT,
  created_at TEXT
);

Insert sample data

Paste this to populate both tables with realistic fintech data.

sqlite>

INSERT INTO clients VALUES ('C01','Alpha Bank','Multinet','Bank'), ('C02','Beta Wallet','Jazz PSL','Wallet'), ('C03','Gamma Finance','Jazz PSL','MFB'), ('C04','Delta Pay','Multinet','Processor');

INSERT INTO transactions VALUES ('TXN-001','C01',5000,'SUCCESS','2024-03-15'), ('TXN-002','C02',2000,'FAILED','2024-03-15'), ('TXN-003','C01',8500,'FAILED','2024-03-15'), ('TXN-004','C03',1200,'SUCCESS','2024-03-15'), ('TXN-005','C02',3300,'PENDING','2024-03-15'), ('TXN-006','C01',9900,'SUCCESS','2024-03-15'), ('TXN-007','C04',4400,'FAILED','2024-03-15'), ('TXN-008','C03',6600,'SUCCESS','2024-03-15');

✅ You now have 4 clients and 8 transactions in your local database.

Query 1 — Find all failed transactions

Use SELECT with WHERE to pull only the failed ones.

sqlite>

SELECT * FROM transactions
WHERE status = 'FAILED';

→ Should return 3 rows: TXN-002, TXN-003, TXN-007

Query 2 — Join to see client names with failed transactions

Combine transactions with clients table to get meaningful names.

sqlite>

SELECT t.txn_id, c.client_name, t.amount, t.status
FROM transactions t
JOIN clients c ON t.client_id = c.client_id
WHERE t.status = 'FAILED';

→ You now see client names instead of just C01/C02 — much more readable for a report.

Query 3 — Count total transactions by status

Use GROUP BY to get a full status breakdown — your summary report.

sqlite>

SELECT status, COUNT(*) AS total
FROM transactions
GROUP BY status;

→ Result: SUCCESS=4, FAILED=3, PENDING=1 — that's your daily transaction report.

Query 4 — Failed transactions per client (the full report)

Combine JOIN + COUNT + GROUP BY for the most powerful report query.

sqlite>

SELECT c.client_name, COUNT(*) AS failed_txns
FROM transactions t
JOIN clients c ON t.client_id = c.client_id
WHERE t.status = 'FAILED'
GROUP BY c.client_name;

→ This is your DB report. Alpha Bank: 1 failure, Beta Wallet: 1 failure, Delta Pay: 1 failure. Ready to paste into Jira or send to your manager.

📊 The Generated DB Report — What to Send Your Manager

Based on the queries above, this is what a clean daily transaction stats report looks like:

📊 Daily Transaction Report · 2024-03-15 · All Clients

Client	Total Txns	Success	Failed	Pending
Alpha Bank	3	2	1	0
Beta Wallet	2	0	1	1
Gamma Finance	2	2	0	0
Delta Pay	1	0	1	0

05 Quick Cheat Sheet

All 4 Commands at a Glance

SELECT * FROM tableGet all rows and columns from a table

WHERE status = 'FAILED'Filter rows by a condition

JOIN table2 ON t1.id = t2.idCombine two tables by matching column

COUNT(*)Count how many rows match

GROUP BY columnSummarise data by category

SELECT col, COUNT(*) GROUP BY colThe full reporting pattern

06 Real L2 Scenarios

Client calls: "Did transaction TXN-4421 go through?" You run SELECT * FROM transactions WHERE txn_id = 'TXN-4421' — you see status is FAILED with a timestamp. You confirm to the client in 10 seconds, no guessing.

After an outage, manager asks: "How many transactions were affected?" You run SELECT COUNT(*) FROM transactions WHERE status = 'FAILED' AND created_at = '2024-03-15' — you have the exact number in seconds.

Your lead says: "Which client had the most failures this week?" You run the GROUP BY query with a date range filter — you get a ranked list of clients by failure count. You now know which client needs priority attention.

A client says "there are transactions pending for over 2 hours." You run SELECT * FROM transactions WHERE status = 'PENDING' AND client_id = 'C02' — you see exactly which transactions are stuck, when they came in, and how long they've been waiting.

✅ Week 2 · Day 3 Outcomes — Can You Do This?

Use SELECT to retrieve specific columns and rows from a database table
Use WHERE to filter results — by status, date, client ID, or transaction ID
Use JOIN to combine two tables and show meaningful data instead of just IDs
Use COUNT to get the number of transactions matching any condition
Use GROUP BY to summarise data by category — status, client, date
Combine JOIN + COUNT + GROUP BY to generate a full client transaction report
Complete the hands-on lab on Kali Linux — create a SQLite database and run all 4 queries
Produce a clean daily transaction stats report from raw database data