

# Creating and managing tables

Before diving into the intricacies of SQL, let's start by creating and managing tables in a relational database. Tables are the fundamental building blocks of a relational database, and they store data in a structured format.

To create a table, you'll need to define its columns, which are also known as attributes. Each column has a name and a data type. The data type determines the kind of data that can be stored in that column. Some common data types include integers, strings, dates, and booleans.

Here's an example of creating a table called `employees` with four columns: `id`, `name`, `position`, and `salary`:

```sql
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10, 2)
);
```

In this example, the `id` column is an integer and is the primary key for the table. The `name` and `position` columns are strings with a maximum length of 50 characters. The `salary` column is a decimal number with two decimal places.

To manage tables, you can perform various operations like adding, deleting, or modifying columns. For example, to add a new column called `email` to the `employees` table, you can use the following SQL statement:

```sql
ALTER TABLE employees
ADD COLUMN email VARCHAR(100);
```

## Exercise
Create a table called `students` with the following columns:
- `id`: an integer, primary key
- `name`: a string, maximum length 50
- `age`: an integer
- `grade`: a string, maximum length 2

# Working with data types and schemas

Common data types in relational databases include:

- Integers: used to store whole numbers, e.g., `INT`
- Decimals: used to store numbers with decimal points, e.g., `DECIMAL(10, 2)`
- Strings: used to store text, e.g., `VARCHAR(50)`
- Dates: used to store date and time values, e.g., `DATE`
- Booleans: used to store true or false values, e.g., `BOOLEAN`

In addition to data types, schemas define the structure of a table and its columns. A schema is a collection of rules that dictate how data should be organized and stored. It ensures that the data is consistent and follows a specific format.

In SQL, you can create a schema using the `CREATE SCHEMA` statement. For example, to create a schema called `school`, you can use the following SQL statement:

```sql
CREATE SCHEMA school;
```

## Exercise
Create a table called `courses` in the `school` schema with the following columns:
- `id`: an integer, primary key
- `name`: a string, maximum length 100
- `credits`: an integer

# Writing and executing SQL queries

SQL (Structured Query Language) is a powerful language used to interact with relational databases. It allows you to retrieve, insert, update, and delete data from tables.

To retrieve data from a table, you can use the `SELECT` statement. For example, to retrieve all the records from the `employees` table, you can use the following SQL statement:

```sql
SELECT * FROM employees;
```

To filter the results, you can use the `WHERE` clause. For example, to retrieve all the employees with a salary greater than 50000, you can use the following SQL statement:

```sql
SELECT * FROM employees WHERE salary > 50000;
```

To insert data into a table, you can use the `INSERT INTO` statement. For example, to insert a new employee into the `employees` table, you can use the following SQL statement:

```sql
INSERT INTO employees (id, name, position, salary)
VALUES (1, 'John Doe', 'Software Engineer', 60000);
```

## Exercise
Insert two new records into the `courses` table in the `school` schema.

# Understanding joins and their types

Joins are a powerful feature in SQL that allows you to combine data from multiple tables based on a related column. There are different types of joins, including:

- Inner join: returns only the records that have matching values in both tables.
- Left join: returns all the records from the left table and the matching records from the right table.
- Right join: returns all the records from the right table and the matching records from the left table.
- Full outer join: returns all the records from both tables, including those with no matching values.

Here's an example of an inner join that retrieves the employees and their corresponding departments from the `employees` and `departments` tables:

```sql
SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
```

## Exercise
Write an SQL statement that retrieves all the students and their corresponding courses from the `students` and `courses` tables using a left join.

# Managing transactions and concurrency control

Transactions are a fundamental concept in relational databases. They ensure that a series of operations are executed as a single unit of work, either all at once or not at all. This helps maintain data consistency and integrity.

To manage transactions, you can use the `BEGIN`, `COMMIT`, and `ROLLBACK` statements. For example, to insert a new student and course into the `students` and `courses` tables within a transaction, you can use the following SQL statements:

```sql
BEGIN;

INSERT INTO students (id, name, age, grade)
VALUES (1, 'Alice', 20, 'A');

INSERT INTO courses (id, name, credits)
VALUES (1, 'Calculus', 3);

COMMIT;
```

If any of the insert statements fail, you can use the `ROLLBACK` statement to undo all the changes made in the transaction:

```sql
ROLLBACK;
```

## Exercise
Write an SQL statement that inserts a new employee and department into the `employees` and `departments` tables within a transaction. If any of the insert statements fail, use the `ROLLBACK` statement to undo all the changes.

# Creating and managing relationships between tables

