0 - SQL How To



##############

What is MySQL:

##############

By: kossboss

 This is just a copy paste of some articles from here. All credit goes to them. I only claim that I formatted it like this an cancotanated it all like so

WORK CITED/BIBLIOGRAPHY: http://www.w3schools.com/sql/default.asp

### How data stored in relational db? ###

* Data is stored in tables

* Relational database has tables

* Data stored in tables has name, table has columns and rows

* Record equals row

* columns have a name

* structured like English

A query is a command window. Where you can type a command or two and execute them. You can put keywords like GO and -- to control the flow of the commands

There is also a GUI thing you can do for every Command line query, here how ever I will just cover the querys

To execute commands above use GO n. where is an integer like GO 10. Will repeat the commands above the GO 10 times

##############

Make DATABASE:

##############

CREATE DATABASE database_name

###########

Make Table:

###########

NOTE:

Make sure to specify the key, and NULL or NOT NULL, you will see what this means after reading this section

===Simplest Form:===

CREATE TABLE table_name

(

column_name1 data_type,

column_name2 data_type,

column_name3 data_type,

....

)

===Example:===

CREATE TABLE Persons

(

P_Id int,

LastName varchar(255),

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

CONSTRAINTS ON MAKING A TABLE

#############################

When making a table can have 6 different constraints (important ones are NULL and PRIMARY KEY, so ill start with those):

### NOT NULL ###

By default, a table column can hold NULL values... Thus if not specified like in above simple case its like saying NULL, thus meaning can hold empty/null values

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

===example:===

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

### UNIQUE ###

Meaning cant have more than one of the same value, like the ID of people. Cant have entry number 12 and entry number 12 again.

The UNIQUE constraint uniquely identifies each record in a database table.

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

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

===Example:===

Slightly different form in SQL Server/Oracle/MS Access vs MySQL:

===MySQL:===

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)

)

===SQL Server / Oracle / MS Access:===

CREATE TABLE Persons

(

P_Id int NOT NULL UNIQUE,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

===Multiple Example (SAME FOR ALL SQL programs):===

===MySQL / SQL Server / Oracle / MS Access:===

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

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)

)

### PRIMARY KEY ###

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key.

===Example:===

Its different with MySQL vs SQL Server/Oracle/MS access:

===MySQL:===

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)

)

===SQL Server / Oracle / MS Access:===

CREATE TABLE Persons

(

P_Id int NOT NULL PRIMARY KEY,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

===Multiple Example (SAME FOR ALL SQL programs):===

===MySQL / SQL Server / Oracle / MS Access:===

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

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)

)

### FOREIGN KEY ###

SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Let's illustrate the foreign key with an example. Look at the following two tables:

The "Persons" table:

P_Id, LastName, FirstName, Address, City

1, Hansen, Ola, Timoteivn 10, Sandnes

2, Svendson, Tove, Borgvn 23, Sandnes

3, Pettersen, Kari, Storgt 20, Stavanger

The "Orders" table:

O_Id, OrderNo, P_Id

1, 77895, 3

2, 44678, 3

3, 22456, 2

4, 24562, 1

Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.

The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "P_Id" 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 that invalid data form 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 Constraint on CREATE TABLE

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

===MySQL:===

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)

)

===SQL Server / Oracle / MS Access:===

CREATE TABLE Orders

(

O_Id int NOT NULL PRIMARY KEY,

OrderNo int NOT NULL,

P_Id int FOREIGN KEY REFERENCES Persons(P_Id)

)

===Multiple Example (SAME FOR ALL SQL programs):===

===MySQL / SQL Server / Oracle / MS Access:===

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

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)

)

### CHECK ###

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.

===MySQL:===

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)

)

===SQL Server / Oracle / MS Access:===

CREATE TABLE Persons

(

P_Id int NOT NULL CHECK (P_Id>0),

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

===Multiple Example (SAME FOR ALL SQL programs):===

===MySQL / SQL Server / Oracle / MS Access:===

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

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

)

