ROYAL UNIVERSITY OF PHNOM PENH
DATABASE II
Chapter 8
How to use functions (Lab)
Lecturer: Mr. Chhim Bunchhun,
chhim.bunchhun@rupp.edu.kh, 093 222 380
A SELECT statement that uses three functions
SELECT vendor_name,
CONCAT_WS(', ', vendor_contact_last_name,
vendor_contact_first_name) AS contact_name,
RIGHT(vendor_phone, 8) AS phone
FROM vendors
WHERE LEFT(vendor_phone, 4) = '(559'
ORDER BY contact_name
Chapter 8: How to use functions 2
How to sort by a string column that contains numbers (part 1)
Sorted by the emp_id column
SELECT *
FROM string_sample
ORDER BY emp_id
Chapter 8: How to use functions 3
How to sort by a string column that contains numbers (part 2)
Sorted by the emp_id column explicitly cast as an integer
SELECT *
FROM string_sample
ORDER BY CAST(emp_id AS SIGNED)
Chapter 8: How to use functions 4
How to sort by a string column that contains numbers (part 3)
Sorted by the emp_id column implicitly cast as an integer
SELECT *
FROM string_sample
ORDER BY emp_id + 0
Chapter 8: How to use functions 5
How to sort by a string column that contains numbers (part 4)
Sorted by the emp_id column after it has been padded with leading zeros
SELECT LPAD(emp_id, 2, '0') AS emp_id, emp_name
FROM string_sample
ORDER BY emp_id
Chapter 8: How to use functions 6
How to use the SUBSTRING_INDEX function to parse a string
SELECT emp_name,
SUBSTRING_INDEX(emp_name, ' ', 1) AS first_name,
SUBSTRING_INDEX(emp_name, ' ', -1) AS last_name
FROM string_sample
Chapter 8: How to use functions 7
How to use the LOCATE function to find a character in a string
SELECT emp_name,
LOCATE(' ', emp_name) AS first_space,
LOCATE(' ', emp_name, LOCATE(' ', emp_name) + 1)
AS second_space
FROM string_sample
Chapter 8: How to use functions 8
How to use the SUBSTRING function to parse a string
SELECT emp_name,
SUBSTRING(emp_name, 1, LOCATE(' ', emp_name) - 1)
AS first_name,
SUBSTRING(emp_name, LOCATE(' ', emp_name) + 1)
AS last_name
FROM string_sample
Chapter 8: How to use functions 9
The Float_Sample table
A search for an exact value that doesn’t include two approximate values
SELECT *
FROM float_sample
WHERE float_value = 1
Chapter 8: How to use functions 10
How to search for approximate values
Search for a range of values
SELECT *
FROM float_sample
WHERE float_value BETWEEN 0.99 AND 1.01
Search for rounded values
SELECT *
FROM float_sample
WHERE ROUND(float_value, 2) = 1.00
Chapter 8: How to use functions 11
The contents of the Date_Sample table with times
A SELECT statement that fails to return a row
SELECT *
FROM date_sample
WHERE start_date = '2018-02-28'
Chapter 8: How to use functions 12
Three techniques for ignoring time values
Search for a range of dates
SELECT *
FROM date_sample
WHERE start_date >= '2018-02-28'
AND start_date < '2018-03-01'
Search for month, day, and year integers
SELECT *
FROM date_sample
WHERE MONTH(start_date) = 2 AND
DAYOFMONTH(start_date) = 28 AND
YEAR(start_date) = 2018
Search for a formatted date
SELECT *
FROM date_sample
WHERE DATE_FORMAT(start_date, '%m-%d-%Y') = '02-28-2018'
Chapter 8: How to use functions 13
Examples that ignore date values
Search for a time that has been formatted
SELECT * FROM date_sample
WHERE DATE_FORMAT(start_date, '%T') = '10:00:00'
Search for an hour of the day
SELECT * FROM date_sample
WHERE HOUR(start_date) = 9
Search for a range of times
SELECT * FROM date_sample
WHERE EXTRACT(HOUR_MINUTE FROM start_date)
BETWEEN 900 AND 1200
Chapter 8: How to use functions 14
The syntax of the simple CASE function
CASE input_expression
WHEN when_expression_1 THEN result_expression_1
[WHEN when_expression_2 THEN result_expression_2]...
[ELSE else_result_expression]
END
A statement that uses a simple CASE function
SELECT invoice_number, terms_id,
CASE terms_id
WHEN 1 THEN 'Net due 10 days'
WHEN 2 THEN 'Net due 20 days'
WHEN 3 THEN 'Net due 30 days'
WHEN 4 THEN 'Net due 60 days'
WHEN 5 THEN 'Net due 90 days'
END AS terms
FROM invoices
Chapter 8: How to use functions 15
The syntax of the searched CASE function
CASE
WHEN conditional_expression_1
THEN result_expression_1
[WHEN conditional_expression_2
THEN result_expression_2]...
[ELSE else_result_expression]
END
A statement that uses a searched CASE function
SELECT invoice_number, invoice_total, invoice_date,
invoice_due_date,
CASE
WHEN DATEDIFF(NOW(), invoice_due_date) > 30
THEN 'Over 30 days past due'
WHEN DATEDIFF(NOW(), invoice_due_date) > 0
THEN '1 to 30 days past due'
ELSE 'Current'
END AS invoice_status
FROM invoices
WHERE invoice_total - payment_total - credit_total > 0
Chapter 8: How to use functions 16
The syntax of the IF function
IF(test_expression, if_true_expression, else_expression)
A SELECT statement that uses the IF function
SELECT vendor_name,
IF(vendor_city = 'Fresno', 'Yes', 'No')
AS is_city_fresno
FROM vendors
Chapter 8: How to use functions 17
The syntax of the IFNULL function
IFNULL(test_expression, replacement_value)
A SELECT statement that uses the IFNULL function
SELECT payment_date,
IFNULL(payment_date, 'No Payment') AS new_date
FROM invoices
Chapter 8: How to use functions 18
THANK YOU
for your attention !