Relationships between tables are an essential aspect of relational databases. They define how data in one table is related to data in another table. There are three types of relationships:

- One-to-one: a single record in one table is related to a single record in another table.
- One-to-many: a single record in one table is related to multiple records in another table.
- Many-to-many: multiple records in one table are related to multiple records in another table.

To create a relationship between tables, you'll need to include a foreign key column in one of the tables. A foreign key is a column that refers to the primary key of another table. For example, to create a relationship between the `employees` and `departments` tables, you can add a `department_id` column to the `employees` table:

```sql
ALTER TABLE employees
ADD COLUMN department_id INT;
```

## Exercise
Create a relationship between the `students` and `courses` tables by adding a `course_id` column to the `students` table.

# Optimizing database performance and indexing

Optimizing database performance is crucial for ensuring efficient data retrieval and manipulation. Indexes are a powerful tool that can improve the speed of data retrieval operations. They work by creating a data structure that allows for faster access to the data in a table.

To create an index, you can use the `CREATE INDEX` statement. For example, to create an index on the `name` column of the `employees` table, you can use the following SQL statement:

```sql
CREATE INDEX idx_employees_name ON employees (name);
```

## Exercise
Create an index on the `name` column of the `students` table.

# Advanced SQL concepts and best practices

- Subqueries: a query that is nested within another query, often used to filter or aggregate data.
- Common table expressions (CTEs): a temporary result set that can be referenced within a query.
- Window functions: functions that perform calculations across a set of table rows that are related to the current row.
- Stored procedures: precompiled SQL statements that can be executed by the database server.

## Exercise
Write a SQL statement that retrieves the total salary and average salary of employees in each department.

# Implementing data validation and constraints

Data validation and constraints are essential for ensuring the integrity and consistency of data in a relational database. They help prevent invalid or inconsistent data from being inserted or updated.

Some common constraints in relational databases include:

- Primary key: a unique identifier for each record in a table.
- Foreign key: a column that refers to the primary key of another table.
- Unique: ensures that all values in a column are distinct.
- Not null: ensures that a column cannot have null values.
- Check: a condition that must be satisfied for a record to be inserted or updated.

For example, to add a unique constraint to the `email` column of the `employees` table, you can use the following SQL statement:

```sql
ALTER TABLE employees
ADD CONSTRAINT uq_email UNIQUE (email);
```

## Exercise
Add a not null constraint to the `name` column of the `students` table.

# Working with views and stored procedures

Views and stored procedures are advanced features in relational databases that can help simplify complex queries and automate repetitive tasks.

A view is a virtual table that is based on the result of a SQL query. It allows you to retrieve data from multiple tables in a single query. For example, to create a view that retrieves the total salary and average salary of employees in each department, you can use the following SQL statement:

```sql
CREATE VIEW vw_employee_salaries AS
SELECT department_id, SUM(salary) AS total_salary, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;
```

A stored procedure is a collection of SQL statements that can be executed by the database server. It can accept input parameters and return output values. For example, to create a stored procedure that inserts a new employee into the `employees` table, you can use the following SQL statement:

```sql
CREATE PROCEDURE sp_insert_employee (
    p_name VARCHAR(50),
    p_position VARCHAR(50),
    p_salary DECIMAL(10, 2)
)
LANGUAGE SQL
AS $$
INSERT INTO employees (name, position, salary)
VALUES (p_name, p_position, p_salary);
$$;
```

## Exercise
Create a stored procedure that inserts a new student into the `students` table.

# Integrating databases with other systems

Relational databases can be integrated with other systems and technologies to enhance their functionality and capabilities. Some common integration techniques include:

- Exporting data to other formats: you can export data from a database to formats like CSV, JSON, or Excel.
- Integrating with application programming interfaces (APIs): you can create APIs that expose the data in a database to other applications or services.
- Using database connectors: you can use database connectors to connect your database to other systems or tools.

For example, to export data from the `employees` table to a CSV file, you can use the following SQL statement:

```sql
COPY employees TO 'employees.csv' WITH (FORMAT CSV, HEADER);
```

## Exercise
Create an API that retrieves all the students from the `students` table.

# Course

Table Of Contents
1. Creating and managing tables
2. Working with data types and schemas
3. Writing and executing SQL queries
4. Understanding joins and their types
5. Managing transactions and concurrency control
6. Creating and managing relationships between tables
7. Optimizing database performance and indexing
8. Advanced SQL concepts and best practices
9. Implementing data validation and constraints
10. Working with views and stored procedures
11. Integrating databases with other systems