What is Visual Basic .NET

Visual Basic .NET is the result of a significant rebuild of Visual Basic for the Microsoft .NET Framework.
Visual Basic .NET gives you access to system resources that in the past required the use of languages like C++. One of the most important additions is object inheritance.

• System 
• Run time functions
• Object-Oriented Programming
• Form
• Statements

RANK and DENSE_RANK SQL Server

SQL Server > Built-in Functions > RANK & DENSE_RANK

RANK returns the rank of each row within the partition of a result set

DENSE_RANK returns the rank of rows within the partition of a result set  without any gaps in the ranking

                    OVER ( [ PARTION BY ] [ORDER BY]) 
Example:

CREATE TABLE #LocalTempTable(

       ID            int,
       Name   varchar(50),
       Salary int,
       DeptId int)

insert into #LocalTempTable(Id, Name, Salary, DeptId) values (1,'p1',100,1),  (2,'p2',200,1) ,  (3,'p3', 100,1) ,   (2,'p4', 50,2)

 

--see image 1

select * from #LocalTempTable

--see image 2

SELECT i.*

    ,RANK() OVER
    ( ORDER BY i.Salary DESC) AS Rank
FROM
       #LocalTempTable i
ORDER BY i.DeptId;

--see image 3

SELECT i.*

    ,RANK() OVER
    (PARTITION BY i.DeptId  ORDER BY i.Salary DESC) AS Rank
FROM
       #LocalTempTable i
ORDER BY i.DeptId;

--see image 4

SELECT i.*

    ,DENSE_RANK() OVER
    ( ORDER BY i.Salary DESC) AS Rank
FROM
       #LocalTempTable i
ORDER BY i.DeptId;

drop table #LocalTempTable

Results:

 

Image 1 Salary Data

Image 2 RANK without PARTITION BY will rank all rows. Note the gap between 2 and 4

Image 3 RANK with PARTITION BY will rank rows in the same DeptID.

Image 2 DENSE_RANK  Note there are not gaps

 

 

 



 



MIN function SQL Server Example

SQL Server > Built-in Functions > MIN

MIN returns the minimum value

Warning! Null values are ignored!

Example

CREATE TABLE #LocalTempTable(

       ID            int,
       Name   varchar(50),
       Salary int,
       DeptId int)

insert into #LocalTempTable(Id, Name, Salary, DeptId) values (1,'p1',100,1),  (2,'p2',200,1) ,  (2,'p3', null,2) ,   (2,'p4', 50,2)

select * from #LocalTempTable

select min(Salary) MIN_salary from #LocalTempTable
select min(Salary) MIN_salary, DeptId from #LocalTempTable group by DeptId

drop table #LocalTempTable

Results:

ID Name Salary DeptId
1 p1 100 1
2 p2 200 1
2 p3 NULL 2
2 p4 50 2

MIN_salary
50

MIN_salary DeptId
100 1
50 2

AVG function SQL Server Example

SQL Server > Built-in Functions > AVG

AVG returns the average of the values in a group without Null values

Example:

1. Calculate average salary global and for each department

CREATE TABLE #LocalTempTable(

       ID            int,
       Name   varchar(50),
       Salary int,
       DeptId int)

insert into #LocalTempTable(Id, Name, Salary, DeptId) values (1,'p1',100,1),  (2,'p2',200,1) ,  (2,'p2', null,2)

select avg(Salary) avg_salary from #LocalTempTable

select avg(Salary) avg_salary, DeptId from #LocalTempTable group by DeptId

drop table #LocalTempTable

Results:

avg_salary
150

avg_salary DeptId
150            1
NULL       2

2. Average without zero (0)

UPPER SQL Server Example

SQL Server > Built-in Functions > UPPER

UPPER  converts a character expression to uppercase.

Example

SELECT UPPER('this is lowercase expression')
Result:
THIS IS LOWERCASE EXPRESSION

CHAR Function SQL Server

SQL Server > Built-in Functions > CHAR

Converts an ASCII code to a character.

Example

SELECT CHAR(68),CHAR(100)
Result:
D d

ASCII SQL Server

SQL Server > Built-in Functions > ASCII

Returns the ASCII code value of the character






Example

SELECT ASCII('D'),ASCII('d')

Result:
68 100