SQL

01. Concepto General

SQL keywords are NOT case sensitive: select is the same as SELECT

Semicolon after SQL Statements?
Some database systems require a semicolon at the end of each SQL statement.

Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

In this tutorial, we will use semicolon at the end of each SQL statement.

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

En caso de que el nombre de la columna incluya espacios o comillas se requiere encerrar el nombre de la columna en comillas dobles

CRUD :

Create
Read
Update
Delete

02.1 SQL SELECT Statement 1

SELECT INSERT INTO:

Copia datos de una tabla y los inserta en una tabla existente sin afectar las filas previamente existentes en la tabla de destino.

Es una suma horizontal de campos

Sintaxis 1: se copian todas las columnas de una tabla a otra tabla previamente existente

		INSERT INTO table2
		SELECT * FROM table1;
		
	
Sintaxis 2: se copian unicamente determinadas columnas a otra tabla previamente existente
	
		INSERT INTO table2
		(column_name(s))
		SELECT column_name(s)
		FROM table1;
		
Ejemplo 1 : se copian solamente algunas columnas de la tabla Suppliers a la tabla Country
	
		INSERT INTO Customers (CustomerName, Country)
		SELECT SupplierName, Country FROM Suppliers;
		
	
Ejemplo 2 : se copian solamente los proveedores Alemanes a la tabla de clientes
	
		INSERT INTO Customers (CustomerName, Country)
		SELECT SupplierName, Country FROM Suppliers
		WHERE Country='Germany';
		
		

02.2 SQL SELECT Statement 2

SELECT:

Se usa para seleccionar registros de una base de datos:

	• Sintaxis :
	
		SELECT column_name,column_name
		FROM table_name;
		
	y :
	
		SELECT * FROM table_name;
		
	• Ejemplo 1 :
	
		SELECT CustomerName,City FROM Customers;
		
	• Ejemplo 2 :
	
		SELECT * FROM Customers;

02.3 DISTINCT

Select Distinct :

Se usa para seleccionar solo los valores diferentes de una tabla

Sintaxis :
	
		SELECT DISTINCT column_name,column_name
		FROM table_name;
		
Ejemplo :
	
		SELECT DISTINCT City FROM Customers;

02.4 WHERE

WHERE:

Esta clausula se usa pra filtrar records en un select

Sintaxis :
	
		SELECT column_name,column_name
		FROM table_name
		WHERE column_name operator value;
		
Ejemplo :
	
		SELECT * FROM Customers
		WHERE Country='Mexico';
		
Es importante tener en cuenta que para el caso de los strings, estos deben estar encerrados en comillas simples, los numeros no se encierran en comillas
	
Operadores :

Operator	Description	
=	            Equal	
>	            Greater than	
<	            Less than	
>=	            Greater than or equal	
<=	            Less than or equal	
<>	            Not equal. Note: In some versions of SQL this operator may be written as !=	
BETWEEN	   Between a certain range	
LIKE	            Search for a pattern	
IN	            To specify multiple possible values for a column

02.5 AND & OR

AND & OR :

Estos operadores se usan para filtrar datos basados en mas de una condicion

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

02.6 ORDER BY

ORDER BY :

Se usa para ordenar los resultados del query por una o mas columnas.

El ordenamiento por default se hace en orden ascendente, para ejecutar el sort en orden descendente se usa DESC

Syntax :
	
		SELECT column_name, column_name
		FROM table_name
		ORDER BY column_name ASC|DESC, column_name ASC|DESC;
		
Ejemplo ORDER BY :
	
		SELECT * FROM Customers
		ORDER BY Country;
		
Ejemplo ORDER BY DESC :
	
		SELECT * FROM Customers
		ORDER BY Country DESC;
		
Ejemplo ORDER BY con varias columnas:
	
		SELECT * FROM Customers
		ORDER BY Country, CustomerName;
		
Ejemplo ORDER BY con varias columnas, ordenando ascendentemente por una y descendentemente por otra :
	
		SELECT * FROM Customers
		ORDER BY Country ASC, CustomerName DESC;

02.7 SELECT INTO

SELECT INTO :

Con esta instruccion se copian datos de una tabla y se insertan en una tabla nueva

Sintaxis 1: se copian todos los campos a la nueva tabla
	
		SELECT *
		INTO newtable [IN externaldb]
		FROM table1;
	
	
Sintaxis 2: se copian determinados campos a la nueva tabla
	
		SELECT column_name(s)
		INTO newtable [IN externaldb]
		FROM table1;
		
La nueva tabla sera creada con los nombres y los tipos de datos definidos en la sentencia SELECT. Si se desean nuevos nombres se usa la clausula AS
	
Ejemplo 1 : se crea un backup de la tabla Customers
	
		SELECT *
		INTO CustomersBackup2013
		FROM Customers;
		
	
Ejemplo 2 : se copian solo algunos campos a la nueva tabla
	
		SELECT CustomerName, ContactName
		INTO CustomersBackup2013
		FROM Customers;
		
	
Ejemplo 3 : se copian solo los clientes Alemanes a la nueva tabla
	
		SELECT *
		INTO CustomersBackup2013
		FROM Customers
		WHERE Country='Germany';
		
	
Ejemplo 4 : se copian datos de mas de una tabla a la nueva tabla
	
		SELECT Customers.CustomerName, Orders.OrderID
		INTO CustomersOrderBackup2013
		FROM Customers
		LEFT JOIN Orders
		ON Customers.CustomerID=Orders.CustomerID;
	
	
Ejemplo 5 : se puede usar para crear una nueva tabla vacia con el esquema de otra
	
		SELECT *
		INTO newtable
		FROM table1
		WHERE 1=0;

02.8 SELECT INSERT INTO

SELECT INSERT INTO:

Copia datos de una tabla y los inserta en una tabla existente sin afectar las filas previamente existentes en la tabla de destino.

Es una suma horizontal de campos

Sintaxis 1: se copian todas las columnas de una tabla a otra tabla previamente existente

		INSERT INTO table2
		SELECT * FROM table1;
		
	
Sintaxis 2: se copian unicamente determinadas columnas a otra tabla previamente existente
	
		INSERT INTO table2
		(column_name(s))
		SELECT column_name(s)
		FROM table1;
		
Ejemplo 1 : se copian solamente algunas columnas de la tabla Suppliers a la tabla Country
	
		INSERT INTO Customers (CustomerName, Country)
		SELECT SupplierName, Country FROM Suppliers;
		
	
Ejemplo 2 : se copian solamente los proveedores Alemanes a la tabla de clientes
	
		INSERT INTO Customers (CustomerName, Country)
		SELECT SupplierName, Country FROM Suppliers
		WHERE Country='Germany';
		
		

03. SQL SELECT DISTINCT Statement

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

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

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

SELECT Example Without DISTINCT
The following SQL statement selects ALL (including the duplicates) values from the "Country" column in the "Customers" table:

Example

SELECT Country FROM Customers;
Now, let us use the DISTINCT keyword with the above SELECT statement and see the result.


SELECT DISTINCT Examples
The following SQL statement selects only the DISTINCT values from the "Country" column in the "Customers" table:

Example

SELECT DISTINCT Country FROM Customers;
The following SQL statement lists the number of different (distinct) customer countries:

Example

SELECT COUNT(DISTINCT Country) FROM Customers;

Note: The example above will not work in Firefox and Microsoft Edge! Because COUNT(DISTINCT column_name) is not supported in Microsoft Access databases. Firefox and Microsoft Edge are using Microsoft Access in our examples.

Here is the workaround for MS Access:

Example

SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT Country FROM Customers);

04. SQL WHERE Clause

The SQL WHERE Clause
The WHERE clause is used to filter records.

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

WHERE Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Note: The WHERE clause is not only used in SELECT statement, it is also used in UPDATE, DELETE statement, etc.!

WHERE Clause Example
The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table:

Example
SELECT * FROM Customers
WHERE Country='Mexico';
Text Fields vs. Numeric Fields
SQL requires single quotes around text values (most database systems will also allow double quotes).

However, numeric fields should not be enclosed in quotes:

Example
SELECT * FROM Customers
WHERE CustomerID=1;
Operators in The WHERE Clause
The following operators can be used in the WHERE clause:

Operator	Description	
=	            Equal	
>	            Greater than	
<	            Less than	
>=	            Greater than or equal	
<=	            Less than or equal	
<>	            Not equal. Note: In some versions of SQL this operator may be written as !=	
BETWEEN	   Between a certain range	
LIKE	            Search for a pattern	
IN	            To specify multiple possible values for a column

05. SQL AND, OR and NOT Operators

The SQL AND, OR and NOT Operators
The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one condition:

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.

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

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

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

AND Example
The following SQL statement selects all fields from "Customers" where country is "Germany" AND city is "Berlin":

Example
SELECT * FROM Customers
WHERE Country='Germany' AND City='Berlin';

OR Example
The following SQL statement selects all fields from "Customers" where city is "Berlin" OR "München":

Example
SELECT * FROM Customers
WHERE City='Berlin' OR City='München';

The following SQL statement selects all fields from "Customers" where country is "Germany" OR "Spain":

Example
SELECT * FROM Customers
WHERE Country='Germany' OR Country='Spain';

NOT Example
The following SQL statement selects all fields from "Customers" where country is NOT "Germany":

Example
SELECT * FROM Customers
WHERE NOT Country='Germany';

Combining AND, OR and NOT
You can also combine the AND, OR and NOT operators.

The following SQL statement selects all fields from "Customers" where country is "Germany" AND city must be "Berlin" OR "München" (use parenthesis to form complex expressions):

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

The following SQL statement selects all fields from "Customers" where country is NOT "Germany" and NOT "USA":

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

06. SQL ORDER BY Keyword

The SQL ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

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

ORDER BY Example
The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" column:

Example
SELECT * FROM Customers
ORDER BY Country;

ORDER BY DESC Example

The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column:

Example
SELECT * FROM Customers
ORDER BY Country DESC;
ORDER BY Several Columns Example

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:

Example
SELECT * FROM Customers
ORDER BY Country, CustomerName;

ORDER BY Several Columns Example 2
The following SQL statement selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column:

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

07. SQL INSERT INTO Statement

The SQL INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Syntax
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, ...);

INSERT INTO Example
The following SQL statement inserts a new record in the "Customers" table:

Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
The selection from the "Customers" table will now look like this:

Did you notice that we did not insert any number into the CustomerID field?
The CustomerID column is an auto-increment field and will be generated automatically when a new record is inserted into the table.

Insert Data Only in Specified Columns
It is also possible to only insert data in specific columns.

The following SQL statement will insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns (CustomerID will be updated automatically):

Example
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
The selection from the "Customers" table will now look like this:

Se usa para insertar nuevos registros en una tabla

SINTAXIS :
	
Esta instruccion es posible escribirla en dos formas :
	
La primera forma, no especifica los nombres de las columnas donde los datos seran insertados, solo los valores a insertar :
			
				INSERT INTO table_name
				VALUES (value1,value2,value3,...);
				 
La segunda forma especifica los nombres de las columnas y los datos :
		
				INSERT INTO table_name (column1,column2,column3,...)
				VALUES (value1,value2,value3,...);
				
EJEMPLO INSERTANDO UNA FILA COMPLETA EN UNA TABLA :
	
		INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
		VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');
		
EJEMPLO INSERTANDO DATOS EN COLUMNAS ESPECIFICAS :
	
		INSERT INTO Customers (CustomerName, City, Country)
		VALUES ('Cardinal', 'Stavanger', 'Norway');

08. SQL NULL Values

What is a NULL Value?
A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

How to Test for NULL Values?
It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

IS NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

IS NOT NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

The IS NULL Operator
The IS NULL operator is used to test for empty values (NULL values).

The following SQL lists all customers with a NULL value in the "Address" field:

Example
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;

Tip: Always use IS NULL to look for NULL values.

The IS NOT NULL Operator
The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).

The following SQL lists all customers with a value in the "Address" field:

Example
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;

09. SQL UPDATE Statement

SQL UPDATE Statement
The SQL UPDATE Statement
The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!


UPDATE Table
The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city.

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


UPDATE Multiple Records
It is the WHERE clause that determines how many records will be updated.

The following SQL statement will update the contactname to "Juan" for all records where country is "Mexico":

Example
UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';


Update Warning!
Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

Example
UPDATE Customers
SET ContactName='Juan';

Se utiliza para actualizar registros en una tabla

Sintaxis :
	
		UPDATE table_name
		SET column1=value1,column2=value2,...
		WHERE some_column=some_value;
		
Ejemplo :
	
		UPDATE Customers
		SET ContactName='Alfred Schmidt', City='Hamburg'
		WHERE CustomerName='Alfreds Futterkiste';

10. SQL DELETE Statement

The SQL DELETE Statement
The DELETE statement is used to delete existing records in a table.

DELETE Syntax
DELETE FROM table_name WHERE condition;
Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!

SQL DELETE Example
The following SQL statement deletes the customer "Alfreds Futterkiste" from the "Customers" table:

Example
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

Delete All Records
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 table_name;

The following SQL statement deletes all rows in the "Customers" table, without deleting the table:

Example
DELETE FROM Customers;

Se usa para eliminar registros de una tabla

Sintaxis :
	
		DELETE FROM table_name
		WHERE some_column=some_value;
		
Ejemplo eliminando un registro especifico:
	
		DELETE FROM Customers
		WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';
		
Ejemplo eliminando todos los registros :
	
		DELETE FROM table_name;
		
		or
		
		DELETE * FROM table_name;

11. SQL TOP, LIMIT or ROWNUM Clause

The SQL SELECT TOP Clause

