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
Powered by Blogger.