My Notes on Structured Query Language

SQL is the standard language for storing, manupulating, and retrieving data in databases.

What is SQl?

Lets you access and manipulate databases

Became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

What can SQL do?

Execute queries against a database
Retrieve data from a database
Insert records in a database
Update records in a database
Delete records from a database
Create new databases
Create new tables in a database
Create stored procedures in a database
Create views in a database
Set permissions on tables, procedures and views

SQL is a Standard

There are three different verions of the SQL Language.

To be compliant with the ANSI standard, they all support at least the following major commands:

SELECT
UPDATE
INSERT
WHERE

Most SQL database programs alshave their own proprietary extensions in addition to the SQL standard.

Using SQL in your own website

To build a web site that shows data from a database you will need the following:

A RDBMS database program (Microsoft Access, SQL Server, MySQL)
A server-side scripting language, like PHP or ASP
Use SQL to get the data you want
Use HTML / CSS to style the page

Relational Database Management System (RDBMS)

is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.

Every table is broken up into smaller entities called fields. A field is a column in a table that is designed to maintain specific information about every record in the table.

A record, also called a row, is each individual entry that exists in a table. A record is a horizontal entity in a table.

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

Database tables

A database most often contains one or more tables.

SQL Statements

Most of the actions you need to perform on a database are done with SQL statements.

The following SQL statement selects all the records in the "Customers" table:
SELECT * FROM Customers;

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.

Some 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

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT Syntax

SELECT column1 , column2 FROM table_name;


	In the example above column 1 and column 2 are the field names of the table you want to select data from. If you want to select all the field available in the table, use this syntax:
SELECT 
	* FROM table_name;





	The SELECT DISTINCT Statement 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;

SQL WHERE Clause
	The WHERE Clause is used to filter records.
	It is used to extract only records to fulfill a specific condition.
	WHERE Syntax Example:

	SELECT column1, column2

	from table_name

	WHERE condition;

Text vs Numeric Fields
	SQL requires single quotes around text values however, most database systems will also allow double quotes.
	Numeric fields should not be encloses in quotes:
	SELECT * FROMCustomers

	WHERE CustomerID=1;


Operators that can be used in the WHERE clause:

	
		Operator
		Description
	
	
		=
		Equal
	
	
		>
		Greater than
	
	
		<
		Less than
	
	
		>=
		Greater tham or equal to
	
	
		<=
		Less than or equal to
	
	
		<>
		Not equal, can also be wrtten as !=
	
	
		BETWEEN
		Between certain range
	
	
		LIKE
		Search for a pattern
	
	
		IN
		To specify multiple possible values for a column
	


	

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



	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 Example	
	SELECT *  Customers

	ORDER BY Country;
	

ORDER BY DESC Example	
	SELECT *  Customers

	ORDER BY CountryDESC;
	

ORDER BY Examples	
	SELECT * FROM Customers

	ORDER BY Country, CustomerName;
	
	SELECT * FROM Customers

	ORDER BY Country ASC, CustomerName DESC;
	





	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:
	Specify both the column names and the values to be inserted. (as shown below)
	INSERT INTO table_name (column1, column2, column3)

	The VALUES(value1, value2, value3);
	

	Or 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.
	INSERT INTO table_name

	The VALUES(value1, value2, value3);
	


	


	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.
	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.
	The IS NULL operator is used to test for empty values (NULL values).
	We will have to use the IS NULL and IS NOT NULL operators
	IS NULL Syntax Example:

	SELECT column_names

	FROM table_name

	WHERE column_name IS NULL;
	
	IS NOT NULL Syntax Example:

	SELECT column_names

	FROM table_name

	WHERE column_name IS NOT NULL;
	
	Always use IS NULL to look for NULL values.

	


	The UPDATE statement is used to modify the existing records in a table.
	UPDATE Syntax Example:

	UPDATE table name

	SET column1 = value1, column2 = value2

	WHERE condition;
	
	Be aware if you omit the WHERE clause, all records in the table will be updated!

	


	The DELETE statement is used to delete existing records in a table.
	DELETE Syntax Example:

	DELETE FROM  table_name where condition:
	
	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 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, because returning a large number of records can impact performance.
	Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM.

