Algorithm to find maximum of two unequal numbers

Problem: Algorithm to find maximum of two unequal numbers and use the same to find maximum of four unequal numbers.

Input: For sub-algorithm 2 numbers and for main algorithm 4 numbers.

Output : Returns the maximum of 4 numbers

MAX_OF_2(NUM1, NUM2)      [Sub-algorithm]
[NUM1 and NUM2 are two numbers]
If(NUM1>NUM2) Then:
Returns NUM1
Else:
Return NUM2
[End of If]
Exit.
MAX_OF_4(NUM1, NUM2, NUM3, NUM4)
[NUM1, NUM2, NUM3, and NUM4 are numbers]
TEMP1 <-- MAX_OF_2(NUM1, NUM2)
TEMP2 <-- MAX_OF_2(NUM3, NUM4)
MAX   <-- MAX_OF_2(TEMP1, TEMP2)
Return MAX
Exit.

Design an algorithm to find average of salary

This example is little complex. You will understand it more clearly after reading the user defined data types.
Problem: Design an algorithm to find average of SALARY in an array of EMP struct containing information EMPNO, EMPNAME, and SALARY.
Input: A list employees of the type struct.
Output : Display the average of SALARY.

AVG_SALARY(LIST, SIZE)
[LIST is an array of EMP, SIZE is size of array]
SUM<-- 0
Repeat For I=1,2,3,........SIZE
SUM<--SUM +LIST[I].SALARY
[END of For I]
AVG <-- SUM / SIZE
Write :  'The average Salary is', AVG
Exit

Note : An algorithm, that perform sub-task, may be called in another algorithm. It is a better practice to divide the given problem into sub-problems ans write the individual algorithm to solve such sub-problems. Write the main algorithm to call the sub-algorithms in order to solve the main problem.

Design an algorithm to find the average

Problem :  Design an algorithm to find the average of a subject marks of 'N' number of students.
Input: A list of marks and number of students.
Output: Returns the average marks calculated.

AVG_OF_MARKS(LIST,N)
[LIST is an array containing marks, N is the size of array]
SUM <-- 0
Repeat For I=1,2,3,.........N
SUM<-- SUM+LIST[I]
[END of For I]
AVG <-- SUM/N
Returns AVG
Exit

Design an algorithm to find the grade on the basis of the following criteria

Marks Obtained                                                                                       Grade
85 or above 85                                                                                             S
75 or above 75                but less than 85                                                    A
65 or above 65                but less than 75                                                    B
55 or above 55                but less than 65                                                    C
50 or above 50                but less than 55                                                    D
Less than 50                                                                                                 F

Input:      Marks obtained
Output  : Returns the grade on the basis of given criteria

GRADE(M)
[M is the marks obtained]
if(M>= 85) Then:
Returns 'S'
Else:
If (M>= 75) Then:
Returns 'A'
Else:
If(M>=65) Then:
Returns 'B'
Else:
If(M>= 55) Then:
Returns 'C'
Else
If(M>=50) Then:
Returns 'D'
Else
Returns 'F'
[End of If]
[End of If]
Exit.

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.

Design an algorithm to compare two numbers

Input : Two numbers that are to be compared
Output : Messages of comparison

COMPARE (N1, N2)
[N1 and N2 are the numbers]
If(N1==N2) Then :
Write : 'Both the numbers are equal'
Else:
If (N1<N2) Then:
Write : 'First number is smaller then second number'
Else:
Write : 'First number is bigger number than second number'
[End of If]
[End of If]
Exit