Chapter 3: Master SQL for Data Analysis

Step 2: Get Tools to Interact

**Theory:** You need a client tool to write SQL queries and see the results from your database.

How to Perform:

• **For SQLite:** Download **DB Browser for SQLite** (sqlitebrowser.org). It's a free, visual tool that lets you create databases, define tables, and run SQL queries against your SQLite file.
• **For PostgreSQL/MySQL:** You'll need to install the database server itself (PostgreSQL downloads, MySQL downloads). Then use client tools like:
  • **psql** (PostgreSQL command-line) / **mysql** (MySQL command-line)
  • **pgAdmin** (Free GUI for PostgreSQL)
  • **DBeaver** (Free, universal GUI client for many databases)
  • **VS Code Extensions:** Many extensions allow connecting to databases and running queries directly within VS Code.

Step 3: Get Sample Data

**Theory:** You need data to query! Many sample databases are available online (e.g., Sakila database for MySQL, Chinook database for SQLite/PostgreSQL).

How to Perform (Using DB Browser for SQLite with Chinook):

1. Download the Chinook database file for SQLite (search "Chinook database SQLite"). It's usually a `.sqlite` or `.db` file.
2. Open DB Browser for SQLite.
3. Click "Open Database" and select the downloaded Chinook file.
4. Go to the "Execute SQL" tab. You're ready to write queries!

Task 2: Basic SQL Queries - The `SELECT` Statement 🔍

**Theory:** The `SELECT` statement is the workhorse of SQL for retrieving data. As an analyst, you'll spend most of your time writing `SELECT` queries.

Selecting All Columns from a Table

How to Perform:

Use `SELECT *` to get all columns. Use `FROM` to specify the table.

-- Select all columns and all rows from the 'Customers' table
SELECT * FROM Customers; 

(Note: SQL keywords like `SELECT` and `FROM` are often written in uppercase by convention, but SQL is generally case-insensitive for keywords and identifiers. Table/column names might be case-sensitive depending on the database system). Semicolons `;` mark the end of a statement.

Selecting Specific Columns

How to Perform:

List the column names you want after `SELECT`, separated by commas.

-- Select only the FirstName, LastName, and Email from the 'Customers' table
SELECT FirstName, LastName, Email 
FROM Customers;

Limiting Results

How to Perform:

Use `LIMIT` (syntax might vary slightly across databases, e.g., `TOP` in SQL Server) to get only the first N rows. Useful for previewing data.

-- Get the first 5 customers
SELECT FirstName, LastName, Email 
FROM Customers
LIMIT 5;

Task 3: Filtering Data with `WHERE` 🎯

**Theory:** The `WHERE` clause allows you to filter rows based on specific conditions.

How to Perform (Using Comparison Operators):

-- Find all customers from Brazil
SELECT FirstName, LastName, Country
FROM Customers
WHERE Country = 'Brazil';

-- Find all invoices with a total greater than 10
SELECT InvoiceId, CustomerId, Total
FROM Invoices
WHERE Total > 10;

-- Find all tracks that are NOT composer 'Jimi Hendrix'
SELECT Name, Composer
FROM Tracks
WHERE Composer != 'Jimi Hendrix'; -- Or use <>

How to Perform (Using `LIKE`, `IN`, `BETWEEN`):

-- Find all customers whose FirstName starts with 'A'
-- % is a wildcard matching any sequence of characters
SELECT FirstName, LastName
FROM Customers
WHERE FirstName LIKE 'A%'; 

-- Find customers from USA or Canada
SELECT FirstName, LastName, Country
FROM Customers
WHERE Country IN ('USA', 'Canada');

-- Find invoices with totals between 5 and 10 (inclusive)
SELECT InvoiceId, Total
FROM Invoices
WHERE Total BETWEEN 5 AND 10;

How to Perform (Using `AND`, `OR`):

-- Find customers from Germany AND the city is Berlin
SELECT FirstName, LastName, City, Country
FROM Customers
WHERE Country = 'Germany' AND City = 'Berlin';

-- Find customers from Brazil OR Portugal
SELECT FirstName, LastName, Country
FROM Customers
WHERE Country = 'Brazil' OR Country = 'Portugal';

Task 4: Sorting Results with `ORDER BY` ↕️

**Theory:** The `ORDER BY` clause sorts the rows in your result set.

How to Perform:

-- Get all customers, ordered alphabetically by FirstName
SELECT FirstName, LastName, Country
FROM Customers
ORDER BY FirstName ASC; -- ASC (ascending) is the default, can be omitted

-- Get the 10 most expensive invoices, ordered by Total descending
SELECT InvoiceId, CustomerId, Total
FROM Invoices
ORDER BY Total DESC -- DESC means descending
LIMIT 10;

-- Order by Country, then by City within each Country
SELECT FirstName, LastName, Country, City
FROM Customers
ORDER BY Country, City; 

Task 5: Aggregating Data (`COUNT`, `SUM`, `AVG`, `GROUP BY`) 🧮

**Theory:** Aggregate functions perform calculations on a set of rows and return a single value. They are often used with the `GROUP BY` clause.