Example Syntax for Different SQL Langauages
	
	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 12 Syntax
		SELECT  column_name(s)

		FROM  table_name

		ORDER BY  column_name(s)

		FETCH FIRST  number ROWS ONLY;
		

	Older Oracle Syntax
		SELECT  column_name(s)

		FROM  table_name

		WHERE  ROWNUM <= number;
		

	Older Oracle Syntac with ORDER BY
		SELECT  *

		FROM (SELECT  column_name(s) FROM  table_name ORDER BY  column_name(s))

		WHERE  ROWNUM <= number;
		


			


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

Examples:
	MIN() Syntax:

	SELECT  MIN(column_name)

	FROM  table_name

	WHERE  condition;
	

	MAX() Syntax:

	SELECT  MAX(column_name)

	FROM  table_name

	WHERE  condition;
	

			


	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. 

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





	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 sign (_) represents one, single character
	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!
	
You can also combine any number of conditions using AND or OR operators.

LIKE Syntax:
	SELECT  column1, column2

	FROM  table_name

	WHERE  columnn LIKE  pattern;
	

Examples using LIKE operator with "%" and "_" wildscards:
	
		
			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"
		
	




	A wildcard character is used to substitute one or more characters in a string.
	Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Microsoft Access Wildcards
	
		
			Symbol
			Description
		
		
			*
			Represents zero or more characters
		
		
			?
			Represents a single character
		
		
			[]
			Represents any single character within the brackets
		
		
			!
			Represents any character not in the brackets
		
		
			-
			Represents any single character within the specified range
		
		
			#
			Represents any single numeric character
		
	

SQL Server Wildcards
	
		
			Symbol
			Description
		
		
			%
			Represents zero or more characters
		
		
			_
			Represents a single character
		
		
			[]
			Represents any single character within the brackets
		
			
			^
			Represents any character not in the brackets
		

			-
			Represents any single character within the specified range
		
	



	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 can be:
	SELECT  column_name(s)

	FROM  table_name

	WHERE  IN  (SELECT STATEMENT);
	



	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;
	

			


	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 that query.
	An alias is created with the AS keyword.

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;
	

			


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

There are different Types of JOINs

(INNER) JOIN: Returns records that have matching values in both tables
	The INNER JOIN keyword selects records that have matching values in both tables.
	Syntax:

	SELECT  column_name(s)

	FROM   table1

	INNER JOIN   table2

	ON   table1.column_name = table2.column_name;
	


LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
	The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.
	Syntax

	SELECT  column_name(s)

	FROM   table1

	LEFT JOIN  table2

	ON   table1.column_name = table2.column_name;
	
	
	 In some databases LEFT JOIN is called LEFT OUTER JOIN.

RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
	The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.
	Syntax

	SELECT  column_name(s)

	FROM   table1

	RIGHT JOIN   table2

	ON   table1.column_name = table2.column_name;
	

	In some databases RIGHT JOIN is called RIGHT OUTER JOIN.	

FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
	The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records. FULL OUTER JOIN and FULL JOIN are the same.
	FULL OUTER JOIN can potentially return very large result-sets.
	Syntax

	SELECT  column_name(s)

	FROM   table1

	FULL OUTER JOIN   table2

	ON   table1.column_name = table2.column_name

	WHERE   condition;
	