The SELECT TOP clause is used to specify the number of records to return.

The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.

Note: 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;


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;

SQL TOP, LIMIT and ROWNUM Examples

The following SQL statement selects the first three records from the "Customers" table (for SQL Server/MS Access):

Example
SELECT TOP 3 * FROM Customers;
The following SQL statement shows the equivalent example using the LIMIT clause (for MySQL):

Example
SELECT * FROM Customers
LIMIT 3;
The following SQL statement shows the equivalent example using ROWNUM (for Oracle):

Example
SELECT * FROM Customers
WHERE ROWNUM <= 3;
SQL TOP PERCENT Example
The following SQL statement selects the first 50% of the records from the "Customers" table (for SQL Server/MS Access):

Example
SELECT TOP 50 PERCENT * FROM Customers;
ADD a WHERE CLAUSE

The following SQL statement selects the first three records from the "Customers" table, where the country is "Germany" (for SQL Server/MS Access):

Example
SELECT TOP 3 * FROM Customers
WHERE Country='Germany';

The following SQL statement shows the equivalent example using the LIMIT clause (for MySQL):

Example
SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3;

The following SQL statement shows the equivalent example using ROWNUM (for Oracle):

Example
SELECT * FROM Customers
WHERE Country='Germany' AND ROWNUM <= 3;

12. SQL MIN() and MAX() Functions

SQL MIN() and MAX() Functions
The SQL MIN() and MAX() Functions

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

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

MIN() Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition;

MAX() Syntax
SELECT MAX(column_name)
FROM table_name
WHERE condition;

MIN() Example
The following SQL statement finds the price of the cheapest product:

Example
SELECT MIN(Price) AS SmallestPrice
FROM Products;

MAX() Example
The following SQL statement finds the price of the most expensive product:

Example
SELECT MAX(Price) AS LargestPrice
FROM Products;

13. SQL COUNT(), AVG() and SUM()

The SQL COUNT(), AVG() and SUM() Functions

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

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

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

COUNT() Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;

AVG() Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;

SUM() Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;

COUNT() Example
The following SQL statement finds the number of products:

Example
SELECT COUNT(ProductID)
FROM Products;
Note: NULL values are not counted.

AVG() Example
The following SQL statement finds the average price of all products:

Example
SELECT AVG(Price)
FROM Products;
Note: NULL values are ignored.

SUM() Example
The following SQL statement finds the sum of the "Quantity" fields in the "OrderDetails" table:

Example
SELECT SUM(Quantity)
FROM OrderDetails;
Note: NULL values are ignored.

14. SQL LIKE Operator

The SQL LIKE Operator
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

Note: MS Access uses an asterisk (*) instead of the percent sign (%), and a question mark (?) instead of the underscore (_).

The percent sign and the underscore can also be used in combinations!

LIKE Syntax
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
Tip: You can also combine any number of conditions using AND or OR operators.

Here are some examples showing different LIKE operators with '%' and '_' wildcards:

                                    LIKE Operator	            Description
WHERE CustomerName      LIKE 'a%'	        Finds any values that start with "a"
WHERE CustomerName      LIKE '%a'	        Finds any values that end with "a"
WHERE CustomerName      LIKE '%or%'.     Finds any values that have "or" in any position
WHERE CustomerName      LIKE '_r%'	        Finds any values that have "r" in the second position
WHERE CustomerName      LIKE 'a_%'	        Finds any values that start with "a" and are at least 2 characters in length
WHERE CustomerName      LIKE 'a__%'.      Finds any values that start with "a" and are at least 3 characters in length
WHERE ContactName         LIKE 'a%o'	        Finds any values that start with "a" and ends with "o"

SQL LIKE Examples

15. SQL Wildcards

SQL Wildcard Characters
A wildcard character is used to substitute one or more characters in a string.

Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Wildcard Characters in MS Access
Symbol	Description	Example
*	Represents zero or more characters	bl* finds bl, black, blue, and blob
?	Represents a single character	h?t finds hot, hat, and hit
[]	Represents any single character within the brackets	h[oa]t finds hot and hat, but not hit
!	Represents any character not in the brackets	h[!oa]t finds hit, but not hot and hat
-	Represents a range of characters	c[a-b]t finds cat and cbt
#	Represents any single numeric character	2#5 finds 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295

Wildcard Characters in SQL Server
Symbol	Description	Example
%	Represents zero or more characters	bl% finds bl, black, blue, and blob
_	Represents a single character	h_t finds hot, hat, and hit
[]	Represents any single character within the brackets	h[oa]t finds hot and hat, but not hit
^	Represents any character not in the brackets	h[^oa]t finds hit, but not hot and hat
-	Represents a range of characters	c[a-b]t finds cat and cbt
All the wildcards can also be used in combinations!

Here are some examples showing different LIKE operators with '%' and '_' wildcards:

LIKE                                    Operator	                        Description
WHERE CustomerName          LIKE 'a%'	Finds any values that starts with "a"
WHERE CustomerName          LIKE '%a'	Finds any values that ends with "a"
WHERE CustomerName          LIKE '%or%'	Finds any values that have "or" in any position
WHERE CustomerName          LIKE '_r%'	Finds any values that have "r" in the second position
WHERE CustomerName          LIKE 'a_%_%'	Finds any values that starts with "a" and are at least 3 characters in length
WHERE ContactName             LIKE 'a%o'	Finds any values that starts with "a" and ends with "o"


Using the % Wildcard

The following SQL statement selects all customers with a City starting with "ber":

Example
SELECT * FROM Customers
WHERE City LIKE 'ber%';



The following SQL statement selects all customers with a City containing the pattern "es": 

Example
SELECT * FROM Customers
WHERE City LIKE '%es%';

Using the _ Wildcard



The following SQL statement selects all customers with a City starting with any character, followed by "ondon":

Example
SELECT * FROM Customers
WHERE City LIKE '_ondon';



The following SQL statement selects all customers with a City starting with "L", followed by any character, followed by "n", followed by any character, followed by "on":

Example
SELECT * FROM Customers
WHERE City LIKE 'L_n_on';



Using the [charlist] Wildcard

The following SQL statement selects all customers with a City starting with "b", "s", or "p":

Example
SELECT * FROM Customers
WHERE City LIKE '[bsp]%';



The following SQL statement selects all customers with a City starting with "a", "b", or "c":

Example
SELECT * FROM Customers
WHERE City LIKE '[a-c]%';
Using the [!charlist] Wildcard



The two following SQL statements select all customers with a City NOT starting with "b", "s", or "p":

Example
SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';
Or:

Example
SELECT * FROM Customers
WHERE City NOT LIKE '[bsp]%';

16. SQL IN Operator

The SQL IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

or:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);


IN Operator Examples
The following SQL statement selects all customers that are located in "Germany", "France" or "UK":

Example
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

The following SQL statement selects all customers that are NOT located in "Germany", "France" or "UK":

Example
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

The following SQL statement selects all customers that are from the same countries as the suppliers:

Example
SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

17. SQL BETWEEN Operator

The SQL BETWEEN Operator
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included. 

BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

BETWEEN Example
The following SQL statement selects all products with a price BETWEEN 10 and 20:

Example
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

NOT BETWEEN Example
To display the products outside the range of the previous example, use NOT BETWEEN:

Example
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

BETWEEN with IN Example
The following SQL statement selects all products with a price BETWEEN 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3:

Example
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);

BETWEEN Text Values Example
The following SQL statement selects all products with a ProductName BETWEEN Carnarvon Tigers and Mozzarella di Giovanni:

Example
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

The following SQL statement selects all products with a ProductName BETWEEN Carnarvon Tigers and Chef Antons Cajun Seasoning:

Example
SELECT * FROM Products
WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning"
ORDER BY ProductName;

NOT BETWEEN Text Values Example
The following SQL statement selects all products with a ProductName NOT BETWEEN Carnarvon Tigers and Mozzarella di Giovanni:

Example
SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

BETWEEN Dates Example
The following SQL statement selects all orders with an OrderDate BETWEEN '01-July-1996' and '31-July-1996':

Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#;

OR:

Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

18. SQL Aliases

SQL Aliases
SQL aliases are used to give a table, or a column in a table, a temporary name.

Aliases are often used to make column names more readable.

An alias only exists for the duration of the query.

Alias Column Syntax
SELECT column_name AS alias_name
FROM table_name;

Alias Table Syntax
SELECT column_name(s)
FROM table_name AS alias_name;

Alias for Columns Examples
The following SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:

Example
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

The following SQL statement creates two aliases, one for the CustomerName column and one for the ContactName column. Note: It requires double quotation marks or square brackets if the alias name contains spaces:

Example
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;


The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country):

Example
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;

Note: To get the SQL statement above to work in MySQL use the following:

SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;

Alias for Tables Example
The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (Here we use aliases to make the SQL shorter):

Example
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;

The following SQL statement is the same as above, but without aliases:

Example
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;

Aliases can be useful when:

There are more than one table involved in a query
Functions are used in the query
Column names are big or not very readable
Two or more columns are combined together

ALIASES:

Los alias son usados para temporalmente darle un nombre a una tabla de la base de datos o a una columna de la tabla. Basicamente los alias sirven para asignar temporalmente nombres mas faciles y rapidos de leer.

Sintaxis para Columnas:
	
		SELECT column_name AS alias_name
		FROM table_name;
		
Sintaxis para Tablas:
	
		SELECT column_name(s)
		FROM table_name AS alias_name;

------------------------------------------------------------------------------------------------------------------------------------------
		
		
COLUMNAS:
	
Ejemplo 1 : se especifican dos alias, uno para la columna CustomerName y otro para la columna ContactName
	
		SELECT CustomerName AS Customer, ContactName AS [Contact Person]
		FROM Customers;
		
	
Ejemplo 2 : en el siguiente ejemplo se combinan cuatro columnas (Address, City, PostalCode y Country) y se crea un alias llamado Address
	
		SELECT CustomerName, CONCAT(Address,', ',City,', ',PostalCode,', ',Country) AS Address
		FROM Customers;

------------------------------------------------------------------------------------------------------------------------------------------
		
	
TABLES:
	
Ejemplo  : 
	
		SELECT o.OrderID, o.OrderDate, c.CustomerName
		FROM Customers AS c, Orders AS o
		WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;

------------------------------------------------------------------------------------------------------------------------------------------
		
UTILIDAD :

Los Alias son muy utiles para :

- Cuando hay mas de una tabla involucrada en un query
- Se usan funciones en el query
- Los nombres de las columnas son muy largos o muy dificiles de leer y escribir
- Dos o mas columnas se van a combinar 

19. SQL Joins

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

Let's look at a selection from the "Orders" table:

OrderID	CustomerID	   OrderDate
10308	            2	            1996-09-18
10309	            37	            1996-09-19
10310	            77	            1996-09-20

Then, look at a selection from the "Customers" table:

CustomerID	                CustomerName	                                 ContactName	                Country
1	                        Alfreds Futterkiste	                                Maria Anders	               Germany
2	              Ana Trujillo Emparedados y helados	                        Ana Trujillo	                Mexico
3	                    Antonio Moreno Taquería	                                Antonio Moreno	                Mexico

Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.

Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:

Example
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
and it will produce something like this:

OrderID	        CustomerName	                        OrderDate
10308	    Ana Trujillo Emparedados y helados	        9/18/1996
10365	    Antonio Moreno Taquería	                        11/27/1996
10383	            Around the Horn	                        12/16/1996
10355	            Around the Horn	                        11/15/1996
10278	           Berglunds snabbköp	                    8/12/1996

Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:

(INNER) JOIN: Returns records that have matching values in both tables : EQUIVALENTE A INTERSECCION

LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table : EQUIVALENTE A INTERSECCION + TODO EL CONJUNTO DE LA IZQUIERDA

RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table :
EQUIVALENTE A INTERSECCION + TODO EL CONJUNTO DE LA DERECHA

FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table : EQUIVALENTE A LA INTERSECCION Y TODO EL CONJUNTO DE LA IZQUIERDA Y TODO EL CONJUNTO DE LA DERECHA


JOIN :

La clausula join es usada para combinar filas de dos o mas tablas, basandose en un campo comun.

Las tablas usadas para los ejemplos de join :

INNER JOIN :

Seleccionta los campos especificados de todas las filas de ambas tablas en tanto halla match entre las columnas de de las tablas

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

Sintaxis 2:
	
		SELECT column_name(s)
		FROM table1
		JOIN table2
		ON table1.column_name=table2.column_name;
		

INNER JOIN es lo mismo que simplemente escribir JOIN
	
			
	• Ejemplo : en este codigo se retornan todos los clientes con ordenes
	
		SELECT Customers.CustomerName, Orders.OrderID
		FROM Customers
		INNER JOIN Orders
		ON Customers.CustomerID=Orders.CustomerID
		ORDER BY Customers.CustomerName;
		
Con este INNER JOIN se seleccionan los campos de CustomerName y OrderID de todos los registros de las tablas Customers y Orders cuyos ID hagan match
		
LEFT JOIN :

Selecciona determinados campos de todas las filas de la tabla izquierda junto con aquellas filas que hagan match con la tabla derecha. El resultado es NULL si en la tabla del lado derecho no hay match.

Es decir a la tabla izquierda le trae todos los campos correspondientes a las filas de la tabla derecha y si no hay correspondencia le trae NULL.

Sintaxis 1:
	
		SELECT column_name(s)
		FROM table1
		LEFT JOIN table2
		ON table1.column_name=table2.column_name;
	

Sintaxis 2:
	
		SELECT column_name(s)
		FROM table1
		LEFT OUTER JOIN table2
		ON table1.column_name=table2.column_name;
		
