SQL for Data Analysis

Course Overview

Week 1: Introduction to SQL and Databases

Topics:

1. What is SQL?
2. Types of Databases (Relational, NoSQL, etc.)
3. Introduction to RDBMS (Relational Database Management System)
4. Understanding Tables, Rows, and Columns.

Practical:

1. Setting up a database.
2. Creating your first table.
3. Inserting sample data into tables.

Week 2: Basic SQL Commands

Topics:

1. SELECT Statement
2. Filtering Data with WHERE
3. Using Logical Operators (AND, OR, NOT)
4. Sorting Data (ORDER BY)
5. Aliases (AS)
6. SQL String Functions:
7. LTRIM Function
8. UPPER Function
9. RTRIM Function in SQL
10. CONCATENATE Function

Practical:

1. Querying and filtering datasets.
2. Sorting and formatting output using aliases.

Week 3: Aggregation and Grouping

Topics:

1. Aggregate Functions (SUM, AVG, COUNT, MAX, MIN)
2. GROUP BY Clause
3. HAVING Clause

Practical:

1. Writing queries to summarize data.
2. Grouping and filtering aggregated results.

Week 4: Joining Tables

Topics:

1. Types of Joins:
2. INNER JOIN
3. LEFT JOIN (OUTER JOIN)
4. RIGHT JOIN
5. FULL OUTER JOIN
6. Self Joins
7. Cross Joins

Practical:

1. Joining multiple tables.
2. Writing queries for real-world scenarios.

Week 5: Advanced SQL Concepts

Topics:

1. Subqueries and Nested Queries
2. Common Table Expressions (CTEs)
3. Window Functions (ROW_NUMBER, RANK, PARTITION BY)
4. Transactions (COMMIT, ROLLBACK, SAVEPOINT)

Practical:

1. Writing nested queries and CTEs.
2. Experimenting with window functions.
3. Implementing transactions.
4. Indexing for Performance
5. Triggers (AFTER, BEFORE):
6. INSERT
7. DELETE
8. UPDATE

Week 6: Database Management and Advanced Queries

Topics:

1. Data Integrity Constraints (Primary Key, Foreign Key, Unique, Check)
2. Views
3. Stored Procedures and Functions

Practical:

1. Creating views and stored procedures.