SQL FUNCTIONS TO MANAGE DATA IN SNAPLOGIC

MySQL function?

SQL functions are simply sub-programs, which are commonly used and re-used throughout SQL database applications for processing or manipulating data.?

Why use functions??

If data manipulation is handled by Business layer, we need to implement other interfaces, thereby increasing re-work and risk of data inconsistency.?

Types of Functions?

MySQL – Execute Snap?

We can use the query in SQL statement which is in the above image?

Sample Table?

Table Name: student_details?

1. SCALAR FUNCTION?

A) CONCAT: The CONCAT() function can combine multiple strings together into a single string. For example, if we want to combine first name and last name, we can use this function?

SELECT CONCAT(FirstName,’ ’,LastName) as FullName FROM student_details?        

B) UCASE: The UCASE() function converts a string to upper-case. For example, they? want the LastName as uppercase, we can use this.?

SELECT UCASE(LastName) as LastName FROM student_details?        

C) LCASE: The LCASE() function converts a string to lower-case. For example, if they? give email as capital then we can convert to lowercase.?

SELECT LCASE(Email) as Email FROM student_details?        

D) REPLACE: The REPLACE() function replaces all occurrences of a sub-string within?? a string, with a new sub-string.?

Note: This function performs a case-sensitive replacement.?

SELECT REPLACE(Knowledge,’SQL’,’MySQL’) as Knowledge FROM???? student_details?        

2. AGGREGATE FUNCTION?

A) COUNT: The COUNT() function returns a number of records by a select query. For example, to get how many records we have.?

Note: NULL values are not counted.?

SELECT COUNT(FirstName) as Total FROM student_details?        

B) MAX: The MAX() function is to get highest value in the set of values. For example, if we need the highest score from the table.?

SELECT MAX(CGPA) as Top_Score FROM student_details?        

C) MIN: The MIN() function is to get lowest value in the set of values. For example, if we need the lowest score from the table.?

SELECT MIN(CGPA) as Least_Score FROM student_details?        

D) AVG: The AVG() function is to get Average value in the set of values. For example, if we need the Average score from the table.?

SELECT AVG(CGPA) as Average FROM student_details?