function(argument1, argument2, ...)
SQL functions are of two types :
Group (or aggregate) functions
These functions take a collection ( a set ) of values as input and return a single value.
Single row (or scalar ) functions
These functions take a single value as input and return a single value.
SQL Queries Aggregate Functions syntax
MIN() - Returns the smallest value
SELECT MIN (column_name) from table_name;
Example:
SELECT MIN(SALARY) FROM INSTRUCTOR;
SELECT MIN(SALARY) “Minimun Salary” FROM INSTRUCTOR;
MAX() - Returns the largest value
SELECT MAX (column_name) from table_name;
SUM() - Returns the sum
SELECT SUM (column_name) from table_name;
AVG() - Returns the average value
SELECT AVG(column_name) from table_name;
COUNT() - Returns the number of rows
SELECT COUNT(column_name) from table_name;
EXAMPLE:
SELECT COUNT(ID) FROM INSTRUCTOR;
SELECT COUNT(*) FROM INSTRUCTOR;
SELECT COUNT(DISTINCT DEPT_NAME) FROM INSTRUCTOR;
AGGREGATION WITH GROUPING
Aggregate functions can also be applied to a group of sets of tuples instead of a single set
of tuples by using a GROUP BY clause.
SQL Queries Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
Example:
SELECT MAX(SALARY) FROM INSTRUCTOR;
SELECT DNAME, MAX(SALARY) FROM INSTRUCTOR GROUP BY DNAME;
SELECT DNAME, MAX(SALARY) FROM INSTRUCTOR GROUP BY DNAME
ORDER BY DNAME;
SQL Queries HAVING CLAUSE
A condition can be applied to groups rather than to tuples using a HAVING clause
with the GROUP BY clause.
SQL Queries HAVING CLAUSE Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
Example:
SELECT DNAME, AVG(SALARY) FROM INSTRUCTOR GROUP BY DNAME
HAVING AVG(SALARY) > 40000;
SQL queries operator Syntax
DESC DUAL;
SELECT * FROM DUAL; SELECT SYSDATE FROM DUAL;
Arithmetic Operators
Operator
|
Description
|
Example
|
+ (unary)
|
Makes operand positive
|
SELECT +3 FROM DUAL;
|
- (unary)
|
Negates operand
|
SELECT -4 FROM DUAL;
|
/
|
Division (numbers and dates)
|
SELECT SALARY / 10 FROM INSTRUCTOR;
|
*
|
Multiplication
|
SELECT SALARY *1.1 FROM INSTRUCTOR;
|
+
|
Addition (numbers and dates)
|
SELECT SALARY + 2000 FROM INSTRUCTOR;
|
-
|
Subtraction (numbers and dates)
|
SELECT SALARY - 1000 FROM INSTRUCTOR;
|
Character Operators
Operator
|
Description
|
Example
|
||
|
Concatenates character
|
SELECT 'The Name of the instructor is: ' ||
|
Operator
|
Description
|
Example
|
strings
|
NAME FROM INSTRUCTOR;
|
Comparison Operators
Operator
|
Description
|
Example
|
=
|
Equality test.
|
SELECT NAME FROM
INSTRUCTOR WHERE SALARY = 70000;
|
!=, ^=, <>
|
Inequality test.
|
SELECT NAME FROM
INSTRUCTOR WHERE SALARY <> 70000;
|
>
|
Greater than test.
|
SELECT NAME FROM
INSTRUCTOR WHERE SALARY > 70000;
|
<
|
Less than test.
|
|
>=
|
Greater than or equal to test.
|
|
<=
|
Less than or equal to test.
|
|
IN
|
"Equivalent to any member of"
test. Equivalent to "= ANY".
|
SELECT * FROM INSTRUCTOR WHERE NAME IN ('WU', 'KIM','CRICK');
|
NOT IN
|
Equivalent to "!= ANY". Evaluates
to FALSE if any member of the set is NULL.
|
SELECT * FROM INSTRUCTOR WHERE NAME NOTIN ('WU', 'KIM','CRICK');
|
Operator
|
Description
|
Example
|
[NOT]
BETWEEN
x AND y
|
[Not] greater than or equal to
x and less than or equal to y.
|
SELECT NAME, DEPT_NAME FROM INSTRUCTOR WHERE
SALARY BETWEEN 30000
AND 80000;
|
x LIKE y
|
TRUE if x matches the pattern y.
|
SELECT * FROM INSTRUCTOR WHERE NAME LIKE 'S%';
|
Logical Operators
Operator
|
Description
|
Example
|
NOT
|
Returns TRUE if the following condition is FALSE.
Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.
|
SELECT * FROM INSTRUCTOR WHERE NOT (DEPT_NAME IS
NULL)
|
SELECT * FROM
|
Operator
|
Description
|
Example
|
INSTRUCTOR WHERE NOT (
SALARY BETWEEN 40000 AND
60000)
|
||
AND
|
Returns TRUE if both component conditions are
TRUE. Returns FALSE if either is FALSE; otherwise returns UNKNOWN.
|
SELECT * FROM INSTRUCTOR WHERE DEPT_NAME='FINANCE'
AND SALARY=90000;
|
OR
|
Returns TRUE if either
component condition is TRUE. Returns FALSE if both are FALSE. Otherwise,
returns UNKNOWN.
|
SELECT * FROM INSTRUCTOR WHERE DEPT_NAME='FINANCE'
OR SALARY=90000;
|
Set Operators
Operator
|
Description
|
Example
|
UNION
|
Returns all distinct rows selected by either
query.
|
SELECT * FROM INSTRUCTOR WHERE DNAME='FINANCE' AND
SALARY>=80000
|
UNION
|
||
SELECT * FROM INSTRUCTOR WHERE DNAME='HISTORY' AND
SALARY BETWEEN 60000
AND 70000 ;
|
||
UNION ALL
|
Returns all rows selected
by either query, including all duplicates.
|
SELECT * FROM INSTRUCTOR WHERE DNAME='FINANCE' AND
SALARY>=80000
|
Operator
|
Description
|
Example
|
UNION ALL
SELECT * FROM INSTRUCTOR WHERE DNAME='HISTORY' AND
SALARY BETWEEN 60000
AND 70000 ;
|
||
INTERSECT
and INTERSECT ALL
|
Returns all distinct rows
selected by both queries.
|
SELECT * FROM INSTRUCTOR
WHERE DNAME='FINANCE' AND SALARY>=80000
INTERSECT SELECT * FROM
INSTRUCTOR WHERE DNAME='HISTORY' AND SALARY
BETWEEN 60000
AND 70000 ;
|
MINUS
|
Returns all distinct rows
selected by the first query but not the second.
|
SELECT * FROM INSTRUCTOR WHERE DNAME='FINANCE' AND
SALARY>=80000
MINUS
SELECT * FROM INSTRUCTOR WHERE DNAME='HISTORY' AND
SALARY BETWEEN 60000
AND 70000 ;
|
0 Comments
Please do not Enter any spam link in the comment box