Database development Basics-sql Server aggregate functions, mathematical Functions, String functions, time-date functions

SQL has many built-in functions that can be used for counting and computation.

Syntax of the function

The syntax for the built-in SQL function is:

SELECT function (column) from table

Types of functions

In SQL, there are several kinds of basic function types and types. The basic types of functions are:

• Aggregate function
• Scalar function

aggregate function (Aggregate functions)

The operation of the Aggregate function targets a series of values and returns a single value.

Note: If a SELECT statement is used in many other expressions in the list of items in the SELECT statement, the select must use the GROUP by statement!

"Persons" table (used in most cases) Age

Name
Adams, John.	38
Bush, George.	33
Carter, Thomas.	28

Aggregate functions in MS Access

function	Description
AVG (column)	Returns the average of a column
COUNT (column)	Returns the number of rows in a column (not including NULL values)
COUNT (*)	Returns the number of rows selected
First (column)	Returns the value of the first record in a specified field
Last (column)	Returns the value of the last record in the specified field
MAX (column)	Returns the highest value of a column
MIN (column)	Returns the lowest value of a column
STDEV (column)
STDEVP (column)
SUM (column)	Returns the sum of a column
VAR (column)
VARP (column)

Aggregate functions in SQL Server

function	Description
AVG (column)	Returns the average of a column
Binary_checksum
CHECKSUM
Checksum_agg
COUNT (column)	Returns the number of rows in a column (not including null values)
COUNT (*)	Returns the number of rows selected
COUNT (DISTINCT column)	Returns the number of distinct results
First (column)	Returns the value of the first record in a specified field (SQLServer2000 not supported)
Last (column)	Returns the value of the last record in the specified field (SQLServer2000 not supported)
MAX (column)	Returns the highest value of a column
MIN (column)	Returns the lowest value of a column
STDEV (column)
STDEVP (column)
SUM (column)	Returns the sum of a column
VAR (column)
VARP (column)

Scalar function

The operation of the Scalar function targets a single value and returns a single value based on the input value.

Scalar functions in MS Access

function	Description
UCASE (c)	Convert a field to uppercase
LCASE (c)	Convert a field to lowercase
MID (C,start[,end])	Extracting characters from a text field
LEN (c)	Returns the length of a text field
INSTR (C,char)	Returns the numeric position of the specified character in a text field
Left (C,number_of_char)	Returns the left part of a requested text field
Right (C,number_of_char)	Returns the right part of a requested text field
ROUND (C,decimals)	Rounding the specified number of decimal digits for a numeric field
MOD (x, y)	Returns the remainder of a division operation
Now ()	Returns the current system date
FORMAT (C,format)	Change the way a field is displayed
DATEDIFF (D,DATE1,DATE2)	Used to perform date calculations

SQL AVG function

• SQL functions
• SQL count ()

Definition and usage

The AVG function returns the average of a numeric column. NULL values are not included in the calculation.

SQL AVG () syntax

SELECT AVG (column_name) from table_name

SQL AVG () instance

We have the following "Orders" table:

o_id	OrderDate	Orderprice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Example 1

Now, we want to calculate the average of the "Orderprice" field.

We use the following SQL statement:

SELECT AVG (Orderprice) as Orderaverage from Orders

The result set looks like this:

Orderaverage
950

Example 2

Now, we want to find customers with Orderprice values above the Orderprice average.

We use the following SQL statement:

Select Customer from Orderswhere orderprice> (select AVG (orderprice) from Orders)

The result set looks like this:

Customer
Bush
Carter
Adams

SQL COUNT () function

• SQL avg ()
• SQL First ()

The count () function returns the number of rows that match the specified criteria.

SQL count () syntax SQL COUNT (column_name) syntax

The count (column_name) function returns the number of values for the specified column (NULL does not count in):

SELECT COUNT (column_name) from table_name

SQL COUNT (*) syntax

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

SELECT COUNT (*) from table_name

SQL COUNT (DISTINCT column_name) syntax

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

SELECT COUNT (DISTINCT column_name) from table_name

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

SQL COUNT (column_name) instance

We have the following "Orders" table:

o_id	OrderDate	Orderprice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Now, we want to calculate the number of orders for the customer "Carter".

We use the following SQL statement:

SELECT COUNT (Customer) as Customernilsen from Orderswhere customer= ' Carter '

The result of the above SQL statement is 2 because there are 2 orders for customer Carter:

Customernilsen
2

