Functions of SQL

FUNCTIONS IN SQL:

SQL having some default functions to perform calculation on data.

it is broadly classified into two types:

- aggregate functions.

- scalar funtions

AGGREGATE FUNCTIONS:

these type of functions returns a single value from the values obtained in the columns of a table.

some of the aggregate functions are:

AVG() - Returns the average value

COUNT() - Returns the number of rows

MAX() - Returns the largest value

MIN() - Returns the smallest value

SUM() - Returns the sum.

all the functions are in corporated in a single coding by taking a sample table of products:

and the result was found as:

SCALAR FUNCTION:

SQL scalar functions return a single value, based on the input value.

Useful scalar functions:

UCASE() - Converts a field to upper case

LCASE() - Converts a field to lower case

MID() - Extract characters from a text field

LEN() - Returns the length of a text field

ROUND() - Rounds a numeric field to the number of decimals specified

NOW() - Returns the current system date and time

FORMAT() - Formats how a field is to be displayed

UPPER():

this will convert the lower case values of the field to the upper case values.

and the result as follows:

lOWER():

this will convert the upper case values of the field to the lower case values. consider the coding as below with the trail table of products:

and the result was found as follows:

SUBSTRING:

this is a special function in which the values of the field can be shortened to the desired form and can be displayed, like in the above ex, we can shrink the name of the products to first two letters.

syntax: substring(fieldname,m,n)

where

m - the position of the letter to start

n- the no of letters to display from the start of the word as mentioned in m.

consider the below coding:

and i got the result as

in the above case, the the product name is shrinked to 2 letters and the starting position is taken as 5th letter of the word and from their the next letter to be displayed. but in the view, you can notice that there was only one letter in the first row, its because that the word 'beans' consist of only 5 letters and hence the 5th letter it displays as 's' and further no words are there and hence didn't display anything eventhough it was mentioned like 5th and 6th letter to be displayed.

len():

this function returns the value of the string length of each row value of a field and coding of it can be found below.

and the result found as:

round():

as the name implies, this function will round off the values on the specified column and display the output.

the coding for the above function is below mentioned:

and the result was found to be in accordance with the terms mentioned as:

date function():

SQL - DATE FUNCTIONS:

sql having several date functions for making the process on date and time, as because many things in real time was corelated with the time. for ex: the cost of gold was not a saturated one, it is flexible according to the time.

GETDATE() Returns the current date and time

DATEPART() Returns a single part of a date/time

DATEADD() Adds or subtracts a specified time interval from a date

DATEDIFF() Returns the time between two dates

CONVERT() Displays date/time data in different format

these are the various date functions available with the SQL. lets see some of it with the written code below:

and the result was found as:

in the above view, the according to the date(dd) was created with the help of 2 functions namely: getdate() and datepart().

getdate():

syntax: getdate() - returns the current date and time.

datepart():

syntax: datepart(xx,date_field) - returns the part of the date that we want.

in our example , i mentioned there as dd which implies the date part of the date and hence returned the date alone.

in the second table of the view, the product price till the date and the offer lasts for was framed by the fucntions such as dateadd() and datediff().

dateadd():

syntax: dateadd(dd,2,getdate())

where the dd represents that the date part to be incremented.

the numeral 2 represents that the date to be increased by the value of 2.

the getdate() here i used instead of that the value of date from where the increament should happen can be mentioned there. or like '2015-09-15' this can also be mentioned but be sure that the input what you are giving should be in the date format as 'yyyy-mm-dd'.

datediff():

syntax: datediff(day,'2015-09-07','2015-09-09')

i have used this function to use at the offer till lasts column. because i know like which date i have to extend the offer and hence when placing the start and end dates in the datediff fucntion, it will return the difference between the 2 values.

and finally in the third column the value is displayed in the date format by neglecting the time in it. this happens by the use of the convert().

convert():

this function helps us to convert the default date format to the required date format, by that way here i got in the format 'dd-mm-yyyy'.

syntax: convert(field_datatype, data_fields, x);

where

field_datatype - the datatype of the date field have to be mentioned usually it's a varchar.

data_field - the field of date entry has to be made as input, here i gave the input as current date using the getdate().

x - this defines to which format it has to displayed. the range starts from 0 or 100 to 131.

*****************************************************************************************