En algunas bases de datos LEFT JOIN es denominado LEFT OUTER JOIN
	
Ejemplo : en este codigo se retornan todos los clientes y sus ordenes en caso de existir, si un cliente no tiene ordenes le trae NULL al numero de orden
	
		SELECT Customers.CustomerName, Orders.OrderID
		FROM Customers
		LEFT JOIN Orders
		ON Customers.CustomerID=Orders.CustomerID
		ORDER BY Customers.CustomerName;
		
Con este LEFT JOIN se traen los campos de CustomerName y ORDERID correspondientes a todas las filas de la tabla izquierda, independientemente halla o no match en el ID con la tabla de la derecha, para aquellas filas de la izquierda donde no halla match en el ID se asigna NULL para el valor del OrderID

RIGHT JOIN :

Selecciona determinados campos de todas las filas de la tabla derecha junto con aquellas filas que hagan match con la tabla izquierda. El resultado es NULL si en la tabla del lado izquierdo no hay match.

Es decir a la tabla derecha le trae todos los campos correspondientes a las filas de la tabla izquierda y si no hay correspondencia le trae NULL.

Sintaxis 1:
	
		SELECT column_name(s)
		FROM table1
		RIGHT JOIN table2
		ON table1.column_name=table2.column_name;
	

Sintaxis 2:
	
		SELECT column_name(s)
		FROM table1
		RIGHT OUTER JOIN table2
		ON table1.column_name=table2.column_name;
		
En algunas bases de datos RIGHT JOIN es denominado RIGHT OUTER JOIN
	
Ejemplo : en este codigo se retornan todos los empleados incluso si no tomaron ninguna orden
	
		SELECT Orders.OrderID, Employees.FirstName
		FROM Orders
		RIGHT JOIN Employees
		ON Orders.EmployeeID=Employees.EmployeeID
		ORDER BY Orders.OrderID;
		
Con este RIGHT JOIN se traen los campos requeridos de la tabla de Empleados, incluso si no hay match con la tabla de Ordenes

FULL JOIN :

Selecciona determinados campos de todas las filas de la tabla izquierda y de todas las filas de la derecha

Sintaxis :
	
		SELECT column_name(s)
		FROM table1
		FULL OUTER JOIN table2
		ON table1.column_name=table2.column_name;
	
En algunas bases de datos RIGHT JOIN es denominado RIGHT OUTER JOIN
	
Ejemplo : en este codigo se retornan todos los empleados incluso si no tomaron ninguna orden
	
		SELECT Customers.CustomerName, Orders.OrderID
		FROM Customers
		FULL OUTER JOIN Orders
		ON Customers.CustomerID=Orders.CustomerID
		ORDER BY Customers.CustomerName;
		
Con este RIGHT JOIN se traen los campos requeridos de todos las filas de la tabla de Clientes y de la tabla de Ordenes

20. SQL INNER JOIN Keyword

SQL INNER JOIN Keyword
The INNER JOIN keyword selects records that have matching values in both tables.

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

SQL INNER JOIN

Demo Database
In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Orders" table:

OrderID	CustomerID	EmployeeID	OrderDate	    ShipperID
10308	            2	                    7	            1996-09-18	    3
10309	            37	                    3	            1996-09-19	    1
10310	            77	                    8	            1996-09-20	    2

And a selection from the "Customers" table:

CustomerID	CustomerName	        ContactName	             Address	             City	    PostalCode	        Country
1                   Alfreds Futterkiste	    Maria Anders	         Obere Str. 57	             Berlin	        12209	            Germany
2	        AnaTrujillo Emparedados	    Ana Trujillo	        Constitución 2222	    México D.F.	    05021	        Mexico
3	        Antonio Moreno Taquería	Antonio Moreno	        Mataderos 2312	    México D.F.	    05023	        Mexico

SQL INNER JOIN Example
The following SQL statement selects all orders with customer information:

Example
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown!

 JOIN Three Tables
The following SQL statement selects all orders with customer and shipper information:

Example
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

21. SQL LEFT JOIN Keyword

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

LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.

SQL LEFT JOIN

Demo Database
In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

CustomerID	CustomerName	            ContactName	        Address	            City	    PostalCode	    Country
1                   Alfreds Futterkiste	      Maria Anders	    Obere Str. 57	        Berlin	         12209         Germany
2	               Ana Trujillo Empare          Ana Trujillo	    Constitución 2222	México D.F.	    05021	    Mexico
3	            AntonioMoreno Taquería	Antonio Moreno	    Mataderos 2312	      México D.F.     05023	    Mexico

And a selection from the "Orders" table:

OrderID	CustomerID	EmployeeID	OrderDate	        ShipperID
10308	            2	                    7	           1996-09-18	             3
10309	            37	                    3	           1996-09-19	             1
10310	            77	                    8	           1996-09-20	             2

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

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

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

22. SQL RIGHT JOIN Keyword

SQL RIGHT JOIN Keyword
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.

RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SQL RIGHT JOIN

Demo Database
In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Orders" table:

OrderID	CustomerID	EmployeeID	OrderDate	    ShipperID
10308	            2	                    7	            1996-09-18	    3
10309	            37	                    3	            1996-09-19	    1
10310	            77	                    8	            1996-09-20	    2

And a selection from the "Employees" table:

EmployeeID	LastName	    FirstName	        BirthDate	        Photo
1	                    Davolio	        Nancy	            12/8/1968	    EmpID1.pic
2	                    Fuller           	Andrew	            2/19/1952	    EmpID2.pic
3	                    Leverling	        Janet	            8/30/1963	    EmpID3.pic

SQL RIGHT JOIN Example
The following SQL statement will return all employees, and any orders they might have placed:

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

Note: The RIGHT JOIN keyword returns all records from the right table (Employees), even if there are no matches in the left table (Orders).

23. SQL FULL OUTER JOIN Keyword

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

Note: FULL OUTER JOIN can potentially return very large result-sets!

Tip: FULL OUTER JOIN and FULL JOIN are the same.

FULL OUTER JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

SQL FULL OUTER JOIN

Demo Database
In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

CustomerID	CustomerName	            ContactName	        Address	            City	    PostalCode	    Country
1                   Alfreds Futterkiste	      Maria Anders	    Obere Str. 57	        Berlin	         12209         Germany
2	               Ana Trujillo Empare          Ana Trujillo	    Constitución 2222	México D.F.	    05021	    Mexico
3	            AntonioMoreno Taquería	Antonio Moreno	    Mataderos 2312	      México D.F.     05023	    Mexico

And a selection from the "Orders" table:

OrderID	CustomerID	EmployeeID	OrderDate	        ShipperID
10308	            2	                    7	           1996-09-18	             3
10309	            37	                    3	           1996-09-19	             1
10310	            77	                    8	           1996-09-20	             2

SQL FULL OUTER JOIN Example
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;

A selection from the result set may look like this:

CustomerName	                                                OrderID
Alfreds Futterkiste	                                            Null
Ana Trujillo Emparedados y helados	                    10308
Antonio Moreno Taquería	                                10365

Note: The FULL OUTER JOIN keyword returns all matching records from both tables whether the other table matches or not. So, if there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.

24. SQL Self JOIN

SQL Self JOIN

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

Self JOIN Syntax

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
T1 and T2 are different table aliases for the same table.

Demo Database
In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

CustomerID	CustomerName	            ContactName	        Address	            City	    PostalCode	    Country
1                   Alfreds Futterkiste	      Maria Anders	    Obere Str. 57	        Berlin	         12209         Germany
2	               Ana Trujillo Empare          Ana Trujillo	    Constitución 2222	México D.F.	    05021	    Mexico
3	            AntonioMoreno Taquería	Antonio Moreno	    Mataderos 2312	      México D.F.     05023	    Mexico

SQL Self JOIN Example
The following SQL statement matches customers that are from the same city:

Example

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

25. SQL UNION Operator

The SQL UNION Operator
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

UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION ALL Syntax
The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Note: The column names in the result-set are usually equal to the column names in the first SELECT statement in the UNION.

Demo Database
In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

CustomerID	CustomerName	            ContactName	        Address	            City	    PostalCode	    Country
1                   Alfreds Futterkiste	      Maria Anders	    Obere Str. 57	        Berlin	         12209         Germany
2	               Ana Trujillo Empare          Ana Trujillo	    Constitución 2222	México D.F.	    05021	    Mexico
3	            AntonioMoreno Taquería	Antonio Moreno	    Mataderos 2312	      México D.F.     05023	    Mexico

And a selection from the "Suppliers" table:

SupplierID	SupplierName	                    ContactName     	Address	            City	            Postal  Code	Country
1	            Exotic Liquid	                Charlotte Cooper	49 Gilbert St.	London	            EC1 4SD	        UK
2	       New Orleans Cajun Delights	Shelley Burke	    P.O. Box 78934	New Orleans	  70117	            USA
3	      Grandma Kellys Homestead	      Regina Murphy	    707 Oxford Rd.	Ann Arbor	        48104           	USA

SQL UNION Example
The following SQL statement returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table:

Example
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!

SQL UNION ALL Example
The following SQL statement returns the cities (duplicate values also) from both the "Customers" and the "Suppliers" table:

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

SQL UNION With WHERE
The following SQL statement returns the German cities (only distinct values) from both the "Customers" and the "Suppliers" table:

Example
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

SQL UNION ALL With WHERE
The following SQL statement returns the German cities (duplicate values also) from both the "Customers" and the "Suppliers" table:

Example
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

Another UNION Example
The following SQL statement lists all customers and suppliers:

Example
SELECT 'Customer' AS Type, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;

Notice the "AS Type" above - it is an alias. SQL Aliases are used to give a table or a column a temporary name. An alias only exists for the duration of the query. So, here we have created a temporary column named "Type", that list whether the contact person is a "Customer" or a "Supplier".

26. SQL GROUP BY Statement

The SQL GROUP BY Statement
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.

GROUP BY Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

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

Example
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:

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

GROUP BY With JOIN Example
The following SQL statement lists the number of orders sent by each shipper:

27. SQL HAVING Clause

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

HAVING Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:

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

The following SQL statement lists the number of customers in each country, sorted high to low (Only include countries with more than 5 customers):

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

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

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

The following SQL statement lists if the employees "Davolio" or "Fuller" have registered more than 25 orders:

Example
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;

28. SQL EXISTS Operator

The SQL EXISTS Operator
The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns true if the subquery returns one or more records.

EXISTS Syntax
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

SQL EXISTS Examples
The following SQL statement returns TRUE and lists the suppliers with a product price less than 20:

Example
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

The following SQL statement returns TRUE and lists the suppliers with a product price equal to 22:

Example
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);

29. SQL ANY and ALL Operators

The SQL ANY and ALL Operators
The ANY and ALL operators are used with a WHERE or HAVING clause.

The ANY operator returns true if any of the subquery values meet the condition.

The ALL operator returns true if all of the subquery values meet the condition.

ANY Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);

ALL Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);
Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

SQL ANY Examples
The ANY operator returns TRUE if any of the subquery values meet the condition.

The following SQL statement returns TRUE and lists the product names if it finds ANY records in the OrderDetails table that quantity = 10:

Example
SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

The following SQL statement returns TRUE and lists the product names if it finds ANY records in the OrderDetails table that quantity > 99:

Example
SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 99);
SQL ALL Example

The ALL operator returns TRUE if all of the subquery values meet the condition.

The following SQL statement returns TRUE and lists the product names if ALL the records in the OrderDetails table has quantity = 10 (so, this example will return FALSE, because not ALL records in the OrderDetails table has quantity = 10):

Example
SELECT ProductName
FROM Products
WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

30. SQL SELECT INTO Statement

The SQL SELECT INTO Statement
The SELECT INTO statement copies data from one table into a new table.

SELECT INTO Syntax
Copy all columns into a new table:

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

Copy only some columns into a new table:

SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

The new table will be created with the column-names and types as defined in the old table. You can create new column names using the AS clause.

SQL SELECT INTO Examples
The following SQL statement creates a backup copy of Customers:

SELECT * INTO CustomersBackup2017
FROM Customers;

The following SQL statement uses the IN clause to copy the table into a new table in another database:

SELECT * INTO CustomersBackup2017 IN 'Backup.mdb'
FROM Customers;

The following SQL statement copies only a few columns into a new table:

SELECT CustomerName, ContactName INTO CustomersBackup2017
FROM Customers;

The following SQL statement copies only the German customers into a new table:

SELECT * INTO CustomersGermany
FROM Customers
WHERE Country = 'Germany';

The following SQL statement copies data from more than one table into a new table:

SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2017
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Tip: SELECT INTO can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data:

SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;

31. SQL INSERT INTO SELECT Statement

The SQL INSERT INTO SELECT Statement
The INSERT INTO SELECT statement copies data from one table and inserts it into another table.

INSERT INTO SELECT requires that data types in source and target tables match
The existing records in the target table are unaffected
INSERT INTO SELECT Syntax

Copy all columns from one table to another table:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

Copy only some columns from one table into another table:

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

SQL INSERT INTO SELECT Examples
The following SQL statement copies "Suppliers" into "Customers" (the columns that are not filled with data, will contain NULL):

Example
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;
The following SQL statement copies "Suppliers" into "Customers" (fill all columns):

Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;
The following SQL statement copies only the German suppliers into "Customers":

Example
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';

32. SQL CASE Statement

The SQL CASE Statement
The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

CASE Syntax
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

SQL CASE Examples
The following SQL goes through conditions and returns a value when the first condition is met:

Example
SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

The following SQL will order the customers by City. However, if City is NULL, then order by Country:

Example
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

33. SQL NULL Functions

SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions
Look at the following "Products" table:

