Select

Purpose:

Retrieve the data of specified columns from the specified table.

Syntax:

SELECT column_name(s) FROM table_name

Explanation:

Select specified columns from the database, and allow selecting one or more specified columns or rows from one or more specified tables. The full syntax of the SELECT statement is quite complex, but the main clauses can be summarized as:

SELECT select_list 
[ INTO new_table ] 
FROM table_source 
[ WHERE search_condition ] 
[ GROUP BY group_by_expression ] 
[ HAVING search_condition ] 
[ ORDER BY order_expression [ ASC | DESC ] ] 

Example:

The data in the "Persons" table is:

Select the data of the fields "LastName" and "FirstName":

SELECT LastName,FirstName FROM Persons

Result:

Select the data of all fields:

SELECT * FROM Persons

Result:

Where

Purpose:

Used to specify the criteria for a selection query.

Syntax:

SELECT column FROM table WHERE column condition value

The following operators can be used in WHERE:

=,<>,>,<,>=,<=,BETWEEN,LIKE

Note: In some versions of SQL, the not-equal operator < > can be written as != .

Explanation:

The SELECT statement returns the data for which the condition in the WHERE clause is true.

Example:

Select the people living in "Sandnes" from the "Persons" table:

SELECT * FROM Persons WHERE City='Sandnes'

The data in the "Persons" table is:

Result:

And & Or

Purpose:

In the WHERE clause, AND and OR are used to connect two or more conditions.

Explanation:

AND, when combining two Boolean expressions, returns TRUE only when both expressions are TRUE.

OR, when combining two Boolean expressions, returns TRUE as long as one of the conditions is TRUE.

Example:

The original data in the "Persons" table:

Use the AND operator to find data in the "Persons" table where FirstName is "Tove" and LastName is "Svendson":

SELECT * FROM Persons
WHERE FirstName='Tove'
AND LastName='Svendson'

Result:

Use the OR operator to find data in the "Persons" table where FirstName is "Tove" or LastName is "Svendson":

SELECT * FROM Persons
WHERE firstname='Tove'
OR lastname='Svendson'

Result:

You can also combine AND and OR (using parentheses to form complex expressions), for example:

SELECT * FROM Persons WHERE
(FirstName='Tove' OR FirstName='Stephen')
AND LastName='Svendson'

Result:

Between…And

Purpose:

Specify the range of data to be returned.

Syntax:

SELECT column_name FROM table_name
WHERE column_name
BETWEEN value1 AND value2

Example:

The original data in the "Persons" table:

Use BETWEEN...AND to return data where LastName is from "Hansen" to "Pettersen":

SELECT * FROM Persons WHERE LastName 
BETWEEN 'Hansen' AND 'Pettersen'

Result:

To display data outside the specified range, you can also use the NOT operator:

SELECT * FROM Persons WHERE LastName 
NOT BETWEEN 'Hansen' AND 'Pettersen'

Result:

Distinct

Purpose:

The DISTINCT keyword is used to return unique values.

Syntax:

SELECT DISTINCT column-name(s) FROM table-name

Explanation:

When there are duplicate values in column-name(s), only one is kept in the returned result.

Example:

The original data in the "Orders" table:

Use the DISTINCT keyword to return unique values in the Company field:

SELECT DISTINCT Company FROM Orders

Result:

Order by

Purpose:

Specify the sorting of the result set.

Syntax:

SELECT column-name(s) FROM table-name ORDER BY  { order_by_expression [ ASC | DESC ] } 

Explanation:

Specify the sorting of the result set. It can be sorted using ASC (ascending order, from the lowest value to the highest value) or DESC (descending order, from the highest value to the lowest value); the default is ASC.

Example:

The original data in the "Orders" table:

Return the result set in ascending order of the Company field:

SELECT Company, OrderNumber FROM Orders
ORDER BY Company

Result:

Return the result set in descending order of the Company field:

SELECT Company, OrderNumber FROM Orders
ORDER BY Company DESC

Result:

Group by

Purpose:

Group the result set, often used with aggregate functions.

Syntax:

SELECT column,SUM(column) FROM table GROUP BY column

Example:

The original data in the "Sales" table:

Group by the Company field, calculate the total of Amout for each Company:

SELECT Company,SUM(Amount) FROM Sales
GROUP BY Company

Result:

Having

Purpose:

Specify search conditions for a group or an aggregate.

Syntax:

SELECT column,SUM(column) FROM table
GROUP BY column
HAVING SUM(column) condition value

Explanation:

HAVING is usually used together with the GROUP BY clause. When GROUP BY is not used, HAVING is similar in function to the WHERE clause.

Example:

The original data in the "Sales" table:

Group by the Company field, and find data where the total Amout for each Company is above 10000:

SELECT Company,SUM(Amount) FROM Sales
GROUP BY Company HAVING SUM(Amount)>10000

Result:

Join

Purpose:

