INTRODUCTION TO SQL FOR DATA ANALYSIS

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