SQL COUNT (*) instance

If we omit the WHERE clause, such as this:

SELECT COUNT (*) as numberoforders from Orders

The result set looks like this:

Numberoforders
6

This is the total number of rows in the table.

SQL COUNT (DISTINCT column_name) instance

Now, we want to calculate the number of different customers in the Orders table.

We use the following SQL statement:

SELECT COUNT (DISTINCT Customer) as Numberofcustomers from Orders

The result set looks like this:

Numberofcustomers
3

This is the number of different customers (Bush, Carter, and Adams) in the Orders table.

SQL First () function

• SQL count ()
• SQL last ()

First () function

The first () function returns the value of the number one record in the specified field.

Tip: You can use the order BY statement to sort records.

SQL First () syntax

SELECT First (column_name) from table_name

SQL First () instance

We have the following "Orders" table:

o_id	OrderDate	Orderprice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

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 looks like this:

Firstorderprice
1000

SQL Last () function

• SQL First ()
• SQL Max ()

Last () function

The last () function returns the value of the final record in the specified field.

Tip: You can use the order BY statement to sort records.

SQL last () syntax

SELECT last (column_name) from table_name

SQL last () instance

We have the following "Orders" table:

o_id	OrderDate	Orderprice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

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 looks like this:

Lastorderprice
100

SQL MAX () function

• SQL last ()
• SQL min ()

MAX () function

The Max function returns the maximum value in a column. NULL values are not included in the calculation.

SQL MAX () syntax

SELECT MAX (column_name) from table_name

Note: MIN and MAX can also be used for text columns to get the highest or lowest values in alphabetical order.

SQL MAX () instance

We have the following "Orders" table:

o_id	OrderDate	Orderprice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Now, we want to find the maximum value for the "Orderprice" column.

We use the following SQL statement:

SELECT MAX (Orderprice) as Largestorderprice from Orders

The result set looks like this:

Largestorderprice
2000

SQL MIN () function

• SQL Max ()
• SQL sum ()

MIN () function

The Min function returns the minimum value in a column. NULL values are not included in the calculation.

SQL MIN () syntax

SELECT MIN (column_name) from table_name

Note: MIN and MAX can also be used for text columns to get the highest or lowest values in alphabetical order.

SQL MIN () instance

We have the following "Orders" table:

o_id	OrderDate	Orderprice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Now, we want to find the minimum value for the "Orderprice" column.

We use the following SQL statement:

SELECT MIN (Orderprice) as Smallestorderprice from Orders

The result set looks like this:

Smallestorderprice
100

SUM () function

The SUM function returns the total number of numeric columns (total).

SQL SUM () syntax

SELECT SUM (column_name) from table_name

SQL SUM () instance

We have the following "Orders" table:

o_id	OrderDate	Orderprice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Now, we want to find the total number of "Orderprice" fields.

We use the following SQL statement:

SELECT SUM (Orderprice) as OrderTotal from Orders

The result set looks like this:

OrderTotal
5700

SQL GROUP by statement

• SQL sum ()
• SQL have

Aggregate functions, such as SUM, often require the addition of a GROUP by statement.

GROUP by statement

The GROUP BY statement is used to combine aggregate functions to group result sets based on one or more columns.

SQL GROUP by syntax

SELECT column_name, Aggregate_function (column_name) from Table_namewhere column_name operator Valuegroup by column_name

SQL GROUP by instance

We have the following "Orders" table:

o_id	OrderDate	Orderprice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Now we want to find the total amount (total order) for each customer.

We want to use GROUP by statements to group customers.

We use the following SQL statements:

SELECT Customer,sum (Orderprice) from Ordersgroup by Customer

The result set looks like this:

Customer	SUM (Orderprice)
Bush	2000
Carter	1700
Adams	2000

It's great, isn't it?

Let's take a look at what happens if you omit GROUP by:

SELECT Customer,sum (orderprice) from Orders

The result set looks like this:

Customer	SUM (Orderprice)
Bush	5700
Carter	5700
Bush	5700
Bush	5700
Adams	5700
Carter	5700

The result set above is not what we need.

So why not use the SELECT statement above? This is explained as follows: The above SELECT statement specifies two columns (Customer and SUM (Orderprice)). SUM (Orderprice) returns a separate value (total for the "orderprice" column), and "Customer" returns 6 values (each corresponding to each row in the Orders table). Therefore, we do not get the right results. However, you have seen that the GROUP by statement solves this problem.

