Design an algorithm to find the GCD and LCM of two positive numbers

Problem : Design an algorithm to find the GCD (Greatest Common Divisor) of two positive numbers and use the same to find LCM of two positive numbers.

Input: Two Numbers
Output : Least Common Multiple of two numbers.

GCD(NUM1, NUM2)
Rem <-- NUM1 % NUM2  [% Modulus Operator]
Repeat While Rem <> 0
NUM1 <-- NUM2
NUM2 <-- Rem
Rem <-- NUM1 % NUM2
[End of While]
Return NUM2
Exit.

LCM(NUM1, NUM2)
Return (NUM1 * NUM2) / GCD( NUM1, NUM2)
Exit.

NOTE : Here in this example algorithm, finding LCM in the main problem that can be divided into sub-problem like finding the GCD first and using the same to solve the main problem. So, GCD algorithm is written first and than the other algorithm that can be divided into small problems and algorithm can be written to solve such small problems.

Tracing

Suppose that the LCM algorithm is called using 12 and 16. The algorithm calls GCD with 12 and 16.

Rem <-- 12 % 16 i.e. Rem= 12
Rem is not equal to zero. So, NUM1=16 and NUM2 =12
Rem<-- 16%12 i.e. Rem=4
Rem is not equal to Zero. So, NUM1 =12 and NUM2 =4
Rem <-- 12 % 4 i.e. Rem=0
Rem is equal to Zero.
So, GCD algorithm returns NUM2 that is 4.

When the algorithm LCM gets the result from GCD algorithm its find the expression value
(NUM1 * NUM2) / GCD (NUM1, NUM2).  i.e. (12 * 16)/4

It is equal to 48. 48 is the LCM of 12 and 16

Design an algorithm to find minimum of two distinct numbers

Problem:  Design an algorithm to find minimum of two distinct numbers and using the same, find minimum of three distinct numbers.

Input :  For sub-algorithm 2 numbers and for main algorithm 3 numbers.
Output: Minimum number

MIN_OF_2(NUM1, NUM2)                                                   [Sub-Algorithm]
[NUM1 and NUM2 are two numbers]
If NUM1<NUM2 Then:
Return NUM1
Else:
Return NUM2
[End of If]
Exit

MIN_OF_3(NUM1,NUM2,NUM3)                                     [Main Algorithm]
[NUM1, NUM2, and NUM3 are distinct numbers]
Returns MIN_OF_2(MIN_OF_2(NUM1, NUM2), NUM3)
Exit

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.