W08 Flashcards
Functions
Operations performed on data that will manipulate or convert it.
ROUND function
The ROUND function uses two parameters to determine how to round a given number.
EXAMPLE:
ROUND(12.39, 0) —–> 12
Concatenate
Combining values, by appending them to each other, to form a single longer value.
CONCAT function
This functions manipulates data to return in a string form, usually in the order inputted.
EXAMPLE:
CONCAT(fname,’ ‘, lname)
——-> Sue Jones
UPPER function
Return strings in all upper case.
EXAMPLE:
UPPER(lname) —–> JONES
LOWER function
Return all strings in lower case.
EXAMPLE:
LOWER(lname) —–> jones
LENGTH function
Returns the number of characters in a string value.
EXAMPLE:
LENGTH(password) ——> 8
SUBSTRING function
Returns a substring or part of a given string parameter. The parameters here are: 1. string, 2 start, 3. length.
EXAMPLE:
ssn = 222-33-1234
SUBSTRING(ssn, 8, 3) —–> 123
——> 123
Numeric Functions
Operates over a single number value and returns one value.
CEILING function
Will round the next highest whole number. Regardless of decimal.
EXAMPLE:
CEILING(12.2) —–> 13
FLOOR function
Will get the next lowest whole number. Regardless of decimal.
EXAMPLE:
FLOOR(12.8) —–> 12
ABS function
Returns the absolute value of a function.
EXAMPLE:
ABS(-2.3) —–> 2.3
SQRT function
Returns the square root of a function.
SQRT(25) ——> 5
YEAR, MONTH, DAY functions
These date functions take one parameter and return a value.
EXAMPLE:
YEAR(dob) —-> 1999
MONTH(dob) —–> 12
DAY(dob) ——> 20
NOW function
Returns the current local date and time from your systems clock.
EXAMPLE:
NOW( )
DATE_ADD function
Can return a date and a specific interval such as months, years or days.
EXAMPLE:
DATE_ADD(invoice_date, INTERVAL 30 DAY)
—–> returns date that is 30 days past invoice date.
DATEDIFF function
Takes two dates as parameters, it returns the difference in days between those two dates.
EXAMPLE:
DATEDIFF(NOW(), dob)
DATE_FORMAT
Returns a formatted string from a date value.
EXAMPLE:
dob = 1999-12-20
DATE_FORMAT(dob, ‘%M %d %Y’)
——> December 20, 1999
What is id?
In most database tables, id is a common column that serves as a unique identifier for each record. This column is usually the Primary Key of the table.
TIMESTAMPDIFF
Calculates full years between two dates. Best used to find whole years.
Example: TIMESTAMPDIFF(YEAR, ‘2015-06-10’, ‘2020-12-20’)
INTERVAL
Used to define a time duration that can be added or subtracted from a date or datetime value.
Example:
DATE_ADD(‘2025-03-12’, INTERVAL 6 MONTH)
– adds 6 months
TRIM
This function removes any leading or trailing spaces around the string.
SUBSTRING_INDEX
Used to extract a portion of a string, based on a delimiter, and a specified count of occurrences.
Example:
SUBSTRING_INDEX(string, delimiter, count)
