SQL Queries Aggregate Functions, group by, having clause and Operators Syntax

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 ;