P_Id	ProductName	UnitPrice	UnitsInStock	UnitsOnOrder
1	        Jarlsberg	          10.45	        16	            15
2	        Mascarpone	    32.56	        23	 
3	        Gorgonzola	    15.67	        9	                  20

Suppose that the "UnitsOnOrder" column is optional, and may contain NULL values.

Look at the following SELECT statement:

SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
FROM Products;

In the example above, if any of the "UnitsOnOrder" values are NULL, the result will be NULL.

Solutions
MySQL

The MySQL IFNULL() function lets you return an alternative value if an expression is NULL:

SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;

or we can use the COALESCE() function, like this:

SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products;
SQL Server

34. SQL Stored Procedures for SQL Server

What is a Stored Procedure?
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

Stored Procedure Syntax
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
Execute a Stored Procedure
EXEC procedure_name;

Stored Procedure Example
The following SQL statement creates a stored procedure named "SelectAllCustomers" that selects all records from the "Customers" table:

Example
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

Execute the stored procedure above as follows:

Example
EXEC SelectAllCustomers;

Stored Procedure With One Parameter
The following SQL statement creates a stored procedure that selects Customers from a particular City from the "Customers" table:

Example
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

Execute the stored procedure above as follows:

Example
EXEC SelectAllCustomers @City = 'London';

Stored Procedure With Multiple Parameters
Setting up multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below.

The following SQL statement creates a stored procedure that selects Customers from a particular City with a particular PostalCode from the "Customers" table:

Example
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

Execute the stored procedure above as follows:

Example
EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';

35. SQL Comments

SQL Comments
Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements.

Note: The examples in this chapter will not work in Firefox and Microsoft Edge!

Comments are not supported in Microsoft Access databases. Firefox and Microsoft Edge are using Microsoft Access database in our examples.

Single Line Comments
Single line comments start with --.

Any text between -- and the end of the line will be ignored (will not be executed).

The following example uses a single-line comment as an explanation:

Example
--Select all:
SELECT * FROM Customers;

The following example uses a single-line comment to ignore the end of a line:

Example
SELECT * FROM Customers -- WHERE City='Berlin';

The following example uses a single-line comment to ignore a statement:

Example
--SELECT * FROM Customers;
SELECT * FROM Products;

36. Injection

Injection :

Es una tecnica con la cual usuarios maliciosos pueden inyectar comandos de SQL en un statement de SQL a traves de la entrada de datos por la web.

Esta tecnica puede alterar las instrucciones originales y comprometer la seguridad de la aplicacion.

https://wpengine.com/resources/prevent-sql-injection-attack-wordpress/?SSAID=742098&sscid=71k4_rpyz9&utm_source=SAS&utm_medium=affiliate&utm_campaign=742098&utm_content=394686

Prevent SQL Injections with All In One WP Security & Firewall
Finally, you could choose All In One WP Security & Firewall as your security plugin. Not only does it provide you with an extra firewall, but it makes it harder for bots to attempt to register as users. This protects your code, and blocks any IP addresses that may be causing too many 404 errors and phishing for information.

37.1 Operators - Wildcards

Wildcards:

Los comodines son utilizados para sustituir cualquier caracter(s) en un string

------------------------------------------------------------------------------------------------------------------------------------------


% WILDCARD :

Ejemplo 1 : selecciona todos los clientes con una ciudad cuyo nombre comience con "ber"

		SELECT * FROM Customers
		WHERE City LIKE 'ber%';
		
Ejemplo 2 : selecciona todos los clientes con una ciudad que contenga "es"

		SELECT * FROM Customers
		WHERE City LIKE '%es%';
		
------------------------------------------------------------------------------------------------------------------------------------------

_ WILDCARD :

Ejemplo 1 : selecciona todos los clientes con una ciudad cuyo nombre comience por cualquier caracter seguido de "erlin"

		SELECT * FROM Customers
		WHERE City LIKE '_erlin';
		
Ejemplo 2 : selecciona todos los clientes con una ciudad que comience por "L", seguida de cualquier caracter, seguida por "n", seguida de cualquier caracter, seguida por"on"

		SELECT * FROM Customers
		WHERE City LIKE 'L_n_on';

------------------------------------------------------------------------------------------------------------------------------------------

[CHARLIST] WILDCARD :

Ejemplo 1 : selecciona todos los clientes con una ciudad cuyo nombre comience por "b",  "s" , "p" :

		SELECT * FROM Customers
		WHERE City LIKE '[bsp]%';
		
Ejemplo 2 : selecciona todos los clientes con una ciudad cuyo nombre comience por "a",  "b" , "c" :

		SELECT * FROM Customers
		WHERE City LIKE '[a-c]%';

Ejemplo 3 : selecciona todos los clientes con una ciudad cuyo nombre NO comience por "b",  "s" , "p" :
	
		SELECT * FROM Customers
		WHERE City LIKE '[!bsp]%';
		
		or
		
		SELECT * FROM Customers
		WHERE City NOT LIKE '[bsp]%';
	

37.2 Operators - LIKE

LIKE :

Este operador se usa en una claususa WHERE con el fin de buscar un patron especifico en una pregunta

Sintaxis :

		SELECT column_name(s)
		FROM table_name
		WHERE column_name LIKE pattern;
	
Ejemplo 1:  se seleccionan los registros de los clientes cuyas ciudades comiencen con "s"
	
		SELECT * FROM Customers
		WHERE City LIKE 's%';

Ejemplo 2:  se seleccionan los registros de los clientes cuyas ciudades terminen con "s"

		SELECT * FROM Customers
		WHERE City LIKE '%s';
		
Ejemplo 3:  se seleccionan los registros de los clientes cuyas ciudades contengan "land"
	
		SELECT * FROM Customers
		WHERE Country LIKE '%land%';
	
Ejemplo 3:  se seleccionan los registros de los clientes cuyas ciudades NO contengan "land"

		SELECT * FROM Customers
		WHERE Country NOT LIKE '%land%';

37.3 Operators - IN

IN :

Este operador permite especificar multiples valores en un clausula WHERE

Sintax :
	
		SELECT column_name(s)
		FROM table_name
		WHERE column_name IN (value1,value2,...);
		
Ejemplo : se seleccionan todos los clientes cuya ciudad sea Paris o Londres
	
		SELECT * FROM Customers
		WHERE City IN ('Paris','London');

37.4 Operators - BETWEEN

BETWEEN :

Este operador se utiliza para seleccionar valores en un rango determinado

Sintaxis :
	
		SELECT column_name(s)
		FROM table_name
		WHERE column_name BETWEEN value1 AND value2;
		
Ejemplo 1: se seleccionan todos los productos con un precio entre 10 y 20
	
		SELECT * FROM Products
		WHERE Price BETWEEN 10 AND 20;
		
Ejemplo 2: se seleccionan todos los productos con un precio que no este entre 10 y 20
	
		SELECT * FROM Products
		WHERE Price NOT BETWEEN 10 AND 20;
		
Ejemplo 3: se seleccionan todos los productos con un precio entre 10 y 20, pero no se mostraran productos con un CategoryID de 1,2 o 3

		SELECT * FROM Products
		WHERE (Price BETWEEN 10 AND 20)
		AND NOT CategoryID IN (1,2,3);
	
Ejemplo 4: se seleccionan todos los productos con un nombre que comience con letras entre C y M

		SELECT * FROM Products
		WHERE ProductName BETWEEN 'C' AND 'M';
	
Ejemplo 5: se seleccionan todos los productos con un nombre que NO comience con letras entre C y M
	
		SELECT * FROM Products
		WHERE ProductName NOT BETWEEN 'C' AND 'M';
	
Ejemplo 6: se seleccionan todos los productos con una orden de pedido fechada entre '04-July-1996' and '09-July-1996'

		SELECT * FROM Orders
		WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

37.5 Operators - UNION

UNION :

El operador UNION combina los resultados de dos o mas SELECT, en donde cada uno de los SELECT debe contener el mismo numero de columnas y tener tipos de datos similares por columna, ademas deben estan en el mismo orden.

Es una suma vertical de registros

Sintaxis 1: por default solo se selecionan valores distintos
		
		SELECT column_name(s) FROM table1
		UNION
		SELECT column_name(s) FROM table2;


Sintaxis 2: si se desea que se incluyan duplicados
		
		SELECT column_name(s) FROM table1
		UNION ALL
		SELECT column_name(s) FROM table2;

Ejemplo 1 : se seleccionan todas las ciudades diferentes de las tablas Customers y Suppliers sin incluir duplicados
	
		SELECT City FROM Customers
		UNION
		SELECT City FROM Suppliers
		ORDER BY City;
		
	
Ejemplo 2 : se seleccionan todas las ciudades diferentes de las tablas Customers y Suppliers inluyendo duplicados
		
		SELECT City FROM Customers
		UNION ALL
		SELECT City FROM Suppliers
		ORDER BY City;

	
Ejemplo 3 : se seleccionan todas las ciudades de Alemania tanto de la tabla de Customers como de la tabla de Suppliers incluyendo duplicados
		
		SELECT City, Country FROM Customers
		WHERE Country='Germany'
		UNION ALL
		SELECT City, Country FROM Suppliers
		WHERE Country='Germany'
		ORDER BY City;

37.6 Operators - NULL Values

NULL  Values:

- Representan datos faltantes y desconocidos

- Por default una tabla puede contener NULL values

- Si una columna es opcional, se puede insertar un nuevo registro o actualizarl un registro existente sin tener que agregarle un valor a esta columna, lo cual implica que el campo sera grabado con un valor NULL

- Es imposible comparar NULL con 0 pues no son valores equivalentes, es como comparar desconocido con 0

----------------------------------------------------------------------------------------------------------------------------------------

IS NULL :

No es posible chequear valores NULL con operadores tales como : =,<,>,<>

Para chequear un valor NULL se usa :

	SELECT LastName,FirstName,Address FROM Persons
	WHERE Address IS NULL

----------------------------------------------------------------------------------------------------------------------------------------	
	
IS NOT NULL :

No es posible chequear valores NULL con operadores tales como : =,<,>,<>

Para chequear un valor NOT NULL se usa :

	SELECT LastName,FirstName,Address FROM Persons
	WHERE Address IS NOT NULL
	
----------------------------------------------------------------------------------------------------------------------------------------
	
IFNULL y COALESCE :
	
Ejemplo :  si se tiene la siguiente tabla

	SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder)
	FROM Products
	
Como la columna UnitsOnOrder es opcional, puede contener valores NULL, asi las cosas si alguno de los registros tiene un valor NULL en UnitsOnOrder el resultado del statement anterior sera NULL tambien.

Para poder manejar esta situacion :

Posibilidad 1 :

	SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
	FROM Products	

Posibilidad 2 :
	
	SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
	FROM Products

38.1 Constraints - Constraints

Constraints:

Se usan para establecer reglas para los datos en la tabla.

Estos constraints pueden ser especificadas al momento de creacion de la tabla dentro de un statement CREATE TABLE o despues de creada la tabla con el statement ALTER TABLE

Sintaxis :

	CREATE TABLE table_name
	(
	column_name1 data_type(size) constraint_name,
	column_name2 data_type(size) constraint_name,
	column_name3 data_type(size) constraint_name,
	....
	);
	
En SQL existen las siguientes Constraints :

- NOT NULL : Indica que la columna no puede almacenar un valor NULL

- UNIQUE : Asegura que cada fila de una columna tenga un valor unico

- PRIMARY KEY : Es una combinacion de NOT NULL y UNIQUE , asegura que una columna o una combinacion de dos o mas columnas tengan una identidad unica que ayude a encontrar un registro en particular dentro de una tabla de una manera mas facil y rapida

- FOREIGN KEY : Asegura la referencia de los datos en una tabla con los datos en otra tabla

- CHECK : Asegura que el valor en la columna cumpla con un requisito especifico

- DEFAULT : Especifica un valor por default para la columna

	

38.2 Constraints - NOT NULL

NOT NULL :

- Por default un campo de una tabla puede almacenar valores NULL

- La restriccion NOT NULL obliga a que una columna no acepte valores NULL

- Se obliga de esta manera a que un campo siempre tenga un valor

- Con esta restriccion no se puede insertar un nuevo registro o actualizar un registro sin especificar un valor

Ejemplo : en el siguiente ejemplo se especifica que los campos P_Id y LastName no pueden aceptar valores NULL

	CREATE TABLE PersonsNotNull
	(
	P_Id int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255)
	)

38.3 Constraints - UNIQUE

UNIQUE :

- Esta restriccion asegura que cada registro en la base de datos se identifique de manera unica

- Las restricciones UNIQUE y PRIMARY KEY garantizan la unicidad de identificacion de los registros

- Una restriccion PRIMARY KEY tiene definida automaticamene una restriccion UNIQUE

- Se pueden tener multiples restricciones UNIQUE en  una misma tabla, pero solo se puede tener un PRIMARY KEY por tabla

----------------------------------------------------------------------------------------------------------------------------------------

UNIQUE Constraint on CREATE TABLE :

Ejemplo 1 : se crea una restriccion UNIQUE en el campo P_Id cuando se crea la tabla Persons 
	
		CREATE TABLE Persons
		(
		P_Id int NOT NULL,
		LastName varchar(255) NOT NULL,
		FirstName varchar(255),
		Address varchar(255),
		City varchar(255),
		UNIQUE (P_Id)
		)
		
	
Ejemplo 2 : se define un nombre para la constante UNIQUE y la define para multiples columnas
	
		CREATE TABLE Persons
		(
		P_Id int NOT NULL,
		LastName varchar(255) NOT NULL,
		FirstName varchar(255),
		Address varchar(255),
		City varchar(255),
		CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
		)

