# SQL

SQL is a standard language for storing, manipulating and retrieving data in databases.

## Relational vs Non-relational database

Relational databases are structured. You have tables and these tables may have dependencies on each other, or relationships. A database for a store will have a table for customers and one for orders. These two tables are related, because an order is made by a customer.

Non-relational databases are document-oriented. This so called document type storage allows multiple 'categories' of data to be stored in one construct or Document. So using the previous example, a Customer document, would have the customer's information, a sub-category for all their orders, etc.

#### Relational SQL Databases
A relational database uses SQL. This is a fairly rigid and standard way of storing data using tables, columns and rows. Data is defined in a table which is usually atomic in nature; this means that a table should really only store data records on one entity or object at a time. Eg. A Table Customers should ONLY be storing customer records.

When additional details are required, or data needs to be associated with a record from one table to another, then we have what we call relationships. A common key is established between the two (or more) tables and this is used for that association there after.

Popular Relational SQL Database Systems:
- Microsoft SQL Server
- Oracle
- MySQL / MariaDB
- PostgreSQL
- Microsoft Azure SQL

#### Non-Relational NoSQL Database
No-sql databases allow far more flexibility and adaptability as you design your application.

NoSQL databases are document-oriented. Instead of using tables, these documents allow us to store unstructured data in a single document. So a document could contain a customer's details, as well as all their orders to date, their favourites, etc. Thi is more intuitive and requires fewer hops across tables to find all the data relating to a customer.The storage will not be as highly organized at with an Relational Database.

Popular Non-Relational No-SQL Databases:
- MongoDB
- Oracle NoSQL
- Apache CouchDB
- Redis

## Some of The Most Important SQL Commands
`SELECT` - extracts data from a database
`UPDATE` - updates data in a database
`DELETE` - deletes data from a database
`INSERT INTO` - inserts new data into a database
`CREATE DATABASE` - creates a new database
`ALTER DATABASE` - modifies a database
`CREATE TABLE` - creates a new table
`ALTER TABLE` - modifies a table
`DROP TABLE` - deletes a table
`CREATE INDEX` - creates an index (search key)
`DROP INDEX` - deletes an index

#### SELECT

SELECT * FROM Customers;
SELECT name, phone FROM Customers;

The `SELECT DISTINCT` statement is used to return only distinct (different) values.

SELECT DISTINCT column1, column2, ...
FROM table_name;

#### WHERE

The WHERE clause is used to extract only those records that fulfill a specified condition.

SELECT * FROM Customers
WHERE CustomerID=1;

WHERE + Operators

/* Between a certain range */
SELECT * FROM Products
WHERE Price BETWEEN 50 AND 60;

/* Search for a pattern */
SELECT * FROM Customers
WHERE City LIKE 's%';

/* To specify multiple possible values for a column */
SELECT * FROM Customers
WHERE City IN ('Paris','London', 'Berlin');

#### AND, OR and NOT Operators

- The AND operator displays a record if all the conditions separated by AND are TRUE.
- The OR operator displays a record if any of the conditions separated by OR is TRUE.
- The NOT operator displays a record if the condition(s) is NOT TRUE.

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

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

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München');

SELECT * FROM Customers
WHERE NOT Country='Germany' AND NOT Country='USA';

#### ORDER BY

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column. This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName:

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

#### INSERT INTO

It is possible to write the INSERT INTO statement in two ways.

The first way specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. The INSERT INTO syntax would be as follows:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

#### UPDATE

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

#### DELETE

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

DELETE FROM Customers;

#### TOP, LIMIT or ROWNUM

Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses ROWNUM.

SQL Server / MS Access Syntax:

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

/* e.g. to select 50 % of all records: */
SELECT TOP 50 PERCENT * FROM Customers;

MySQL Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

Oracle Syntax:

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

#### MIN() and MAX()

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

SELECT MIN(Price) AS SmallestPrice
FROM Products;

#### COUNT(), AVG() and SUM()

The COUNT() function returns the number of rows that matches a specified criteria.

The AVG() function returns the average value of a numeric column.

The SUM() function returns the total sum of a numeric column.

SELECT AVG(Price)
FROM Products;

#### LIKE

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

`%` - The percent sign represents zero, one, or multiple characters
`_` - The underscore represents a single character

/* ending with a: */
SELECT * FROM Customers
WHERE CustomerName LIKE '%a';

/* has 'or' in any position: */
SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';

/* has "r" in the second position: */
SELECT * FROM Customers
WHERE CustomerName LIKE '_r%';

/* starts with "a" and has at least 3 characters in length: */
SELECT * FROM Customers
WHERE CustomerName LIKE 'a__%';

/* does NOT start with "a": */
SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%';

#### JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

*Inner Join*


SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

*Left Join*


The following SQL statement will select all customers, and any orders they might have:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).

*Right Join*


The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

*Full Outer Join*


The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

The following SQL statement selects all customers, and all orders:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

*Self Join*

A self JOIN is a regular join, but the table is joined with itself.

One classic example is where you wanted to get a list of employees and their immediate managers:

SELECT e.employee AS employee, b.employee AS boss
FROM emptable e, emptable b
WHERE e.manager_id = b.empolyee_id

It's basically used where there is any relationship between rows stored in the same table.

#### UNION

The UNION operator is used to combine the result-set of two or more SELECT statements.

- Each SELECT statement within UNION must have the same number of columns
- The columns must also have similar data types
- The columns in each SELECT statement must also be in the same order

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

Note: If some customers or suppliers have the same city, each city will only be listed once, because `UNION` selects only distinct values. Use `UNION ALL` to also select duplicate values!

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

#### GROUP BY

The `GROUP BY` statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The `GROUP BY` statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

The following SQL statement lists the number of customers in each country:

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

The following SQL statement lists the number of customers in each country, sorted high to low:

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

#### HAVING

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

The following SQL statement lists the employees that have registered more than 10 orders:

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

## Primary and Foreign keys

The **PRIMARY KEY** constraint uniquely identifies each record in a table.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

For example ID can be used as PRIMARY KEY

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

A **FOREIGN KEY** is a key used to link two tables together.

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

"Persons" table:

| Person Id | Name | Age |
| -------- | -------- | --- |
| 1 | Debrow Alex | 22 |
| 2 | Merill Alice| 30 |

"Orders" table:

| Order Id | Order number | Person Id |
| -------- | -------- | -------- |
| 3233 | 3234bncss | 2 |
| 3234 | sd33244df | 2 |
| 3235 | 12ddds223 | 1 |

Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in the "Persons" table.

The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

How it's created:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);