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) );