SQL Statements

- Basic commands
SELECT statement

- SELECT DISTINCT
WHERE clause
Operators
AND, OR, NOT operators

- AND

- OR

- NOT
Combinations
ORDER BY keyword

- ORDER BY several columns

INSERET INTO statement
NULL values

- Testing for NULL values

UPDATE statement

- UPDATE table

- UPDATE multiple records

DELETE statement

- DELETE all records

SELECT TOP
MIN() and MAX() functions
COUNT(), AVG(), SUM() functions
LIKE operator
Wildcards
IN operator
BETWEEN operator
Aliases
Joins
Different types of JOINs
UNION operator
UNION ALL
UNION with WHERE
GROUP BY
HAVING clause
Exists
ANY, ALL operators
ANY
ALL
SELECT INTO
INSERT INTO SELECT statement
CASE expression
IFNULL functions
Stored Procedures
CREATE DATABASE
DROP DATABASE
BACKUP DATABASE
DIFFERENTIAL
CREATE TABLE
CREATE TABLE using another table
DROP TABLE
TRUNCATE TABLE
ALTER TABLE
ADD Column
DROP Column
RENAME Column
ALTER/MODIFY datatype
Constraints
NOT NULL
UNIQUE constraint
PRIMARY KEY constraint
FOREIGN KEY constraint
CHECK constraint
DEFAULT constraint
CREATE INDEX statement
AUTO INCREMENT field
DATES
MySQL
SQL Server
Views
Injection
Data Types
 String Data Types
Numeric Data Types

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

Basic commands
SELECT : extracts data from a database
UPDATE : updates data in a database
DELETE : deletes data from a a database
INSERT INTO : inserts new data into a database
CREATE DATABASE : creates a new database
ALTER DATABASE : modifies a database
CREATE TABLE : creates a new table
ALTER TABLE : modifies a table
DROP TABLE : deletes a table
CREATE INDEX : creates an index (search key)
DROP INDEX : deletes an index

SELECT statement
SELECT column1, column2

FROM table_name;

To select all fields available in the table:

SELECT * FROM table_name;

SELECT CustomerName, City FROM Customers;

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

SELECT DISTINCT column1, column2

FROM table_name;

List the number of distinct customer countries: SELECT COUNT(DISTINCT Country)

FROM Customers;
WHERE clause
The WHERE clause is used to filter records

SELECT column1, column2

FROM table_name

WHERE condition;

SELECT * FROM Customers

WHERE Country='Mexico';

SELECT * FROM Customers

WHERE CustomerID=1;

Operators
Operator Description

= Equal

> Greater than

< Less than

>= Greater than or equal

<= Less than or equal

<> Not equal

BETWEEN Between a certain range

LIKE Search for pattern

IN Multiple possible values for a column

AND, OR, NOT operators

AND

SELECT column1, column2

FROM table_name

WHERE condition1 AND condition2 AND condition3;

SELECT * FROM Customers

WHERE Country='Germany' AND City='Berlin';

OR

SELECT column1, column2

FROM table_name

WHERE condition1 OR condition2 OR condition3;

SELECT * FROM Customers

WHERE City='Berlin' OR City='Munchen';

NOT

SELECT column1, column2

FROM table_name

WHERE NOT condition;

SELECT * FROM Customers

WHERE NOT Country='Germany';

Combinations
SELECT * FROM Customers

WHERE Country='Germany' AND (City='Berlin' OR City='Munchen');

SELECT * FROM Customers

WHERE NOT Country='Germany' AND NOT Country='USA';

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

SELECT column1, column2

FROM table_name

ORDER BY column1, column2, ASC|DESC;

SELECT * FROM Customers

ORDER BY Country;

SELECT * FROM Customers

ORDER BY Country DESC;

ORDER BY several columns

SELECT * FROM Customers

ORDER BY Country, CustomerName;

SELECT * FROM Customers

ORDER BY Country ASC, CustomerName DESC;

INSERET INTO statement

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

Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3)

VALUES (value1, value2, value3);

