Add New Elements or Content in jQuery

JQuery provides some standard functions through which developer can easily add element/content to an existing element. User can add an element after or before an existing element by using jQuery functions. These element can also be added using callback functions. Just check some value and according to condition, we can add element easily.

Following are jQuery methods that are used to add new content:

Append method

This function insert content or new element at the end of selected html element. For example

$("#lblComment").append("last comments");

This function will add specified text after the existing text of selected label.

prepend()

This function insert content or new element at the beginning of selected html element. For example

$("#lblComment").prepend("start comments");

This function will add specified text before the existing text of selected label. We can use both these methods to add multiple content by using multiple parameters. These functions can easily get multiple parameters as shown below:

$("#lblComment").prepend("one", "two", "three");
$("#lblComment").append("eight", "nine", "ten");

After() and before()

After method inserts content after the selected html element and before method inserts content before the selected html element. More can be explained by following jQuery code fragment:

$("#lblComment").after("after adding text");
$("#lblComment").before("before adding text");

Both these functions will add the specified text after and before the selected label element respectively. Same as append and prepend, these functions can also be used for adding multiple elements to an existing.

In above examples, we have added only texts only but we can add any other element in place of that text. Just write the element’s html code and place as parameter of these functions, that element will add easily. In further article we will remove the added element/content and how to use/append css classes through jQuery code fragment.

Implementing Callback Functions in JQuery

Callback function reference will execute after the current effect/function/event finished completely. This type of function can be used after any event completion like to set an element’s value after execution of some function finished.

In earlier article we have discussed about callback function execution after finishing effect. These functions executes after current effect have been finished. As the effect completed, the function reference specified as callback will be executed.

Following code block will change the text field of element having id "testComment" as mentioned in the code. This whole process will take place after button (btnComment) click event done.

$("#btnChange").click(function(){
  $("#testComment").text(function(i,origText){
    return "Old text: " + origText + " New text: Changed! on index: " + i;
  });
});

A callback function is one which is passed as an argument in another function and which is invoked after some kind of event. The call back nature of the argument is that, once its parent method completes, the function which this argument represents is then called; that is to say that the parent method calls back and executes the method provided as an argument.

So we can perform anything after any event or any ajax request also e.g. we want to change the html of a div element after clicking on a button, the following code fragment helps us:

$("#btnChange").click(function(){
  $("#testDiv").html(function(i,origText){
    return "Old html: " + origText + " New html: This is new HTML for this div element";
  });
});

Callbacks are so-called due to their usage with pointer languages. If you don't use one of those, just understand that it is just a name to describe a method that's supplied as an argument to other method, such that when the first method is called and its method body completes, the callback method is then invoked, or in other words "called at the back" of the other function.

Get and Set Attribute’s Value in jQuery

JQuery library functions have more functionality than we can think of. All we want through jQuery can be easily implemented like to get values of any element on the web-page. Whether the value is simple text or whole html of that element.

Earlier article was about to get content of text, html and value field assigned to an element on the web-page. Element on the web-page have many attributes like id, name, title, href etc. and its corresponding value that is specific for that attribute.

JQuery library function have some in-built function that can be used to get those attributes value and developer can set those values by using other provided functions. For example the following code fragment will get href value of an anchor tag with specified selector:

alert($("#anchor").attr("href"));

This alert message can be included wherever we want like in any button’s click event or any function called as per requirement.

Developer can also set particular value for any attribute of any element. Following example will set an anchor tag’s (above code fragment) href attribute’s value:

$("#anchor").attr("href","http://dotprogramming.blogspot.com");

Same as above function we can easily set attribute’s value either one or multiple at once. Following code will assign title and href attribute’s value for the same anchor tag only in one function:

$("#anchor").attr({
    "href" : " http://dotprogramming.blogspot.com",
    "title" : "JQuery Learning Material"
  });

These functions for set an attribute’s value can be used callback functions to be execute further operation after setting the value. In the next article we will show those callback functions with syntax and execute some more operations.

Get Content and Attributes in jQuery

Getting and assigning content of elements in jQuery is very simple because of its in-built functions. JQuery provides many in-built functions that can be used to get content of any element and can set html part for any element.