SELF Join
	A self join is a regular join, but the table is joined with itself.
	Syntax

	SELECT  column_name(s)

	FROM  table1 T1, table1 T2

	WHERE  condition;
	




	The UNION operator is used to combine the result-set of two or more SELECT statements.
	Every SELECT statement within UNION must have the same number of columns.
	The columns must also have similar data types.
	The columns in every SELECT statement must also be in the same order.
	Syntax

	SELECT  column_name(s)  FROM  table1

	UNION
 
	SELECT   column_name(s)  FROM  table2;
	

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

	SELECT  column_name(s)  FROM  table1

	UNION ALL
 
	SELECT   column_name(s)  FROM  table2;
	

			


	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.
	Syntax

	SELECT  column_name(s)

	FROM  table_name

	WHERE  condition

	GROUP BY  column_name(s)

	ORDER BY  column_name(s);
	

			


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

	SELECT  column_name(s)

	FROM   table_name

	WHERE   condition

	GROUP BY   column_name(s)

	HAVING   condition

	ORDER BY   column_name(s);
	

			


	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.
	Syntax

	SELECT  column_name(s)

	FROM   table_name

	WHERE EXISTS

	(SELECT  column_name FROM  table_name WHERE  condition);
	

			


	The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.
	
ANY Operator
	The ANYoperator; returns a boolean value as a result; returns TRUE if ANY of the subquery values meet the condition.
	ANY means that the condition will be true if the operation is true for any of the values in the range
	Syntax

	SELECT  column_name(s)

	FROM   table_name

	WHERE   column_name operator ANY
	  (SELECT   column_name

	  FROM   table_name

	  WHERE   condition);
	

ALL Operator
	The ALL operator; returns a boolean value as a result; returns TRUE if ALL of the subquery values meet the condition; is used with SELECT, WHERE and HAVING statements
	ALL means that the condition will be true only if the operation is true for all values in the range.
	Syntac with SELECT

	SELECT ALL  column_name(s)

	FROM  table_name

	WHERE  condition;
	
	Syntax with WHERE or HAVING

	SELECT  column_name(s)

	FROM  table_name

	WHERE  column_name operator ALL

	  (SELECT  column_name

	  FROM  table_name

	  WHERE  condition);
	


			


	The SELECT INTO statement copies data from one table into a new table.
	Syntax (to copy all columns into a new table)

	SELECT  *

	INTO  newtable [IN externaldb]

	FROM  oldtable
	WHERE condition;

	
Syntax (Copy only some columns into a new table)

	SELECT  column1, column2, column3

	INTO  newtable [IN externaldb]

	FROM  oldtable

	HERE  condition;
	

			


	The INSERT INTO SELECT statement copies data from one table and inserts it into another table.
	The INSERT INTO SELECT statement requires that the data types in source and target tables match. The existing records in the target table are unaffected.
	Syntax (Copy all columns from one table to another)

	INSERT INTO table2

	SELECT *  FROM table1

	WHERE  condition;
	
Syntax (Copy only some columns from one table to another)

	INSERT INTO  table2 (column1, column2, column3)

	SELECT  column1, column2, column3

	FROM  table1

	WHERE  condition;
	

			


	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.
	Syntax

	CASE

	    WHEN  condition1 THEN  result1

	    WHEN  condition2 THEN  result2

	    WHEN  conditionN THEN  resultN

	    ELSE  result

	END; 
	

			


	MySQL
	The MySQL IFNULL() function lets you return an alternative value if an expression is NULL or use the COALESCE() function
	SQL Server
	The SQL Server ISNULL() function lets you return an alternative value when an expression is NULL or use the COALESCE() function
	Microsoft Access
	The MS Access IsNull() function returns TRUE (-1) if the expression is a null value, otherwise FALSE (0)
	Oracle
	The Oracle NVL() function achieves the same result or use the COALESCE() function

			


	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.
	Syntax

	CREATE PROCEDURE  procedure_name

	AS

	 sql_statement

	 GO;
	
	Execute Stored Procedure Syntax

	EXEC  procedure_name;
	

			


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

Single Line Comments
	Single line comments start with --, any text between -- and the end of the line will be ignored meaning will not be executed.
	Syntax

	 --Select all:

	SELECT  * FROM  Country; or

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

Multi-Line Comments	
	Multi-line comments start with /* and end with */ and any text between /* and */ will be ignored.
	Syntax

	 /*Select all the columns

	 of all the records

	 in the Customers table:*/

	SELECT  * FROM  Customers;
	

			



Arithmetic
	
		
			Description
			Example
		
		
			+
			Add
		
		
			-
			Subtract
		
		
			*
			Multiply
		
		
			/
			Divide
		
		
			%
			Modulo
		
	

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

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

