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.
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'. |
SUM(QTY) | AVG(QTY) |
250 | 125 |
Examples of Group Functions:
SUM
AVG
COUNT
MAX
MIN
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. |
Item # | SUM(Qty) |
HW1 | 100 |
HW2 | 35 |
HW3 | 200 |
SW1 | 250 |
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. |
Item# | SUM(QTY) |
HW3 | 200 |
SW1 | 250 |
SELECT item#, SUM(qty)
FROM ord_items
GROUP BY item#
HAVING COUNT(*)>2;
Query 33 - Result:
Item# | SUM(Qty) |
HW3 | 200 |
Leave a Comment