Topics 16-20
Topic 16-Select with, Group by and Order By
Topic 17-Set and Logical
Topic 18 SQL
functions
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
SQL numeric functions
Numeric functions are
used primarily for numeric manipulation and/or mathematical calculations. The
following table details the numeric functions:
|
Name
|
Description
|
|
Returns the absolute value of numeric
expression.
|
|
|
Returns numeric expression converted from
radians to degrees.
|
|
|
Returns the largest value of the input
expressions.
|
|
|
Takes multiple expressions exp1, exp2 and
exp3 so on.. and returns 0 if exp1 is less than exp2, returns 1 if exp1 is
less than exp3 and so on.
|
|
|
Returns the minimum-valued input when given
two or more.
|
|
|
Returns the natural logarithm of the passed
numeric expression.
|
|
|
Returns the base-10 logarithm of the passed
numeric expression.
|
|
|
Returns the remainder of one expression by
diving by another expression.
|
|
|
Returns the non-negative square root of
numeric expression.
|
|
|
Returns the standard deviation of the
numeric expression.
|
|
|
Returns the standard deviation of the
numeric expression.
|
|
|
Returns the tangent of numeric expression
expressed in radians.
|
|
|
Returns numeric exp1 truncated to exp2
decimal places. If exp2 is 0, then the result will have no decimal point.
|
The ABS() function returns the absolute value of
X. Consider the following example:
SQL> SELECT ABS(2);
+---------------------------------------------------------+
|
ABS(2)
|
+---------------------------------------------------------+
| 2
|
+---------------------------------------------------------+
1
row in set (0.00 sec)
SQL> SELECT ABS(-2);
+---------------------------------------------------------+
|
ABS(2)
|
+---------------------------------------------------------+
| 2
|
+---------------------------------------------------------+
1
row in set (0.00 sec)
This function returns the value of X converted
from radians to degrees.
SQL>SELECT DEGREES(PI());
+---------------------------------------------------------+
|
DEGREES(PI()) |
+---------------------------------------------------------+
| 180.000000 |
+---------------------------------------------------------+
1
row in set (0.00 sec)
The GREATEST() function returns the greatest
value in the set of input parameters (n1, n2, n3, a nd so on). The following
example uses the GREATEST() function to return the largest number from a set of
numeric values:
SQL>SELECT GREATEST(3,5,1,8,33,99,34,55,67,43);
+---------------------------------------------------------+
|
GREATEST(3,5,1,8,33,99,34,55,67,43) |
+---------------------------------------------------------+
| 99
|
+---------------------------------------------------------+
1
row in set (0.00 sec)
The
INTERVAL() function compares the value of N to the value list (N1, N2, N3, and
so on ). The function returns 0 if N < N1, 1 if N < N2, 2 if N <N3,
and so on. It will return .1 if N is NULL. The value list must be in the form
N1 < N2 < N3 in order to work properly. The following code is a simple
example of how the INTERVAL() function works:
SQL>SELECT INTERVAL(6,1,2,3,4,5,6,7,8,9,10);
+---------------------------------------------------------+
| INTERVAL(6,1,2,3,4,5,6,7,8,9,10) |
+---------------------------------------------------------+
| 6
|
+---------------------------------------------------------+
1 row in set (0.00 sec)
INTERVAL(N,N1,N2,N3,..........)
The INTERVAL() function compares the value of N
to the value list (N1, N2, N3, and so on ). The function returns 0 if N <
N1, 1 if N < N2, 2 if N <N3, and so on. It will return .1 if N is NULL.
The value list must be in the form N1 < N2 < N3 in order to work
properly. The following code is a simple example of how the INTERVAL() function
works:
SQL>SELECT INTERVAL(6,1,2,3,4,5,6,7,8,9,10);
+---------------------------------------------------------+
|
INTERVAL(6,1,2,3,4,5,6,7,8,9,10) |
+---------------------------------------------------------+
| 6
|
+---------------------------------------------------------+
1
row in set (0.00 sec)
Remember that 6 is the zero-based index in the
value list of the first value that was greater than N. In our case, 7 was the
offending value and is located in the sixth index slot.
The LEAST() function is the opposite of the
GREATEST() function. Its purpose is to return the least-valued item from the
value list (N1, N2, N3, and so on). The following example shows the proper
usage and output for the LEAST() function:
SQL>SELECT LEAST(3,5,1,8,33,99,34,55,67,43);
+---------------------------------------------------------+
|
LEAST(3,5,1,8,33,99,34,55,67,43) |
+---------------------------------------------------------+
| 1
|
+---------------------------------------------------------+
1
row in set (0.00 sec)
This function returns the remainder of N divided
by M. Consider the following example:
SQL>SELECT MOD(29,3);
+---------------------------------------------------------+
|
MOD(29,3) |
+---------------------------------------------------------+
| 2
|
+---------------------------------------------------------+
1
row in set (0.00 sec)
This function returns the non-negative square
root of X. Consider the following example:
SQL>SELECT SQRT(49);
+---------------------------------------------------------+
|
SQRT(49)
|
+---------------------------------------------------------+
| 7
|
+---------------------------------------------------------+
1
row in set (0.00 sec)
STDDEV(expression)
The STD() function is used to return the
standard deviation of expression. This is equivalent to taking the square root
of the VARIANCE() of expression. The following example computes the standard
deviation of the PRICE column in our CARS table:
SQL>SELECT STD(PRICE) STD_DEVIATION FROM
CARS;
+---------------------------------------------------------+
|
STD_DEVIATION |
+---------------------------------------------------------+
| 7650.2146 |
+---------------------------------------------------------+
1
row in set (0.00 sec)
This function returns the tangent of the
argument X, which is expressed in radians.
SQL>SELECT TAN(45);
+---------------------------------------------------------+
|
TAN(45)
|
+---------------------------------------------------------+
| 1.619775
|
+---------------------------------------------------------+
1
row in set (0.00 sec)
This function is used to return the value of X
truncated to D number of decimal places. If D is 0, then the decimal point is
removed. If D is negative, then D number of values in the integer part of the
value is truncated. Consider the following example:
SQL>SELECT TRUNCATE(7.536432,2);
+---------------------------------------------------------+
|
TRUNCATE(7.536432,2) |
+---------------------------------------------------------+
| 7.53
|
+---------------------------------------------------------+
1
row in set (0.00 sec)
SQL GROUP/AGGREGATE Functions
Group functions are built-in SQL functions that operate on
groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM,
DISTINCT
SQL COUNT (): This
function returns the number of rows in the table that satisfies the condition
specified in the WHERE condition. If the WHERE condition is not specified, then
the query returns the total number of rows in the table.
For Example: If you
want the number of employees in a particular department, the query would be:
SELECT COUNT (*) FROM employee WHERE dept = 'Electronics';
The
output would be '2' rows.
If you
want the total number of employees in all the departments, the query would take
the form:
SELECT COUNT (*) FROM employee;
The
output would be '5' rows.
SQL DISTINCT(): This function is used to select the
distinct rows.
For Example: If you
want to select all distinct department names from employee table, the query
would be:
SELECT DISTINCT dept FROM employee;
To get
the count of employees with unique name, the query would be:
SELECT COUNT (DISTINCT name) FROM employee;
SQL MAX(): This
function is used to get the maximum value from a column.
To get
the maximum salary drawn by an employee, the query would be:
SELECT MAX (salary) FROM employee;
SQL MIN(): This
function is used to get the minimum value from a column.
To get
the minimum salary drawn by an employee, he query would be:
SELECT MIN (salary) FROM employee;
SQL AVG(): This
function is used to get the average value of a numeric column.
To get
the average salary, the query would be
SELECT AVG (salary) FROM employee;
SQL SUM(): This
function is used to get the sum of a numeric column
To get
the total salary given out to the employees,
SELECT SUM (salary) FROM employee;
Topic
19, 20
No comments:
Post a Comment