###### tags: `Back-end`,`Restful API`,`ASP.NET CORE`,`Aon`
# Week 03 - Database(SQL), CRUD in .NET Core
## Database and SQL Fundamentals
### What is a Database?
A database is an organized collection of data. Think of it as a place where information is stored and can be easily retrieved, updated, or deleted. Databases are essential for almost any application that needs to manage large amounts of data efficiently.
### What is SQL?
SQL (Structured Query Language) is the standard language used to communicate with databases. It allows us to perform actions like creating tables, inserting, updating, deleting data, and querying (searching) the database.

### Setting Up SQLite
Before we dive into creating tables and writing SQL queries, it's important to set up the right environment for practicing SQL commands. We will use SQLite, which is a lightweight, easy-to-use database engine. You don’t need a full database server to get started, and you can practice directly on your local machine.

**Why SQLite?**
* Lightweight: It doesn’t require complex setup like some other databases (e.g., MySQL, PostgreSQL).
* Portable: It stores the entire database in a single file.
* Easy to use: You can use SQLite to practice SQL commands in minutes.

We will use SQLite Browser to interact with the database and execute SQL commands.

### Steps to Prepare SQL Environment
**Install SQLite and SQLite Browser**
Follow these steps to install SQLite Browser, which provides a graphical interface to manage SQLite databases:
* Download SQLite Browser:
* Visit the official website SQLite Browser https://sqlitebrowser.org/d.
* Download the version that matches your operating system (Windows, macOS, or Linux).
* Install SQLite Browser:
* For Windows, open the .exe file and follow the installation instructions.

Once installed, open the application. You’ll see an interface where you can create and manage databases.

### Create New Database
Now that you have SQLite Browser installed, let’s create your first SQLite database.

Open SQLite Browser.
Click on File → New Database.
Choose a location and name your database (e.g., AonAppDB.db).
Click Save. You now have an empty database ready to use!

### Executing SQL Commands in SQLite Browser
To start writing and executing SQL commands:

* In SQLite Browser, click on the Execute SQL tab at the top.
* You’ll see a large text area where you can write your SQL queries.
* After writing your query, click the Execute button to run it. The results (if any) will appear below.

### Create Table Using SQL Commands
Now that you have set up your environment, let’s dive into SQL commands, starting with creating a table.

In a relational database like SQLite, data is stored in tables. Each table consists of columns (attributes) and rows (records).
Syntax for Creating a Table
To create a table, we use the CREATE TABLE command. Let’s look at an example.

Example:

CREATE TABLE projects (
    Id INTEGER PRIMARY KEY,
    Title TEXT NOT NULL,
    Budget REAL
);

**Explanation:**
* Id: This is the unique identifier for each project. It’s of type INTEGER and is marked as the PRIMARY KEY, meaning it will be unique for every row.
* Name: This stores the name of the project. It is of type TEXT, and it’s marked as NOT NULL, meaning this field cannot be left empty.

In SQLite Browser, go to the Execute SQL tab.
Paste the above CREATE TABLE query into the text area.
Click Execute.

You have now successfully created a table named Projects. You can view it by going to the Browse Data tab and selecting Projects from the dropdown.

### Insert Data into a Table
Now that you have created the Projects table, let’s insert some sample data into it. We use the INSERT INTO command for this.

INSERT INTO Projects (Id, Name, Specialization, Salary)
VALUES (1, 'Pro A');

**Explanation:**
* INSERT INTO Projects: This specifies that we are inserting data into the Projects table.
* (Id, Name): These are the columns in which we are inserting values.
* VALUES (1, 'Pro A'): These are the values that correspond to the columns. We are inserting a project with Id=1, Name='Pro A'

### View Data from a Table
the SELECT statement is used to retrieve data from the database. By default, it retrieves all the rows in the table. However, you often need to filter the data using the WHERE clause to get specific records based on certain conditions.

To view the data stored in a table, we use the SELECT statement.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

**Explanation:**
* SELECT *: This means we are selecting all columns (*) from the table.
* FROM Projects: This specifies the table from which we want to retrieve the data.

**Example 1: Retrieve specific columns**
Let’s retrieve only the Name from the Projects table:

SELECT Name
FROM Projects;

Using the WHERE Clause
To filter results and retrieve only the projects with a Budget greater than 2500, you can use the WHERE clause:

SELECT Name, Budget
FROM Projects
WHERE Budget > 2500;

**Explanation:**
SELECT Name, Budget: Retrieves only the Name and Budget columns.
WHERE Budget > 2500: Filters the results to show only rows where the Budget is greater than 2500.

### UPDATE Data in a Table
The UPDATE statement is used to modify existing records in a table. You can update specific rows using the WHERE clause.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Let’s update the budget of a project with Id = 1:

UPDATE Projects
SET Budget = 3500
WHERE Id = 1;

**Explanation:**
UPDATE Projects: Specifies the table where the data should be updated.
SET Budget = 3500: Modifies the Budget column to 3500.
WHERE Id = 1: Ensures that only the record with Id = 1 is updated.

After executing, you can verify the update by selecting the data again:

SELECT * FROM Projects;

### DELETE Data from a Table
The DELETE statement is used to remove rows from a table. Be cautious when using DELETE, especially without the WHERE clause, as it will remove all records from the table.

DELETE FROM table_name
WHERE condition;

Let’s remove a project from the Projects table where the Id = 1:

DELETE FROM Projects
WHERE Id = 1;

**Explanation:**
DELETE FROM Projects: Specifies the table where the data should be deleted.
WHERE Id = 1: Ensures that only the record with Id = 1 is deleted.

