Chapter 1: Introduction to SQL for Data Analytics and Data Science

1.1 What is SQL and Why is it Important for Data Analytics and Data Science?

Introduction

SQL (Structured Query Language) is the standard language used for managing and querying relational databases. It allows data analysts and data scientists to access, manipulate, and analyze structured data efficiently. SQL is essential for:

• Retrieving and analyzing large datasets.
• Cleaning and preprocessing data.
• Aggregating and summarizing data for reporting.
• Performing data joins and transformations.

Example: Fetching Data with SQL

SELECT customer_id, purchase_date, total_amount
FROM transactions
WHERE total_amount > 100;
    

This query retrieves all transactions where the purchase amount exceeds $100.

1.2 Understanding Databases: Relational vs. Non-Relational Databases

Relational Databases (SQL-based)

Relational databases store data in structured tables with relationships between them. They follow ACID properties (Atomicity, Consistency, Isolation, Durability). Examples include:

• PostgreSQL
• MySQL
• SQLite
• Microsoft SQL Server

Non-Relational Databases (NoSQL)

Non-relational databases store data in flexible structures such as key-value pairs, documents, graphs, or wide-column stores. They are often used for big data applications. Examples include:

• MongoDB (Document-based)
• Redis (Key-value store)
• Cassandra (Wide-column store)
• Neo4j (Graph-based)

Example: Relational Data Model

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE,
    total_amount DECIMAL(10, 2)
);
    

1.3 SQL in the Data Science Workflow

SQL plays a crucial role in the data science pipeline, from data extraction to model deployment. Here’s how SQL integrates with data science workflows:

1. Data Collection: Pull raw data from databases using SQL queries.
2. Data Cleaning: Handle missing values, remove duplicates, and transform data.
3. Exploratory Data Analysis (EDA): Aggregate, filter, and visualize key insights.
4. Feature Engineering: Extract meaningful features from raw data.
5. Model Training: Export SQL data into machine learning tools such as Python or R.
6. Model Deployment: Store predictions or results back into SQL databases.

Example: Data Aggregation for EDA

SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;
    

1.4 Setting Up Your SQL Environment (PostgreSQL/MySQL/SQLite)

To start using SQL for data science, you need to set up a database environment. Below are steps for installing PostgreSQL, MySQL, and SQLite.

PostgreSQL Installation

• Windows: Download from postgresql.org.
• Mac: Use Homebrew:
  brew install postgresql
• Linux: Use:
  sudo apt-get install postgresql

MySQL Installation

• Windows & Mac: Download from mysql.com.
• Linux:
  sudo apt-get install mysql-server

SQLite Installation

SQLite is lightweight and doesn’t require a server.

• Install with:
  sudo apt-get install sqlite3
• Create a new database:
  sqlite3 my_database.db

1.5 Exercise: Writing Your First SQL Query

Step 1: Create a Sample Database

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    grade TEXT
);
    

Step 2: Insert Sample Data

INSERT INTO students (name, age, grade) VALUES
('Alice', 22, 'A'),
('Bob', 23, 'B'),
('Charlie', 21, 'A');
    

Step 3: Query the Data

SELECT * FROM students;
    

Step 4: Filter Data

SELECT name, age FROM students WHERE grade = 'A';
    

Summary

• SQL is essential for querying and analyzing structured data in data science.
• Relational databases store data in structured tables, while NoSQL databases offer flexible storage models.
• SQL is used in the entire data science workflow, from data extraction to analysis.
• Setting up a SQL environment involves choosing between PostgreSQL, MySQL, or SQLite.
• The interactive exercise introduced basic SQL operations.

This chapter serves as the foundation for using SQL in data science, preparing you for more advanced data manipulation, joins, and analytical queries in the following chapters.