----------------------------------------------------------------------------------------------------------------------------------------

UNIQUE Constraint on ALTER TABLE :

Ejemplo 1 : se crea una restriccion UNIQUE en el campo P_Id cuando se la tabla ya existe 
	
		ALTER TABLE Persons
		ADD UNIQUE (P_Id)
		
	
Ejemplo 2 : se define un nombre para la constante UNIQUE y la define para multiples columnas en una tabla existente
	
		ALTER TABLE Persons
		ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

----------------------------------------------------------------------------------------------------------------------------------------

DROP a UNIQUE Constraint :

Ejemplo  : se crea elimina la restriccion UNIQUE  
	
		ALTER TABLE Persons
		DROP INDEX uc_PersonID

38.4 Constraints - PRIMARY KEY

PRIMARY KEY :

- Esta restriccion permite garantizar que se identifique de manera unica cada registro en la base de datos

- Todo PRIMARY KEY debe contener valores UNIQUE

- Un PRIMARY KEY no puede contener valores NULL

- Cada tabla puede tener solo un PRIMARY KEY

--------------------------------------------------------------------------------------------------------------------------------------

PRIMARY KEY Constraint on CREATE TABLE :

Ejemplo  1: se crea una restriccion PRIMARY KEY en el campo P_Id cuando se crea la tabla Persons 
	
		CREATE TABLE Persons
		(
		P_Id int NOT NULL,
		LastName varchar(255) NOT NULL,
		FirstName varchar(255),
		Address varchar(255),
		City varchar(255),
		PRIMARY KEY (P_Id)
		)
		
	
Ejemplo 2 : se define un nombre para la constante PRIMARY KEY y la define para multiples columnas
	
		CREATE TABLE Persons
		(
		P_Id int NOT NULL,
		LastName varchar(255) NOT NULL,
		FirstName varchar(255),
		Address varchar(255),
		City varchar(255),
		CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
		)
	
--------------------------------------------------------------------------------------------------------------------------------------

PRIMARY KEY Constraint on ALTER TABLE :

Ejemplo  1: se crea una restriccion PRIMARY KEY en el campo P_Id cuando la tabla ya existe 
	
		ALTER TABLE Persons
		ADD PRIMARY KEY (P_Id)
		
	
Ejemplo 2 : se define un nombre para la constante PRIMARY KEY y la define para multiples columnas en una tabla existente
	
		ALTER TABLE Persons
		ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

--------------------------------------------------------------------------------------------------------------------------------------

DROP a PRIMARY KEY Constraint  :

	• Ejemplo  : eliminando una restriccion PRIMARY KEY  
	
		ALTER TABLE Persons
		DROP PRIMARY KEY

38.5 Constraints - FOREIGN KEY

FOREIGN KEY :

La FOREIGN KEY en una tabla apunta a la PRIMARY KEY de otra tabla

----------------------------------------------------------------------------------------------------------------------------------------

FOREIGN KEY Constraint On CREATE TABLE :

Ejemplo 1 : se crea un FOREIGN KEY en la columna P_Id cuando se crea la tabla Orders

		CREATE TABLE Orders
		(
		O_Id int NOT NULL,
		OrderNo int NOT NULL,
		P_Id int,
		PRIMARY KEY (O_Id),
		FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
		)
	
Ejemplo 2 : se le da nombre al FOREIGN KEY y se define para multiples columnas

		CREATE TABLE Orders
		(
		O_Id int NOT NULL,
		OrderNo int NOT NULL,
		P_Id int,
		PRIMARY KEY (O_Id),
		CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
		REFERENCES Persons(P_Id)
		)
	
----------------------------------------------------------------------------------------------------------------------------------------	

FOREIGN KEY Constraint On ALTER TABLE :

Ejemplo 1 : se crea un FOREIGN KEY en la columna P_Id en una tabla existente

		ALTER TABLE Orders
		ADD FOREIGN KEY (P_Id)
		REFERENCES Persons(P_Id)
	
Ejemplo 2 : se le da nombre al FOREIGN KEY y se define para multiples columnas en una tabla existente

		ALTER TABLE Orders
		ADD CONSTRAINT fk_PerOrders
		FOREIGN KEY (P_Id)
		REFERENCES Persons(P_Id)

----------------------------------------------------------------------------------------------------------------------------------------

DROP a FOREIGN KEY Constraint :

	• Ejemplo  : se crea un FOREIGN KEY en la columna P_Id en una tabla existente
	
		ALTER TABLE Orders
		DROP FOREIGN KEY fk_PerOrders

38.6 Constraints - CHECK

CHECK :

- Se usa para limitar el rango de valores que puede ser colocado en una columna

- Es decir que si se define solo determinados valores pueden ser aceptados en la columna

- Se puede definir la restriccion en funcion de los valores de otras columnas de la misma tabla

----------------------------------------------------------------------------------------------------------------------------------------

CHECK On CREATE TABLE :

Ejemplo 1 : se crea un CHECK que garantice que solo se almacenen valores enteros mayores que cero en el campo P_Id al crear una tablanueva

		CREATE TABLE Persons
		(
		P_Id int NOT NULL,
		LastName varchar(255) NOT NULL,
		FirstName varchar(255),
		Address varchar(255),
		City varchar(255),
		CHECK (P_Id>0)
		)
	
	
Ejemplo 2 : se crea un nombre para la constraint CHECK y se define para multiples columnas al crear una tabla nueva
	
		CREATE TABLE Persons
		(
		P_Id int NOT NULL,
		LastName varchar(255) NOT NULL,
		FirstName varchar(255),
		Address varchar(255),
		City varchar(255),
		CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
		)
	
----------------------------------------------------------------------------------------------------------------------------------------

CHECK On ALTER TABLE :

Ejemplo 1 : se crea un CHECK que garantice que solo se almacenen valores enteros mayores que cero en el campo P_Id en una tabla previamente existente

		ALTER TABLE Persons
		ADD CHECK (P_Id>0)
	
	
Ejemplo 2 : se crea un nombre para la constraint CHECK y se define para multiples columnas en una tabla previamente existente
	
		ALTER TABLE Persons
		ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
		
----------------------------------------------------------------------------------------------------------------------------------------
		
DROP CHECK :

Ejemplo  : se elimina la constraint CHECK 

		ALTER TABLE Persons
		DROP CHECK chk_Person

38.7 Constraints - DEFAULT

DEFAULT :

- Esta restriccion es usada para establecer un valor en una columna por default

- El valor por default es asignado a todos los registros nuevos a no ser que se especifique otro valor

-----------------------------------------------------------------------------------------------------------------------------------------

DEFAULT Constraint On CREATE TABLE :

Ejemplo 1 : el siguiente codigo crea una restriccion en la columna City cuando la tabla Persons es creada

		CREATE TABLE Persons
		(
		P_Id int NOT NULL,
		LastName varchar(255) NOT NULL,
		FirstName varchar(255),
		Address varchar(255),
		City varchar(255) DEFAULT 'Sandnes'
		)
	

Ejemplo 2 : tambien se usa para insertar valores del sistema, usando funciones tales como GETDATE() cuando se esta creando la tabla Orders

		CREATE TABLE Orders
		(
		O_Id int NOT NULL,
		OrderNo int NOT NULL,
		P_Id int,
		OrderDate date DEFAULT GETDATE()
		)
	
-----------------------------------------------------------------------------------------------------------------------------------------

DEFAULT Constraint On ALTER TABLE :

Ejemplo : el siguiente codigo crea una restriccion en la columna City en una tabla previamente creada

		ALTER TABLE Persons
		ALTER City SET DEFAULT 'SANDNES'
	

DROP DEFAULT Constraint :

Ejemplo : elimina la constraint DEFAULT

		ALTER TABLE Persons
		ALTER City DROP DEFAULT

39. SQL CREATE DATABASE Statement

The SQL CREATE DATABASE Statement
The CREATE DATABASE statement is used to create a new SQL database.

Syntax
CREATE DATABASE databasename;

CREATE DATABASE Example
The following SQL statement creates a database called "testDB":

Example
CREATE DATABASE testDB;

Tip: Make sure you have admin privilege before creating any database. Once a database is created, you can check it in the list of databases with the following SQL command: SHOW DATABASES;

----------------------------------------------------------------------------------------------------------------------------------------------------------

Esta instruccion se usa para crear una base de datos.

Sintaxis :
	
		CREATE DATABASE dbname;
		
Ejemplo :
	
		CREATE DATABASE my_db;

40. SQL DROP DATABASE Statement

The SQL DROP DATABASE Statement
The DROP DATABASE statement is used to drop an existing SQL database.

Syntax
DROP DATABASE databasename;

Note: Be careful before dropping a database. Deleting a database will result in loss of complete information stored in the database!

DROP DATABASE Example
The following SQL statement drops the existing database "testDB":

Example
DROP DATABASE testDB;

Tip: Make sure you have admin privilege before dropping any database. Once a database is dropped, you can check it in the list of databases with the following SQL command: SHOW DATABASES;

----------------------------------------------------------------------------------------------------------------------------------------------------------

DROP :

Este statement sirve para eliminar bases de datos, tablas, indices

DROP INDEX :

	ALTER TABLE table_name DROP INDEX index_name
	
DROP TABLE :

	DROP TABLE table_name
	
DROP DATABASE :

	DROP DATABASE database_name
	
En caso de que solo se desee eliminar los datos de la tabla, pero no la tabla en si misma se usa TRUNCATE  TABLE :

	TRUNCATE TABLE table_name

41. SQL BACKUP DATABASE for SQL

The SQL BACKUP DATABASE Statement
The BACKUP DATABASE statement is used in SQL Server to create a full back up of an existing SQL database.

Syntax
BACKUP DATABASE databasename
TO DISK = 'filepath';

The SQL BACKUP WITH DIFFERENTIAL Statement
A differential back up only backs up the parts of the database that have changed since the last full database backup.

Syntax
BACKUP DATABASE databasename
TO DISK = 'filepath'
WITH DIFFERENTIAL;

BACKUP DATABASE Example
The following SQL statement creates a full back up of the existing database "testDB" to the D disk:

Example
BACKUP DATABASE testDB
TO DISK = 'D:\backups\testDB.bak';

Tip: Always back up the database to a different drive than the actual database. Then, if you get a disk crash, you will not lose your backup file along with the database.

BACKUP WITH DIFFERENTIAL Example
The following SQL statement creates a differential back up of the database "testDB":

Example
BACKUP DATABASE testDB
TO DISK = 'D:\backups\testDB.bak'
WITH DIFFERENTIAL;

Tip: A differential back up reduces the back up time (since only the changes are backed up).

42. SQL CREATE TABLE Statement

The SQL CREATE TABLE Statement
The CREATE TABLE statement is used to create a new table in a database.

Syntax
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

The column parameters specify the names of the columns of the table.

The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).

Tip: For an overview of the available data types, go to our complete Data Types Reference.

SQL CREATE TABLE Example
The following example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City:

Example
CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

The PersonID column is of type int and will hold an integer.

The LastName, FirstName, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters.

The empty "Persons" table will now look like this:

PersonID	LastName	FirstName	Address	City
 	 	 	 	 
Tip: The empty "Persons" table can now be filled with data with the SQL INSERT INTO statement.

Create Table Using Another Table
A copy of an existing table can also be created using CREATE TABLE.

The new table gets the same column definitions. All columns or specific columns can be selected.

If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

Syntax
CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

The following SQL creates a new table called "TestTables" (which is a copy of the "Customers" table): 

Example
CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

------------------------------------------------------------------------------------------------------------------------------------------------------

Esta instruccion se usa para crear una tabla en una base de datos.

Sintaxis :
	
		CREATE TABLE table_name
		(
		column_name1 data_type(size),
		column_name2 data_type(size),
		column_name3 data_type(size),
		....
		);
		
Ejemplo :
	
		CREATE TABLE Persons
		(
		PersonID int,
		LastName varchar(255),
		FirstName varchar(255),
		Address varchar(255),
		City varchar(255)
		);
	
La tabla creada puede ser llenada con el statement INSERT INTO

43. SQL DROP TABLE Statement

The SQL DROP TABLE Statement
The DROP TABLE statement is used to drop an existing table in a database.

Syntax
DROP TABLE table_name;
Note: Be careful before dropping a table. Deleting a table will result in loss of complete information stored in the table!

SQL DROP TABLE Example
The following SQL statement drops the existing table "Shippers":

Example
DROP TABLE Shippers;

SQL TRUNCATE TABLE
The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.

Syntax
TRUNCATE TABLE table_name;

44. SQL ALTER TABLE Statement

SQL ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

ALTER TABLE - ADD Column
To add a column in a table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype;

The following SQL adds an "Email" column to the "Customers" table:

Example
ALTER TABLE Customers
ADD Email varchar(255);

ALTER TABLE - DROP COLUMN
To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

ALTER TABLE table_name
DROP COLUMN column_name;

The following SQL deletes the "Email" column from the "Customers" table:

Example
ALTER TABLE Customers
DROP COLUMN Email;
ALTER TABLE - ALTER/MODIFY COLUMN

To change the data type of a column in a table, use the following syntax:


SQL Server / MS Access:

ALTER TABLE table_name
ALTER COLUMN column_name datatype;



My SQL / Oracle (prior version 10G):

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;


Oracle 10G and later:

ALTER TABLE table_name
MODIFY column_name datatype;

SQL ALTER TABLE Example

Now we want to add a column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons
ADD DateOfBirth date;
Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data type specifies what type of data the column can hold. For a complete reference of all the data types available in MS Access, MySQL, and SQL Server, go to our complete Data Types reference.

