C2

Row-wise operations

Inside our selected space, we can directly perform row-wise operations.

e.g.

Given the following table Products

ProductCode	SellingPrice	AccessoryPrice
HG100	17.60	2.40
HG101	16.9	NULL

We can get the total selling price by doing SellingPrice + AccessoryPrice.

SELECT SellingPrice , AccessoryPrice, SellingPrice + AccessoryPrice AS 'Total Selling Price'
FROM Products

NOTE:

Columns Involved in the row-wise operation (this case SellingPrice and AccessoryPrice) MUST be SELECTED prior to the calculation of the new attribute.

We will get a result similar to that below

Total Selling Price
20.0
NULL

See how when NULL is involved in the operation, the result is ALWAYS NULL.

Handling Null Values with IFNULL()

Syntax:

IFNULL(<expression> , <value if null>)

Note that the expression and value if null has to have the same value type.

Following the above example:

SELECT SellingPrice , AccessoryPrice, SellingPrice + IFNULL(AccessoryPrice, 0) AS 'Total Selling Price'
FROM Products

Will handle the NULL value inside of AccessoryPrice

Result should look something like below

Total Selling Price
20.0
16.9

String Functions

Function	Brief Description	Syntax	Example Usage
NOW()	Returns the current date and time.	NOW()	SELECT NOW();
UPPER(string)	Converts a string to uppercase.	UPPER(string)	SELECT UPPER('hello');
LOWER(string)	Converts a string to lowercase.	LOWER(string)	SELECT LOWER('WORLD');
SUBSTRING(Source, s, l)	Extracts a substring from a string. Note SQL uses 1 indexing not 0 indexing like most other languages	SUBSTRING(Source, start, length)	SELECT SUBSTRING('example', 1, 3);
LENGTH(string)	Returns the length of a string.	LENGTH(string)	SELECT LENGTH('database');
CAST(columnName as datatype(size))	Converts an expression of one data type to another.	CAST(columnName AS datatype(size))	SELECT CAST(dateString AS DATE);
CONVERT(datatype(size), columnName, [style])	Converts an expression of one data type to another with an optional style.	CONVERT(datatype(size), columnName, [style])	SELECT CONVERT(VARCHAR(10), date_col, 101);

Aggregate Functions

Note that aggregation functions will ignore NULL.

Aggregate Functions Handling Strings

Lets say we have a table as such: |StudentId|GPA| |-|-| |A01|3.0| |A09|NULL| |A69|2.0| |A420|1.0|

By default, when we query the result will be 2.0

This is derived from

\[ \frac{3 + 2 + 1}{3} = 2.0 \]

NULL is ignored as per how aggregate functions works.

HOWEVER, lets say the GPA is stored as a string

StudentId	GPA
A01	'3.0'
A09	'NULL'
A69	'2.0'
A420	'1.0'

'NULL' will be treated as 0.

So our aggregation will be 1.5.

This is derived from

\[ \frac{3 + 2 + 1 + 0}{4} = 1.5 \]

Aggregation with Groups

Let's say that we want to figure out the average unit price across suppliers.

In order to aggregate this way, we need to use GROUP BY clause.

This will perform aggregation based on the GROUPs that we created

e.g.

Given this table stock

Prod_Code	Prod_Desc	Unit_px	Supplier_ID	Qty
HG7160	Sale Dress White	15.90	S1001	30
HG9298	Sale Top + Skirt Red	19.80	S1001	20
RQ0207	Dress White	18.60	S1002	40
HG7166	Dress Blue	15.90	NULL	10
HG6159	Sale Dress Pink	15.40	S1002	40
HT5402	Pink Skirt	15.00	NULL	20

SELECT Supplier_ID,
AVG(unit_px) 'Average Unit Price' , AVG(qty) 'Average Quantity'
FROM stock
GROUP BY Supplier_ID;

The results will be as follows:

Supplier ID	Average Unit Price	Average Quantity
NULL	15.45	15
S1001	17.85	25
S1002	17	40

Notice that NULL values are treated as a group as well.

Filtering a Group using HAVING

What if we only want the suppliers with an average quantity greater than 20.

We have to use the HAVING clause.

Why not WHERE clause?

The WHERE clause is designed for filtering before grouping

The HAVING clause is designed for filtering values within groups

e.g.

SELECT Supplier_ID,
AVG(unit_px) 'Average Unit Price' , AVG(qty) 'Average Quantity'
FROM stock
GROUP BY Supplier_ID
HAVING AVG(qty) > 20; 

The results will be as follows:

Supplier ID	Average Unit Price	Average Quantity
S1001	17.85	25
S1002	17	40