SQL is a standard language for storing, manipulating and retrieving data in databases.
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.
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:
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:
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
The SELECT DISTINCT
statement is used to return only distinct (different) values.
The WHERE clause is used to extract only those records that fulfill a specified condition.
WHERE + Operators
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:
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:
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:
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:
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:
MySQL Syntax:
Oracle Syntax:
The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
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.
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
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Inner Join
Left Join
The following SQL statement will select all customers, and any orders they might have:
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.
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:
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:
It's basically used where there is any relationship between rows stored in the same table.
The UNION operator is used to combine the result-set of two or more SELECT statements.
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!
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:
The following SQL statement lists the number of customers in each country, sorted high to low:
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
The following SQL statement lists the employees that have registered more than 10 orders:
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
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: