# SQLite Basic

In this exercise, you will learn the basics of the Structured query language (SQL)

We're going to create tables, insert, edit, and remove data in a database.

## Pre-requisites

Please make sure that you install the following before you begin:

* [SQLite3](https://www.sqlite.org/download.html)
* [DB Browser for SQLite](https://sqlitebrowser.org/)

You will also need basic knowledge of SQL.

You can watch this video [here](https://www.freecodecamp.org/news/quincylarson/sql-and-databases-full-course--FLkLcFzA) before starting this exercise. You may refer to this tutorial throughout this exercise.

You can also refer to w3schools tutorials [here](https://www.w3schools.com/sql/default.asp).

## Goals

* Control a full database
* Understand tables in database
* Practice writing simple SQL queries
* **Competencies**: 
  * Databases
  * SQL

## Instructions

* Fork this repo
* Clone it on your laptop
* Open DB Browser for SQLite, Press **Open Database** in top menu, and select the file named **myDatabase** in the repo
* For each of the questions below, write in **answers.sh** the query you used to solve the problem

Example:

Problem 0: Get all the data from the students table in the database.

Answer: select \* from students

Inside **answers.sh** write: "select \* from students"

### Basic Queries

In the **Execute SQL** tab, do the following: 1. Get all the names of students in the database 2. Get all the data of students above 30 years old 3. Get the names of the females who are 30 years old 4. Get the number of Points of **Alex** 5. Add yourself as a new student (your name, your age...) 6. Increase the points of **Basma** because she solved a new exercise 7. Decrease the points of **Alex** because he's late today 8. Remember to update **answers.sh** file 9. Commit ("Basic Queries")

### Creating Table

Layal graduated on 08/09/2018. Create a table **graduates** that includes:

* ID, Integer, Not Null, Primary key, Auto incerement
* Name, Text, Not Null, Unique
* Age, Integer
* Gender, Text
* Points, Integer
* Graduation, Text that will point to the date of the graduation

In the **Execute SQL** tab, do the following:

1. Copy Layal's data from **students** to **graduates**
2. Add the graduation date previously mentioned to Layal's record in **graduates**
3. Remove Layal's record from **students**
4. Commit ("Creating Table") 

### Joins

Now, open the database **carDealership**. You will find 2 tables:

* Companies
  * ID
  * Name of the company
  * Date when the company was made
* Employees
  * ID
  * Name of the employee
  * Role of the employee
  * Company of the employee

In the **Execute SQL** tab, do the following:

1. Produce a table that contains, for each employee, his/her name, company name, and company date.
2. Find the name of **employees** that work in companies made before 2000.
3. Find the name of company that has a graphic designer.
4. Commit ("Joins") 

### Count & Filter

Go back the **myDatabase**.

In the **Execute SQL** tab, do the following:

1. Find the person with the highest number of points in **students**
2. Find the average of points in **students**
3. Find the number of students that have 500 points
4. Find the names of students that contains 's'
5. Find all students based on the decreasing order of their points
6. commit ("Count & Filter") 


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://codi.gitbook.io/docs/03_exercises/04_database/sql-exercise-master.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.