JQuery library contains many DOM related functions that make it easy to use and understand how it is working. Programmer can easily manipulate content of any element or attributes by using existing functions. In rare chances programmer have to write its own functions with collection of JQuery library functions.

To get content of any element on the web-page, programmer can use following three mostly used functions:

• val(): get or set the value of forms field.
• text(): get or set the text content of selected elements.
• html(): get of set the whole content of selected elements including html markup.

Lookout the following example through which we will use all three types of functions and show the values returns by them.

$("#btn").click(function(){
alert($("#lblText1").val());
        alert($("#lblText2").text());
        alert($("#lblText3").html());
});

All the above alert messages will return respective values for selected label. The below code fragment will assign some values to all three labels:

$("#btn").click(function(){
alert($("#lblText1").val("value"));
        alert($("#lblText2").text("text"));
        alert($("#lblText3").html("html"));
});

In the next article we will get and set attribute's value for an element on the web-page.

Callback Functions Execution in jQuery

Callback functions are function which can be invoked under certain conditions. These functions executes after current effect have been finished. As the effect completed, the function reference specified as callback will be executed.

JQuery statements are executed line after line in a sequence and perform action written. In case of effects discussed earlier, next line (whatever you write) will be execute before the effect completes its part. If the next line depends on the effect then it can create errors.

Callback function reference will execute after the current effect finished completely. These functions can easily remove those errors, here is the syntax:

$(selector).show(speed, callback);

Following example will show an alert after div is shown on the button click:

$("#btnShow").click(function(){
$("#divToShow").show("slow", function(){
        alert("Div has been shown");
        });
});

In the above code a callback function have been written that will show an alert message after div will show. If we don’t write any callback function then it will show an alert message without showing the div properly.

$("#btnShow").click(function(){
$("#divToShow").show(2000);
        alert("Div has been shown");
});

This code will not wait for div to show and show an alert message specified. So these type of situations must have callback functions.

Creating Table-Valued Functions in SQL

A table-valued function returns a table as an output, which can be derived as a part of a SELECT statement. Table-valued function return the output as a table data type. The table data is a special data type used to store a set of rows, which return the result set of a table-valued function. Table-valued functions are of two types:

Inline Table-Valued Function

An inline table-valued function returns a variable of a table data type from the result set of a single SELECT statement. An inline function does not contain a function body within the BEGIN and END statements.

Consider an example where the inline table-valued function, fx_Department_GName, accepts a group name as parameter and returns the details of the departments that belong to the group from the Department table. You can create the function by using the following statement:

CREATE FUNCTION fx_Department_GName ( @GrName nvarchar (20) )
RETURNS table
AS
RETURN (
SELECT *
FROM HumanResources.Department
WHERE GroupName=@GrName
)
GO

You can use the following statement to execute the fx_Department_Gname function with a specified argument:

SELECT * FROM fx_Department_GName (‘Manufacturing’)

The preceding statement will return a result set having the group name ‘Manufacturing’.

Consider another example of an inline function that accepts rate a a parameter and returns all the records that have a rate value greater than the parameter value:

CREATE FUNCTION HumanResources.Emp_Pay (@Rate int)
RETURNS table
AS
RETURN (
SELECT e.EmployeeID, e.Title, er.Rate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS er
ON e.EmployeeID=er.EmployeeID WHERE er.Rate<@Rate
)
GO
The preceding function will return a result set that displays all the records of the employees who have the pay rate greater that the parameter.

Multistatement Table-Valued Function

A Multistatement table-valued function uses multiple statements to build the table that is returned to the calling statement. The function body contains a BEGIN…END block, which holds a series of T-SQL statements to build and insert rows into a temporary table. The temporary table is returned in the result set and is created based on the specification mentioned in the function.

Consider an example where the Multistatement table-valued function, PayRate, is created to return a set of records from the EmployeePayHistory table by using the following statements:

CREATE FUNCTION PayRate (@rate money)
RETURNS @table TABLE
(EmployeeID int NOT NULL,
RateChangeDate datetime NOT NULL,
Rate money NOT NULL,
PayFrequency tinyint NOT NULL,
modifiedDate datatime NOT NULL)
AS
BEGIN
INSERT @table
SELECT *
FROM HumanResources.EmployeePayHistory
WHERE Rate > @rate
RETURN
END

The function returns a result set in from of a temporary table, @table, created within the function. You can execute the function by using the following statement:

SELECT * FROM PayRate (45)

Depending on the result set returned by a function can be categorized as deterministic or nondeterministic. Deterministic functions always return the same result whenever they are called with a specific set of input values. However, nondeterministic function may return different results each time they are called with a specific set of input values.

An example of a deterministic function is date add, which returns the same result for any given set of argument values for its three parameters. Get date is a nondeterministic function because it is always invoked without any argument, but the return value changes on every execution.

Creating Scalar functions in SQL

In earlier article we have discussed about user defined functions that have the limited scope in sql programming in compare to stored procedures. User defined function have two types i.e. scalar function and table-valued function. In this article we will discuss about scalar functions.

Scalar function accept a single parameter and return a single data value of the type specified in the RETURNS clause. A scalar function can return any data type except text, ntext, image, cursor, and timestamp. Some scalar functions, such and current_timestamp, do not require any arguments.

A function contains a series of T-SQL statements defined in a BEGIN…END block of the function body that returns a single value.

Consider an example of a scalar function that calculates the monthly salary of the employees accepting the pay rate as an input and returning a single value after multiplying the value with the number of hours and number of days:

CREATE FUNCTION HumanResources.MonthlySal (@PayRate float)
RETURN float
AS
BEGIN
RETURN (@PayRate * 8 * 30)
END

You can execute this function by using the following statements:

DECLARE @PayRate float
SET @PayRate = HumanResources.MonthlySal (12.25)
PRINT @PayRate

In the preceding code, @PayRate is a variable that will store a value returned by the MonthlySal function.

How to Implement User Defined Function in SQL

Similar to the stored procedures, you can also create functions to store a set of T-SQL statements permanently. These functions are also referred to as user-defined functions (UDFs). A UDF is a database object that contains a set of T-SQL statements, accepts parameters, performs an action, and returns the result of that action as a value. The return value can either be a single scalar value or a result set.

UDFs have a limited scope as compared to stored procedures. You can create functions in situations when you need to implement a programming logic that does not involve any permanent changes to the database objects outside the function. For example, you cannot modify a database table from a function.

UDFs are of different types: scalar functions and table-valued function. As a database developer, it is important for you to learn to create and manage different types of UDFs.

Creating UDFs

A UDF contains the following components:

• Function name with optional schema/owner name
• Input parameter name and data type
• Options applicable to the input parameter
• Return parameter data type and optional name
• Options applicable to the return parameter
• One or more T-SQL statements

To create a function, you can use th CREATE FUNCTION statement. The syntax of the CREATE FUNCTION statement is:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [AS ] [ type_schema_name. ]
Parameter_data_type] }
[ = default ] }
[, …n ]
]
)
RETURNS return_data_type
[WITH <function_option> [ , . . .n ] ]
[ AS ]
BEGIN
Function_body
RETURN expression
END
[;]
Where,

• Schema_name is the name of the schema to which the UDF belongs.
• Function_name is the name of the UDF. Function names must comply with the rules for identifiers and must be unique within the database and to its schema.
• @parameter_name is a parameter in the UDF. One or more parameters can be declared.
• [type_schema_name.] parameter_data_type is the data type of the parameter, and optionally the schema to which it belongs.
• [=default ] is a default value for the parameter.
• Return_data_type is the return value of a scalar user-defined function.

How to Modify XML Data using Functions in SQL Server

Similar to any other type of data, programmer might also need to modify the XML data. To modify data, you can use the modify function provided by the XML data type of the SQL Server. The modify function specifies an XQuery expression and a statement that specifies the kind of modification that needs to be done.

This function allows you to perform the following modifications:

