Arbaminch Polytechnic College

Model TTLM for Using Advanced Structured Query Language

Based on
Ethiopian Occupational Standard (EOS)

Using Advanced Structured Query Language

TVET PROGRAM TITLE: Database Administration Level IV

MODULE TITLE: Using Advanced Structured Query Language

MODULE CODE:ICT DBA4 05 0411

NOMINAL DURATION:130 hrs

MODULE DESCRIPTION: This unit covers the skills, knowledge and attitudes required to
use advanced structured query language (SQL) to define, create and manipulate
database structures and associated data in a relational database.

LEARNING OUTCOMES:

At the end of the module the learner will be able to:

1. Write ADVANCED SQL statement to retrieve and sort data

2. Write ADVANCED SQL statements that use functions

Write ADVANCED SQL statements that use aggregation and filtering

Using Advanced Structured Query Language

MODULE CONTENTS:
.Review of basic SQL commands
o SQL Advanced
o SQL Top
o SQL Like
o SQL Wildcards
o SQL In
o SQL Between
o SQL Alias
o SQL Joins
o SQL Inner Join
o Full SQL Left Join
o SQL Right Join
o SQL Union
o SQL Select Into
 SQL Functions
o Overview of SQL Functions
o Sorting commands
 ORDER BY
 GROUP BY….
o Special SQL functions
 Mathematical functions
 String functions
 Date functions
 Aggregate functions

Using Advanced Structured Query Language

 SQL Advanced

SQL TOP Clause

The TOP Clause

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

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

Note: Not all database systems support the TOP clause.

SQL Server Syntax

SELECT TOP number|percent column_name(s)

FROM table_name

SQL TOP Example

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

4 Nilsen Tom Vingvn 23 Stavanger

Now we want to select only the two first records in the table above.

We use the following SELECT statement:

SELECT TOP 2 * FROM Persons

The result-set will look like this:

P_Id LastName FirstName Address City

Using Advanced Structured Query Language

 1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

SQL TOP PERCENT Example

The "Persons" table:

P_Id LastName FirstName Address City

Timoteivn
1 Hansen Ola Sandnes
10

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

4 Nilsen Tom Vingvn 23 Stavanger

Now we want to select only 50% of the records in the table above.

We use the following SELECT statement:

SELECT TOP 50 PERCENT * FROM Persons

The result-set will look like this:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

SQL Like

SQL LIKE Operator

Using Advanced Structured Query Language

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

The LIKE Operator

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

SQL LIKE Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name LIKE pattern

LIKE Operator Example

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 select the persons living in a city that starts with "s" from the table
above.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE 's%'

The "%" sign can be used to define wildcards (missing letters in the pattern) both before
and after the pattern.

The result-set will look like this:

P_IdLastName FirstName Address City

Using Advanced Structured Query Language

 1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

Next, we want to select the persons living in a city that ends with an "s" from the
"Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE '%s'

The result-set will look like this:

P_Id LastName FirstName Address City

Timoteivn
1 Hansen Ola Sandnes
10

2 Svendson Tove Borgvn 23 Sandnes

Next, we want to select the persons living in a city that contains the pattern "tav" from the
"Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE '%tav%'

The result-set will look like this:

P_Id LastName FirstName Address City

3 Pettersen Kari Storgt 20 Stavanger

It is also possible to select the persons living in a city that NOT contains the pattern "tav"
from the "Persons" table, by using the NOT keyword.

Using Advanced Structured Query Language

 We use the following SELECT statement:

SELECT * FROM Persons

WHERE City NOT LIKE '%tav%'

The result-set will look like this:

P_Id LastName FirstName Address City

Timoteivn
1 Hansen Ola Sandnes
10

2 Svendson Tove Borgvn 23 Sandnes

SQL Wildcards

SQL wildcards can be used when searching for data in a database.

SQL Wildcards 

SQL wildcards can substitute for one or more characters when searching for data in a
database.

SQL wildcards must be used with the SQL LIKE operator.

With SQL, the following wildcards can be used:

Description
Wildcard

% A substitute for zero or more characters

_ A substitute for exactly one character

[charlist] Any single character in charlist

[^charlist] Any single character not in charlist

or

Using Advanced Structured Query Language

 [!charlist]

SQL Wildcard Examples

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

