SQL top ideas on how to use built-in functions correctly

In this post I am giving top examples on SQL functions. About built-in functions, I have covered in my previous post.

List of Built-in Functions

1. DATE-TIME functions
2. Numeric functions
3. Char functions
4. Null-related functions

1. DATE/TIME functions

SELECT p_code, launch_dt, CURRENT_DATE FROM product;

CURRENT_DATE returns current date.

SELECT p_code, TO_CHAR(launch_dt, 'DD MONTH YYYY') reformatted_dt FROM product;

TO_CHAR function returns DATE in CHAR format.

2. Numeric functions

SELECT p_code, price, (price - 20), ABS(price - 20.00) FROM product;

ABS - function returns absolute value

SELECT p_code, price, ROUND (price, 1) FROM product;

ROUND function - Round to 1 digit.

SELECT p_code, price, SIGN(price - 15) FROM product;

SIGN function - It returns sign; either + or -

SELECT p_code, price, TRUNC(price, 1) FROM product;

TRUNC function - Truncates to a certain number of decimal places.

3. CHAR functions

SELECT p_code, CONCAT(CONCAT(p_name, ' -- ') , price) FROM product;

CONCAT function - concats two strings.

SELECT p_name, LOWER(p_name), UPPER(p_name) FROM product;

UPPER function returns upper case.

LOWER function returns lower case.

SELECT p_name, LENGTH(p_name) FROM product;

LENGTH function returns length of the string.

SELECT SUBSTR(phone, 3) FROM customer;

SUBSTR function returns sub string from main string.

4. Null related functions

SELECT p_name, price, min_price,
COALESCE((price * 0.9), min_price, 5.0) sale_price FROM product;

COALESCE function: returns first non-null value from the expression.

Summary

• Given list of top built-in functions
• Example SQL queries on built-in function given for you to read quickly
• These examples are almost common in any RDBMS