Compund Operators
	
		
			Operator
			Description
		
		
			+=
			Add equals
		
		
			-=
			Subtract equals
		
		
			*=
			Multiply equals
		
		
			/=
			Divide equals
		
		
			%=
			Modulo equals
		
		
			&=
			Bitwise AND equals
		
		
			^-=
			Bitwise exclusive equals
		
		
			|*=
			Bitwise OR equals
		
	
		
			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	
		
	

			


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

	CREATE DATABASE  databasename;
	

	


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

	DROP DATABASE  databasename;
	

		     


	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';
	
	Syntax (A differential back up only backs up the parts of the database that have changed since the last full database backup.)

	BACKUP DATABASE  databasename

	TO DISK = 'filepath';

	WITH  DIFFERETIAL;

							   


	The CREATE TABLE statement is used to create a new table in a database.
	The column parameters specify the names of the columns of the table.
	The datatype parameter specifies the type of data the column can hold (varchar, integer, date, etc.).
	Syntax

	CREATE TABLE  table_name (

	     Column1 datatype,

	    column2 datatype,

	    column3 datatype,

   	);
	
	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 DROP TABLE statement is used to drop and existing tbale ina database
	Syntax

	DROP TABLE  table_name;
	
	TRUNCATE TABLE is used to delete the data inside a table

	Syntax

	TRUNCATE TABLE  table_names;
	

							   


	The ALTER TABLE statement is used to add, delete, or modify columns in an existing table and also used to add and drop various constraints on an existing table.
	ADD Column Syntax

	ALTER TABLE  table_name

	ADD  column_name datatype;
	
DROP COLUMN Syntax

	ALTER TABLE  table_name

	DROP COLUMN  column_name;
	
	Other 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;
	

							   


	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 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.
	
Commonly used Contraints:

	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 - Prevents actions that would destroy links between tables

	CHECK - Ensures that the values in a column satisfies a specific condition

	DEFAULT - Sets a default value for a column if no value is specified

	CREATE INDEX - Used to create and retrieve data from the database very quickly

	

							   


	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.

		    


	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.

UNIQUE Contraint on CREATE table
	SYNTAX

	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)

	);
	

UNIQUE Contraint on ALTER TABLE
	Syntax

	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.
	MySQL / SQL Server / Oracle / MS Access:

	ALTER TABLE  Persons

	ADD CONSTRAINT  UC_Person UNIQUE  (ID,LastName);
	

DROP a Unique Contraint
	To drop a UNIQUE constraint

	MySQL:

	ALTER TABLE  Persons

	DROP INDEX  UC_Person;

	
	SQL Server / Oracle / MS Access:

	ALTER TABLE  Persons

	DROP CONSTRAINT  UC_Person;
	

							   


	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.

PRIMARY KEY on CREATE TABLE
	MySQL:

	CREATE TABLE  Persons (

	    ID int NOT NULL,

	    LastName varchar(255) NOT NULL,

	    FirstName varchar(255),

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

	);
	

PRIMARY KEY on ALTER TABLE
	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.

	MySQL / SQL Server / Oracle / MS Access:

	ALTER TABLE  Persons

	ADD CONSTRAINT  PK_Person PRIMARY KEY  (ID,LastName);
		

DROP a PRIMARY KEY Constraint
	MySQL:

	ALTER TABLE  Persons

	DROP PRIMARY KEY;

	SQL Server / Oracle / MS Access:
	ALTER TABLE  Persons

	DROP CONSTRAINT  PK_Person;
	

							   


	The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
	A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.
	The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.




	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 column it will allow 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.

									      


	The DEFAULT constraint is used to set a default value for a column.
	The default value will be added to all new records, if no other value is specified.

								  


	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.
	Syntax

	CREATE INDEX  index_name

	ON  table_name (column1, column2);
	

							   


	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.


							   


	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 statements and functions to a view and present the data as if the data were coming from one single table.
	A view is created with the CREATE VIEW statement. 
	Syntax

	CREATE VIEW  view_name AS

	SELECT  column1, column2

	FROM  table_name

	WHERE  condition;
	

							   


	A code injection technique that might destroy your database.
	One of the most common web hacking techniques.
	is the placement of malicious code in SQL statements, via web page input.

