Report on SQL with code samples discussing basics, joins and aggregations.

Technical-paper.md

Introduction To SQL

What is SQL?

SQL stands for Structured Query Language. It is a language used for storing, accessing and manipulating data stored in databases. Relational Database Management systems(RDBMS) like MS SQL Server, MySQL or Oracle are the basis for SQL.

Basics of SQL

Data is stored in the form of tables which consists of rows and columns.
For example, given below is a table called Pets with the below fields.
Each row is called a record.

Pets:

ID	Name	Animal	Weight
1	Jimmy	dog	35
2	Zack	cat	48
3	Tom	dog	40
4	Rim	goat	28

SQL Queries(Basics)

SQL queries are statements that are used to access and manipulate data. We will discuss the basic keywords and their uses.

1. SELECT and FROM

If we want to select all the columns in the above table, we use the SELECT and FROM keywords as shown below.
Note: Keywords are not case sensitive

SELECT * FROM Pets;

This query returns all the records from the Pets table shown above. The * is used to select all the columns.

If we want to select a specific column say Name, then we use:

SELECT Name FROM Pets;

This query returns all the names of pets from the Pets table

2. WHERE

The WHERE clause is used to extract those records that fulfill a specific condition.
For example, if we want to select all names of the animals that are dogs, then we use:

SELECT Name 
FROM Pets 
WHERE  Animal=dog;

3. ORDER BY

The ORDER BY clause is used to order the table by a particular column in ascending or descending order.
For example, the below query orders the above table(Pets) based by the ID column in descending order.

SELECT *
FROM Pets
ORDER BY ID DESC;

SQL Aggregate functions

1. MIN() and MAX()

The MIN() function gives us the minimum value of the selected column. For example,

SELECT MIN(ID)
FROM Pets

The MAX() function gives us the maximum value of the selected column. For example,

SELECT MAX(ID)
FROM Pets

2.COUNT()

The COUNT() function returns the number of records that match a given condition. For example, if we want to return the number of records with the animal as a dog, we use

SELECT COUNT(Animal)
FROM Pets
WHERE Animal=dog

3. AVG()

The AVG() function returns the average value of a numeric column. For example, if we want to get the average weight of all the animals, we can use,

SELECT AVG(Weight)
FROM Pets

4. SUM()

The SUM() function returns the sum of a numeric column. For example, if we want to get the sum of weights of all the animals, we can use,

SELECT SUM(Weight)
FROM Pets

SQL Joins

Joins are used in SQL to combine data from two or more tables. SQL supports for types of JOINS - inner join, left join, right join and full join.
Let us consider the following two tables:-

Pets :

ID	Name	Animal	Weight
1	Jimmy	dog	35
2	Zack	cat	48
3	Tom	dog	40
4	Rim	goat	28
5	Johnny	rabbit	15

Owners :

ID	Name	Pet_ID
1	Agnish	2
2	Prajwal	4
3	Shridhar	1
4	Priya	3
5	Avinash	7

1. INNER JOIN

INNER JOIN is used to select the rows with common values from both the tables.

For example, if we want the owner names and their corresponding pet names of all the animals, we use
Note: AS keyword is used to alias a column name or table name

SELECT Pets.Name AS pet_name,Owners.Name AS owner_name
FROM Pets
INNER JOIN Owners
ON Pets.ID = Owners.Pet_ID

The above query will match the pet id's from both the tables and return the owner name corresponding with their pet name as shown.

pet_name	owner_name
Jimmy	Shridhar
Zack	Agnish
Tom	Priya
Rim	Prajwal

2. LEFT JOIN

LEFT JOIN is used to select all the rows from the left table and all the rows with common matching values from the right table
For example,

SELECT Pets.Name AS pet_name,Owners.Name AS owner_name
FROM Pets
LEFT JOIN Owners
ON Pets.ID = Owners.Pet_ID

The above query will return all the records in the left table and the matching records in both the tables as shown.

pet_name	owner_name
Jimmy	Shridhar
Zack	Agnish
Tom	priya
Rim	Prajwal
Johnny	NULL

3. RIGHT JOIN

RIGHT JOIN is used to select all the rows from the right table and all the rows with common matching values from the left table.
For example,

SELECT Pets.Name AS pet_name,Owners.Name AS owner_name
FROM Pets
RIGHT JOIN Owners
ON Pets.ID = Owners.Pet_ID

The above query will return all the records in the right table and the matching records in both the tables as shown.

pet_name	owner_name
Jimmy	Shridhar
Zack	Agnish
Tom	priya
Rim	Prajwal
NULL	Avinash

4. FULL JOIN

FULL JOIN is used to select all the rows from the left table and the right table.
For example,

SELECT Pets.Name AS pet_name,Owners.Name AS owner_name
FROM Pets
FULL JOIN Owners
ON Pets.ID = Owners.Pet_ID

The above query will return all the records in the right table and the left table as shown.

pet_name	owner_name
Jimmy	Shridhar
Zack	Agnish
Tom	priya
Rim	Prajwal
Johnny	NULL
NULL	Avinash

Conclusion

The basic usage of SQL such as selecting, conditioning, ordering, aggregating and joining of data has been covered. There are many more complex ways for querying a database that can serve to be very useful.

Reference Links

1. https://www.w3schools.com/sql/default.asp
2. https://www.kaggle.com/learn/intro-to-sql
3. https://www.kaggle.com/learn/advanced-sql