Using the % Wildcard

Now we want to select the persons living in a city that starts with "sa" from the "Persons"
table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE 'sa%'

The result-set will look like this:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

Next, we want to select the persons living in a city that contains the pattern "nes" from
the "Persons" table.

We use the following SELECT statement:

Using Advanced Structured Query Language
 SELECT * FROM Persons

WHERE City LIKE '%nes%'

The result-set will look like this:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

Using the _ Wildcard

Now we want to select the persons with a first name that starts with any character,
followed by "la" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE FirstName LIKE '_la'

The result-set will look like this:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

Next, we want to select the persons with a last name that starts with "S", followed by any
character, followed by "end", followed by any character, followed by "on" from the
"Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName LIKE 'S_end_on'

The result-set will look like this:

Using Advanced Structured Query Language

 P_Id LastName FirstName Address City

2 Svendson Tove Borgvn 23 Sandnes

Using the [charlist] Wildcard

Now we want to select the persons with a last name that starts with "b" or "s" or "p" from
the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName LIKE '[bsp]%'

The result-set will look like this:

P_Id LastName FirstName Address City

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

Next, we want to select the persons with a last name that do not start with "b" or "s" or
"p" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName LIKE '[!bsp]%'

The result-set will look like this:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

Using Advanced Structured Query Language

SQL IN Operator

The IN Operator

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

SQL IN Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name IN (value1,value2,...)

IN Operator Example

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 select the persons with a last name equal to "Hansen" or "Pettersen"
from the table above.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName IN ('Hansen','Pettersen')

The result-set will look like this:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

Using Advanced Structured Query Language
 3 Pettersen Kari Storgt 20 Stavanger

4.5. SQL BETWEEN Operators

The BETWEEN operator is used in a WHERE clause to select a range of data between
two values.

The BETWEEN Operator

The BETWEEN operator selects a range of data between two values. The values can be
numbers, text, or dates.

SQL BETWEEN Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name

BETWEEN value1 AND value2

BETWEEN Operator Example

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 select the persons with a last name alphabetically between "Hansen" and
"Pettersen" from the table above.

We use the following SELECT statement:

Using Advanced Structured Query Language

 SELECT * FROM Persons

WHERE LastName

BETWEEN 'Hansen' AND 'Pettersen'

The result-set will look like this:

P_Id LastName FirstName Address City

Timoteivn
1 Hansen Ola Sandnes
10

Note: The BETWEEN operator is treated differently in different databases.

In some databases, persons with the LastName of "Hansen" or "Pettersen" will not be
listed,

Example 2

To display the persons outside the range in the previous example, use NOT BETWEEN:

SELECT * FROM Persons

WHERE LastName

NOT BETWEEN 'Hansen' AND 'Pettersen'

The result-set will look like this:

P_Id LastName FirstName Address City

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

SQL Alias

With SQL, an alias name can be given to a table or to a column.

SQL Alias
Using Advanced Structured Query Language
You can give a table or a column another name by using an alias. This can be a good
thing to do if you have very long or complex table names or column names.

An alias name could be anything, but usually it is short.

SQL Alias Syntax for Tables

SELECT column_name(s)

FROM table_name

AS alias_name

SQL Alias Syntax for Columns

SELECT column_name AS alias_name

FROM table_name

Alias Example

Assume we have a table called "Persons" and another table called "Product_Orders". We
will give the table aliases of "p" an "po" respectively.

Now we want to list all the orders that "Ola Hansen" is responsible for.

We use the following SELECT statement:

SELECT po.OrderID, p.LastName, p.FirstName

FROM Persons AS p,

Product_Orders AS po

WHERE p.LastName='Hansen'

WHERE p.FirstName='Ola'

The same SELECT statement without aliases:

SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName

FROM Persons,
Using Advanced Structured Query Language
 Product_Orders

WHERE Persons.LastName='Hansen'

WHERE Persons.FirstName='Ola'

As you'll see from the two SELECT statements above; aliases can make queries easier to
both write and to read.

SQL Joins

SQL joins are used to query data from two or more tables, based on a relationship
between certain columns in these tables.

SQL JOIN

The JOIN keyword is used in an SQL statement to query data from two or more tables,
based on a relationship between certain columns in these tables.