If adding values for all the columns of the table, you do not need to
specify the column names. However, make sure the order of the values is
in the same order as the columns in the table:

INSERT INTO table_name

VALUES (value1, value2);

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode,

Country)

VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

NULL values
Testing for NULL values
To test for NULL use IS NULL and IS NOT NULL

SELECT column_names

FROM table_name

WHERE column_name IS NULL;

SELECT column_names

FROM table_name

WHERE column_name IS NOT NULL;

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

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

UPDATE Customers

SET ContactName='Alfred Scmidt', City='Frankfurt'

WHERE CustomerID=1;

UPDATE multiple records

UPDATE Customers

SET ContactName='Juan'

WHERE Country='Mexico';

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

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!

DELETE FROM Customers WHERE CustomerName='Alfred';

DELETE all records

 --Delete all rows in a table without deleting the table

DELETE FROM table_names;

SELECT TOP
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  FETCH FIRST _n_ ROWS ONLY  and  ROWNUM .

SELECT TOP number

FROM table_names

WHERE condition;

SELECT column_name(s)

FROM table_name

WHERE condition

LIMIT number;

SELECT * FROM Customers

LIMIT 3;

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

SELECT MIN(column_name)

FROM table_name

WHERE condition;

SELECT MIN(column_name)

FROM table_name

WHERE condition;

SELECT MIN(Price) AS SmalletPrice

FROM Products;

SELECT MAX(Price) AS LargestPrice

FROM Products;

COUNT(), AVG(), SUM() functions

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

SELECT COUNT(column_names)

FROM table_name

WHERE condition;

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

SELECT AVG(column_name)

FROM table_name

WHERE condition;

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

SELECT SUM(column_name)

FROM table_name

WHERE condition;

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

There are 2 wildcards of used with the LIKE operator:

% : represents zero, one, or multiple characters

_ : represents one single character

SELECT column1, column2

FROM table_name

WHERE columnN LIKE pattern;

 SELECT * FROM Customers

WHERE CustomerName LIKE 'A%'

Wildcards
IN operator
The IN operator allows you to specify multiple values in a WHERE clause

Shorthand for multiple OR conditions

SELECT column_name

FROM table_name

WHERE column_name IN (value1, value2)

SELECT column_name

FROM table_name

WHERE column_name IN (SELECT statement)

SELECT * FROM Customers

WHERE Country IN ('Germany', 'France', 'UK');

SELECT * FROM Customers

WHERE Country NOT IN ('Germany', 'France', 'UK');

SELECT * FROM Customers

WHERE Country IN (SELECT Country FROM Suppliers);

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

The BETWEEN operator is inclusive, begin and end values are included

SELECT column_names

FROM table_name

WHERE column_name BETWEEN value1 AND value2

SELECT * FROM Products

WHERE Price BETWEEN 10 AND 20;

SELECT * FROM Products

WHERE Price NOT BETWEEN 10 AND 20;

SELECT * FROM Products

WHERE Price BETWEEN 10 AND 20

AND CategoryID NOT IN (1,2,3);

 SELECT * FROM Products

WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'

ORDER BY ProductName;

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 that query.

An alias is created with the  AS  keyword.

SELECT column_name AS alias_name

FROM table_name;

SELECT column_names

FROM table_names AS alias_name;

SELECT CustomerID AS ID, CustomerName AS Customer

FROM Customers;

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;

Joins
A JOIN clause is used to combine rows from two or more tables, based on a
related column between them.
 SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Different types of JOINs

 (INNER) JOIN : Returns records that have matching values in both tables
LEFT (OUTER) JOIN : Returns all records from the left table, and the
matched records from the right table
RIGHT (OUTER) JOIN : Returns all records from the right table, and the
matched records from the left table
FULL (OUTER) JOIN : Returns all records when there is a match in either
left or right table

UNION operator
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

SELECT column_names FROM table1

UNION

SELECT column_names FROM tables2;

UNION ALL
The  UNION  operator selects only distinct values by default. To allow
duplicate values, use  UNION ALL :
 SELECT column_names FROM table1

