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

Built-In Functions SQL Server

SQL Server > Built-in Functions

String

• REPLICATE
• ASCII
• LTRIM
• RTRIM
• SUBSTRING
• SOUNDEX
• LEN
• LEFT
• RIGHT
• LOWER
• UPPER
• CHAR
• CHARINDEX
• DIFFERENCE
• FORMAT
• REPLACE
• SUBSTRING
• STUFF
• UNICODE
• PATINDEX
• STR
• SPACE
• CONCAT

Aggregate

• AVG
• MIN
• MAX
• COUNT
• COUNT_BIG
• CHECKSUM_AGG
• STDEV
• GROUPING
• GROUPING_ID
• VAR
• VARP

Conversion

• CAST
• CONVERT
• PARSE
• TRY_CAST
• TRY_CONVERT
• TRY_PARSE

Rank

• RANK & DENSE_RANK
• NTILE
• ROW_NUMBER

Date and Time

• DATEADD
• DATEDIFF
• EOMONTH

System

• ISNUMERIC
• HOST_NAME
• NEWID
• ISNULL
• @@TRANCOUNT
• @@ROWCOUNT

Mathematical

• POWER
• ABS

Metadata

• OBJECT_ID
• APP_NAME

Generics C#

C# > Generics

Generics means the concept of type parameters. In this way it is possible to design classes  that defer the specification of types until the class is declared and instantiated.

• generic types maximize code reuse, type safety, and performance.
• T is generic type parameter

Links                                                                                                  

• HashSet
• Generics and array
• Dictionary
• Where
• Generic interface

Example: Simple generic list                                                           

public class GenericList<T>

{
    private List<T> l = new List<T>();
    public void Add(T element)
    {
       l.Add(element);
    }
}

GenericList<int> lst1 = new GenericList<int>();
lst1.Add(1);

GenericList<string> lst2 = new GenericList<string>();
lst2.Add("generics");

HashSet C# Example

C# > Generics > HashSet

HashSet is an optimized set of value collection. A set is a collection that contains no duplicate elements.

Note: The elements are not in a particular order

Example:
 
Use UnionWith to merge two sets with millions of elements
            
            HashSet<int> s1 = new HashSet<int>();
            HashSet<int> s2 = new HashSet<int>();
            for (int i = 0; i < 1000000; i++)
            {
                s1.Add(i);
            }
            for (int i = 1000001; i < 2000000; i++)
            {
                s2.Add(i);
            }
            HashSet<int> s3 = new HashSet<int>(s1);
            s3.UnionWith(s2);
            if (s3.Contains(1500000))
            {
                MessageBox.Show("1,500,000 element found in HashSet"); 
            }