When you want to select a result set from two or more tables, you will use JOIN.

Example:

The data in the "Employees" table is as follows (where ID is the primary key):

The data in the "Orders" table is as follows:

Correlate Employees' ID with Orders' ID to select data:

SELECT Employees.Name, Orders.Product
FROM Employees, Orders
WHERE Employees.ID = Orders.ID

Result:

Or you can also use the JOIN keyword to accomplish the above operation:

SELECT Employees.Name, Orders.Product
FROM Employees
INNER JOIN Orders
ON Employees.ID = Orders.ID

INNER JOINSyntax:

SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

Explanation:

The result set returned by INNER JOIN is all matching data from the two tables.

LEFT JOINSyntax:

SELECT field1, field2, field3
FROM first_table
LEFT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

Use the "Employees" table to left outer join the "Orders" table to find related data:

SELECT Employees.Name, Orders.Product
FROM Employees
LEFT JOIN Orders
ON Employees.ID = Orders.ID

Result:

Explanation:

LEFT JOIN returns all rows from "first_table", even if there is no matching data in "second_table".

RIGHT JOINSyntax:

SELECT field1, field2, field3
FROM first_table
RIGHT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

Use the "Employees" table to right outer join the "Orders" table to find related data:

SELECT Employees.Name, Orders.Product
FROM Employees
RIGHT JOIN Orders
ON Employees.ID = Orders.ID

Result:

Explanation:

RIGHT JOIN returns all rows from "second_table", even if there is no matching data in "first_table".

Alias

Purpose:

Can be used on tables, result sets, or columns to give them a logical name.

Syntax:

Give a column an alias:

SELECT column AS column_alias FROM table

Give a table an alias:

SELECT column FROM table AS table_alias

Example:

The original data in the "Persons" table:

Run the following SQL:

SELECT LastName AS Family, FirstName AS Name
FROM Persons

Result:

Run the following SQL:

SELECT LastName, FirstName
FROM Persons AS Employees

Result:

The data in Employees is:

Insert Into

Purpose:

Insert a new row into a table.

Syntax:

Insert a row of data:

INSERT INTO table_name
VALUES (value1, value2,....)

Insert a row of data into specified fields:

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

Example:

The original data in the "Persons" table:

Run the following SQL to insert a row of data:

INSERT INTO Persons 
VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes')

After inserting, the data in the "Persons" table is:

Run the following SQL to insert a row of data into specified fields:

INSERT INTO Persons (LastName, Address)
VALUES ('Rasmussen', 'Storgt 67')

After inserting, the data in the "Persons" table is:

Update

Purpose:

Update existing data in a table.

Syntax:

UPDATE table_name SET column_name = new_value
WHERE column_name = some_value

Example:

The original data in the "Person" table:

Run the following SQL to update FirstName to "Nina" in the Person table where the LastName field is "Rasmussen":

UPDATE Person SET FirstName = 'Nina'
WHERE LastName = 'Rasmussen'

After updating, the data in the "Person" table is:

Similarly, you can also update multiple fields at the same time with the UPDATE statement:

UPDATE Person
SET Address = 'Stien 12', City = 'Stavanger'
WHERE LastName = 'Rasmussen'

After updating, the data in the "Person" table is:

Delete

Purpose:

Delete data from a table.

Syntax:

DELETE FROM table_name WHERE column_name = some_value

Example:

Original data in the "Person" table:

Delete the data in the Person table where LastName is "Rasmussen":

DELETE FROM Person WHERE LastName = 'Rasmussen'

The data in the "Person" table after executing the DELETE statement:

Create Table

Purpose:

Create a new table.

Syntax:

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

Example:

Create a table called "Person" with 4 fields: "LastName", "FirstName", "Address", "Age":

CREATE TABLE Person 
(
LastName varchar,
FirstName varchar,
Address varchar,
Age int
)

If you want to specify the maximum storage length for a field, you can do it like this:

CREATE TABLE Person 
(
LastName varchar(30),
FirstName varchar(30),
Address varchar(120),
Age int(3) 
)

The following table lists some data types in SQL:

Alter Table

Purpose:

Add or remove fields in an existing table.

Syntax:

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

Note: Some database management systems do not allow removing fields from a table.

Example:

Original data in the "Person" table:

Add a field named City to the Person table:

ALTER TABLE Person ADD City varchar(30)

The data in the table after adding is as follows:

Remove the existing Address field from the Person table:

ALTER TABLE Person DROP COLUMN Address

The data in the table after removing is as follows:

Drop Table

Purpose:

Removes a table definition and all data, indexes, triggers, constraints, and permission specifications in that table from the database.

Syntax:

DROP TABLE table_name

Create Database

Purpose:

Create a new database.

Syntax:

CREATE DATABASE database_name

Drop Database

Purpose:

Remove an existing database.

Syntax:

DROP DATABASE database_name

Aggregate Functions