UNION ALL

SELECT column_names FROM table2;

SELECT City FROM Customers

UNION

SELECT City FROM Suppliers

ORDER BY City;

SELECT City FROM Customers

UNION ALL

SELECT City FROM Suppliers

ORDER BY City;

UNION with WHERE

SELECT City, Country FROM Customers

WHERE Country='Germany'

UNION

SELECT City, Country FROM Suppliers

WHERE Country='Germany'

ORDER BY City;

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

The  GROUP BY  statement is often used with aggregate functions

( COUNT() ,  MAX() ,  MIN() ,  SUM() ,  AVG() ) to group the result-set by one or more
columns.

SELECT column_names

FROM table_name

WHERE condition

GROUP BY column_names

ORDER BY column_names;

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

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country;

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

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country

ORDER BY COUNT(CustomerID) DESC;

The following SQL statement lists the number of orders sent by each shipper:

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders

LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID

GROUP BY ShipperName;

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

SELECT column_name

FROM table_name

WHERE condition

GROUP BY column_names

HAVING condition

ORDER BY column_names

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country

HAVING COUNT(CustomerID) > 5;

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country

HAVING COUNT(CustomerID) > 5

ORDER BY COUNT(CustomerID) DESC;

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

SELECT column_names

FROM talble_name

WHERE EXISTS

(SELECT column_name FROM table_name WHERE condition);

SELECT SupplierName

FROM Suppliers

WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID =

Suppliers.supplierID AND Price < 20);

ANY, ALL operators

The  ANY  and  ALL  operators allow you to perform a comparison between a
single column value and a range of other values.

ANY
The  ANY  operator:

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.

SELECT column_names

FROM table_names

WHERE column_name operator ANY

(SELECT column_name
FROM table_name

WHERE condition);

Note: The operator must be a standard comparison operator (=, <>, !=, >, >=,

<, or <=).

The following SQL statement lists the ProductName if it finds ANY records in
the OrderDetails table has Quantity equal to 10 (this will return TRUE
because the Quantity column has some values of 10):
 SELECT ProductName

FROM Products

WHERE ProductID = ANY

(SELECT ProductID

FROM OrderDetails

WHERE Quantity = 10);

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

SELECT column_names

FROM table_names

WHERE column_name operator ALL

(SELECT column_name
FROM table_name

WHERE condition);

The following SQL statement lists the ProductName if ALL the records in the
OrderDetails table has Quantity equal to 10. This will of course return
FALSE because the Quantity column has many different values (not only the
value of 10):

SELECT ProductName

FROM Products

WHERE ProductID = ALL

(SELECT ProductID

FROM OrderDetails

WHERE Quantity = 10);

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

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

INTO newtable IN externaldb

FROM oldtable

WHERE condition

SELECT * INTO CustomersBackup2017

FROM Customers;

SELECT * INTO CustomersBackup2017 IN 'Backup.mdb'

FROM Customers;

SELECT * INTO CustomersGermany

FROM Customers

WHERE Country = 'Germany';

INSERT INTO SELECT statement

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.

Note: The existing records in the target table are unaffected.

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)

SELECT column1, column2

FROM table1

WHERE condition;

 INSERT INTO Customers (CustomerName, City, Country)

SELECT SupplierName, City, Country FROM Suppliers;

CASE expression
The  CASE  expression 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

WHEN condition1 THEN result1

WHEN condition2 THEN result2

ELSE result

END;

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

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:

SELECT CustomerName, City, Country

FROM Customers

ORDER BY

(CASE

WHEN City IS NULL THEN Country

ELSE City

END);

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

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

FROM Products;

Stored Procedures
Stored Procedure Syntax:

CREATE PROCEDURE procedure_name

AS

sql_statement

GO;

Execute a Stored Procedure

EXEC procedure_name;

CREATE PROCEDURE SelectAllCustomers

AS

SELECT * FROM Customers

GO;

EXEC SelectAllCustomers;

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)

AS

SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode

GO;

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

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

