SQL and Data Analysis
Fundamentals
Module 7: Empowering Business Analysts with database knowledge and
practical SQL skills for independent data analysis and informed decision-
making.
Why Business Analysts Must Learn SQL
Breaking Free from Dependencies
SQL transforms Business Analysts from passive data consumers into active
investigators. Rather than waiting days for developers to extract
information, BA professionals can independently query databases, analyse
trends, and generate insights in real-time.
This autonomy accelerates decision-making cycles and enables immediate
responses to stakeholder questions. When you control your data access, you
control the pace of business intelligence.
What is SQL?
Structured Query Language Universal Data Access Business Intelligence Tool
A standardised programming SQL works across all major database Transform raw data into actionable
language designed specifically for systems - PostgreSQL, MySQL, Oracle, insights by querying, filtering,
managing and manipulating relational SQL Server - making it an essential aggregating, and analysing
databases through clear, readable skill for any data professional. information stored in organisational
commands. databases.
Problems SQL Solves for Business Analysis
1 2 3
Automated Reporting Deep Data Analysis Decision Support
Generate comprehensive reports on Uncover patterns, trends, and Provide stakeholders with data-driven
demand without manual data anomalies hidden within millions of evidence for strategic choices.
compilation. Create recurring records. Perform complex calculations Answer critical business questions
analytical outputs that update and statistical analyses that with quantifiable metrics rather than
automatically as new data enters the spreadsheets cannot handle assumptions or intuition.
system. efficiently.
The Business Analyst's SQL
Advantage
Independence from Development Teams
Extract and analyse data without creating tickets or waiting in
development queues. This autonomy dramatically accelerates your
analytical workflow.
Precise Requirement Documentation
Write technical specifications for dashboards and reports using actual
SQL queries. Developers understand exactly what data and
calculations you need.
Quality Assurance Capability
Identify bugs, data inconsistencies, and logical errors by validating
information directly in the database before issues reach production.
SQL vs Excel vs BI Tools
SQL Excel BI Tools
• Handles millions of records • Limited to ~1 million rows • Visual dashboards
• Complex data relationships • Quick ad-hoc analysis • Pre-built connections
• Real-time queries • Manual data entry • Often powered by SQL
• Source of truth access • Easy visualisations • Limited flexibility
• Requires coding knowledge • User-friendly interface • Great for stakeholders
Each tool serves distinct purposes. SQL provides foundational data access, Excel offers quick calculations, and BI tools deliver
polished presentations. Master SQL first—it powers everything else.
Real Case Study: Declining Customer Retention
The Business Problem
Stakeholder Question: "Why has our customer retention rate dropped by 15% this quarter?"
Without SQL, this investigation might take weeks of back-and-forth with IT teams, spreadsheet exports, and manual analysis.
The SQL Investigation Approach
Understanding Data Types
Data types form the foundation of database design. They define what kind
of information each field can store and how the database processes that
information. Choosing the wrong data type leads to calculation errors,
performance problems, and data integrity issues.
For Business Analysts, understanding data types is crucial for reading data
models, identifying potential issues, and communicating effectively with
technical teams about data requirements.
Essential Data Types for Business Analysts
Integer Types Text Types Date & Time
INT, BIGINT: Whole numbers without VARCHAR, TEXT: Store character DATE, TIMESTAMP: DATE stores only the
decimals. Use INT for counts, IDs, and strings. VARCHAR has a maximum length date (YYYY-MM-DD). TIMESTAMP includes
quantities. BIGINT handles very large limit (efficient for short strings), whilst exact time with timezone information,
numbers like transaction IDs in high- TEXT accommodates unlimited length for essential for tracking precise event
volume systems. descriptions and comments. sequences.
Boolean Precise Numbers
BOOLEAN: True/false values. Perfect for DECIMAL, NUMERIC: Exact decimal
flags like "is_active", "has_subscription", values with no rounding errors. Absolutely
or "email_verified". Simple but powerful essential for financial calculations, prices,
for filtering and logic. and any measurement requiring
precision.
Choosing the Right Data Type
Consider Storage Requirements Anticipate Future Growth Match Business Logic
Larger data types consume more Will your customer count exceed 2 If a field represents money, use
storage and memory. INT uses 4 billion? Then INT isn't sufficient. DECIMAL. If it's a yes/no decision,
bytes, BIGINT uses 8 bytes. Multiply Planning ahead prevents costly data use BOOLEAN. The data type should
this across millions of records and type migrations when systems scale. reflect the real-world concept it
the difference becomes significant represents.
for system performance.
Consequences of Wrong Data Type Selection
Calculation Errors
Using FLOAT or DOUBLE for monetary values introduces rounding
errors. £10.00 might become £9.999999 or £10.000001, leading to
reconciliation nightmares and regulatory compliance issues.
Performance Degradation
Incorrect types force the database to perform constant conversions.
Storing numbers as text means every calculation requires type
casting, slowing queries from milliseconds to seconds.
Data Validation Failures
Without proper type constraints, invalid data enters the system.
Phone numbers stored as integers lose leading zeros, breaking
international formats.
Real-World Data Type Examples
Why Not FLOAT for Money? Phone Numbers Aren't Numbers
Computers store FLOAT values as binary approximations. Despite containing digits, phone numbers are text strings.
The decimal 0.1 cannot be represented exactly in binary, They include special characters (+, -, spaces), have leading
causing accumulating errors in financial calculations. Always zeros, and you never perform mathematical operations on
use DECIMAL(10,2) for currency. them. Use VARCHAR(20).
BA Critical Thinking: When reviewing a data model, ask "Why is this field type X?" Understanding the reasoning behind
data type choices reveals how developers conceptualised the business domain.
Constraints: Ensuring Data Quality
Constraints are rules enforced at the database level that prevent invalid
data from entering the system. They act as automatic gatekeepers,
ensuring data quality without relying on application code or human
vigilance.
For Business Analysts, constraints represent business rules translated into
technical safeguards. When data quality issues arise, constraints are your
first line of defence and investigation.
Types of Database Constraints
NOT NULL UNIQUE
Prevents empty values in critical fields. Ensures essential Guarantees no duplicate values. Email addresses, national ID
information like customer email, order date, or product price numbers, and product codes must be unique to avoid confusion
always exists. and data integrity issues.
CHECK DEFAULT
Validates values against specific conditions. Age must be Provides automatic values when none specified. New user
positive, discount percentage stays between 0-100, order accounts default to "active" status, creation timestamps default
quantity exceeds zero. to current time.
Primary Keys: Unique Record Identification
What Makes a Primary Key?
A Primary Key (PK) uniquely identifies each record in a table. It must be unique, non-null,
and unchanging. Think of it as each record's permanent address within the database.
Natural vs Surrogate Keys
Natural keys use business-meaningful values (email address, ISBN). Surrogate keys
are artificially generated (auto-incrementing IDs). Most modern systems prefer surrogate
keys for stability and simplicity.
Red Flag: A table without a primary key allows duplicate records, making it impossible to reliably update or delete specific entries. This causes data chaos.
Foreign Keys: Connecting Tables
What is a Foreign Key? Referential Integrity
A Foreign Key (FK) is a field in one table that references the Foreign Keys prevent orphaned records. You cannot create
Primary Key of another table, creating relationships between an order for a non-existent customer or delete a customer
data. with active orders.
1 2 3
Relationship Example
An order table's customer_id field is a Foreign Key pointing
to the customer table's primary key, linking each order to its
purchaser.
Practical Example: Customer-Order-
Payment Relationships
Consider an e-commerce system with three interconnected tables:
Customer Table
PK: customer_id
Stores: name, email, phone
Order Table
PK: order_id
FK: customer_id
Stores: date, total, status
Payment Table
PK: payment_id
FK: order_id
Stores: amount, method, timestamp
Each order links to exactly one customer through customer_id. Each payment links to
exactly one order through order_id. These Foreign Key relationships maintain data
consistency across the entire system.
Essential SQL Commands for
Business Analysts
SQL commands enable Business Analysts to retrieve, filter, and analyse
data independently. These fundamental operations form the building blocks
of every analytical query you'll write throughout your career.
Mastering these commands transforms you from someone who requests
data to someone who finds answers.
Foundation SQL Commands
SELECT FROM WHERE
Specifies which columns to retrieve Indicates which table contains the Filters results based on conditions.
from the database. You can select data you want to query. Every SELECT Only records matching your criteria
specific fields or use SELECT * for all statement requires a FROM clause. appear in the result set.
columns.
ORDER BY LIMIT
Sorts results in ascending (ASC) or descending (DESC) order Restricts the number of records returned, essential for
by specified columns. testing queries on large datasets.
Filtering with Logic Operators
AND / OR IN BETWEEN
Combine multiple conditions. AND Tests if a value matches any value in Filters values within a range, inclusive
requires all conditions to be true; OR a list. More elegant than multiple OR of boundaries. WHERE order_date
requires at least one condition to be statements: WHERE country IN ('UK', BETWEEN '2024-01-01' AND '2024-
true. Use parentheses to control 'France', 'Germany'). 12-31' finds all orders in 2024.
evaluation order.
LIKE IS NULL
Performs pattern matching on text. Use % as a wildcard: Identifies missing values. WHERE phone IS NULL finds
WHERE email LIKE '%@[Link]' finds all Gamma email customers without phone numbers. Note: Use IS NULL,
addresses. never = NULL.
Aggregation Functions: Calculating Insights
COUNT MIN / MAX
Counts records. COUNT(*) includes nulls; Finds smallest and largest values in a dataset for range
COUNT(column) excludes them. analysis.
SUM GROUP BY
Totals numeric values. Essential for revenue, quantity, or Divides results into groups for aggregate calculations
any cumulative metric. per category.
AVG HAVING
Calculates mean values. Useful for average order value, Filters grouped results (WHERE filters before grouping;
satisfaction scores. HAVING filters after).
JOIN Operations: Combining
Tables
JOINs are the most powerful feature of relational databases, allowing you to
combine information from multiple tables. Understanding JOINs separates
novice SQL users from proficient analysts.
These operations reconstruct the relationships defined by Foreign Keys,
enabling comprehensive analysis across your entire data model.
Types of JOINs
INNER JOIN LEFT JOIN RIGHT JOIN
Returns only records that have Returns all records from the left table Opposite of LEFT JOIN—returns all
matching values in both tables. Most plus matched records from the right records from the right table plus
restrictive but safest JOIN type. Use table. Unmatched right table values matches from the left. Less commonly
when you need complete information appear as NULL. Essential for finding used but occasionally necessary for
from both tables. gaps or missing data. specific analytical questions.
Database Diagrams: Reading
System Design
Entity-Relationship Diagrams (ERDs) visualise database structure, showing
tables, their fields, and relationships. For Business Analysts, ERDs are maps
of how information flows through systems.
Reading ERDs enables you to understand system architecture, identify data
sources, and communicate effectively with technical teams about data
requirements.
ERD Components
Entities Attributes Relationships
Tables represented as boxes. Each entity Fields within entities, displayed as lists Lines connecting entities show how tables
represents a business concept like inside boxes. Attributes describe entity relate. Crow's foot notation indicates one-
Customer, Order, or Product. characteristics and properties. to-many relationships; straight lines show
one-to-one.
Understanding Cardinality
One-to-Many (1:N)
One record in Table A relates to
multiple records in Table B. Example:
One customer places many orders.
One-to-One (1:1) Most common relationship type.
Each record in Table A relates to
exactly one record in Table B.
Example: Each employee has one
Many-to-Many (N:N)
unique desk assignment. Multiple records in Table A relate to
multiple records in Table B. Requires a
junction table. Example: Students enrol
in multiple courses; courses contain
multiple students.
Primary and Foreign Keys in ERDs
ERDs clearly mark Primary and Foreign Keys using visual indicators:
PK label identifies the Primary Key field
FK label indicates Foreign Key fields
• Lines connect Foreign Keys to their referenced Primary Keys
• Keys often appear at the top of entity boxes
Introduction to Database
Normalisation
Normalisation is the process of organising database tables to reduce redundancy
and improve data integrity. Rather than storing all information in one massive
table, normalisation breaks data into logical, related tables.
Why Normalise?
Eliminate Data Duplication
Customer address stored once, not repeated for every order
Maintain Consistency
Update customer email in one place, not across dozens of records
Reduce Storage Costs
Smaller tables with less redundancy consume less disk space
Improve Update Performance
Fewer rows to modify when information changes