### DEFAULT ###

The DEFAULT constraint is used to insert a default value into a column.

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

===NOTE SAME ON ALL SQL Programs:===

===My SQL / SQL Server / Oracle / MS Access:===

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255) DEFAULT 'Sandnes'

)

==WITH GETDATE:===

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

===Example:===

CREATE TABLE Orders

(

O_Id int NOT NULL,

OrderNo int NOT NULL,

P_Id int,

OrderDate date DEFAULT GETDATE()

)

More Contraints:

################

### Auto Increment ###

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

AUTO INCREMENT a Field

Very often we would like the value of the primary key field to be created automatically every time a new record is inserted.

We would like to create an auto-increment field in a table.

===Syntax for MySQL===

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

CREATE TABLE Persons

(

P_Id int NOT NULL AUTO_INCREMENT,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

PRIMARY KEY (P_Id)

)

===Syntax for SQL Server===

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

CREATE TABLE Persons

(

P_Id int PRIMARY KEY IDENTITY,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

===Syntax for Access===

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

CREATE TABLE Persons

(

P_Id PRIMARY KEY AUTOINCREMENT,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

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

##############################

Put/INSERT entries from table:

##############################

2 Forms/ways/methods to do this:

### Form 1: ###

INSERT INTO table_name

VALUES (value1, value2, value3,...)

---Example:---

INSERT INTO Persons

VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')

### Form 2: ###

INSERT INTO table_name (column1, column2, column3,...)

VALUES (value1, value2, value3,...)

---Example:---

INSERT INTO Persons (P_Id, LastName, FirstName)

VALUES (5, 'Tjessem', 'Jakob')

################

Change an entry:

################

UPDATE table_name

SET column1=value, column2=value2,...

WHERE some_column=some_value

---example:---

UPDATE Persons

SET Address='Nissestien 67', City='Sandnes'

WHERE LastName='Tjessem' AND FirstName='Jakob'

###################################

Deleting/DELETE entries from table:

###################################

DELETE FROM table_name

WHERE some_column=some_value

---example:---

DELETE from table

WHERE firstname='koss'

#######

SELECT:

#######

Used to display results

SELECT column FROM table

Can select 1 or more colums, if more seperate with comma

All *

SELECT column1,column2 FROM table

This tutorial has colums and tables in quotes like so, but Im not using them, because it tends to work without quotes as well... double quotes here by the way... when you set values to string use single quotes

SELECT "column1","column2" FROM "table"

To Select all

SELECT * FROM table

################

SELECT DISTINCT:

################

To select Unique values:

SELECT DISTINCT column FROM table

######

WHERE:

######

Can put WHERE to select subset, its like a filter

SELECT Column FROM table WHERE condition

NOTE with WHERES can have AND/OR

SELECT * FROM Persons

WHERE FirstName='Tove'

OR FirstName='Ola'

--

SELECT * FROM Persons

WHERE FirstName='Tove'

AND LastName='Svendson'

######

ORDER:

######

If want to order the above

add ORDER BY column [ASC|DESC] to the end

SELECT Column FROM table WHERE condition ORDER BY column [ASC|DESC]

OR verticly with line feeds

SELECT "Column_Name"

FROM "Table_Name"

WHERE "Condition"

ORDER BY "Column_Name" [ASC|DESC]

ASC=Ascending... grows, starts from low goes up-- 1 to 1000, a to z

DESC=Descending... shrinks, starts from highest and goes down-- 1000 to 1, z to a

#########

GROUP BY:

#########

SELECT column1, FUNCTION(column2) FROM table GROUP BY column1

or represented like this

SELECT column1,

  FUNCTION(column2)

FROM table

GROUP BY column1

First talk about functions --- SUM, AVG, MIN, MAX, COUNT operates on column

---Example:---

Date, Store, Sales_Amount

Highest Sales entry

SELECT MAX(Sales_amount)

Highest Sales per store

SELECT store, MAX(Sales_amount)

its not enough

need Group By

We want to tell SQL to group same names, thus telling us the info for each store

SELECT c1, FUNCTION(c2) FROM t1 GROUP BY c1

#######

HAVING:

#######

Goes together with GROUP BY quiet well

Also can filter based on results of a function, not just the entry. so instead of WHERE where it looks at the entry we will use HAVING(CONDITION based on FUNCTION) which filters based on result of function

SELECT c1, FUNCTION(c2) FROM t1 GROUP BY c1 HAVING(CONDITION based on FUNCTION)

----

SELECT c1,

 FUNCTION(c2)

FROM t1

GROUP BY c1

HAVING(CONDITION based on FUNCTION)

HAVING needs to go after the GROUP BY or else we would use WHERE

Or in other words:

Any filtering result based on a group by needs to be calculated after the grouping happens so thats why

HAVING goes after GROUP BY

HAVING SUM(sales)>1500

### Example: ###

---Table Store_Information:---

store_name,     Sales,     Date

Los Angeles,    $1500,     Jan-05-1999

San Diego, $250, Jan-07-1999

Los Angeles,    $300, Jan-08-1999

Boston,    $700, Jan-08-1999

---HAVING example:---

SELECT store_name, SUM(sales)

FROM Store_Information

GROUP BY store_name

HAVING SUM(sales) > 1500

---Result:----

store_name,          SUM(Sales)

Los Angeles,         $1800

Read more: http://www.1keydata.com/sql/sqlhaving.html#ixzz2D6YoU3re

''It would group all the stores and add them up and then give the MAX

##############################

Example with WHERE and HAVING:

##############################

We have the following "Orders" table:

O_Id, OrderDate, OrderPrice, Customer

1, 2008/11/12, 1000, Hansen

2, 2008/10/23, 1600, Nilsen

3, 2008/09/02, 700, Hansen

4, 2008/09/03, 300, Hansen

5, 2008/08/30, 2000, Jensen

6, 2008/10/04, 100, Nilsen

Now we want to find if any of the customers have a total order of less than 2000.

We use the following SQL statement:

SELECT Customer,SUM(OrderPrice) FROM Orders

GROUP BY Customer

HAVING SUM(OrderPrice)<2000

The result-set will look like this:

Customer, SUM(OrderPrice)

Nilsen, 1700

Now we want to find if the customers "Hansen" or "Jensen" have a total order of more than 1500.

We add an ordinary WHERE clause to the SQL statement:

SELECT Customer,SUM(OrderPrice) FROM Orders

WHERE Customer='Hansen' OR Customer='Jensen'

GROUP BY Customer

HAVING SUM(OrderPrice)>1500

The result-set will look like this:

Customer, SUM(OrderPrice)

Hansen, 2000

Jensen, 2000

######################

Order of the Commands:

######################

SELECT ... FROM

WHERE

GROUP BY

HAVING

ORDER BY

#######################

ALTER: Changing Tables:

#######################

### The ALTER TABLE Statement ###

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

===SQL ALTER TABLE Syntax===

To add a column in a table, use the following syntax:

ALTER TABLE table_name

ADD column_name datatype

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

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

ALTER TABLE table_name

MODIFY column_name datatype

### SQL ALTER TABLE Example ###

Look at the "Persons" table:

P_Id, LastName, FirstName, Address, City

1, Hansen, Ola, Timoteivn 10, Sandnes

2, Svendson, Tove, Borgvn 23, Sandnes

3, Pettersen, Kari, Storgt 20, Stavanger

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.

The "Persons" table will now like this:

P_Id, LastName, FirstName, Address, City, DateOfBirth

1, Hansen, Ola, Timoteivn 10, Sandnes

2, Svendson     Tove, Borgvn 23, Sandnes  

3, Pettersen, Kari, Storgt 20, Stavanger  

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

The "Persons" table will now like this:

P_Id, LastName, FirstName, Address, City

1, Hansen, Ola, Timoteivn 10, Sandnes

2, Svendson, Tove, Borgvn 23, Sandnes

3, Pettersen, Kari, Storgt 20, Stavanger

CONSTRAINTS and ALTER

#####################

### ALTER with NOT NULL ###

N/A

### ALTER with UNIQUE ###

===MySQL / SQL Server / Oracle / MS Access:===

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

ALTER TABLE Persons

ADD UNIQUE (P_Id)

===MySQL / SQL Server / Oracle / MS Access:===

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

ALTER TABLE Persons

ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

### ALTER with PRIMARY KEY ###

===MySQL / SQL Server / Oracle / MS Access:===

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

ALTER TABLE Persons

ADD PRIMARY KEY (P_Id)

===MySQL / SQL Server / Oracle / MS Access:===

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

ALTER TABLE Persons

ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_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).

### ALTER with FOREIGN KEY ###

===MySQL / SQL Server / Oracle / MS Access:===

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

ALTER TABLE Orders

ADD FOREIGN KEY (P_Id)

REFERENCES Persons(P_Id)

===MySQL / SQL Server / Oracle / MS Access:===

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

ALTER TABLE Orders

ADD CONSTRAINT fk_PerOrders

FOREIGN KEY (P_Id)

REFERENCES Persons(P_Id)

### ALTER with CHECK ###

===MySQL / SQL Server / Oracle / MS Access:===

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

ALTER TABLE Persons

ADD CHECK (P_Id>0)

===MySQL / SQL Server / Oracle / MS Access:===

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

ALTER TABLE Persons

ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

### ALTER with DEFAULT ###

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 / MS Access:===

ALTER TABLE Persons

ALTER COLUMN City SET DEFAULT 'SANDNES'

===Oracle:===

ALTER TABLE Persons

MODIFY City DEFAULT 'SANDNES'

##################################

DROP: Removing things from tables:

##################################

Indexes, tables, and databases can easily be deleted/removed with the DROP statement.

===The DROP INDEX Statement===

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

---DROP INDEX Syntax for MS Access:---

DROP INDEX index_name ON table_name

---DROP INDEX Syntax for MS SQL Server:---

DROP INDEX table_name.index_name

---DROP INDEX Syntax for DB2/Oracle:---

DROP INDEX index_name

----DROP INDEX Syntax for MySQL:---

ALTER TABLE table_name DROP INDEX index_name

===The DROP TABLE Statement===

The DROP TABLE statement is used to delete a table.

DROP TABLE table_name

===The DROP DATABASE Statement===

The DROP DATABASE statement is used to delete a database.

DROP DATABASE database_name

===The TRUNCATE TABLE Statement===

What if we only want to delete the data inside the table, and not the table itself?

Then, use the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name

#### DROP with NOT NULL ###

NA

#### DROP with UNIQUE ###

To drop a UNIQUE constraint, use the following SQL:

===MySQL:===

ALTER TABLE Persons

DROP INDEX uc_PersonID

==SQL Server / Oracle / MS Access:===

ALTER TABLE Persons

DROP CONSTRAINT uc_PersonID

#### DROP with PRIMARY KEY ###

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_PersonID

#### DROP with FOREIGN KEY ###

To drop a FOREIGN KEY constraint, use the following SQL:

===MySQL:===

ALTER TABLE Orders

DROP FOREIGN KEY fk_PerOrders

===SQL Server / Oracle / MS Access:===

ALTER TABLE Orders

DROP CONSTRAINT fk_PerOrders

#### DROP with CHECK ###

< NOTE WHEN I MADE THIS THE MySQL was at the bottom and the SQL SERVER/ORACLE/MS ACCESS were at the top, I moved the sections around to be consistent with the other DROP CONSTRAINTS >

To drop a CHECK constraint, use the following SQL:

===MySQL:===

ALTER TABLE Persons

DROP CHECK chk_Person

===SQL Server / Oracle / MS Access:===

ALTER TABLE Persons

DROP CONSTRAINT chk_Person

#### DROP with DEFAULT ###

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

#############################

INDEXES: Speeding up Queries:

#############################

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

Indexes allow the database application to find data fast; without reading the whole table.

### Indexes ###

An index can be created in a table to find data more quickly and efficiently.

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 you should only create indexes on columns (and tables) that will be frequently searched against.

===SQL CREATE INDEX Syntax:===

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

CREATE INDEX index_name

ON table_name (column_name)

===SQL CREATE UNIQUE INDEX Syntax:===

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name

ON table_name (column_name)

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

### CREATE INDEX Example ###

The SQL statement below creates an index named "PIndex" on the "LastName" column in the "Persons" table:

CREATE INDEX PIndex

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 PIndex

ON Persons (LastName, FirstName)

##########

FUNCTIONS:

##########

SQL has many built-in functions for performing calculations on data.

### SQL Aggregate Functions ###

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

AVG() - Returns the average value

COUNT() - Returns the number of rows

FIRST() - Returns the first value

LAST() - Returns the last value

MAX() - Returns the largest value

MIN() - Returns the smallest value

SUM() - Returns the sum

###SQL Scalar functions ###

They change the way it looks in a way

SQL scalar functions return a single value, based on the input value.

Useful scalar functions:

UCASE() - Converts a field to upper case

LCASE() - Converts a field to lower case

MID() - Extract characters from a text field

LEN() - Returns the length of a text field

ROUND() - Rounds a numeric field to the number of decimals specified

NOW() - Returns the current system date and time

FORMAT() - Formats how a field is to be displayed

Tip: The aggregate functions and the scalar functions will be explained in details in the next chapters.

EXAMPLES OF SOME OF THE FUNCTIONS

#################################

### The AVG() Function ###

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

SQL AVG() Syntax

SELECT AVG(column_name) FROM table_name

===SQL AVG() Example===

We have the following "Orders" table:

O_Id OrderDate  OrderPrice Customer

1    2008/11/12 1000 Hansen

2    2008/10/23 1600 Nilsen

3    2008/09/02 700  Hansen

4    2008/09/03 300  Hansen

5    2008/08/30 2000 Jensen

6    2008/10/04 100  Nilsen

Now we want to find the average value of the "OrderPrice" fields.

We use the following SQL statement:

SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

The result-set will look like this:

OrderAverage

950

Now we want to find the customers that have an OrderPrice value higher than the average OrderPrice value.

We use the following SQL statement:

SELECT Customer FROM Orders

WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

The result-set will look like this:

Customer

Hansen

Nilsen

Jensen

### SQL COUNT() Function ###

===SQL COUNT(column_name) Syntax===

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(column_name) FROM table_name

===SQL COUNT(*) Syntax===

The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name

SQL COUNT(DISTINCT column_name) Syntax

The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name

Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.

SQL COUNT(column_name) Example

We have the following "Orders" table:

O_Id OrderDate  OrderPrice Customer

1    2008/11/12 1000 Hansen

2    2008/10/23 1600 Nilsen

3    2008/09/02 700  Hansen

4    2008/09/03 300  Hansen

5    2008/08/30 2000 Jensen

6    2008/10/04 100  Nilsen

Now we want to count the number of orders from "Customer Nilsen".

We use the following SQL statement:

SELECT COUNT(Customer) AS CustomerNilsen FROM Orders

WHERE Customer='Nilsen'

The result of the SQL statement above will be 2, because the customer Nilsen has made 2 orders in total:

CustomerNilsen

2

===SQL COUNT(*) Example===

If we omit the WHERE clause, like this:

SELECT COUNT(*) AS NumberOfOrders FROM Orders

 The result-set will look like this:

NumberOfOrders

6

which is the total number of rows in the table.

SQL COUNT(DISTINCT column_name) Example

Now we want to count the number of unique customers in the "Orders" table.

We use the following SQL statement:

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders

The result-set will look like this:

NumberOfCustomers

3

which is the number of unique customers (Hansen, Nilsen, and Jensen) in the "Orders" table.

## The UCASE() Function ###

The UCASE() function converts the value of a field to uppercase.

SQL UCASE() Syntax

SELECT UCASE(column_name) FROM table_name

===Syntax for SQL Server===

SELECT UPPER(column_name) FROM table_name

===SQL UCASE() Example===

We have the following "Persons" table:

P_Id LastName   FirstName  Address    City

1    Hansen     Ola  Timoteivn 10    Sandnes

2    Svendson   Tove Borgvn 23  Sandnes

3    Pettersen  Kari Storgt 20  Stavanger

Now we want to select the content of the "LastName" and "FirstName" columns above, and convert the "LastName" column to uppercase.

We use the following SELECT statement:

SELECT UCASE(LastName) as LastName,FirstName FROM Persons

The result-set will look like this:

LastName   FirstName

HANSEN     Ola

SVENDSON   Tove

PETTERSEN  Kari

### The ROUND() Function ###

The ROUND() function is used to round a numeric field to the number of decimals specified.

===SQL ROUND() Syntax===

SELECT ROUND(column_name,decimals) FROM table_name

Parameter  Description

column_name     Required. The field to round.

decimals   Required. Specifies the number of decimals to be returned.

SQL ROUND() Example

We have the following "Products" table:

Prod_Id    ProductName     Unit UnitPrice

1    Jarlsberg  1000 g     10.45

2    Mascarpone 1000 g     32.56

3    Gorgonzola 1000 g     15.67

Now we want to display the product name and the price rounded to the nearest integer.

We use the following SELECT statement:

SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products

The result-set will look like this:

ProductName     UnitPrice

Jarlsberg  10

Mascarpone 33

Gorgonzola 16

### The NOW() Function ###

The NOW() function returns the current system date and time.

===SQL NOW() Syntax===

SELECT NOW() FROM table_name

===SQL NOW() Example===

We have the following "Products" table:

Prod_Id    ProductName     Unit UnitPrice

1    Jarlsberg  1000 g     10.45

2    Mascarpone 1000 g     32.56

3    Gorgonzola 1000 g     15.67

Now we want to display the products and prices per today's date.

We use the following SELECT statement:

SELECT ProductName, UnitPrice, Now() as PerDate FROM Products

The result-set will look like this:

ProductName     UnitPrice  PerDate

Jarlsberg  10.45 10/7/2008 11:25:02 AM

Mascarpone 32.56 10/7/2008 11:25:02 AM

Gorgonzola 15.67 10/7/2008 11:25:02 AM

### The FORMAT() Function ###

The FORMAT() function is used to format how a field is to be displayed.

===SQL FORMAT() Syntax===

SELECT FORMAT(column_name,format) FROM table_name

Parameter  Description

column_name     Required. The field to be formatted.

format     Required. Specifies the format.

===SQL FORMAT() Example===

We have the following "Products" table:

Prod_Id    ProductName     Unit UnitPrice

1    Jarlsberg  1000 g     10.45

2    Mascarpone 1000 g     32.56

3    Gorgonzola 1000 g     15.67

Now we want to display the products and prices per today's date (with today's date displayed in the following format "YYYY-MM-DD").

We use the following SELECT statement:

SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate

FROM Products

The result-set will look like this:

ProductName     UnitPrice  PerDate

Jarlsberg  10.45 2008-10-07

Mascarpone 32.56 2008-10-07

Gorgonzola 15.67 2008-10-07

###################################

SQL Quick Reference From W3Schools:

###################################

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

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

Comments