Common Aggregate Functions:

• `COUNT(*)`: Counts the total number of rows.
• `COUNT(column)`: Counts non-NULL values in a specific column.
• `SUM(column)`: Calculates the sum of values in a numeric column.
• `AVG(column)`: Calculates the average of values in a numeric column.
• `MIN(column)`: Finds the minimum value in a column.
• `MAX(column)`: Finds the maximum value in a column.

How to Perform (Simple Aggregations):

-- Count the total number of customers
SELECT COUNT(*) AS TotalCustomers -- 'AS' gives the result column a name
FROM Customers;

-- Find the total sum of all invoice amounts
SELECT SUM(Total) AS TotalRevenue
FROM Invoices;

-- Find the average track length in milliseconds
SELECT AVG(Milliseconds) AS AverageTrackLength
FROM Tracks;

How to Perform (Using `GROUP BY`):

**Theory:** The `GROUP BY` clause groups rows that have the same values in specified columns into a summary row. Aggregate functions are then applied to each group.

-- Count the number of customers in each country
SELECT Country, COUNT(*) AS NumberOfCustomers
FROM Customers
GROUP BY Country
ORDER BY NumberOfCustomers DESC; -- Show most frequent countries first

-- Calculate the total invoice amount for each customer
SELECT CustomerId, SUM(Total) AS TotalSpentPerCustomer
FROM Invoices
GROUP BY CustomerId
ORDER BY TotalSpentPerCustomer DESC;

How to Perform (Using `HAVING`):

**Theory:** The `HAVING` clause is used to filter groups *after* the `GROUP BY` clause has been applied (similar to how `WHERE` filters individual rows *before* grouping).

-- Find countries with more than 5 customers
SELECT Country, COUNT(*) AS NumberOfCustomers
FROM Customers
GROUP BY Country
HAVING COUNT(*) > 5 -- Filter the groups based on the aggregated count
ORDER BY NumberOfCustomers DESC;

Task 6: Combining Data with `JOIN` 🤝

**Theory:** `JOIN` clauses are used to combine rows from two or more tables based on a related column between them (usually linking a Foreign Key in one table to a Primary Key in another).

Common JOIN Types:

• **`INNER JOIN`:** Returns only the rows where the join condition is met in *both* tables. (The intersection).
• **`LEFT JOIN` (or `LEFT OUTER JOIN`):** Returns *all* rows from the left table, and the matched rows from the right table. If there's no match in the right table, the columns from the right table will have NULL values.
• **`RIGHT JOIN` (or `RIGHT OUTER JOIN`):** Returns *all* rows from the right table, and the matched rows from the left table. (Less common than LEFT JOIN).
• **`FULL OUTER JOIN`:** Returns all rows when there is a match in either the left or the right table. (Not supported by all databases, e.g., MySQL).

How to Perform (Example: Get Customer Names for Invoices):

We need data from `Invoices` (like `InvoiceId`, `Total`) and `Customers` (like `FirstName`, `LastName`). They are linked by `CustomerId`.

-- Using INNER JOIN
SELECT 
    I.InvoiceId,       -- Select InvoiceId from Invoices table (aliased as I)
    I.InvoiceDate,     -- Select InvoiceDate from Invoices
    I.Total,           -- Select Total from Invoices
    C.FirstName,       -- Select FirstName from Customers table (aliased as C)
    C.LastName,        -- Select LastName from Customers
    C.Email            -- Select Email from Customers
FROM 
    Invoices AS I      -- Start with the Invoices table, give it an alias 'I' for brevity
INNER JOIN 
    Customers AS C     -- Join it with the Customers table, aliased as 'C'
ON 
    I.CustomerId = C.CustomerId; -- Specify the join condition (how the tables are related)

-- Using LEFT JOIN (Show all invoices, even if customer info is somehow missing)
SELECT 
    I.InvoiceId, I.InvoiceDate, I.Total,
    C.FirstName, C.LastName, C.Email 
FROM 
    Invoices AS I 
LEFT JOIN 
    Customers AS C ON I.CustomerId = C.CustomerId; 

**Theory:** We select columns from both tables. `FROM Invoices AS I` specifies the first (left) table and gives it a short alias `I`. `INNER JOIN Customers AS C` specifies the second (right) table with alias `C`. `ON I.CustomerId = C.CustomerId` tells the database *how* to match rows: find rows where the `CustomerId` in the `Invoices` table is equal to the `CustomerId` in the `Customers` table.

Conclusion: Unlocking Database Insights 🔑

SQL is a fundamental skill for any data analyst. Mastering `SELECT`, `WHERE`, `ORDER BY`, aggregate functions (`COUNT`, `SUM`, `AVG`) with `GROUP BY`, and `JOIN` operations will allow you to extract and prepare the vast majority of data you'll encounter in relational databases.

Practice is key! Use sample databases and tools like DB Browser for SQLite to experiment with different queries. As you become comfortable, you can explore more advanced topics like subqueries, window functions, and database-specific features.

With your data extracted and prepared using SQL, the next step is often to bring it into a tool for deeper analysis and visualization. Chapter 4 will focus on **Data Visualization**.