SQL Tester Ideas DATE, Numeric, CHAR, NULL
I have shared top SQL Date functions along with Numeric, CHAR and NULL functions.
List of Built-in Functions
- DATE-TIME functions
- Numeric functions
- Char functions
- Null-related functions
1. DATE/TIME functions
SELECT p_code,launch_dt,
CURRENT_DATE
FROM product;
CURRENT_DATE returns the CURRENT date.
SELECT p_code,
To_char(launch_dt, 'DD MONTH YYYY') reformatted_dt
FROM product;
TO_CHAR FUNCTION returns date IN char format.
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 the 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 the sign
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 a substring FROM the 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 the first non-NULL value FROM the expression.
price,
min_price,
COALESCE((price * 0.9), min_price, 5.0) sale_price
FROM product;
COALESCE FUNCTION: returns the first non-NULL value FROM the expression.
Summary
- Given a 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
Comments
Post a Comment
Thanks for your message. We will get back you.