To confirm that the row was deleted, you can select the remaining data from the Projects table:

SELECT * FROM Projects;

### Using the LIKE Operator
The LIKE operator is used in the WHERE clause to search for a specified pattern in a column. The patterns often use wildcard characters:

%: Represents zero or more characters.

Let’s find all projects whose names start with the letter "J":

SELECT *
FROM Projects
WHERE Name LIKE 'J%';

### Understanding Relationships Between Tables
In relational databases, data is stored in multiple tables that are connected via relationships. These relationships typically follow three main types:

#### One-to-One Relationship
In a one-to-one relationship, a record in one table corresponds to exactly one record in another table. This type of relationship is less common but is useful when you need to split a table for organizational or security reasons.

Consider two tables: Users and UserProfiles. Each user in the Users table has exactly one corresponding profile in the UserProfiles table.

Tables:

Users: Stores general information about users.
UserProfiles: Stores additional profile information for each user.

CREATE TABLE Users (
    Id INTEGER PRIMARY KEY,
    Username TEXT,
    Email TEXT
);

CREATE TABLE UserProfiles (
    UserId INTEGER,
    ProfilePicture TEXT,
    Bio TEXT,
    FOREIGN KEY (UserId) REFERENCES Users(Id)
);

#### One-to-Many Relationship
In a one-to-many relationship, a record in one table (the "one" side) can relate to multiple records in another table (the "many" side). This is the most common type of relationship.

Example:
Consider a Trainers table and a Courses table where each trainer can teach multiple courses. This creates a one-to-many relationship between Trainers and Courses.

Tables:

Trainers: Stores information about trainers.
Courses: Stores information about the courses they teach.

CREATE TABLE Users (
    Id INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    Username TEXT NOT NULL UNIQUE,
    Password TEXT,
    Budget REAL
);
CREATE TABLE clients (
    Id INTEGER PRIMARY KEY,
    BusinessName TEXT,
    Email TEXT,
    UserId INTEGER,
    FOREIGN KEY (UserId) REFERENCES Users(Id)

);

CREATE TABLE Projects (
    Id INTEGER PRIMARY KEY,
    Title TEXT NOT NULL,
    Budget REAL,
    ClientId INTEGER,
    FOREIGN KEY (ClientId) REFERENCES Clients(Id)

);

In this case, each project in the Projects table belongs to a Client in the Clients table through the ClientId foreign key.

in this which mean also that each client could have many projects.

#### Many-to-Many Relationship
In a many-to-many relationship, multiple records in one table are associated with multiple records in another table. This type of relationship requires a junction table (also called a bridge table) to link the two tables.

Example:
Consider a scenario where multiple students can enroll in multiple courses. This creates a many-to-many relationship between the Students and Courses tables. To model this relationship, we use a junction table called Enrollments.

Tables:

Students: Stores information about students.
Courses: Stores information about the courses.
Enrollments: A junction table that links Students and Courses.

CREATE TABLE Freelancers (
    Id INTEGER PRIMARY KEY,
    Skills TEXT,
    UserId INTEGER,
    FOREIGN KEY (UserId) REFERENCES Users(Id)

);


CREATE TABLE Projects (
    Id INTEGER PRIMARY KEY,
    Title TEXT NOT NULL,
    Budget REAL,
    ClientId INTEGER,
    FreelancerId INTEGER,
    FOREIGN KEY (ClientId) REFERENCES Clients(Id)
    FOREIGN KEY (FreelancerId) REFERENCES Freelancers(Id)

);

Here, the Projects table contains two foreign keys: ClientId and FreelancerId. This table allows each Freelancer to enroll in multiple projects, and each project to have multiple clients.

**Why are Relationships Important?**
* Data Organization: Relationships help organize data efficiently by breaking it into smaller, logically-related tables.
* Avoiding Redundancy: By using foreign keys to reference other tables, you avoid duplicating data, which keeps your database smaller and more efficient.
* Data Integrity: Relationships enforce constraints such as ensuring that a record in one table corresponds to a valid record in another (e.g., a course must have a valid trainer).

### Foreign Keys: How Relationships are Defined
A foreign key is a column or group of columns in one table that uniquely identifies a row in another table. It’s used to create a link between the two tables and enforce referential integrity.

Example: In the Courses table, TrainerId is a foreign key that references the Id column in the Trainers table. This ensures that every course is associated with a valid trainer.

CREATE TABLE Courses (
    Id INTEGER PRIMARY KEY,
    CourseName TEXT,
    TrainerId INTEGER,
    FOREIGN KEY (TrainerId) REFERENCES Trainers(Id)
);

The foreign key constraint enforces the relationship between the two tables, ensuring that any TrainerId in the Courses table exists in the Trainers table.

### Join Clauses
a JOIN is used to combine rows from two or more tables based on a related column.

Types of Joins:
INNER JOIN: Returns only the rows where there is a match in both tables.
LEFT JOIN: Returns all the rows from the left table, and the matched rows from the right table. If there is no match, the result is NULL on the right side.

Assume you have another table called Certifications:

CREATE TABLE Certifications (
    FreelancerId INTEGER,
    CertificationName TEXT,
    FOREIGN KEY (FreelancerId) REFERENCES Freelancers(Id)
);

Now, to retrieve the names of freelancers along with their certifications, you can use an INNER JOIN:

SELECT Freekancers.Name, Certifications.CertificationName
FROM Freelancers
INNER JOIN Certifications
ON Freelancers.Id = Certifications.FreelancerId;

**Explanation:**
INNER JOIN Certifications ON Freelancers.Id = Certifications.FreelancerId: Joins the two tables on the related column FreelancerId.