Change Data Type Example
Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year;

Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two- or four-digit format.

DROP COLUMN Example
Next, we want to delete the column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons
DROP COLUMN DateOfBirth;

-----------------------------------------------------------------------------------------------------------------------------------------------------------

ALTER TABLE :

Para adicionar una columna en una tabla se usa la siguiente sintaxis :

	ALTER TABLE table_name
	ADD column_name datatype

Para borrar una columna en una tabla se usa la siguiente sintaxis :

	ALTER TABLE table_name
	DROP COLUMN column_name
	
Para cambiar el tipo de datos de una variable :

	ALTER TABLE table_name
	MODIFY COLUMN column_name datatype
	
Ejemplo 1: se adiciona una columna a una tabla existente

		ALTER TABLE Persons
		ADD DateOfBirth date
	
Ejemplo 2: cambiando el tipo de datos de una columna existente

		ALTER TABLE Persons
		ALTER COLUMN DateOfBirth year
	
Ejemplo 3: borrando una columna

		ALTER TABLE Persons
		DROP COLUMN DateOfBirth
	
	

45. SQL Constraints

SQL constraints are used to specify rules for data in a table.

SQL Create Constraints
Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.

Syntax
CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

SQL Constraints
SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY - Uniquely identifies a row/record in another table
CHECK - Ensures that all values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column when no value is specified
INDEX - Used to create and retrieve data from the database very quickly

46. SQL NOT NULL Constraint

SQL NOT NULL Constraint
By default, a column can hold NULL values.

The NOT NULL constraint enforces a column to NOT accept NULL values.

This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

SQL NOT NULL on CREATE TABLE
The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values when the "Persons" table is created:

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

SQL NOT NULL on ALTER TABLE
To create a NOT NULL constraint on the "Age" column when the "Persons" table is already created, use the following SQL:

ALTER TABLE Persons
MODIFY Age int NOT NULL;

47. SQL UNIQUE Constraint

SQL UNIQUE Constraint
The UNIQUE constraint ensures that all values in a column are different.

Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint.

However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

SQL UNIQUE Constraint on CREATE TABLE
The following SQL creates a UNIQUE constraint on the "ID" column when the "Persons" table is created:



SQL Server / Oracle / MS Access:

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



MySQL:

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


To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:


MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT UC_Person UNIQUE (ID,LastName)
);


SQL UNIQUE Constraint on ALTER TABLE

To create a UNIQUE constraint on the "ID" column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD UNIQUE (ID);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:

MySQL:

ALTER TABLE Persons
DROP INDEX UC_Person;
SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT UC_Person;

48. SQL PRIMARY KEY Constraint

SQL PRIMARY KEY Constraint
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).

SQL PRIMARY KEY on CREATE TABLE
The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created:

MySQL:

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



SQL Server / Oracle / MS Access:

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


To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

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



Note: In the example above there is only ONE PRIMARY KEY (PK_Person). However, the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).

SQL PRIMARY KEY on ALTER TABLE
To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the following SQL:


MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD PRIMARY KEY (ID);
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:



MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).

DROP a PRIMARY KEY Constraint
To drop a PRIMARY KEY constraint, use the following SQL:


MySQL:

ALTER TABLE Persons
DROP PRIMARY KEY;


SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT PK_Person;

49. SQL FOREIGN KEY Constraint

SQL FOREIGN KEY Constraint
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.

Look at the following two tables:

"Persons" table:

PersonID	LastName	FirstName	    Age
1	            Hansen	           Ola	           30
2	            Svendson	    Tove	           23
3	            Pettersen	    Kari	           20

"Orders" table:

OrderID	OrderNumber	PersonID
1	                77895	            3
2	                44678	            3
3	                22456	            2
4	                24562	            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.


SQL FOREIGN KEY on CREATE TABLE
The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created:

MySQL:

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



SQL Server / Oracle / MS Access:

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


To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

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


SQL FOREIGN KEY on ALTER TABLE
To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
DROP a FOREIGN KEY Constraint
To drop a FOREIGN KEY constraint, use the following SQL:

MySQL:

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
SQL Server / Oracle / MS Access:

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;

50. SQL CHECK Constraint

SQL CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

SQL CHECK on CREATE TABLE
The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that the age of a person must be 18, or older:

MySQL:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);



SQL Server / Oracle / MS Access:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18)
);


To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);

SQL CHECK on ALTER TABLE
To create a CHECK constraint on the "Age" column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CHECK (Age>=18);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;


MySQL:

ALTER TABLE Persons
DROP CHECK CHK_PersonAge;

51. SQL DEFAULT Constraint

SQL DEFAULT Constraint
The DEFAULT constraint is used to provide a default value for a column.

The default value will be added to all new records IF no other value is specified.

SQL DEFAULT on CREATE TABLE
The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is created:

My SQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
);

The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():

CREATE TABLE Orders (
    ID int NOT NULL,
    OrderNumber int NOT NULL,
    OrderDate date DEFAULT GETDATE()
);

SQL DEFAULT on ALTER TABLE
To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

MySQL:

ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';


SQL Server:

ALTER TABLE Persons
ADD CONSTRAINT df_City
DEFAULT 'Sandnes' FOR City;


MS Access:

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'Sandnes';


Oracle:

ALTER TABLE Persons
MODIFY City DEFAULT 'Sandnes';
DROP a DEFAULT Constraint
To drop a DEFAULT constraint, use the following SQL:


MySQL:

ALTER TABLE Persons
ALTER City DROP DEFAULT;
SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;

52. SQL CREATE INDEX Statement

SQL CREATE INDEX Statement
The CREATE INDEX statement is used to create indexes in tables.

Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against.

CREATE INDEX Syntax
Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column1, column2, ...);
CREATE UNIQUE INDEX Syntax
Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

Note: The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database.

CREATE INDEX Example
The SQL statement below creates an index named "idx_lastname" on the "LastName" column in the "Persons" table:

CREATE INDEX idx_lastname
ON Persons (LastName);

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

CREATE INDEX idx_pname
ON Persons (LastName, FirstName);

DROP INDEX Statement
The DROP INDEX statement is used to delete an index in a table.

MS Access:

DROP INDEX index_name ON table_name;


SQL Server:

DROP INDEX table_name.index_name;


DB2/Oracle:

DROP INDEX index_name;


MySQL:

ALTER TABLE table_name
DROP INDEX index_name;

------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE INDEX :

- Este statement se usa pra crear los indices de una tabla

- Los indices permiten encontrar informacion de manera muy rapida sin necesidad de recorrer toda la tabla

- Los usuarios no pueden ver los indices

- Actualizar una tabla con indices siempre es mas lento que actualizar una tabla sin indices, por eso solo se deben usar indices cuando sea estrictamente necesario

CREATE INDEX :
	
Sintaxis : se crea un indice en la tabla, se aceptan valores duplicados

		CREATE INDEX index_name
		ON table_name (column_name)

CREATE UNIQUE INDEX :
	
Sintaxis : se crea un indice en la tabla, NO se aceptan valores duplicados

		CREATE UNIQUE INDEX index_name
		ON table_name (column_name)
	
Ejemplo 1 : el siguiente codigo crea un indice llamado Pindex en la columna LastName de la tabla Persons

		CREATE INDEX PIndex
		ON Persons (LastName)
	

Ejemplo 2 : si se desea crear un indice con una combinacion de columnas, estas se listan entre parentesis separados por comas

		CREATE INDEX PIndex
		ON Persons (LastName, FirstName)

53. SQL AUTO INCREMENT Field

AUTO INCREMENT Field
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

Syntax for MySQL

The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the "Persons" table:

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

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

ALTER TABLE Persons AUTO_INCREMENT=100;

To insert a new record into the "Persons" table, we will NOT have to specify a value for the "Personid" column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen');

The SQL statement above would insert a new record into the "Persons" table. The "Personid" column would be assigned a unique value. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".


Syntax for SQL Server

The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the "Persons" table:

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

The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.

In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.

Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5).

To insert a new record into the "Persons" table, we will NOT have to specify a value for the "Personid" column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen');

The SQL statement above would insert a new record into the "Persons" table. The "Personid" column would be assigned a unique value. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".


Syntax for Access
The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the "Persons" table:

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


The MS Access uses the AUTOINCREMENT keyword to perform an auto-increment feature.

By default, the starting value for AUTOINCREMENT is 1, and it will increment by 1 for each new record.

Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change the autoincrement to AUTOINCREMENT(10,5).

To insert a new record into the "Persons" table, we will NOT have to specify a value for the "Personid" column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen');
The SQL statement above would insert a new record into the "Persons" table. The "Personid" column would be assigned a unique value. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".


Syntax for Oracle
In Oracle the code is a little bit more tricky.

You will have to create an auto-increment field with the sequence object (this object generates a number sequence).

Use the following CREATE SEQUENCE syntax:

CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;
The code above creates a sequence object called seq_person, that starts with 1 and will increment by 1. It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access.

To insert a new record into the "Persons" table, we will have to use the nextval function (this function retrieves the next value from seq_person sequence):

INSERT INTO Persons (Personid,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen');
The SQL statement above would insert a new record into the "Persons" table. The "Personid" column would be assigned the next number from the seq_person sequence. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".

--------------------------------------------------------------------------------------------------------------------------------------------------------

Permite que se genere un numero unico cuando se inserta un nuevo registro en una tabla

Sintaxis : el siguiente codigo define una columna ID que sera un Primary Key auto incremental en la tabla Persons

	CREATE TABLE Persons
	(
	ID int NOT NULL AUTO_INCREMENT,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255),
	PRIMARY KEY (ID)
	)
	
Por default el valor inicial del AUTO INCREMENT es 1 y se incrementa de a 1 para cada nuevo registro

Para asignar un valor inicial diferente usamos :

	ALTER TABLE Persons AUTO_INCREMENT=100

54. SQL Working With Dates

SQL Dates
The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database.

As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets more complicated.

SQL Date Data Types
MySQL comes with the following data types for storing a date or a date/time value in the database:

DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
YEAR - format YYYY or YY
SQL Server comes with the following data types for storing a date or a date/time value in the database:

DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: a unique number

Note: The date types are chosen for a column when you create a new table in your database!

SQL Working with Dates
You can compare two dates easily if there is no time component involved!

Assume we have the following "Orders" table:

OrderId	                                     ProductName	                OrderDate
1	                                                Geitost	                  2008-11-11
2	                                        Camembert Pierrot	            2008-11-09
3	                                    Mozzarella di Giovanni	            2008-11-11
4	                                        Mascarpone Fabioli	            2008-10-29

Now we want to select the records with an OrderDate of "2008-11-11" from the table above.

We use the following SELECT statement:

SELECT * FROM Orders WHERE OrderDate='2008-11-11'
The result-set will look like this:

OrderId	                                    ProductName	                    OrderDate
1	                                            Geitost	                        2008-11-11
3	                                    Mozzarella di Giovanni	             2008-11-11

Now, assume that the "Orders" table looks like this (notice the time component in the "OrderDate" column):

OrderId	                                    ProductName	                    OrderDate
1	                                            Geitost	                2008-11-11 13:23:44
2	                                    Camembert Pierrot	    2008-11-09 15:45:21
3	                                Mozzarella di Giovanni	    2008-11-11 11:12:01
4	                                    Mascarpone Fabioli	    2008-10-29 14:56:59

If we use the same SELECT statement as above:

SELECT * FROM Orders WHERE OrderDate='2008-11-11'
we will get no result! This is because the query is looking only for dates with no time portion.

Tip: To keep your queries simple and easy to maintain, do not allow time components in your dates!

55. SQL Views

SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Note: A view always shows up-to-date data! The database engine recreates the data, using the views SQL statement, every time a user queries a view.

SQL CREATE VIEW Examples
The following SQL creates a view that shows all customers from Brazil:

Example
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';

We can query the view above as follows:

Example
SELECT * FROM [Brazil Customers];

The following SQL creates a view that selects every product in the "Products" table with a price higher than the average price:

Example
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);

We can query the view above as follows:

Example
SELECT * FROM [Products Above Average Price];

SQL Updating a View
A view can be updated with the CREATE OR REPLACE VIEW command.

SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

The following SQL adds the "City" column to the "Brazil Customers" view:

Example
CREATE OR REPLACE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = 'Brazil';
SQL Dropping a View

A view is deleted with the DROP VIEW command.

SQL DROP VIEW Syntax
DROP VIEW view_name;
The following SQL drops the "Brazil Customers" view:

Example
DROP VIEW [Brazil Customers];

------------------------------------------------------------------------------------------------------------------------------------------------------------

VIEWS :

- Una vista es una tabla virtual  basada en el resultado de un statement de SQL

- Una vista contiene filas y columnas como una tabla real

- Los campos de una vista son campos de una o mas tablas reales en una base de datos

- Se pueden adicionar funciones de SQL, WHERE, y JOIN statements para ver y presentar los datos como si los datos hubieran venido de una tabla simple

- Una vista siempre presenta los datos actualizados, ya que el motor de la base de datos actualiza la vista cada vez que se hace un query sobre la vista


CREATE VIEW :

Sintax :

	CREATE VIEW view_name AS
	SELECT column_name(s)
	FROM table_name
	WHERE condition
	
Ejemplo 1 : creando una vista de todos los productos no discontinuados

	CREATE VIEW [Current Product List] AS
	SELECT ProductID,ProductName
	FROM Products
	WHERE Discontinued=No
	
Ejemplo 2 : haciendo un query sobre la vista recientemente creada

	SELECT * FROM [Current Product List]
	