Tables in a database are often related to each other with keys.

A primary key is a column (or a combination of columns) with a unique value for each
row. Each primary key value must be unique within the table. The purpose is to bind data
together, across tables, without repeating all of the data in every table.

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

Note that the "P_Id" column is the primary key in the "Persons" table. This means that no
two rows can have the same P_Id. The P_Id distinguishes two persons even if they have
the same name.

Using Advanced Structured Query Language

 Next, we have the "Orders" table:

O_Id OrderNo P_Id

1 77895 3

2 44678 3

3 22456 1

4 24562 1

5 34764 15

Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id"
column refers to the persons in the "Persons" table without using their names.

Notice that the relationship between the two tables above is the "P_Id" column.

Different SQL JOINs

Before we continue with examples, we will list the types of JOIN you can use, and the
differences between them.

 JOIN: Return rows when there is at least one match in both tables

 LEFT JOIN: Return all rows from the left table, even if there are no matches in
the right table

 RIGHT JOIN: Return all rows from the right table, even if there are no matches
in the left table

 FULL JOIN: Return rows when there is a match in one of the tables

SQL INNER JOIN Keyword

SQL INNER JOIN Keyword

The INNER JOIN keyword return rows when there is at least one match in both tables.

SQL INNER JOIN Syntax

SELECT column_name(s)

FROM table_name1

Using Advanced Structured Query Language

 INNER JOIN table_name2

ON table_name1.column_name=table_name2.column_name

PS: INNER JOIN is the same as JOIN.

SQL INNER JOIN Example

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 1

4 24562 1

5 34764 15

Now we want to list all the persons with any orders.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

INNER JOIN Orders

ON Persons.P_Id=Orders.P_Id
Using Advanced Structured Query Language
 ORDER BY Persons.LastName

The result-set will look like this:

LastName FirstName OrderNo

Hansen Ola 22456

Hansen Ola 24562

Pettersen Kari 77895

Pettersen Kari 44678

The INNER JOIN keyword return rows when there is at least one match in both tables. If
there are rows in "Persons" that do not have matches in "Orders", those rows will NOT be
listed.

SQL LEFT JOIN Keyword

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there
are no matches in the right table (table_name2).

SQL LEFT JOIN Syntax

SELECT column_name(s)

FROM table_name1

LEFT JOIN table_name2

ON table_name1.column_name=table_name2.column_name

PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.

SQL LEFT JOIN Example

The "Persons" table:

Using Advanced Structured Query Language

 P_Id LastName FirstName Address City

Timoteivn
1 Hansen Ola Sandnes
10

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 1

4 24562 1

5 34764 15

Now we want to list all the persons and their orders - if any, from the tables above.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

LEFT JOIN Orders

ON Persons.P_Id=Orders.P_Id

ORDER BY Persons.LastName

The result-set will look like this:

LastName FirstName OrderNo

Hansen Ola 22456

Using Advanced Structured Query Language

 Hansen Ola 24562

Pettersen Kari 77895

Pettersen Kari 44678

Svendson Tove  

The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there
are no matches in the right table (Orders).

SQL RIGHT JOIN Keyword

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if
there are no matches in the left table (table_name1).

SQL RIGHT JOIN Syntax

SELECT column_name(s)

FROM table_name1

RIGHT JOIN table_name2

ON table_name1.column_name=table_name2.column_name

PS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SQL RIGHT JOIN Exampl

The "Persons" table

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

The
3 Pettersen Kari Storgt 20 Stavanger

"Orders" table:
Using Advanced Structured Query Language
 O_Id OrderNo P_Id

1 77895 3

2 44678 3

3 22456 1

4 24562 1

5 34764 15

Now we want to list all the orders with containing persons - if any, from the tables above.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

RIGHT JOIN Orders

ON Persons.P_Id=Orders.P_Id

ORDER BY Persons.LastName

The result-set will look like this:

LastName FirstName OrderNo

Hansen Ola 22456

Hansen Ola 24562

Pettersen Kari 77895

Pettersen Kari 44678

    34764

The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if
there are no matches in the left table (Persons).

Using Advanced Structured Query Language

SQL FULL JOIN Keyword

SQL FULL JOIN Keyword

The FULL JOIN keyword return rows when there is a match in one of the tables.

SQL FULL JOIN Syntax