• Insert: Used to add nodes to XML in an XML column or variable. For example, the management of AdventureWorks wants to add another column specifying the type of customer, in the CustDetails table. The default value in the Type column should be ‘Credit’. To resolve this problem, the database developer of AdventureWorks will create the following query:
  
  UPDATE CusomtDetails SET Cust_Details.modify (‘ inser attribute Type{“Credit”} as first into (/Customer) [1]’)
  
• Replace: Used to update the XML data. For example, James Stephen, one of the customers of AdventureWorks, has decided to change his customer type from Credit to Cash. As a database developer, you can create the following query to reflect this change:
  
• Delete: Used to remove a node from the XML data. For example, the management of AdventureWorks has decided to remove the ‘City’ column from the customer details. You can write the following query to display the results:
  
  UPDATE CustomDetails SET Cust_Details.modify (‘delete (/Customer/@City) [1]’)

Retreive XML data using XQuery

How to Perform Grouping of Data Matching a Criteria: SQL Programming

The database users might need to view data in a user-defined format. These reports might involve summarizing data on the basis of various criteria. SQL Server allows you to generate summarized data reports using the PIVOT clause of SELECT statement.

The PIVOT operator is used to transform a set of columns into values. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. In addition, it also performs aggregations on the remaining column values if required in the output. Following is the syntax of PIVOT operator is:

SELECT * from table_name
PIVOT (aggregation_function (value_column)
FOR pivot_column
IN (column_list)
) table_alias

Where,

• Table_name: name of table on which query will execute.
• Pivot_column: the only column on which condition perform.
• Table_alias: alias name of the table used in query only.

Consider an example, you want to display the number of purchase orders placed by certain employees, laid down with the vendors. The following query provides this report:

SELECT VendorID, [164] AS Empl, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5 FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198],[223], [231], [233] )
) AS pvt
ORDER BY VendorID

Following output is displayed by the preceding statements.

How to use Compute and ComputeBy Clauses in Query: SQL Programming

The COMPUTE clause, with the SELECT statement, is used to generate summary rows by using aggregate functions in the query results, in sql programming. The COMPUTE BY clause can be used to calculate summary values of the result set on a group of data. The column on which the data is to be grouped is mentioned after the BY keyword.

The GROUP BY clause is used to generate a group summary report and does not produce individual table rows in the result set, whereas the COMPUTE and COMPUTE BY clauses generate the summary report with individual data rows from the table. In other words, the COMPUTE clause is used for control-break summary reporting applications that generate detailed information in the result set.

Syntax:

SELECT column_list
FROM table_name
ORDER BY column_name
COMPUTE aggregate_function (column_name) [, aggregate_function(column_name) …]
[BY column_name [, column_name]…]

Where,

• ORDER BY column_name specifies the name of the column(s) by which data in the result is to be sorted.
• COMPUTE aggregate_function specifies any row aggregate function from the aggregate function list.
• Column_name specifies the name of the column(s) for which the summary report is to be displayed.
• BY column_name specifies the name of the column(s) by which data is to be grouped.

The following SQL query uses the COMPUTE BY clause to calculate the average sickLeaveHours and VacationHours from the Employee table and display them based on Title, VacationHours, and SickLeaveHours:

SELECT Title, 'Average VacationHours' = VacationHours, 'Average SickLeaveHours' = SickLeaveHours FROM HumanResources.Employee
WHERE Title IN ('Recruiter', 'Stocker')
ORDER BY Title, VacationHours, SickLeaveHours
COMPUTE avg(VacationHours), Avg (SickLeaveHours) BY Title

In the preceding query, the data of the VacationHours and SickLeaveHours column is grouped for the recruiter and stocker and the summation of the vacation hours and sick leave hours for both is retrieved, as shown in the following figure.

Consider another example, where you need to use the COMPUTE BY clause to calculate the subtotals of VacationHours and SickLeaveHours for each value in the Designation column. The COMPUTE clause calculates the grand total of VacationHours and SickLeaveHours:

SELECT Title, 'Total VacationHours' = VacationHours, 'Total SickLeaveHours' = SickLeaveHours
FROM HumanResources.Employee
WHERE Title IN ('Recruiter', 'Stocker')
ORDER BY Title, VacationHours, SickLeaveHours
COMPUTE sum (VacationHours), sum(SickLeaveHours) BY Title COMPUTE sum (VacationHours) , sum (SickLeaveHours)

