SQL Basic Functions Chapter1

SQL Functions

 Ø  SUM Function: to get sum the values of the column

Syntax:

SELECT SUM (column_name) FROM Table_Name

Example:

SELECT SUM (Hours) FROM tbl_Abc

Ø  AVG Function: to get the avg of the column

SELECT AVG (hours) FROM tbl_Abc

Ø  Min/MAX Function:

Syntax:

SELECT MIN (wage) AS [Minimum Wage],
MAX(wage) AS [Maximum Wage] FROM tbl_Abc

Example:
MIN Function With BETWEEN Operator

SELECT OrderID, MIN(Quantity) as Quantity
FROM [Order Details]
WHERE OrderID BETWEEN 11000 AND 11002
GROUP BY OrderID

OrderID	Quantity
11000	25
11001	6
11002	15

Modify it one more time for the MAX function:

SELECT OrderID, MAX(Quantity) Quantity
FROM [Order Details]
WHERE OrderID BETWEEN 11000 AND 11002
GROUP BY OrderID

OrderID	Quantity
11000	30
11001	60
11002	56

Ø  Round Function

Syntax:

Select Round (number, n)

Example:

SELECT ROUND (1.2536, 3) as Roundof;
SELECT ROUND (1.2536, 2) as roundof;

Result:

Roundof: - 1.2540Roundof: - 1.2500

Ø  Ceiling Function

Syntax:

Ceiling (number value)

 Number value is the value which is used to find the smallest integer value. Example:

Select Ceiling (32.65) as Ceiled                                                   would return, Ceiled: 33
select Ceiling (32) as Ceiled                                                          would return, Ceiled: 32
Select Ceiling (-32.65) as Ceiled                                                  would return, Ceiled: 32
Select Ceiling (-32) as Ceiled                                                        would return, Ceiled: -32

 Ø  Floor Function:

        Syntax:

Floor (Any number)

 Example:

SELECT FLOOR(4.88)

would return 4

 

Ø  Sqrt Function:

Syntax:

sqrt( value)

Example:

SELECT SQRT(9)

would return 3

 

Ø  Top Function:

Syntax:
Select Top (any number) from table name

 Example:

SELECT TOP 5 Name, CreditRating

FROM tbl_Vendor
ORDER BY CreditRating DESC, Name;

Result:

Name	CreditRating
Merit Bikes	5
Victory Bikes	5
Proseware, Inc.	4
Recreation Place	4
Consumer Cycles	3

 Ø  Bottom Function:

Let’s we’ve below table

Name	Wage
Mike	$250
Jack	$328
Gonaa	$450
Darek	$678
jacob	$1,000
Michel	$870 Bottom of Form

 

 

 

Syntax with Example:

SELECT TOP 2 names, wage FROM tbl_Employee
ORDER BY wage DESC

Result:

Name	Wage
Michel	$870
jacob	$1,000 Bottom of Form

Ø  Distinct Function:

Let’s we’ve below table

Category

A

B

C

A

F

None

 

 

 

Syntax:

SELECT DISTINCT columns FROM tables WHERE predicates;

Example:

SELECT DISTINCT Category FROM Tbl_Category;

Result:

Category

Michel

jacob

 Ø  SQL LEFT/RIGHT

Syntax for LEFT:

SELECT names, LEFT (names, 3) AS [left] FROM tbl_Employee

Syntax for RIGHT:

SELECT names, Right (names, 3) AS [Right] FROM tbl_Employee

Names	Left	Right
Sumon Bagui	Sum	gui
Sudip Bagui	Sud	gui
Priyashi Saha	Pri	aha
Ed Evans	Ed	ans