CREATE DATABASE databasename;

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

DROP DATABASE _databasename_;

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

BACKUP DATABASE _databasename_

TO DISK = 'filepath';

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

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

CREATE TABLE table_name (

column1 datatype,

column2 datatype

);

CREATE TABLE Persons (

PersonID int,

LastName varchar(255),

FirstName varchar(255),

Address varchar(255),

City varchar(255)

);

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.

CREATE TABLE new_table_name AS

SELECT column1, column2

FROM existing_table_name

WHERE condition;

CREATE TABLE TestTable AS

SELECT customername, contactname

FROM customers;

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

DROP TABLE table_name;

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

TRUNCATE TABLE table_name;

ALTER TABLE
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.

ADD Column
ALTER TABLE table_name

ADD column_name datatype;

 ALTER TABLE Customers

ADD Email VARCHAR(255);

DROP Column
ALTER TABLE table_name

DROP COLUMN column_name;

ALTER TABLE Customers

DROP COLUMN Email;

RENAME Column
ALTER TABLE table_name

RENAME COLUMN old_name to new_name;

ALTER/MODIFY datatype
ALTER TABLE table_name

ALTER COLUMN column_name datatype;

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

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.

CREATE TABLE table_name (

column1 datatype constraint,

column2 datatype constraint

);

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

NOT NULL
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.

CREATE TABLE Persons (

ID INT NOT NULL,

LastName VARCHAR(255) NOT NULL,

FirstName VARCHAR(255) NOT NULL,

Age INT

);

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.

CREATE TABLE Persons (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

UNIQUE (ID)

);

ALTER TABLE Persons

ADD UNIQUE (ID);

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

CREATE TABLE Persons (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

PRIMARY KEY (ID)

);

CREATE TABLE Persons (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)

);

FOREIGN KEY constraint

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.

CREATE TABLE Orders (

OrderID int NOT NULL,

OrderNumber int NOT NULL,

PersonID int,

PRIMARY KEY (OrderID),

FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)

);

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)

);

ALTER TABLE Orders

ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

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

CREATE TABLE Persons (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int CHECK (Age>=18)

);

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

);

ALTER TABLE Persons

ADD CHECK (Age>=18);

DEFAULT constraint
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.
 CREATE TABLE Persons (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

City varchar(255) DEFAULT 'Sandnes'

);

CREATE TABLE Orders (

ID int NOT NULL,

OrderNumber int NOT NULL,

OrderDate date DEFAULT GETDATE()

);

ALTER TABLE Persons

ALTER City SET DEFAULT 'Sandnes';

ALTER TABLE Persons

ALTER City DROP DEFAULT;

ALTER TABLE Persons

ALTER COLUMN City DROP DEFAULT;

CREATE INDEX statement

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

Indexes are used to retrieve data from the databases more quickly than
otherwise. The users cannot see the indexes, they are just used to speed up
search/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 index_name

ON table_name (column1, column2);

CREATE UNIQUE INDEX index_name

ON table_name (column1, column2);

ALTER TABLE table_name

DROP INDEX index_name;

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.

CREATE TABLE Persons (

Personid int NOT NULL AUTO_INCREMENT,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

PRIMARY KEY (Personid)

);

INSERT INTO Persons (FirstName,LastName)

VALUES ('Lars','Monsen');

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

ALTER TABLE Persons AUTO_INCREMENT=100;

DATES
MySQL
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
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

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

CREATE VIEW view_name AS

SELECT column1, column2

FROM table_name

WHERE condition;

CREATE VIEW [Brazil Customers] AS

SELECT CustomerName, ContactName

FROM Customers

WHERE Country = 'Brazil';

SELECT * FROM [Brazil Customers];

CREATE VIEW [Products Above Average Price] AS

SELECT ProductName, Price

FROM Products

WHERE Price > (SELECT AVG(Price) FROM Products);

SELECT * FROM [Products Above Average Price];

Injection

SELECT * FROM Users WHERE UserId = 105 OR 1=1;

Data Types
String Data Types
Numeric Data Types