In the preceding example, initially the data of the VacationHours and SickLeaveHours column is grouped for the recruiter and stocker and the summation of the vacation hours and sick leave hours for the recruiter and stocker.

Both these keywords are not supported in the SQL Server management studio 2012, these queries can be executed in 2008 version.

How to Perform Grouping of Data Matching a Criteria: SQL Programming

At times, you need to view data matching specific criteria to be displayed together in the result set. For example, you want to view a list of all the employees with details of employees of each department displayed together.

Grouping can be performed by following clauses:

• GROUP BY
• COMPUTE and COMPUTED BY
• PIVOT

GROUP BY

The GROUP BY clause summarizes the result set into groups as defined in the query by using aggregate functions. The HAVING clause further restricts the result set to produce the data based on a condition. The syntax of the GROUP BY clause is:

SELECT column_list
FROM table_name
WHERE condition
[GROUP BY [ALL] expression [, expression]
[HAVING search_condition]

Where,

• ALL is a keyword used to include those groups that do not meet the search condition.
• expression specifies the column name(s) or expression(s) on which the result set of the SELECT statement is to be grouped.
• search_condition is the conditional expression on which the result is to be produced.

The following SQL query returns the minimum and maximum values of vacation hours for the different types of titles when the vacation hours are greater than 20:

SELECT JobTitle, Minimum = min (VacationHours), Maximum = max (VacationHours)
FROM HumanResources.Employee
WHERE VacationHours > 20 GROUP BY JobTitle

Outputs:

The GROUP BY ……..HAVING clause is same as the SELECT….WHERE clause. The result set produced with the GROUP BY clause eliminates all the records and values that do not meet the condition specified in the HAVING clause. The GROUP BY clause collects data that matches the condition, and summarizes it into an expression to produce a single value for each group. The HAVING clause eliminates all those groups that do not match the condition.

The following SQL query retrieves all the titles along with their average vacation hours when the vacation hours are more than 30 and the group average value is greater than 55:

SELECT Title, ‘Average Vacation Hours’ = avg (VacationHours) FROM HumanResources.Employee WHERE VacationHours > 30 GROUP BY Title HAVING avg (VacationHours) >55

The ALL keyword of the GROUP BY clause is used to display all groups, including those which are excluded by the WHERE clause. The ALL keyword is meaningful to those queries that contain the WHERE clause. If ALL is not used, the GROUP BY clause does not show the groups for which there are no matching rows. However, the GROUP BY ALL shows all rows, even if the groups have no rows meeting the search conditions.

The following SQL query retrieves the records for the employee titles that are eliminated in the WHERE condition:

SELECT Title, VacationHours = sum (VacationHours) FROM HumanResources.Employee WHERE Title IN (‘Recruiter’, ‘Stocker’,’Design Engineer’) GROUP BY ALL Title

How to Get Summarizing Data from Database: SQL Programming

Summary of the data contains aggregated values that help in data analysis at a broader level. For example, to analyse the sales, the users might want to view the average sales or total sales for a specified time period. The SQL Server provides aggregate functions to generate summarized data.

The users might also want to view the summarized data in different group based on specific criteria. For example, the users want to view the average sales data region-wise or product-wise. In such a case, the sales data of each region will be displayed together. You can group the data by using the GROUP BY clause of the SELECT statement. You can also use aggregate function to summarize data when grouping it.

Summarizing Data by Using Aggregate Functions

At times, you need to calculate the summarized values of a column based on a set of rows. For example, the salary of employees is stored in the Rate column of the EmployeePayHistory table and you need to calculate the average salary earned by the employees.

The aggregate functions, on execution, summarize the values for a column or a group of columns, and produce a single value. The syntax of an aggregated function is:

SELECT aggregate_function ([ALL|DISTINCT] expression) FROM table_name

Where,

• All specifies that the aggregate function is applied to all the values in the specified column.
• DISTINCT specifies that the aggregate function is applied to only unique values in the specified column.
• expression specifies a column or an expression with operators.

Avg Returns the average of values on a numeric expression, either all or distinct.
Count: Returns the number of values in an expression, either all or distinct. The count function also accepts (*) as its parameter, but it counts the number of rows returned by the query.

Min Returns the lowest value in the expression. The following SQL query retrieves the minimum value from the Rate column of the EmployeePayHistory table with a user-defined heading:

SELECT ‘Minimum Rate’ = min (Rate) FROM HumanResources.EmployeePayHistory

Max Return the highest value in the expression.

Sum Returns the sum total of values in a numeric expression, either all or distinct. 

System Functions used to Query System Tables in Sql Server: SQL Programming

In SQL Programming, system functions are used to query on system tables. Programmer can easily perform all type of system functions to generate sequential numbers for each row based on specific criteria.

The system functions are used to query the system tables. System tables are a set of tables that are used by the SQL Server to store information about users, databases, tables and security. The system functions are used to access the SQL Server databases or user-related information. For example, to view the host ID of the terminal on which you are logged onto, you can use the following query:

SELECT host_id ( )

The following table lists the system function provided by SQL Server

• host_id (), Returns the current host process ID number of a client process
• host_name (), Returns the current host computer name of a client process
• suser_sid ([‘login_name’]), Returns the security identification (SID) number corresponding to the log on name of the user
• suser_id ([‘login_name’]), Returns the log on identification (ID) number corresponding to the log on name of the user
• suser_sname ([server_user_id]), Returns the log on name of the user corresponding to the security identification number
• user_id ([‘name_in_db’]), Returns the database identification number corresponding to the user name
• user_name ([user_id]), Returns the user name corresponding to the database identification number
• db_id ([‘db_name’]), Returns the database identification number of the database
• db_name ([db_id]), Returns the database name
• object_id (‘objname’), Returns the database object ID number
• object_name (‘obj_id), Returns the database object name

Ranking Functions to Generate Sequential Numbers in Sql Server: SQL Programming

In SQL Programming, ranking functions are used to operate with numeric values. Programmer can easily perform all type of ranking functions to generate sequential numbers for each row based on specific criteria.

You can use ranking functions to generate sequential numbers for each row or to give a rank based on specific criteria. For example, in a manufacturing organization, the management wants to rank the employees based on their salary. To rank the employees, you can use the rank function.

Ranking function return a ranking value for each row. However, based on the criteria, more than one row can get the same rank. You can use the following functions to rank the records:

• row_number
• rank
• dense_rank

All these functions make use of the OVER clause. This clause determines the ascending or descending sequence in which rows are assigned a rank. The row_number function returns the sequential numbers, starting at 1, for the rows in a result set based on a column.

For example, the following SQL query displays the sequential number on a column by using the row_number function:

SELECT BusinessEntityID, Rate, row_number ( ) OVER (ORDER BY Rate desc) AS RANK 
FROM HumanResources.EmployeePayHistory

The following figure displays the output of the preceding query.

dense_rank Function

The dense_rank( ) function is used where consecutive ranking values need to be given based on a specified criteria. It performs the same ranking task as the rank function, but provides consecutive ranking values to an output.

For example, you want to rank the products based on the sales done for that product during a year. If two products A and B have same sale values, both will be assigned a common rank. The next product in the order of sales values, both will be assigned a common rank. The next product in the order of sales values would be assigned the next rank value.

If in the preceding example of the rank function, you need to give the same rank to the employees with the same salary rate and the consecutive rank to the next one. You need to write the following query:

SELECT BusinessEntityID, Rate, dense_rank( ) OVER (ORDER BY Rate desc) AS rank 
FROM HumanResources.EmployeePayHistory

Mathematical Functions to Work with Numerical Values in Sql Server: SQL Programming

In SQL Programming, mathematical functions are used to operate with numeric values. Programmer can easily perform all type of scientific functions as well as simple arithmetic operations using these mathematical functions.

Programmer can use mathematical functions to manipulate the numeric values in a result set. You can perform various numeric and arithmetic operations on the numeric values. For example, you can calculate the absolute value of a number or you can calculate the square or square root of a value.

The following table lists the mathematical functions provided by SQL Server 2005.

• Abs, Returns an absolute value
• Acos, asin and atan, returns the angle in radians whose cosine, sine, or tangent is a floating-point value
• Cos, sin, cot and tan, returns the cosine, sine, cotangent, or tangent of the angle in radians
• Degrees, returns the smallest integer greater than or equal to the specified value
• Exp, returns the exponential value of the specified value
• Floor, returns the largest integer less than or equal to the specified volume
• Log, returns the natural logarithm of the specified value
• Log10, returns the base-10 logarithm of the specified value
• Pi, returns the constant value of 3.141592653589793
• Power, returns the value of numeric_expression to the value of y
• Radians, converts from degrees to radians
• Rand, returns a random float number between 0 and 1
• Round, returns a numeric expression rounded off to the length specified as an integer expression
• Sign, returns positive, negative, or zero
• Sqrt, returns the square root of the specified value

For example, to calculate the round off value of any number, you can use the round mathematical function. The round mathematical function calculates and returns the numeric value based on the input values provided as an argument.

The syntax of the round function is:

round (numeric_expression, length)

where

• numeric_expression is the numeric expression to be rounded off.
• Length is the precision to which the expression is to be rounded off.

The following SQL query retrieves the EmployeeID and Rate for a specified employee id from the EmployeePayHistory table:

SELECT BusinessEntityID, 'Hourly Pay Rate' = round (Rate, 2)

FROM HumanResources.EmployeePayHistory WHERE BusinessEntityID =3

In the result set, the value of the Rate column is rounded off to two decimal places.

While using the round function, if the length is positive, then the expression is rounded to the right of the decimal point. If the length is negative then the expression is rounded to the left of the decimal point. SQL Server provides the following usage of the round function.

• Round (1234.567, 2) outputs 1234.570
• Round (1234.567, 1) outputs 1234.600
• Round (1234.567, 0) outputs 1235.000
• Round (1234.567, -1) outputs 1230.000
• Round (1234.567, -2) outputs 1200.000
• Round (1234.567, -3) outputs 1000.000

Use Date Functions to Operate with Date Values in Sql Server: SQL Programming

In SQL Programming, programmer can use the date functions of the SQL Server to manipulate date-time values. You can either perform arithmetic operations on date values or parse the date values. Date parsing includes extracting components, such as the day, the month, and the year from a date value.

Programmer can also retrieve the system date and use the value in the date manipulation operations. To retrieve the current system date, you can use the getdate function. The following statement displays the current date:

SELECT getdate ( )

The following SQL query uses the datediff function to calculate the difference between the current date and the date of birth of employees in AdventureWorks, Inc. The date of birth of employees is stored in the BirthDate column of the Employee table.

SELECT datediff (yy, BirthDate, getdate()) AS 'Age'
FROM HumanResources.Employee

Outputs:

The following table lists the date functions provided by SQL Server.

• dateadd, adds the number of date parts to the date
• datediff, calculates the number of date parts between two dates
• datename, returns date part from the listed date, as a character value (for example, October)
• datepart, returns date part from the listed date as an integer
• getdate(), returns the current date and time, day, (date), returns an integer, which represents the day getutcdate, returns the current date of the system in Universal Time Coordinate (UTC) time. UTC time is also known as the Greenwich Mean Time (GMT)
• month, returns an integer, which represents the month
• year, returns an integer which represents the year

SQL Server provides the following abbreviation and values of the datepart function

• Year, Abbreviation -- (yy,yyyy),  may have values (1753-9999)
• Qartr, Abbreviation -- (qq, q), may have values (1-4)
• Month, Abbreviation -- (mm, m), may have values (1-12)
• Day of year, Abbreviation -- (dy, y), may have values (1-366)

Use String Functions to Manipulate String Values in Sql Server: SQL Programming

In Sql Programming, programmer can use the string functions to manipulate the string values in the result set. There are list of string functions used in sql server and explained in this article. For example, to display only the first eight characters of the values in a column, you can use the left ( ) string function.

String functions are used with the char and varchar data types. The SQL Server provides string functions that can be used as a part of the character expression. These functions are used for various operations on string.

Syntax:
    SELECT function_name (parameters)

Where

• Function_name is the name of the function
• parameters are the required parameters for the string function.

The following table lists the string functions provided by SQL Server

• Ascii, returns the ASCII code of the leftmost character, e.g. SELECT ascii (‘ABC’) will return ascii code of 'A'.
• Char, return the character equivalent of the ASCII code value, e.g. SELECT char (65)   
• Charindex, returns the starting position of the specified pattern in the expression e.g. SELECT charindex (‘E’, ‘HELLO’)
• Difference, compares two strings and evaluates the similarity between them, returning a value from 0 through 4. The value 4 is the best match e.g. SELECT difference (‘HELLO’, ‘hell’)
• Left, returns apart of the character string equal in size to the integer_expression    characters from the left e.g. SELECT left(‘RICHARD’, 4) will return RICH
• Len, returns the number of characters in the character_expression e.g. SELECT len(‘RICHARD’)
• Lower, returns after converting character_expression to lower case e.g. SELECT lower (‘RICHARD’)
• Ltrim, removes leading blanks from the character expression e.g. SELECT ltrim (‘RICHARD’)
• Patindex, returns staring position of the first occurrence of the pattern in the specified expression, or zeros if the pattern is not found e.g. SELECT patindex (‘%BOX%’, ‘ACTIONBOX’)
• Reverse, returns reverse of the character_expression e.g. SELECT reverse (‘ACTION’)
• Right, returns a part of the character string, after extracting from the right the number of characters specified in the integer_expression e.g. SELECT right (‘RICHARD’, 4) will return HARD
• Rtrim, returns after removing any trailing blanks from the character expression e.g. SELECT rtrim (‘RICHARD   ’)
• Space, spaces are inserted between the first and second word e.g. SELECT ‘RICHARD’+space (2)+’HILL’, will add two spaces between 1st and 2nd word.
• Str, converts numeric data to character data where the length is the total length, including the decimal point, the sign, the digits, and the spaces and the decimal is the number of places to the right of the decimal point e.g. SELECT str (123.45, 6, 2)
• Stuff, deletes the number of characters as specified in the character_expression1 from the start and then inserts char_expression2 into character_expression1 at the start position e.g. SELECT stuff (‘Weather’, 2,2, ‘I’) will returns ‘wither’.
• Substring, returns the part of the source character string from the start position of the expression e.g. SELECT substring (‘weather’, 2,2) will return ‘ea’.
• Upper, converts lower case characters to upper case e.g. SELECT upper (‘Richard’)

The following SQL query uses the upper string function to display data in uppercase. The Name, DepartmentID, and GroupName columns are retrieved from the Department table and the data of the Name column is displayed in uppercase with a user-defined heading, Department Name:
 

SELECT 'Department Name' = upper (Name), DepartmentID, GroupName
FROM HumanResources.Department
 

Outputs:

How to Customize the Result Set using Functions in SQL Server: SQL Programming

SQL server provides some in-built functions to hide the steps and the complexity from other code. Generally in sql programming, functions accepts parameters, perform some actions and return a result.

While querying data from SQL Server, programmer can use various in-built functions to customize the result set. Some of the changes includes changing the format of the string or date values or performing calculations on the numeric values in the result set. For example, if you need to display all the text values in uppercase, you can use the upper () string function. Similarly, if you need to calculate the square of the integer values, you can use the power ( ) mathematical function.

Depending on the utility, the in-built functions provided by SQL Server are categorized as listed below:

• String functions
• Date function
• Mathematical functions
• Ranking functions
• System function.

All these functions are for specific use in sql programming like string functions are used to manipulate the string in result set, to manipulate date values date functions are used, as so on. Arithmetic operations can also be performed with these functions like add, subtract operations on any of the above listed type of functions.

Functions can be easily used anywhere in the sql programming to build the software composable. Programmer can use these functions in constraints, computed columns, where clauses even in other functions. Overall the result, functions are powerful part in sql server.

Further article will describe about the use and example of above listed functions.