Ejemplo 3 : se crea una vista con los productos cuyo precio unitario es mayor al precio unitario promedio de los productos

	CREATE VIEW [Products Above Average Price] AS
	SELECT ProductName,UnitPrice
	FROM Products
	WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
	
Ejemplo 4 : haciendo un query sobre la vista recientemente creada

	SELECT * FROM [Products Above Average Price]
	
Ejemplo 5 : se crea una vista que calcula las ventas por cada categoria para 1997, esta vista selecciona los datos de otra vista llamada "Product Sales for 1997"

	CREATE VIEW [Category Sales For 1997] AS
	SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
	FROM [Product Sales for 1997]
	GROUP BY CategoryName
	
Ejemplo 6 : haciendo un query sobre la vista recientemente creada

	SELECT * FROM [Category Sales For 1997]
	
Ejemplo 7 : ahora se le adiciona una condicion al query sobre la vista

	SELECT * FROM [Category Sales For 1997]
	WHERE CategoryName='Beverages'
	
	
CREATE OR REPLACE A VIEW :

Se puede actualizar una vista 

Sintaxis :

	CREATE OR REPLACE VIEW view_name AS
	SELECT column_name(s)
	FROM table_name
	WHERE condition
	
Ejemplo : 

	CREATE OR REPLACE VIEW [Current Product List] AS
	SELECT ProductID,ProductName,Category
	FROM Products
	WHERE Discontinued=No
	
	
DROP A VIEW :

	DROP VIEW view_name
	

56. SQL Injection

SQL Injection
SQL injection is a code injection technique that might destroy your database.

SQL injection is one of the most common web hacking techniques.

SQL injection is the placement of malicious code in SQL statements, via web page input.

SQL in Web Pages
SQL injection usually occurs when you ask a user for input, like their username/userid, and instead of a name/id, the user gives you an SQL statement that you will unknowingly run on your database.

Look at the following example which creates a SELECT statement by adding a variable (txtUserId) to a select string. The variable is fetched from user input (getRequestString):

Example
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
The rest of this chapter describes the potential dangers of using user input in SQL statements.

SQL Injection Based on 1=1 is Always True
Look at the example above again. The original purpose of the code was to create an SQL statement to select a user, with a given user id.

If there is nothing to prevent a user from entering "wrong" input, the user can enter some "smart" input like this:

UserId: 
105 OR 1=1

Then, the SQL statement will look like this:

SELECT * FROM Users WHERE UserId = 105 OR 1=1;
The SQL above is valid and will return ALL rows from the "Users" table, since OR 1=1 is always TRUE.

Does the example above look dangerous? What if the "Users" table contains names and passwords?

The SQL statement above is much the same as this:

SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or 1=1;
A hacker might get access to all the user names and passwords in a database, by simply inserting 105 OR 1=1 into the input field.


SQL Injection Based on ""="" is Always True
Here is an example of a user login on a web site:

Username:
John Doe

Password:
myPass

Example
uName = getRequestString("username");
uPass = getRequestString("userpassword");

sql = 'SELECT * FROM Users WHERE Name ="' + uName + '" AND Pass ="' + uPass + '"'
Result
SELECT * FROM Users WHERE Name ="John Doe" AND Pass ="myPass"

A hacker might get access to user names and passwords in a database by simply inserting " OR ""=" into the user name or password text box:

User Name:
" or ""="

Password:
" or ""="

The code at the server will create a valid SQL statement like this:

Result
SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""=""
The SQL above is valid and will return all rows from the "Users" table, since OR ""="" is always TRUE.

SQL Injection Based on Batched SQL Statements 
Most databases support batched SQL statement.

A batch of SQL statements is a group of two or more SQL statements, separated by semicolons.

The SQL statement below will return all rows from the "Users" table, then delete the "Suppliers" table.

Example
SELECT * FROM Users; DROP TABLE Suppliers
Look at the following example:

Example
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
And the following input:

User id: 
105; DROP TABLE Suppliers

The valid SQL statement would look like this:

Result
SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers;
Use SQL Parameters for Protection
To protect a web site from SQL injection, you can use SQL parameters.

SQL parameters are values that are added to an SQL query at execution time, in a controlled manner.

ASP.NET Razor Example
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = @0";
db.Execute(txtSQL,txtUserId);
Note that parameters are represented in the SQL statement by a @ marker.

The SQL engine checks each parameter to ensure that it is correct for its column and are treated literally, and not as part of the SQL to be executed.

Another Example
txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
db.Execute(txtSQL,txtNam,txtAdd,txtCit);

Examples
The following examples shows how to build parameterized queries in some common web languages.

SELECT STATEMENT IN ASP.NET:

txtUserId = getRequestString("UserId");
sql = "SELECT * FROM Customers WHERE CustomerId = @0";
command = new SqlCommand(sql);
command.Parameters.AddWithValue("@0",txtUserID);
command.ExecuteReader();


INSERT INTO STATEMENT IN ASP.NET:

txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
command = new SqlCommand(txtSQL);
command.Parameters.AddWithValue("@0",txtNam);
command.Parameters.AddWithValue("@1",txtAdd);
command.Parameters.AddWithValue("@2",txtCit);
command.ExecuteNonQuery();


INSERT INTO STATEMENT IN PHP:

$stmt = $dbh->prepare("INSERT INTO Customers (CustomerName,Address,City)
VALUES (:nam, :add, :cit)");
$stmt->bindParam(':nam', $txtNam);
$stmt->bindParam(':add', $txtAdd);
$stmt->bindParam(':cit', $txtCit);
$stmt->execute();

57. SQL Keywords Reference

This SQL keywords reference contains the reserved words in SQL.

SQL Keywords
Keyword	                                                                    Description
ADD	                                                        Adds a column in an existing table
ADD CONSTRAINT	                                    Adds a constraint after a table is already created
ALTER	                        Adds, deletes, or modifies columns in a table, or changes the data type of a column in a table
ALTER COLUMN	                                        Changes the data type of a column in a table
ALTER TABLE	                                        Adds, deletes, or modifies columns in a table
ALL	                                                Returns true if all of the subquery values meet the condition
AND	                                                    Only includes rows where both conditions is true
ANY	                                            Returns true if any of the subquery values meet the condition
AS	                                                                Renames a column or table with an alias
ASC	                                                            Sorts the result set in ascending order
BACKUP DATABASE	                                        Creates a back up of an existing database
BETWEEN	                                                        Selects values within a given range
CASE	                                                        Creates different outputs based on conditions
CHECK	                                        A constraint that limits the value that can be placed in a column
COLUMN	                                    Changes the data type of a column or deletes a column in a table
CONSTRAINT	                                                        Adds or deletes a constraint
CREATE	                                                Creates a database, index, view, table, or procedure
CREATE DATABASE	                                                Creates a new SQL database
CREATE INDEX	                                    Creates an index on a table (allows duplicate values)
CREATE OR REPLACE VIEW	                                                Updates a view
CREATE TABLE	                                                    Creates a new table in the database
CREATE PROCEDURE	                                            Creates a stored procedure
CREATE UNIQUE INDEX	                        Creates a unique index on a table (no duplicate values)
CREATE VIEW	                                Creates a view based on the result set of a SELECT statement
DATABASE	                                                    Creates or deletes an SQL database
DEFAULT	                                            A constraint that provides a default value for a column
DELETE	                                                                Deletes rows from a table
DESC	                                                        Sorts the result set in descending order
DISTINCT	                                                    Selects only distinct (different) values
DROP	                                        Deletes a column, constraint, database, index, table, or view
DROP COLUMN	                                                        Deletes a column in a table
DROP CONSTRAINT	                    Deletes a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint
DROP DATABASE	                                            Deletes an existing SQL database
DROP DEFAULT	                                                    Deletes a DEFAULT constraint
DROP INDEX	                                                        Deletes an index in a table
DROP TABLE	                                            Deletes an existing table in the database
DROP VIEW	                                                                    Deletes a view
EXEC	                                                                Executes a stored procedure
EXISTS	                                                Tests for the existence of any record in a subquery
FOREIGN KEY	                                A constraint that is a key used to link two tables together
FROM	                                                Specifies which table to select or delete data from
FULL OUTER JOIN	                Returns all rows when there is a match in either left table or right table
GROUP BY	                    Groups the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG)
HAVING	                                            Used instead of WHERE with aggregate functions
IN	                                                    Allows you to specify multiple values in a WHERE clause
INDEX	                                                        Creates or deletes an index in a table
INNER JOIN	                                Returns rows that have matching values in both tables
INSERT INTO	                                                    Inserts new rows in a table
INSERT INTO SELECT	                            Copies data from one table into another table
IS NULL	                                                            Tests for empty values
IS NOT NULL	                                                    Tests for non-empty values
JOIN	                                                                            Joins tables
LEFT JOIN	                        Returns all rows from the left table, and the matching rows from the right table
LIKE	                                                        Searches for a specified pattern in a column
LIMIT	                                            Specifies the number of records to return in the result set
NOT	                                                    Only includes rows where a condition is not true
NOT NULL	                                    A constraint that enforces a column to not accept NULL values
OR	                                                        Includes rows where either condition is true
ORDER BY	                                        Sorts the result set in ascending or descending order
OUTER JOIN	                        Returns all rows when there is a match in either left table or right table
PRIMARY KEY	                        A constraint that uniquely identifies each record in a database table
PROCEDURE	                                                            A stored procedure
RIGHT JOIN	                        Returns all rows from the right table, and the matching rows from the left table
ROWNUM	                                        Specifies the number of records to return in the result set
SELECT	                                                            Selects data from a database
SELECT DISTINCT	                                    Selects only distinct (different) values
SELECT INTO	                                    Copies data from one table into a new table
SELECT TOP	                                     Specifies the number of records to return in the result set
SET	                                        Specifies which columns and values that should be updated in a table
TABLE	        Creates a table, or adds, deletes, or modifies columns in a table, or deletes a table or data inside a table
TOP	                                                Specifies the number of records to return in the result set
TRUNCATE TABLE	                            Deletes the data inside a table, but not the table itself
UNION	                            Combines the result set of two or more SELECT statements (only distinct values)
UNION ALL	                    Combines the result set of two or more SELECT statements (allows duplicate values)
UNIQUE	                                A constraint that ensures that all values in a column are unique
UPDATE	                                                                Updates existing rows in a table
VALUES	                                                    Specifies the values of an INSERT INTO statement
VIEW	                                                            Creates, updates, or deletes a view
WHERE	                                    Filters a result set to include only records that fulfill a specified condition

58. SQL Operators

SQL Arithmetic Operators

Operator	       Description	
+	                    Add	
-	                    Subtract	
*	                    Multiply	
/	                    Divide	
%	                    Modulo	


SQL Bitwise Operators

Operator	        Description
&	                    Bitwise AND
|	                    Bitwise OR
^	                Bitwise exclusive OR

SQL Comparison Operators

Operator	            Description	
=	                        Equal to	
>	                        Greater than	
<	                        Less than	
>=	                        Greater than or equal to	
<=	                         Less than or equal to	
<>	                         Not equal to	

SQL Compound Operators

Operator	            Description
+=	                        Add equals
-=	                        Subtract equals
*=	                      Multiply equals
/=	                        Divide equals
%=	                        Modulo equals
&=	                    Bitwise AND equals
^-=	            Bitwise exclusive equals
|*=	                        Bitwise OR equals

SQL Logical Operators

Operator	                                    Description	
ALL	                        TRUE if all of the subquery values meet the condition	
AND	                TRUE if all the conditions separated by AND is TRUE	
ANY	                TRUE if any of the subquery values meet the condition	
BETWEEN	            TRUE if the operand is within the range of comparisons	
EXISTS	                    TRUE if the subquery returns one or more records	
IN	                        TRUE if the operand is equal to one of a list of expressions	
LIKE	                                TRUE if the operand matches a pattern	
NOT	                        Displays a record if the condition(s) is NOT TRUE	
OR	                        TRUE if any of the conditions separated by OR is TRUE	
SOME	                        TRUE if any of the subquery values meet the condition

59. SQL Quick Reference

SQL Statement	Syntax


AND / OR :	                    

SELECT column_name(s)
FROM table_name
WHERE condition
AND|OR condition

                                        
------------------------------------------------


ALTER TABLE :	               

ALTER TABLE table_name
ADD column_name datatype
                or
ALTER TABLE table_name
DROP COLUMN column_name


------------------------------------------------


AS (alias) :
	                    
SELECT column_name AS column_alias
FROM table_name
            or
SELECT column_name
FROM table_name  AS table_alias


------------------------------------------------


BETWEEN : 
                   
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2


------------------------------------------------


CREATE DATABASE	:        

CREATE DATABASE database_name


------------------------------------------------


CREATE TABLE :
	               
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
...
)


------------------------------------------------



CREATE INDEX	:                

CREATE INDEX index_name
ON table_name (column_name)
                   or
CREATE UNIQUE INDEX index_name
 ON table_name (column_name)


------------------------------------------------


CREATE VIEW :
	                
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition


------------------------------------------------


DELETE :
	                            
DELETE FROM table_name
WHERE some_column=some_value
                  or
DELETE FROM table_name
(Note: Deletes the entire table!!)

DELETE * FROM table_name
(Note: Deletes the entire table!!)


------------------------------------------------


DROP DATABASE :	            

DROP DATABASE database_name


------------------------------------------------


DROP INDEX :	                  

DROP INDEX table_name.index_name (SQL Server)
DROP INDEX index_name ON table_name (MS Access)
DROP INDEX index_name (DB2/Oracle)

ALTER TABLE table_name
DROP INDEX index_name (MySQL)


------------------------------------------------


DROP TABLE :	                

DROP TABLE table_name


------------------------------------------------


EXISTS :	                            

