SQL

Course Title: Introduction to SQL and Relational Databases

Course Goals:

Understand the fundamentals of relational databases.

Learn to write SQL queries to retrieve, manipulate, and manage data.

Gain practical experience in using SQL for data analysis.

Course Outline:

Module 1: Introduction to Relational Databases and SQL (Week 1)

1.1. Introduction to Databases:

What is a database?

Types of databases (relational, NoSQL, etc.)

Advantages of using databases.

1.2. Relational Database Concepts:

Tables, rows, and columns.

Primary keys, foreign keys, and relationships.

Database schemas.

1.3. Introduction to SQL:

What is SQL?

SQL standards (ANSI SQL).

SQL dialects (MySQL, PostgreSQL, SQL Server, etc.).

Introduction to Database Management systems(DBMS)

1.4. Setting up a development environment.

Installing a DBMS.

Introduction to database clients.

Module 2: Basic SQL Queries: Retrieving Data (Week 2-3)

2.1. The SELECT statement:

Selecting all columns (SELECT *).

Selecting specific columns.

Aliasing columns.

2.2. The WHERE clause:

Filtering data using comparison operators (=, !=, >, <, >=, <=).

Logical operators (AND, OR, NOT).

IN, BETWEEN, and LIKE operators.

2.3. Sorting and Limiting Results:

The ORDER BY clause (ascending and descending).

The LIMIT or TOP clause (depending on the SQL dialect).

2.4. NULL values.

Working with NULL

IS NULL and IS NOT NULL

Module 3: Advanced SQL Queries: Joining and Aggregating Data (Week 4-5)

3.1. Joining Tables:

INNER JOIN.

LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

Cross joins.

Self joins.

3.2. Aggregate Functions:

COUNT(), SUM(), AVG(), MIN(), MAX().

3.3. The GROUP BY and HAVING clauses.

Grouping data by one or more columns.

Filtering grouped data using HAVING.

3.4. Subqueries:

What are subqueries.

Subqueries in the WHERE clause.

Subqueries in the SELECT clause.

Module 4: Data Manipulation and Management (Week 6-7)

4.1. Inserting Data:

The INSERT INTO statement.

Inserting single and multiple rows.

4.2. Updating Data:

The UPDATE statement.

Updating specific rows using the WHERE clause.

4.3. Deleting Data:

The DELETE FROM statement.

Deleting specific rows using the WHERE clause.

4.4. Creating and Modifying Tables:

The CREATE TABLE statement.

Data types.

ALTER TABLE (adding, modifying, and deleting columns).

DROP TABLE

4.5. Constraints:

Primary key constraints.

Foreign key constraints.

NOT NULL, UNIQUE, and CHECK constraints.

Module 5: Views and Indexes (Week 8)

5.1. Views:

Creating views (CREATE VIEW).

Using views for data abstraction and security.

Updating views.

5.2. Indexes:

What are indexes?

Creating indexes (CREATE INDEX).

Types of indexes.

Benefits of indexes for query performance.

5.3. Transactions.

What are transactions.

BEGIN TRANSACTION, COMMIT, and ROLLBACK.

ACID properties.

Assessment:

Assignments and exercises throughout the course.

Midterm exam covering Modules 1-3.

Final project involving designing and querying a database.

Final exam covering all modules.

Tools and Resources:

SQL DBMS (MySQL, PostgreSQL, SQL Server, SQLite).

Database client (DBeaver, pgAdmin, MySQL Workbench).

Online resources and documentation.