SCALAR FUNCTIONS &
ARITHMETIC
ICT200/ ITS232
Unit Objectives
After completing this unit, you should be able to:
01 Use arithmetic in the SELECT and WHERE clauses
02 Use date and time scalar functions
03 Use the CONCAT operator
Selecting Calculated Values
● Example:
The duration of staff to in-charge in each attraction place has been
add-on 2 hours from the original time. Write a SQL statement to
display EMP_NUM, HOURS_PER_ATTRACT and a new duration.
● SQL statement:
SELECT EMP_NUM, HOURS_PER_ATTRACT, HOURS_PER_ATTRACT + 2
FROM HOURS;
Naming Result Columns
● Example:
SELECT EMP_NUM, HOURS_PER_ATTRACT, HOURS_PER_ATTRACT + 2 AS NEW_DURATION
FROM HOURS
ORDER BY NEW_DURATION;
Substitution of NULL Values
● Example:
Replace the null value of ATTRACT_NAME with UNKOWN.
● SQL Statement:
SELECT ATTRACT_NO, PARK_CODE, COALESCE(ATTRACT_NAME, 'UNKNOWN') AS PLACE
FROM ATTRACTION;
Arithmetic with NULL Values
● Example of arithmetic with null values without COALESCE function:
SELECT EMP_NUM, ATTRACT_NO, HOURS_PER_ATTRACT, HOUR_RATE,
HOUR_RATE * HOURS_PER_ATTRACT AS "WAGES“
FROM HOURS;
● Example of arithmetic with null values with COALESCE function:
SELECT EMP_NUM, ATTRACT_NO, HOURS_PER_ATTRACT, HOUR_RATE,
HOUR_RATE * COALESCE(HOURS_PER_ATTRACT,0) AS "WAGES“
FROM HOURS;
Calculated Values
● Example:
SELECT PARK_CODE, TICKET_PRICE, TICKET_PRICE + (TICKET_PRICE *0.1) AS "NEW PRICE“
FROM TICKET;
Calculated Values (Condition on calculated value)
● Example:
SELECT PARK_CODE, TICKET_PRICE, TICKET_PRICE + (TICKET_PRICE *0.1) AS "NEW PRICE“
FROM TICKET
WHERE TICKET_PRICE + (TICKET_PRICE *0.1) > 25;
Truncation and Rounding
● ROUND : Rounds a value to a specified precision (number of digits)
Syntax: ROUND(numeric_value, p) where p = precision
● TRUNCATE: Truncates a value to a specified precision (number of digits)
Syntax: TRUNC(numeric_value, p) where p = precision
Truncation and Rounding
● Example: Displays the individual LINE_PRICE from the sales line table, rounded to
one and zero places and truncated where the quantity of tickets
purchased on that line is greater than 2.
● SQL Statement:
SELECT LINE_PRICE, ROUND(LINE_PRICE,1) AS "LINE_PRICE1", ROUND(LINE_PRICE,0) AS
"LINE_PRICE1",TRUNCATE(LINE_PRICE,0) AS "TRUNCATED VALUE“
FROM SALES_LINE
WHERE LINE_QTY > 2;
Date & Time
● MySQL Date and Time data types:
DATETIME YYYY-MM-DD HH:MM:SS
DATE YYYY-MM-DD
TIMESTAMP YYYYMMDDHHSSMM
TIME HH:MM:SS
YEAR YYYY
Comparison with Dates
● Example:
SELECT *
FROM EMPLOYEE
WHERE EMP_HIRE_DATE > 2001-01-01;
DATE_ADD and DATE_SUB
● The DATE_ADD and DATE_SUB functions both perform date arithmetic and allow
you to either add or subtract two dates from one another.
● The syntax of these functions is:
DATE_ADD(date,INTERVAL expr unit)
DATE_SUB(date,INTERVAL expr unit)
Where expr is an expression specifying the interval value to be added or
subtracted from the starting date and unit is a keyword indicating the units in
which the expression should be interpreted.
DATE_ADD and DATE_SUB
● Example: Write a query which lists the hire dates of all employees along with the
date of their first work appraisal (one year from the hiredate).
● SQL Statement:
SELECT EMP_LNAME, EMP_FNAME, EMP_HIRE_DATE,
ADDDATE(EMP_HIRE_DATE, INTERVAL 12 MONTH )AS “FIRST APPRAISAL”
FROM EMPLOYEE;
MONTH, DAYOFMONTH and YEAR
● MySQL provides functions for extracting the month, day or year from any given
date.
● The syntax of each function is as follows:
DAYOFMONTH(date) returns the day of the month for date, in the range 0 to 31.
MONTH(date) returns the month for date, in the range 0 to 12.
YEAR(date) returns the year for date, in the range 1000 to 9999, or 0 for the
“zero” date.
MONTH, DAYOFMONTH and YEAR
● Example: Write a SQL statement to display different parts of an employee’s
date of birth. Include also EMP_LNAME, EMP_FNAME and EMP_DOB in
this SQL statement.
● SQL Statement:
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, DAYOFMONTH(EMP_DOB) AS “Day”,
MONTH(EMP_DOB) AS “Month”, YEAR(EMP_DOB) AS “Year”
FROM EMPLOYEE;
CONCAT Function
● CONCAT: Concatenates data from two different character columns and returns a
single column.
● Syntax: CONCAT(strg_value, strg_value)
● Example: Lists all employee first and last names concatenated together.
● SQL Statement:
SELECT CONCAT(EMP_LNAME ,EMP_FNAME) AS NAME
FROM EMPLOYEE;
SUBSTR Function
● SUBSTR: Returns a substring or part of a given string parameter
● Syntax: SUBSTR(strg_value, p, l) where p = start position and l = length of
characters.
● Example: Lists the first three characters of all the employees’ first name. Include
also the EMP_NUM in this query.
● SQL Statement:
SELECT EMP_NUM, SUBSTR(EMP_FNAME,1,3)
FROM EMPLOYEE;