In Web Pages
	QL 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.

Parameters fro 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.
	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 on how to build parameterized queries
	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();
	


	



SQL Hosting
	If you want your web site to be able to store and retrieve data from a database, your web server should have access to a database-system that uses the SQL language.
	If your web server is hosted by an Internet Service Provider (ISP), you will have to look for SQL hosting plans.
	The most common SQL hosting databases are MS SQL Server, Oracle, MySQL, and MS Access.

MS SQL Server
	Microsoft's SQL Server is a popular database software for database-driven web sites with high traffic.
	SQL Server is a very powerful, robust and full featured SQL database system.

Oracle
	Oracle is also a popular database software for database-driven web sites with high traffic.
	Oracle is a very powerful, robust and full featured SQL database system.

MySQL
	MySQL is also a popular database software for web sites.
	MySQL is a very powerful, robust and full featured SQL database system.
	MySQL is an inexpensive alternative to the expensive Microsoft and Oracle solutions.

MS Access
	When a web site requires only a simple database, Microsoft Access can be a solution.
	MS Access is not well suited for very high-traffic, and not as powerful as MySQL, SQL Server, or Oracle.

	


	The data type of a column defines what value the column can hold: integer, character, money, date and time, binary, and so on.
	Check out W3 School SQL Data Types Tables

	


	
		SQL Keywords
		MySQL Functions
		SQL Server Functions
		MS Access Functions
		SQL Quick Reference

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater tham or equal to
<=	Less than or equal to
<>	Not equal, can also be wrtten as !=
BETWEEN	Between certain range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

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"

Symbol	Description
*	Represents zero or more characters
?	Represents a single character
[]	Represents any single character within the brackets
!	Represents any character not in the brackets
-	Represents any single character within the specified range
#	Represents any single numeric character

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

Structured Query Language

What is SQl?

What can SQL do?

SQL is a Standard

Using SQL in your own website

Relational Database Management System (RDBMS)

SQL Syntax

Database tables

SQL Statements

Semicolon after SQL Statements

Some Important SQL Commands

SQL SELECT Statement

SELECT Syntax

SQL SELECT DISTINCT Statement

SELECT DISTINCT Syntax

SQL WHERE Clause

Text vs Numeric Fields

Operators that can be used in the WHERE clause:

SQL AND, OR and NOT Operators

AND Syntax

OR Syntax

NOT Syntax

ORDER BY Keyword

ORDER BY Example

ORDER BY DESC Example

ORDER BY Examples

INSERT INTO Statement

INSERT INTO Syntax

NULL Value

How to test for NULL Values

UPDATE Statement

DELETE Statement

SELECT TOP Clause

Example Syntax for Different SQL Langauages

SQL Server / MS Access Syntax

MySQL Syntax

Oracle 12 Syntax

Older Oracle Syntax

Older Oracle Syntac with ORDER BY

MIN() and MAX() Fucntions

Examples:

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

Examples:

LIKE Operator

LIKE Syntax:

Examples using LIKE operator with "%" and "_" wildscards:

Wildcards

Microsoft Access Wildcards

SQL Server Wildcards

IN Operator

IN Syntax

or can be:

BETWEEN Operator

BETWEEN Syntax

Aliases

Alias Column Syntax

Alias Table Syntax

Joins

There are different Types of JOINs

UNION Operator

GROUP BY Statement

HAVING Clause

EXISTS Operator

ANY and ALL Operators

ANY Operator

ALL Operator

SELECT INTO Statement

INSERT INTO SELECT Statement

CASE Statement

IFNULL(), ISNULL(), COALESCE(), and NVL() Functions

MySQL

SQL Server

Microsoft Access

Oracle

Stored Procedures

Comments

Single Line Comments

Multi-Line Comments

Operators

Arithmetic