Chapter 3 Home | Prev: 3.4 Aliases | Next: 3.6 Filtering Data with WHERE
ALL calculated values should be defined with an alias. Otherwise, the name of the column will be the calculation formula itself, which is almost impossible to use when data is moved to software.
In SQL, it is almost always required to manipulate the data to calculate new values. This is how information can be retrieved from data. Therefore, calculations are performed in most SQL SELECT statements and are a basic feature that learners need to understand.
-- example
SELECT
productCode,
price,
quantityOrdered AS quantity,
price * quantityOrdered AS subtotal
FROM orders;
The column defined as subtotal does not exist in the source table. It is a calculated value. The output may look something like this| PRODUCTCODE | PRICE | QUANTITY | SUBTOTAL |
| 123 | 1.25 | 3 | 3.75 |
| 456 | 5.95 | 2 | 11.90 |
Calculated fields are not always mathematical in nature. String concatenation is also a form of calculation, but using strings.
Any output value that is not directly taken from a single field will be a calculated value.
SELECT firstname || ' ' || lastname AS fullName
FROM employees;
-- or using single-line functions
SELECT Concat(firstName, Concat(' ', lastName)) AS fullName
FROM employees;
SELECT firstname + ' ' + lastname AS fullName
FROM employees;
-- or using single-line functions
SELECT Concat(firstName, ' ', lastName) AS fullName
FROM employees;
| Employee Name |
| John Smith |
| Mary Jones |
The one and only column in this query is a calculated field. String concatenation changes the values of the original fields, and therefore result in calculated fields.
There are many functions available for use within SQL. Most functions are standardized across DBMSs, but there are slight differences in some functions. We will concentrate on the oracle version of the functions for this course.
A fairly complete list of functions are available at Oracle SQL Function List.
A list of some functions for MSSQL is available at: MS SQL Functions List
There are three main categories for functions: Numeric, String, and Date based functions. There are some more, but most functions fall under one of these three categories.
Numeric Functions are functions involved in mathematical calculations. Some of the most common include ROUND, SQRT, and MOD
SELECT studentID, mark, maxmarks, round(100 * mark / maxmarks, 2) AS grade FROM studentMarks
| STUDENTID | MARK | MAXMARKS | GRADE |
| 1 | 27 | 53 | 45.28 |
| 2 | 33 | 53 | 62.26 |
| 3 | 48 | 53 | 90.57 |
String Functions manipulating strings to create custom output. Some common examples are: CONCAT, UPPER, LENGTH and SUBSTR or SUBSTRING.
SELECT
firstname,
lastname,
Concat(Concat(firstname, ' '), lastname) AS fullName,
Upper(firstname) AS first,
Length(lastname) AS lenLast,
Substr(lastname, 2, 3) AS Sub
FROM students;
SELECT
firstname,
lastname,
Concat(firstname, ' ', lastname) AS fullName,
Upper(firstname) AS first,
Len(lastname) AS lenLast,
Substring(lastname, 2, 3) AS Sub
FROM students;
| FIRSTNAME | LASTNAME | FULLNAME | FIRST | LENLAST | Sub |
| John | Smith | JohnSmith | JOHN | 5 | mit |
| Mary | Johnston | MaryJohnston | MARY | 8 | ohn |
Date Functions are used to manipulate dates and perform calculations with respect to dates. The formatting of dates are amongst the most common, but also age, length of time etc are also important.
Some important data functions in Oracle include TO_DATE, MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, SYSDATE, and EXTRACT.
In MS SQL, some of the important date functions are more generic, like FORMAT, CONVERT, etc. but there is DateDiff, DateAdd, Month(), year(), and day()
SELECT sysdate FROM dual;
SELECT getDate();
would output a single-row, single-column, table, know as a scalar table, with the current date displayed.
SELECT Months_Between(to_date('05082020','mmddyyyy'), to_date('09222020','mmddyyyy')) AS NumMonths FROM dual;
would output the value -4.4516129032258064516129032258064516129. Note the negative value, so the function expects the last date first for positive values. Using the ROUND(#,2) function outside the MONTHS_BETWEEN function would produce -4.45.
The TO_DATE function is necessary when hard coding dates to ensure dates are what they were intended. '05082020' could be interpreted as May 8, or August 5, pending which country you live in (Canada and the USA would be different) and more importantly what language keyboard was selected when the operating system was installed. Most Canadian computers are setup with USA keyboards. By using the TO_DATE function, your are explicitly declaring the date and it will work using any international standard.
SELECT
DateDiff(year, Convert(date,'050820',1), Convert(date, '092220', 1)) AS yearsBetween,
DateDiff(day, Convert(date,'050820',1), Convert(date, '092220', 1)) AS daysBetween,
FROM orders;
The CONVERT function is necessary when hard coding dates to ensure dates are what they were intended. '050820' could be interpreted as May 8, or August 5, pending which country you live in (Canada and the USA would be different) and more importantly what language keyboard was selected when the operating system was installed. Most Canadian computers are setup with USA keyboards. By using the CONVERT function, your are explicitly declaring the date and it will work using any international standard. The 3rd parameter in the function is a built in date style where 1 refers to 'mmddyy'
SELECT
firstname,
birthDate,
EXTRACT(month FROM birthDate) as Mon,
EXTRACT(year FROM birthDate) as YR
EXTRACT(day FROM birthDate) as Dy
FROM employees;
| FIRSTNAME | BIRTHDATE | MON | YR | Dy |
| John | 12-Feb-72 | 2 | 1972 | 12 |
| Mary | 23-Sep-76 | 9 | 1976 | 23 |
SELECT
orderNumber,
orderDate,
Month(orderDate) as Mon,
Year(orderDate) as YR,
Day(orderDate) as Dy,
DatePart(WeekDay, orderDate) as wd,
DateName(WeekDay, orderDate) as wdName
FROM orders;
The extract function can be used in many queries to obtain values through date manipulation.