INTRODUCTION TO SQL FOR DATA ANALYSIS
Tags: datascience, sql, database
Published: 2023-02-18 07:19:53

#INTRODUCTION TO SQL
**SQL** is a short form for **Structured Query Language**. It is the most popular query language used by major relational database management systems such as MySQL, ORACLE and SQL Server. SQL is easy to learn as the statements comprise of descriptive English words and are not case sensitive.
We can create and interact with a database using SQL in an efficient and easy way. The benefit with SQL is that we don’t have to specify how to get the data from the database. Rather, we simply specify what is to be retrieved, and SQL does the rest. Although called a query language, SQL can do much more besides querying. SQL provides statements for defining the structure of
the data, manipulating data in the database, declare constraints and retrieve data from the database in various ways, depending on our requirements. 

## Relational Databases
Here we'll be discussing on Relational Databases, which is a collection of pre-defined relationships between them. It collects different types of data sets that use tables, rows(records) and columns(fields). A **table** is used to store information about an entity, for example, a student entity, whereas **fields** are used to hold the various characteristics of an entity such as the student registration number, age and course, while **records** are the stored data about a particular entity.

## Basic SQL Syntax

**- CREATE DATABASE**
The create database statement is used in creating a database, for example, creating a database called Student, we create as below:

```
CREATE DATABASE DatabaseName; 
USE DatabaseName;
```
**NB:** A Semi-colon is used at the end of each statement to mark the end of a single SQL statement.
**NB:** USE statement is used for making the specified
database as active database.

**- CREATE Table**
After creating a database, we create tables which are used to hold the various database entities, their records and fields, for example:

```
CREATE TABLE Student{
StudentID PRIMARY KEY INT NOT NULL,
StudentName VARCHAR(20) NOT NULL,
Course VARCHAR(20) NOT NULL
};

```
**NB:** A primary key is used to uniquely identify each record in a table.

**- DESCRIBE Table**
The DESCRIBE statement is used to create the structure of an already created table, as below;

```
DESCRIBE tablename;
```

**- DROP Statement**
The DROP statement is used to remove a table or a database permanently from a system. However, one should be very cautious while using this statement because it cannot be undone.
_Syntax to drop a table:_

```
DROP TABLE tableName;

```
_Syntax to drop a database:_

```
DROP DATABASE databaseName;

```

## SQL For Data Manipulation
Data Manipulation using SQL means either retrieval of existing data, insertion of new data, removal of existing data or modification of existing data in the database.

**- Insertion of Records**
The INSERT INTO statement is used to insert new records in a table. For example, in a student table with, registration number, name, age, and gender as fields, records can be inserted as below:

```
INSERT INTO Student(reg_no, name, age, gender) VALUES(123, "James",23,"Male"),(234,"Mary",34,"Female");
```

**- Data Updation**
The UPDATE statement is used to make changes in the value(s) of one or more columns of existing records in a table. For example:

```
UPDATE table_name
SET attribute1 = value1, attribute2 = value2
WHERE condition 

```

**- Data Deletion**
The DELETE statement is used to delete one or more
records from a table.Synax:

```
DELETE FROM table_name
WHERE condition;
```

## SQL For Data Queries
SQL has efficient mechanisms to retrieve data stored in multiple tables. The user enters the SQL commands called queries where
the specific requirements for data to be retrieved are
provided.

**- SELECT Statement**
Is used to retrieve data from the tables in a database.
_Syntax:_
```
SELECT attribute1, attribute2, ...
FROM table_name
WHERE condition;
```
_For example;_

```
SELECT name, age, course
FROM Students
WHERE age>20;
```

**- WHERE Clause**
Is used to retrieve data that meet
some specified conditions. For example, the below example is used to select students whose age is greater than 20 years old;

```
SELECT name, age, course
FROM Students
WHERE age>20;

```

**- ORDER BY Clause**
The ORDER BY clause is used to display data in an ordered form with respect to a specified column.For example;

```
SELECT *
FROM employee
ORDER BY salary;
```

## SQL Joins
They are used to query data from two or more tables, based on a relationship between certain columns in these tables.

**Different SQL Joins**

**JOIN**-Returns rows when there is at least one match in both tables.
**LEFT JOIN**-Returns all rows from the left table, even if there are no matches in the right table.
**RIGHT JOIN**-Returns all rows from the right table even if there are no matches in the left table. 
**FULL JOIN**-Returns rows when there is a match in one of the tables.

##Different SQL Joins
###Inner Join
The INNER JOIN keyword returns rows when there is at least one match in both. For example the persons and orders taable below:

_Persons Table:_

| P_ID | Name | Address | City  |
| --- | --- | --- | --- |
| 1    | John   | 13 | Kisumu   |
| 2    | Nimrod | 2  | Nyeri    |
| 3    | Elvis  | 1  |  Muranga |

_Orders table:_

| Order_ID | Order_No | Order_PID |
| --- | --- | --- |
| 1 | 1245 | 3 |
| 2 | 1256 | 3 |
| 3 | 1367 | 1 |
| 4 | 4398 | 1 |
| 5 | 4392 | 15 |

To perform INNER JOIN, we use the following SELECT STATEMENT:

```
SELECT P_ID, Name, Order_ID
FROM person
INNER JOIN P_ID ON P_ID=Order_PID;
```
The results set will look lie this:

| Name | Order_ID |
| --- | --- |
| Elvis | 1245 |
| Elvis | 1256 |
| John | 1367 |
| John | 4398 |

###Left Join
The LEFT JOIN keyword returns all the rows from the left table(Persons), even if there are no matches in the right table(Orders).
_Syntax:_

```
SELECT Name, Order_ID
FROM Persons
LEFT JOIN Orders ON P_ID=Order_PID;
```
This yields the following set:

|Name | Order_ID |
| --- | --- |
| Elvis | 1245 |
| Elvis | 1256 |
| John | 1367 |
| John | 4398 |
| Nimrod |  |

###Right Join
The RIGHT JOIN keyword returns all the rows from the right table(Orders), even if there are no matches in the left table(Persons).
_Syntax:_

```
SELECT Name, Order_ID
FROM Persons
RIGHT JOIN Orders ON P_ID=Order_PID;
```
This returns the following result:

| Name | Order_ID |
| --- | --- |
| Elvis | 1245 |
| Elvis | 1256 |
| John | 1367 |
| John | 4398 |
|  | 4392 |

###Full Join
Returns rows when there is a match in one of the tables.
_Syntax:_

```
SELECT Names, Order_ID
FROM Persons
FULL JOIN Orders ON P_ID=Orders_PID;
```
This would yield the following:

| Name | Order_ID |
| --- | --- |
| Elvis | 1245 |
| Elvis | 1256 |
| John | 1367 |
| John | 4398 |
| Nimrod | |
|  | 4392 |