SELECT column_name(s)

FROM table_name1

FULL JOIN table_name2

ON table_name1.column_name=table_name2.column_name

SQL FULL JOIN Example

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 1

4 24562 1

5 34764 15

Now we want to list all the persons and their orders, and all the orders with their persons.
Using Advanced Structured Query Language
 We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

FULL JOIN Orders

ON Persons.P_Id=Orders.P_Id

ORDER BY Persons.LastName

The result-set will look like this:

LastName FirstName OrderNo

Hansen Ola 22456

Hansen Ola 24562

Pettersen Kari 77895

Pettersen Kari 44678

Svendson Tove  

    34764

The FULL JOIN keyword returns all the rows from the left table (Persons), and all the
rows from the right table (Orders). If there are rows in "Persons" that do not have
matches in "Orders", or if there are rows in "Orders" that do not have matches in
"Persons", those rows will be listed as well.

SQL UNION Operator

The SQL UNION operator combines two or more SELECT statements.

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT
statements.

Using Advanced Structured Query Language

Notice that each SELECT statement within the UNION must have the same number of
columns. The columns must also have similar data types. Also, the columns in each
SELECT statement must be in the same order.

SQL UNION Syntax

SELECT column_name(s) FROM table_name1

UNION

SELECT column_name(s) FROM table_name2

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

SQL UNION ALL Syntax

SELECT column_name(s) FROM table_name1

UNION ALL

SELECT column_name(s) FROM table_name2

PS: The column names in the result-set of a UNION are always equal to the column
names in the first SELECT statement in the UNION.

SQL UNION Example

Look at the following tables:

"Employees_Norway":

E_ID E_Name

01 Hansen, Ola

02 Svendson, Tove

03 Svendson, Stephen

04 Pettersen, Kari

"Employees_USA":

Using Advanced Structured Query Language

 E_ID E_Name

01 Turner, Sally

02 Kent, Clark

03 Svendson, Stephen

04 Scott, Stephen

Now we want to list all the different employees in Norway and USA.

We use the following SELECT statement:

SELECT E_Name FROM Employees_Norway

UNION

SELECT E_Name FROM Employees_USA

The result-set will look like this:

E_Name

Hansen, Ola

Svendson, Tove

Svendson, Stephen

Pettersen, Kari

Turner, Sally

Kent, Clark

Scott, Stephen

Note: This command cannot be used to list all employees in Norway and USA. In the
example above we have two employees with equal names, and only one of them will be
listed. The UNION command selects only distinct values.
Using Advanced Structured Query Language
 SQL UNION ALL Example

Now we want to list all employees in Norway and USA:

SELECT E_Name FROM Employees_Norway

UNION ALL

SELECT E_Name FROM Employees_USA

Result

E_Name

Hansen, Ola

Svendson, Tove

Svendson, Stephen

Pettersen, Kari

Turner, Sally

Kent, Clark

Svendson, Stephen

Scott, Stephen

SQL SELECT INTO Statement

The SQL SELECT INTO statement can be used to create backup copies of tables.

The SQL SELECT INTO Statement

The SELECT INTO statement selects data from one table and inserts it into a different
table.

The SELECT INTO statement is most often used to create backup copies of tables.

Using Advanced Structured Query Language

SQL SELECT INTO Syntax

We can select all columns into the new table:

SELECT *

INTO new_table_name [IN externaldatabase]

FROM old_tablename

Or we can select only the columns we want into the new table:

SELECT column_name(s)

INTO new_table_name [IN externaldatabase]

FROM old_tablename

SQL SELECT INTO Example

Make a Backup Copy - Now we want to make an exact copy of the data in our
"Persons" table.

We use the following SQL statement:

SELECT *

INTO Persons_Backup

FROM Persons

We can also use the IN clause to copy the table into another database:

SELECT *

INTO Persons_Backup IN 'Backup.mdb'

FROM Persons

We can also copy only a few fields into the new table:

Using Advanced Structured Query Language

 SELECT LastName,FirstName

INTO Persons_Backup

FROM Persons

SQL SELECT INTO - With a WHERE Clause

We can also add a WHERE clause.

The following SQL statement creates a "Persons_Backup" table with only the persons
who lives in the city "Sandnes":

SELECT LastName,Firstname