IF EXISTS (SELECT * FROM table_name WHERE id = ?)
BEGIN
--do what needs to be done if exists
END
ELSE
BEGIN
--do what needs to be done if not
END


------------------------------------------------


GROUP BY :	                    

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name


------------------------------------------------



HAVING :
	                    
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value


------------------------------------------------


IN :	                                

SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)


------------------------------------------------


INSERT INTO :	                

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


------------------------------------------------



INNER JOIN :	                

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name


------------------------------------------------



LEFT JOIN :	                     

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name


------------------------------------------------



RIGHT JOIN :	                

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name


------------------------------------------------



FULL JOIN :	                      

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name


------------------------------------------------



LIKE :	                           

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern


------------------------------------------------



ORDER BY :	                    

SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]


------------------------------------------------



SELECT :	                          

SELECT column_name(s)
FROM table_name


------------------------------------------------



SELECT * :	                    

SELECT *
FROM table_name


------------------------------------------------



SELECT DISTINCT :	        

SELECT DISTINCT column_name(s)
FROM table_name


------------------------------------------------


SELECT INTO :	            

SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_table_name
              or
SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_table_name


------------------------------------------------


SELECT TOP :	             

SELECT TOP number|percent column_name(s)
FROM table_name


------------------------------------------------


TRUNCATE TABLE :	        

TRUNCATE TABLE table_name


------------------------------------------------



UNION :	                          

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2


------------------------------------------------


UNION ALL :	                    

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2


------------------------------------------------



UPDATE	 :                         

UPDATE table_name
SET column1=value, column2=value,...
WHERE some_column=some_value


------------------------------------------------



WHERE :	                          

SELECT column_name(s)
FROM table_name
WHERE column_name operator va

60. SQL Data Types

SQL Data Types
Each column in a database table is required to have a name and a data type.

An SQL developer must decide what type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.

Note: Data types might have different names in different database. And even if the name is the same, the size and other details may be different! Always check the documentation!


------------------------------------------------------------------------------------------------------------------------------------


MySQL Data Types (Version 8.0)
In MySQL there are three main data types: string, numeric, and date and time.


------------------------------------------------------------------------------------------------------------------------------------

String data types:
Data type :	Description

CHAR(size) :	      A FIXED length string (can contain letters, numbers, and special characters). The size parameter specifies the column length in characters - can be from 0 to 255. Default is 1

VARCHAR(size) :	A VARIABLE length string (can contain letters, numbers, and special characters). The size parameter specifies the maximum column length in characters - can be from 0 to 65535

BINARY(size) :	      Equal to CHAR(), but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1

VARBINARY(size) :	Equal to VARCHAR(), but stores binary byte strings. The size parameter specifies the maximum column length in bytes.

TINYBLOB :	            For BLOBs (Binary Large OBjects). Max length: 255 bytes

TINYTEXT :	            Holds a string with a maximum length of 255 characters

TEXT(size) :	      Holds a string with a maximum length of 65,535 bytes

BLOB(size) :	      For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data

MEDIUMTEXT :	      Holds a string with a maximum length of 16,777,215 characters

MEDIUMBLOB :	      For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data

LONGTEXT :	      Holds a string with a maximum length of 4,294,967,295 characters

LONGBLOB :	      For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data

ENUM(val1, val2, val3, ...) :       A string object that can have only one value, chosen from a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. The values are sorted in the order you enter them

SET(val1, val2, val3, ...) :	       A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list


------------------------------------------------------------------------------------------------------------------------------------


Numeric data types:
Data type : Description

BIT(size) :	            A bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64. The default value for size is 1.

TINYINT(size) : 	A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255. The size parameter specifies the maximum display width (which is 255)

BOOL :              	Zero is considered as false, nonzero values are considered as true.

BOOLEAN :       	Equal to BOOL

SMALLINT(size)	 :   A small integer. Signed range is from -32768 to 32767. Unsigned range is from 0 to 65535. The size parameter specifies the maximum display width (which is 255)

MEDIUMINT(size) :	A medium integer. Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215. The size parameter specifies the maximum display width (which is 255)

INT(size) :         	A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255)

INTEGER(size) :	Equal to INT(size)

BIGINT(size) :      	A large integer. Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. The size parameter specifies the maximum display width (which is 255)

FLOAT(size, d) :	A floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. This syntax is deprecated in MySQL 8.0.17, and it will be removed in future MySQL versions

FLOAT(p) :          	A floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE()

DOUBLE(size, d) :	A normal-size floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter

DOUBLE PRECISION(size, d)	 

DECIMAL(size, d) :	An exact fixed-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0.

DEC(size, d) :      Equal to DECIMAL(size,d)
Note: All the numeric data types may have an extra option: UNSIGNED or ZEROFILL. If you add the UNSIGNED option, MySQL disallows negative values for the column. If you add the ZEROFILL option, MySQL automatically also adds the UNSIGNED attribute to the column.


------------------------------------------------------------------------------------------------------------------------------------


Date and Time data types:
Data type :	Description


DATE :	                 A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'

DATETIME(fsp) :	A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time

TIMESTAMP(fsp) :	A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition

TIME(fsp) :         	A time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59'

YEAR :              	A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000.
MySQL 8.0 does not support year in two-digit format.

61. MySQL Functions

MySQL has many built-in functions.

------------------------------------------------------------------------------------------------------------------------------------

This reference contains string, numeric, date, and some advanced functions in MySQL.

------------------------------------------------------------------------------------------------------------------------------------


MySQL String Functions
Function :	Description


------------------------------------------------------------------------------------------------------------------------------------

ASCII : 	Returns the ASCII value for the specific character

CHAR_LENGTH	:                Returns the length of a string (in characters)

CHARACTER_LENGTH :  	Returns the length of a string (in characters)

CONCAT :    	    Adds two or more expressions together

CONCAT_WS : 	    Adds two or more expressions together with a separator

FIELD : 	          Returns the index position of a value in a list of values

FIND_IN_SET :     Returns the position of a string within a list of strings

FORMAT :    Formats a number to a format like "#,###,###.##", rounded to a specified number of decimal places

INSERT	:     Inserts a string within a string at the specified position and for a certain number of characters

INSTR : 	Returns the position of the first occurrence of a string in another string

LCASE :	Converts a string to lower-case

LEFT :	      Extracts a number of characters from a string (starting from left)

LENGTH :	Returns the length of a string (in bytes)

LOCATE :	Returns the position of the first occurrence of a substring in a string

LOWER :	Converts a string to lower-case

LPAD :  	Left-pads a string with another string, to a certain length

LTRIM : 	Removes leading spaces from a string

MID :       	Extracts a substring from a string (starting at any position)

POSITION :	Returns the position of the first occurrence of a substring in a string

REPEAT :	Repeats a string as many times as specified

REPLACE :	Replaces all occurrences of a substring within a string, with a new substring

REVERSE :	Reverses a string and returns the result

RIGHT :      Extracts a number of characters from a string (starting from right)

RPAD :  	Right-pads a string with another string, to a certain length

RTRIM :	Removes trailing spaces from a string

SPACE :	Returns a string of the specified number of space characters

STRCMP :	Compares two strings

SUBSTR :	Extracts a substring from a string (starting at any position)

SUBSTRING :             	Extracts a substring from a string (starting at any position)

SUBSTRING_INDEX	 :    Returns a substring of a string before a specified number of delimiter occurs

TRIM :       	Removes leading and trailing spaces from a string

UCASE :	Converts a string to upper-case

UPPER :	Converts a string to upper-case


------------------------------------------------------------------------------------------------------------------------------------


MySQL Numeric Functions
Function	Description


------------------------------------------------------------------------------------------------------------------------------------


ABS :	Returns the absolute value of a number

ACOS :	Returns the arc cosine of a number

ASIN :	Returns the arc sine of a number

ATAN :	Returns the arc tangent of one or two numbers

ATAN2 :	Returns the arc tangent of two numbers

AVG :	Returns the average value of an expression

CEIL :	Returns the smallest integer value that is >= to a number

CEILING :	Returns the smallest integer value that is >= to a number

COS :	Returns the cosine of a number

COT :	Returns the cotangent of a number

COUNT :	Returns the number of records returned by a select query

DEGREES :	Converts a value in radians to degrees

DIV	 :    Used for integer division

EXP :	Returns e raised to the power of a specified number

FLOOR :	Returns the largest integer value that is <= to a number

GREATEST :	Returns the greatest value of the list of arguments

LEAST : 	Returns the smallest value of the list of arguments

LN :        	Returns the natural logarithm of a number

LOG :	Returns the natural logarithm of a number, or the logarithm of a number to a specified base

LOG10 :	Returns the natural logarithm of a number to base 10

LOG2 :	Returns the natural logarithm of a number to base 2

MAX :	Returns the maximum value in a set of values

MIN :	Returns the minimum value in a set of values

MOD :	Returns the remainder of a number divided by another number

PI :    	Returns the value of PI

POW :	Returns the value of a number raised to the power of another number

POWER :	Returns the value of a number raised to the power of another number

RADIANS :	Converts a degree value into radians

RAND :	Returns a random number

ROUND :	Rounds a number to a specified number of decimal places

SIGN :	Returns the sign of a number

SIN :	Returns the sine of a number

SQRT :	Returns the square root of a number

SUM : 	Calculates the sum of a set of values

TAN	 :    Returns the tangent of a number

TRUNCATE :	Truncates a number to the specified number of decimal places


------------------------------------------------------------------------------------------------------------------------------------


MySQL Date Functions
Function	Description


------------------------------------------------------------------------------------------------------------------------------------


ADDDATE :	Adds a time/date interval to a date and then returns the date

ADDTIME :	Adds a time interval to a time/datetime and then returns the time/datetime

CURDATE :	Returns the current date

CURRENT_DATE :	Returns the current date

CURRENT_TIME	 :    Returns the current time

CURRENT_TIMESTAMP :	Returns the current date and time

CURTIME :   Returns the current time

DATE :  	 Extracts the date part from a datetime expression

DATEDIFF :  Returns the number of days between two date values

DATE_ADD : Adds a time/date interval to a date and then returns the date

DATE_FORMAT :	Formats a date

DATE_SUB :	Subtracts a time/date interval from a date and then returns the date

DAY :           Returns the day of the month for a given date

DAYNAME :	  Returns the weekday name for a given date

DAYOFMONTH : Returns the day of the month for a given date

DAYOFWEEK :	Returns the weekday index for a given date

DAYOFYEAR :	Returns the day of the year for a given date

EXTRACT :	Extracts a part from a given date

FROM_DAYS :	Returns a date from a numeric datevalue

HOUR :	Returns the hour part for a given date

LAST_DAY :	Extracts the last day of the month for a given date

LOCALTIME :	Returns the current date and time

LOCALTIMESTAMP :    	Returns the current date and time

MAKEDATE :	Creates and returns a date based on a year and a number of days value

MAKETIME :	Creates and returns a time based on an hour, minute, and second value

MICROSECOND :       	Returns the microsecond part of a time/datetime

MINUTE :	Returns the minute part of a time/datetime

MONTH :	Returns the month part for a given date

MONTHNAME :	Returns the name of the month for a given date

NOW :   	Returns the current date and time

PERIOD_ADD :	Adds a specified number of months to a period

PERIOD_DIFF :	Returns the difference between two periods

QUARTER :	Returns the quarter of the year for a given date value

SECOND :	Returns the seconds part of a time/datetime

SEC_TO_TIME :   	Returns a time value based on the specified seconds

STR_TO_DATE :	Returns a date based on a string and a format

SUBDATE :	Subtracts a time/date interval from a date and then returns the date

SUBTIME :	Subtracts a time interval from a datetime and then returns the time/datetime

SYSDATE :	Returns the current date and time

TIME :	Extracts the time part from a given time/datetime

TIME_FORMAT :	Formats a time by a specified format

TIME_TO_SEC :   	Converts a time value into seconds

TIMEDIFF :          	Returns the difference between two time/datetime expressions

TIMESTAMP :     	Returns a datetime value based on a date or datetime value

TO_DAYS :           	Returns the number of days between a date and date "0000-00-00"

WEEK :              	Returns the week number for a given date

WEEKDAY :       	Returns the weekday number for a given date

WEEKOFYEAR :    	Returns the week number for a given date

YEAR :              	Returns the year part for a given date

YEARWEEK :       	Returns the year and week number for a given date


------------------------------------------------------------------------------------------------------------------------------------


MySQL Advanced Functions

Function	Description


------------------------------------------------------------------------------------------------------------------------------------


BIN :	Returns a binary representation of a number

BINARY :    Converts a value to a binary string

CASE :	Goes through conditions and return a value when the first condition is met

CAST :	Converts a value (of any type) into a specified datatype

COALESCE :	Returns the first non-null value in a list

CONNECTION_ID :	Returns the unique connection ID for the current connection

CONV :          	Converts a number from one numeric base system to another

CONVERT :   	Converts a value into the specified datatype or character set

CURRENT_USER :	Returns the user name and host name for the MySQL account that the server used to authenticate 
the current client

DATABASE :	Returns the name of the current database

IF :    	Returns a value if a condition is TRUE, or another value if a condition is FALSE

IFNULL :    Return a specified value if the expression is NULL, otherwise return the expression

ISNULL	 :   Returns 1 or 0 depending on whether an expression is NULL

LAST_INSERT_ID :	Returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table

NULLIF :	Compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned

SESSION_USER :	Returns the current MySQL user name and host name

SYSTEM_USER :	Returns the current MySQL user name and host name

USER :	Returns the current MySQL user name and host name

VERSION :	Returns the current version of the MySQL database