GROUP by more than one column

We can also apply the GROUP by statement to more than one column, just like this:

SELECT Customer,orderdate,sum (Orderprice) from Ordersgroup by customer,orderdate

SQL HAVING clause

• SQL Group by
• SQL UCase ()

HAVING clause

The addition of the HAVING clause in SQL is because the WHERE keyword cannot be used with the aggregate function.

SQL having syntax

SELECT column_name, Aggregate_function (column_name) from Table_namewhere column_name operator Valuegroup by Column_ Namehaving aggregate_function (column_name) operator value

SQL having instance

We have the following "Orders" table:

o_id	OrderDate	Orderprice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Now we want to find customers with a total order amount of less than 2000.

We use the following SQL statement:

SELECT Customer,sum (Orderprice) from Ordersgroup by Customerhaving SUM (Orderprice) <2000

The result set is similar to:

Customer	SUM (Orderprice)
Carter	1700

Now we want to find the customer "Bush" or "Adams" with more than 1500 of the total order amount.

We have added a common where clause to the SQL statement:

SELECT Customer,sum (orderprice) from Orderswhere customer= ' Bush ' OR customer= ' Adams ' GROUP by Customerhaving SUM ( Orderprice) >1500

Result set:

Customer	SUM (Orderprice)
Bush	2000
Adams	2000

SQL Date function

• SQL View
• SQL Nulls

SQL Date

When we work on dates, the hardest task is to ensure that the date you insert is formatted to match the format of the date column in the database.

As long as the data contains only the date parts, running the query will not be a problem. However, if time is involved, the situation is a little more complicated.

Before we discuss the complexity of the date query, let's look at the most important built-in date processing functions.

MySQL Date function

The following table lists the most important built-in date functions in MySQL:

function	Description
Now ()	Returns the current date and time
Curdate ()	Returns the current date
Curtime ()	Returns the current time
DATE ()	Extract date part of date or date/time expression
EXTRACT ()	Returns a separate part of the date/time Press
Date_add ()	Add a specified time interval to a date
Date_sub ()	Subtract a specified time interval from a date
DATEDIFF ()	Returns the number of days between two dates
Date_format ()	Display Date/time in a different format

SQL Server Date function

The following table lists the most important built-in date functions in SQL Server:

function	Description
GETDATE ()	Returns the current date and time
DATEPART ()	Returns a separate part of the date/time
DATEADD ()	Add or subtract a specified time interval in a date
DATEDIFF ()	Returns a time between two dates
CONVERT ()	Display Date/time in a different format

SQL Date Data type

MySQL stores date or date/time values in the database using the following data types:

• DATE-Format YYYY-MM-DD
• DATETIME-format: Yyyy-mm-dd HH:MM:SS
• TIMESTAMP-format: Yyyy-mm-dd HH:MM:SS
• Year-format YYYY or YY

SQL Server uses the following data types to store date or date/time values in the database:

• DATE-Format YYYY-MM-DD
• DATETIME-format: Yyyy-mm-dd HH:MM:SS
• smalldatetime-format: Yyyy-mm-dd HH:MM:SS
• TIMESTAMP-Format: Unique number

SQL Date Processing

If the time part is not involved, then we can easily compare two dates!

Suppose we have the following "Orders" table:

OrderId	ProductName	OrderDate
1	Computer	2008-12-26
2	Printer	2008-12-26
3	Electrograph	2008-11-12
4	Telephone	2008-10-19

Now, we want to select a record from the table above that OrderDate is "2008-12-26".

We use the following SELECT statement:

SELECT * from Orders WHERE orderdate= ' 2008-12-26 '

Result set:

OrderId	ProductName	OrderDate
1	Computer	2008-12-26
3	Electrograph	2008-12-26

Now suppose that "Orders" is similar (note the time section in the "OrderDate" column):

OrderId	ProductName	OrderDate
1	Computer	2008-12-26 16:23:55
2	Printer	2008-12-26 10:45:26
3	Electrograph	2008-11-12 14:12:08
4	Telephone	2008-10-19 12:56:10

If we use the SELECT statement above:

SELECT * from Orders WHERE orderdate= ' 2008-12-26 '

Then we won't get the result. This is because the query does not contain a date for the time part.

Tip: If you want to make queries simple and easier to maintain, don't use the time section in dates!

Database development Basics-sql Server aggregate functions, mathematical Functions, String functions, time-date functions