INTO Persons_Backup

FROM Persons

WHERE City='Sandnes'

SQL SELECT INTO - Joined Tables

Selecting data from more than one table is also possible.

The following example creates a "Persons_Order_Backup" table contains data from the
two tables "Persons" and "Orders":

SELECT Persons.LastName,Orders.OrderNo

INTO Persons_Order_Backup

FROM Persons

INNER JOIN Orders

ON Persons.P_Id=Orders.P_Id

SQL Functions
Using Advanced Structured Query Language
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

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.

Using Advanced Structured Query Language

SQL AVG() Function

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

Using Advanced Structured Query Language

Now we want to find the customers that have an OrderPrice value higher then 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

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

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:
Using Advanced Structured Query Language
 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

SQL COUNT(*) Example

Using Advanced Structured Query Language
If we omit the WHERE clause, like this:

SELECT COUNT(*) AS NumberOfOrders FROM Orders

The result-set will look like this:

NumberOfOrders

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

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

SQL FIRST() Function

The FIRST() Function

The FIRST() function returns the first value of the selected column.

SQL FIRST() Syntax

SELECT FIRST(column_name) FROM table_name

SQL FIRST() Example

Using Advanced Structured Query Language
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 first value of the "OrderPrice" column.

We use the following SQL statement:

SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders

The result-set will look like this:

FirstOrderPrice

1000

SQL LAST() Function

The LAST() Function

The LAST() function returns the last value of the selected column.

SQL LAST() Syntax

SELECT LAST(column_name) FROM table_name

SQL LAST() Example

Using Advanced Structured Query Language

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 last value of the "OrderPrice" column.

We use the following SQL statement:

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders

The result-set will look like this:

LastOrderPrice

100

SQL MAX() Function

The MAX() Function

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

SQL MAX() Syntax

SELECT MAX(column_name) FROM table_name

SQL MAX() Example

We have the following "Orders" table:

Using Advanced Structured Query Language

 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 largest value of the "OrderPrice" column.

We use the following SQL statement:

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

The result-set will look like this:

LargestOrderPrice

2000

SQL MIN() Function

The MIN() Function

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

SQL MIN() Syntax

SELECT MIN(column_name) FROM table_name

SQL MIN() Example

We have the following "Orders" table:

Using Advanced Structured Query Language

 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 smallest value of the "OrderPrice" column.

We use the following SQL statement:

SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders

The result-set will look like this:

SmallestOrderPrice

100

SQL SUM() Function

The SUM() Function

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

SQL SUM() Syntax

SELECT SUM(column_name) FROM table_name

SQL SUM() Example

We have the following "Orders" table:

Using Advanced Structured Query Language

 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 sum of all "OrderPrice" fields".

We use the following SQL statement:

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

The result-set will look like this:

OrderTotal

5700

SQL GROUP BY Statement

Aggregate functions often need an added GROUP BY statement.

The GROUP BY Statement

The GROUP BY statement is used in conjunction with the aggregate functions to group
the result-set by one or more columns.

SQL GROUP BY Syntax

SELECT column_name, aggregate_function(column_name)

FROM table_name

Using Advanced Structured Query Language

 WHERE column_name operator value

GROUP BY column_name

SQL GROUP BY Example

We have the following "Orders" table:

O_IdOrderDate 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 total sum (total order) of each customer.

We will have to use the GROUP BY statement to group the customers.

We use the following SQL statement:

SELECT Customer,SUM(OrderPrice) FROM Orders

GROUP BY Customer

The result-set will look like this:

Customer SUM(OrderPrice)

Hansen 2000

Nilsen 1700

Using Advanced Structured Query Language

 Jensen 2000

Let's see what happens if we omit the GROUP BY statement:

SELECT Customer,SUM(OrderPrice) FROM Orders

The result-set will look like this:

Customer SUM(OrderPrice)

Hansen 5700

Nilsen 5700

Hansen 5700

Hansen 5700

Jensen 5700

Nilsen 5700

The result-set above is not what we wanted.

