4.2.1.6 Character Expressions & Functions

|| - Concatenate operator

Query 29:

SELECT custname || ' - ' || city

FROM customers;

Query 29 - Result:

CustName||'-'|| City

Shah - Bombay

Srinivasan - Madras

Gupta - Delhi

Banerjee - Calcutta

Apte - Bombay

Examples of Character Functions:

INITCAP(string)

UPPER(string)

LOWER(string)

SUBSTR(string,start,no. of characters)

Group Functions

Group functions are functions which act on the entire column of selected rows.

Query 30:

SELECT SUM(qty), AVG(qty) <--------------- FROM ord_items WHERE item#='SW1';

SUM and AVG are examples of Group Functions. They compute the sum/average of qty values of all rows where item#='SW1'.

Query 30 - Result:

SUM(QTY)	AVG(QTY)
250	125

Examples of Group Functions:

SUM

AVG

COUNT

MAX

MIN

Query 31:

SELECT item#, SUM(qty) FROM ord_items GROUP BY item#; <-------------------------

GROUP BY clause used to group rows according to the value of item# in the result. SUM function acts individually on each group of rows.

Query 31 - Result:

Item #	SUM(Qty)
HW1	100
HW2	35
HW3	200
SW1	250

Query 32:

SELECT item#, SUM(qty) FROM ord_items GROUP BY item# HAVING SUM(qty)>100; <------------------

HAVING clause used to apply the condition to be applied on the grouped rows and display the final result.

Query 32 - Result:

Item#	SUM(QTY)
HW3	200
SW1	250

Query 33:

SELECT item#, SUM(qty)

FROM ord_items

GROUP BY item#

HAVING COUNT(*)>2;

Query 33 - Result:

Item#	SUM(Qty)
HW3	200