count

Purpose:

Returns the number of rows in the selected result set.

Syntax:

SELECT COUNT(column_name) FROM table_name

Example:

The original data in the "Persons" table is as follows:

Select the total number of records:

SELECT COUNT(Name) FROM Persons

Execution result:

3

sum

Purpose:

Returns the sum of all values, or only DISTINCT values, based on an expression. SUM can only be used with numeric rows. Null values are ignored.

Syntax:

SELECT SUM(column_name) FROM table_name

Example:

The original data in the "Persons" table is as follows:

Select the total age of all people in the "Persons" table:

SELECT SUM(Age) FROM Persons

Execution result:

98

Select the total age of people over 20 years old in the "Persons" table:

SELECT SUM(Age) FROM Persons WHERE Age>20

Execution result:

79

avg

Purpose:

Returns the average value of the selected result set. Null values are ignored.

Syntax:

SELECT AVG(column_name) FROM table_name

Example:

The original data in the "Persons" table is as follows:

Select the average age of all people in the "Persons" table:

SELECT AVG(Age) FROM Persons

Execution result:

32.67

Select the average age of people over 20 years old in the "Persons" table:

SELECT AVG(Age) FROM Persons WHERE Age>20

Execution result:

39.5

max

Purpose:

Returns the maximum value in the selected result set. Null values are ignored.

Syntax:

SELECT MAX(column_name) FROM table_name

Example:

The original data in the "Persons" table is as follows:

Select the maximum age from the "Persons" table:

SELECT MAX(Age) FROM Persons

Execution result:

45

min

Purpose:

Returns the minimum value in the selected result set. Null values are ignored.

Syntax:

SELECT MIN(column_name) FROM table_name

Example:

The original data in the "Persons" table is as follows:

Select the minimum age from the "Persons" table:

SELECT MIN(Age) FROM Persons

Execution result:

19

Arithmetic Functions

abs

Purpose:

Returns the absolute value of the specified numeric expression.

Syntax:

ABS(numeric_expression )

Example:

ABS(-1.0) ABS(0.0) ABS(1.0)

Execution result:

1.0         0.0        1.0

ceil

Purpose:

Returns the smallest integer greater than or equal to the given numeric expression.

Syntax:

CEIL(numeric_expression )

Example:

CEIL(123.45)   CEIL(-123.45)

Execution result:

124.00            -123.00

floor

Purpose:

Returns the largest integer less than or equal to the given numeric expression.

Syntax:

FLOOR(numeric_expression )

Example:

FLOOR(123.45)   FLOOR(-123.45)

Execution result:

123.00             -124.00

cos

Purpose:

A mathematical function that returns the trigonometric cosine of the specified angle (in radians) in the specified expression.

Syntax:

COS(numeric_expression )

Example:

COS(14.78)

Execution result:

-0.599465

cosh

Purpose:

Returns the angle in radians whose cosine is the specified float expression, also known as arccosine.

Syntax:

COSH(numeric_expression )

Example:

COSH(-1)

Execution result:

3.14159

sin

Purpose:

Returns the trigonometric sine of the given angle (in radians) as an approximate numeric (float) expression.

Syntax:

SIN(numeric_expression )

Example:

SIN(45.175643)

Execution result:

0.929607

sinh

Purpose:

Returns the angle in radians whose sine is the specified float expression, also known as arcsine.

Syntax:

SINH(numeric_expression )

Example:

SINH(-1.00)

Execution result:

-1.5708

tan

Purpose:

Returns the tangent of the input expression.

Syntax:

TAN(numeric_expression )

Example:

TAN(3.14159265358979/2)

Execution result:

1.6331778728383844E+16

tanh

Purpose:

Returns the angle in radians whose tangent is the specified float expression, also known as arctangent.

Syntax:

TANH(numeric_expression )

Example:

TANH(-45.01)

Execution result:

-1.54858

exp

Purpose:

Returns the exponential value of the given float expression.

Syntax:

EXP(numeric_expression )

Example:

EXP(378.615345498)

Execution result:

2.69498e+164 

log

Purpose:

Returns the natural logarithm of the given float expression.

Syntax:

LOG(numeric_expression )

Example:

LOG(5.175643)

Execution result:

1.64396 

power

Purpose:

Returns the value of the given expression raised to the specified power.

Syntax:

POWER(numeric_expression,v )

Example:

POWER(2,6)

Execution result:

64

sign

Purpose:

Returns the sign of the given expression: positive (+1), zero (0), or negative (-1).

Syntax:

SIGN(numeric_expression )

Example:

SIGN(123)    SIGN(0)    SIGN(-456)

Execution result:

1             0          -1

sqrt

Purpose:

Returns the square of the given expression.

Syntax:

SQRT(numeric_expression )

Example:

SQRT(10)

Execution result:

100

More Tutorials

For more detailed tutorials, refer to:SQL Tutorial