Explanation of why the above SELECT statement cannot be used: The SELECT
statement above has two columns specified (Customer and SUM(OrderPrice). The
"SUM(OrderPrice)" returns a single value (that is the total sum of the "OrderPrice"
column), while "Customer" returns 6 values (one value for each row in the "Orders"
table). This will therefore not give us the correct result. However, you have seen that the
GROUP BY statement solves this problem.

GROUP BY More Than One Column

We can also use the GROUP BY statement on more than one column, like this:

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders

GROUP BY Customer,OrderDate

SQL HAVING Clause

Using Advanced Structured Query Language

The HAVING Clause

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

SQL HAVING Syntax

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

SQL HAVING 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 if any of the customers have a total order of less than 2000.
Using Advanced Structured Query Language
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

SQL UCASE() Function

Using Advanced Structured Query Language

The UCASE() Function

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

SQL UCASE() Syntax

SELECT UCASE(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

SQL LCASE() Function

The LCASE() Function

Using Advanced Structured Query Language

The LCASE() function converts the value of a field to lowercase.

SQL LCASE() Syntax

SELECT LCASE(column_name) FROM table_name

SQL LCASE() 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 lowercase.

We use the following SELECT statement:

SELECT LCASE(LastName) as LastName,FirstName FROM Persons

The result-set will look like this:

LastName FirstName

hansen Ola

svendson Tove

pettersen Kari

SQL MID() Function

Using Advanced Structured Query Language
The MID() Function

The MID() function is used to extract characters from a text field.

SQL MID() Syntax

SELECT MID(column_name,start[,length]) FROM table_name

Parameter Description

column_name Required. The field to extract characters from.

start Required. Specifies the starting position (starts at 1).

length Optional. The number of characters to return. If omitted, the

MID() function returns the rest of the text.

SQL MID() 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 extract the first four characters of the "City" column above.

We use the following SELECT statement:

SELECT MID(City,1,4) as SmallCity FROM Persons

The result-set will look like this:

SmallCity

Using Advanced Structured Query Language

 Sand

Sand

Stav

SQL LEN() Function

The LEN() Function

The LEN() function returns the length of the value in a text field.

SQL LEN() Syntax

SELECT LEN(column_name) FROM table_name

SQL LEN() Example

We have the following "Persons" table:

P_IdLastName 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 length of the values in the "Address" column above.

We use the following SELECT statement:

SELECT LEN(Address) as LengthOfAddress FROM Persons

Using Advanced Structured Query Language

The result-set will look like this:

LengthOfAddress

12

SQL ROUND() Function

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_nameRequired. 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 ProductNameUnit UnitPrice

1 Jarlsberg 1000 g 10.45

Using Advanced Structured Query Language

 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

SQL NOW() Function

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:

Using Advanced Structured Query Language

 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

SQL FORMAT() Function

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.

Using Advanced Structured Query Language

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

SQL Syntax
Statement
Using Advanced Structured Query Language
AND / OR SELECT column_name(s)
FROM table_name
WHERE condition
AND|OR condition

ALTER ALTER TABLE table_name

TABLE 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 CREATE DATABASE database_name

DATABASE

CREATE CREATE TABLE table_name

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

CREATE CREATE INDEX index_name

INDEX ON table_name (column_name)

or

Using Advanced Structured Query Language

 CREATE UNIQUE INDEX index_name
ON table_name (column_name)

CREATE CREATE VIEW view_name AS

VIEW 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 DROP DATABASE database_name

DATABASE

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,

Using Advanced Structured Query Language

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

LEFT JOIN SELECT column_name(s)

FROM table_name1
LEFT JOIN table_name2
ON
table_name1.column_name=table_name2.column_nam
e

RIGHT JOIN SELECT column_name(s)

FROM table_name1
RIGHT JOIN table_name2
ON
table_name1.column_name=table_name2.column_nam
e

FULL JOIN SELECT column_name(s)

FROM table_name1
Using Advanced Structured Query Language
 FULL JOIN table_name2
ON
table_name1.column_name=table_name2.column_nam
e

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 SELECT DISTINCT column_name(s)

DISTINCT FROM table_name

SELECT INTOSELECT *
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 TRUNCATE TABLE table_name

TABLE

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
Using Advanced Structured Query Language
 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

Using Advanced Structured Query Language

Reference

1. Fundamentals of Relational Database Design

2. Fundamentals of SQL server 2005

3. Basic SQL reference

4. www.Microsoft .com

Using Advanced Structured Query Language