SqlTutorial-1 : Lesson-11 : Class-1
Microsoft SQL Server Training:
Free Online-Learning Classes (OLC) for T-SQL
Built-in Sql functions: Aggregate and Scalar



Microsoft SQL Server Training Online Learning Classes Built in functions Aggregate Scalar

The following are various types of functions provided SQL-server as part of T-SQL language library.

BUILT-IN FUNCTIONS
  1. Aggregate functions
  2. Scalar functions / global variables @@variable
  3. String functions
  4. Data Conversion functions
  5. Null checking functions.
  6. Date functions
  7. Mathematical functions
  8. Analytical functions


1. Aggregate functions ( 6 methods)

  • Aggregate functions are applied to a group of data values from a column. But not on single value nor on a group of values of a row.
  • Aggregate functions always return a single value. The T-SQL language support following functions.
    1. AVG()   : Returns average of given column; The column must contain numeric values.
    2. MAX() : Returns Max-value from given column; The column can contain numeric, string, and date/time values. Ignore the NULL values.
    3. MIN()  :  Returns Mininimum value from the given column; The column can contain numeric, string, and date/time values. Ignore the NULL values.
    4. SUM()  :  Returns Sum of values avialable in given column. The column must contain numeric values.
    5. COUNT()  : Returns No. of rows or values in the given column; The only aggregate function not being applied to column is COUNT(*).
    6. COUNT_BIG() :  Similar to COUNT() except returns BIGINT data instead of int.

1.1 - AVG():

Calculates the average for selected records of numeric data in a column or the average for DISTINCT (unique) values of the selected records.

Syntax

AVG ( [ ALL | DISTINCT ] Expression ); 

Eamples

  1. Select AVG (qty) 
    From Sales;
  2. Select AVG(DISTINCT Salary)
    FROM Employee;
  3. Select AVG(SalesPrice), AVG(AverageCost)
    From CD WHERE Classif_ID = 1
  4. Select Titles.title, Titles.price
    From Titles JOIN Sales ON Sales.title_id = Titles.title_id
    WHERE Sales.qty > (SELECT AVG(qty) FROM sales)

1.2 MAX():

Returns the largest value in a column of all selected records by the query of any char, number, or datetime datatype.

Syntax :

MAX ( [ ALL | DISTINCT ] expression )

Examples:

  1. Find Last ordered date for Sales

    Select MAX(ord_date)
    FROM  Sales;
  2. The use of the aggregate function MAX in subquery

    SELECT *
    FROM employee
    WHERE start_date = (SELECT MAX(start_date) FROM employee WHERE id > 3)

1.3: MIN():

Returns the smallest value in a column of all selected records by the column of any char, number, or datetime datatype.

Syntax :

MIN ( [ ALL | DISTINCT ] expression )

Examples:

  1. Find first order_date

    Select MIN(ord_date)
    From sales
  2. Finding the Earliest Date Using MIN()

    SELECT *
    FROM Employee
    WHERE Start_Date =( SELECT MIN (Start_Date) FROM Employee)

1.4 SUM :

Returns the total value in a column of all selected records or all the DISTINCT (unique) values selected by the query.

Syntax :

SUM ( [ ALL | DISTINCT ] expression )
  • This function is used for only number datatypes.
  • All NULL values are ignored.

Examples:

  1. SELECT dept_name, emp_cnt, SUM(budget) sum_of_budgets FROM department GROUP BY dept_name, emp_cnt;
  2. Sum with CUBE

    SELECT dept_name, emp_cnt, SUM(budget) sum_of_budgets FROM department GROUP BY dept_name, emp_cnt WITH CUBE;
  3. SUM with WITH ROLLUP

    SELECT dept_name, emp_cnt, SUM(budget) sum_of_budgets FROM department GROUP BY emp_cnt, dept_name WITH ROLLUP

1.5 COUNT():

Returns the total count of records in a column or the count for DISTINCT(unique) values selected in column.

Syntax :

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } ) 

Examples:

  1. All NULL values are ignored in the total count.

    (a) select COUNT(qty) from sales;
    (b) select COUNT(DISTINCT Classif_ID) from CD
  2. COUNT(*) returns the total count of all records (rows) in the column, including NULL values of selected records by the query. DISTINCT is not allowed with this function.

    Select COUNT(*) 
    From Sales;
  3. A common mistake with DISTINCT is to code the DISTINCT before the COUNT statement.

    SELECT DISTINCT COUNT(Classif_ID)   --DISTINCT no effect here
    From CD 
  4. Using ISNULL to count all values including NULLs.

    SELECT COUNT(ISNULL(name, ' ')) AS Expr1
    FROM student
  5. Count table row in dynamic sql

    EXEC(N ' SELECT COUNT(*)
                    FROM ' + @objectname + N';');
Difference between COUNT(*), COUNT(col) & COUNT(DISTINCT col)
  • COUNT(*) - Returns the number of records in the group(table). This includes NULL values and duplicates.
  • COUNT(ALL expression)- Returns no. of values in the given column including duplicates & excluding null values.
  • COUNT(DISTINCT expression) similar to COUNT(ALL column) except excludes duplicates.


2. Scalar functions - Global Variables (@@)

  • These are special types of functions that doe not takes any parameters and returns single value
  • These are invocable as @@function name; 
  • These are also called Global Variables.

@@VERSION, @@LANGUAGE, @@LangID, @@servername, @@Max_connection, @@Connections, @@CPU_BUSY, @@Idle, @@Total_Errors, @@NestLevel, @@TextSize, @@Max_precision, @@DateFirst, @@Cursor_Rows, @@Fetch_Status, @@Error, @@RowCount, @@Trancount, @@Identit;




Function Name
Description
1 )   @@VERSION
Returns version, processor architecture, build date, and operating system for the current installation of SQL Server.
Example: SELECT @@Version
è    Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
        Feb  9 2007 22:47:07
       Copyright (c) 1988-2005 Microsoft Corporation
       Express Edition on Windows NT 5.2 (Service Pack 2, v.4354)
2 )  @@LANGUAGUE
Returns the name of the language currently being used.
Example
(1)  Select @@Languageè us_english  //Default
(2)  SET LANGUAGE Italian // Customizing
       Select @@LanguageèItalian
3 )  @@LangID
Returns the local language identifier (ID) of the language that is currently being used.
Example: Select @@LangID è 6
4 )  @@servername
Returns the name of the local server that is running SQL Server.
Example: Select @@Servernameè abc\SQLEXPRESS
5 )  @@Max_connection
Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server.
Example: Select @@Max_Connection è 32767
6 )  @@Connections
Returns the number of attempted connections, either successful or unsuccessful since SQL Server was last started.
Example:  SELECT @@CONNECTIONS è208
7 )  @@CPU_BUSY
Returns the time that SQL Server has spent working since it was last started
Example: Select CPU_BUSYè 97 mnts.
8 )  @@Idle
Returns the time that SQL Server has been idle since it was last started.
Example: Select @@Idle è 373820 mnts
9 )  @@Total_Errors
Returns the number of disk write errors encountered by SQL Server since SQL Server last started
Example: Select @@Total_Errors.
10 )   @@NestLeve


Note: Use it with recursion of procedure l
Returns the nesting level of the current stored procedure execution (initially 0) on the local server
CREATE PROCEDURE SP_OuterProc AS
    SELECT @@NESTLEVEL AS 'Outer Level';
    EXEC SP_InnerProc;
GO

CREATE PROCEDURE SP_InnerProc AS
    SELECT @@NESTLEVEL AS 'Inner Level';
GO

EXECUTE SP_OuterProc;
GO
Outer Level      Inner Level
-----------             -----------
1                        2
11 )  @@TextSize

Returns the current value of the TEXTSIZE option of the SET statement. This specifies the maximum length, in bytes, of varchar(max), nvarchar(max), varbinary(max), text, or image data that a SELECT statement returns.
Examples:
  (1) Select @@TextSize è 2147483647 //default
  (2) SET TEXTSIZE 2048     //customizing
        SELECT @@TEXTSIZE è 2048
12 )   @@Max_precision
Returns the precision level used by decimal and numeric data types as currently set in the server.
Example: Select @@Max_precision è 38
13 )  @@DateFirst
Returns the current value, for the session, of SET DATEFIRST.
Examples:
(1)    Select @@DateFirst è7 //default
(2)    SET DATEFIRST 5  //Customizing
         Select @@DateFirst è 5

SqlTutorial-1 : Lesson-3 : Class-2
Microsoft SQL Server Training:
Free Online-Learning Classes (OLC) for T-SQL
Basic SELECT- Operations:

Microsoft SQL Server Training Online Learning Classes Basic SELECT Operations
In this article, you will find all SELECT-Operations of T-SQL at one-place. It contains huge combinations of examples.

INDEX
  • Syntax
  • Examples
    1. Simple-SELECT (Without any Clause even no FROM)
    2. SELECT-FROM 
    3. SELECT-FROM-WHERE with OPERTORS
      • Relational Operators
      • Logical Operators
      • Pattern Matching Operators
      • List Operators li>
      • NULL-CHECKING Operators
      • SET Operators
    4. SELECT-FROM-WHERE-GROUP By-HAVING
    5. SELECT-FROM-ORDER By


Syntax

SELECT [DISTINCT] { * | column1[,column2..] | expressions }
   [INTO new_destTable ]
   [FROM table_source]
   [WHERE search_condition]
   [GROUP BY expression1[,..n] [WITH {CUBE | ROLLUP} ]
   [HAVING search_condition]
   [ORDER BY col1[ASC/DESC] [,col2…]]

The order of SELECT statement is as follows:
  1. FROM
  2. ON (joining)
  3. WHERE
  4. GROUP BY
  5. CUBE | ROLLUP
  6. HAVING
  7. SELECT
  8. DISTINCT
  9. ORDER BY
  10. TOP | ROW NUMBER | RANK | COMPUTE etc.


1. Simple-SELECT (Without any clause even no FROM)

  1. Displaying text message.

    SELECT 'Welcome ' AS message
  2. Displaying a result of expression.

    SELECT 1 + 6 / 2 AS RESULT => 4
  3. Displaying a result based on condition.

    SELECT CASE 2
    WHEN 1 THEN 'ONE '
    WHEN 2 THEN 'TWO '
    END
  4. Scripting through SELECT.

    SELECT 'THE SQUARE OF 2 IS ' + CONVERT(CHAR, square(2))
    -- THE SQUARE OF 2 IS 4
  5. SELECT with ALL BUILT-IN functions.

    SELECT SQRT(4)   => 2
    SELECT POWER(2,4) =>16
    SELECT SIN(90)    => 1

2. SELECT-FROM 

  1. Selecting all columns of a table.

    SELECT * FROM employee
  2. Selecting particular column of a table.

    SELECT Id, name FROM employee
  3. T-SQL is case insensitive.

    SELECT Id, name FROM employee
    = SELECT ID,NAME FROM EMPLOYEE
  4. Using ALIAS with tables

    SELECT t1.*, t2.Id
    FROM Table1 AS t1, Table2 AS t2;
  5. Arithmetic operations on columns (AS clause / ALIASE with columns)

    SELECT col1+col2 AS ADDITION
    FROM Tablen 1

    col1–col2, col1/col2, and col1 * col2 is also possible.
  6. Complex ALIASE (integer, multiple words etc): significance of [ ]

    SELECT name AS [MY NAME]
    FROM Table1

    Giving Numbers as alias also requires “[ ]” as [10], otherwise it is compilation error.
  7. Eliminating duplicate values from results.

    SELECT DISTINCT name, salary
    FROM employee

    • You cannot SELECT DISTRINCT * . It is compilation error.
    • You can select MULTIPLE DISTRINCT as SELECT DISTINCT col1,DISTINCT col2
  8. Implementing MULTIPLE DISTINCT (directly not possible)

    SELECT name, salary
    FROM employee
    GROUP BY name, salary
  9. Sorting of data.(ORDER BY clause)

    SELECT DISTINCT name, salary FROM employee
    ORDER BY name DESC          -- Default is ASC
  10.  SELECT with CASE clause(Condition based result)

    SELECT CASE 
        WHEN (salary BETWEEN 1000 AND 5000) THEN 'low salary'
        WHEN (salary BETWEEN 5000 AND 10000) THEN 'avg salary'
        WHEN (salary BETWEEN 10000 AND 15000) THEN 'above avg'
        WHEN (salary BETWEEN 150000 AND 20000) THEN 'Good'
      END
    FROM employee

    In SELECT statement, CASE clause cannot be in FROM, WHERE clauses.
  11. Defining a new table using existing table. (SELECT INTO clause)

    SELECT * INTO NewTemp FROM Employee

    Note: It automatically creates a new table (here name NewTemp ) with all columns of Employee.
  12. Defining a new table with additional column of type “date”.

    SELECT *, CAST(NULL AS datetime) AS col1 INTO temp2 FROM Table1


3. SELECT-FROM-WHERE with OPERATORS

Examples SELECT with WHERE etc.


In WHERE clause almost all operators such as relational, Logical, Range, LIKE. But some operators such as SET operators, CUBE, ROLL UP, etc are not allowed in WHERE clause.

Operator
Description
Example
1: RELATIONAL OPERATORS
=
Equals to
WHERE ename =’abc’
!= or <>
Not equals to
WHERE salary != 5000
Grater than
WHERE salary > 5000
Less than
WHERE salary < 1000
>=
Greater than or equals to
WHERE salary >= 6000
<=
Less than or equals to
WHERE salary <= 6000
!>
Not greater than
WHERE salary !> 6000
!<
Not less than
WHERE salary !> 6000
2: LOGICAL OPERATORS
AND
Logical AND
WHERE salary>1000 AND salary<2000
OR
Logical OR
WHERE salary=1000 OR salary=2000
NOT
Logical NOT
WHERE salary NOT= 2000
WHERE salary NOT NULL
WHERE salary NOT BETWEEN 1000 AND 2000
3: PATTERN MATCHING

Symbol  Description
%               Matches any string of zero or more characters.
_                Matches any single character.
[ ]               Matches a single character listed within the brackets.
[ - ]             Matches a single character within the given range.
[ ^ ]            Matches a single character not listed after the caret.
ESCAPE   Ignores specified character.
[NOT] LIKE
WHERE name LIKE 'J%'
WHERE name LIKE '_a%' è 2nd char “a”
WHERE name LIKE '[C-E]%'starts C,D,E
WHERE name LIKE '[^C-E]%' not starts C,D,E
WHERE BankerCity LIKE 'SAN%'
WHERE BankerName LIKE 'DAMI[EO]N'
WHERE BankerState LIKE 'M[A-J]'
WHERE BankerState LIKE 'M[^K-Y]'
WHERE BankerZipCode NOT LIKE '[1-9]%'è000
WHERE name NOT LIKE '[^J-O]%'           =
WHERE  NOT ( first_name LIKE '[^J-O]%') =
WHERE  name LIKE '[^J-O]%'

//Any of the wildcard characters (%, _' [ ], or ?) enclosed in square brackets stand for themselves.
WHERE first_name LIKE '%[_]%' è Jon_Wiliam

//Find the names begins with letter A-Z except B
WHERE(name LIKE '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%' ESCAPE 'B')
Note: EXCAPE not works as WHERE(name LIKE '[A-Z]%' ESCAPE 'B')
4: LIST  OPERATORS
BETWEEN AND
Between a range of values
(both inclusive)

Syntax:
WHERE  expression  [NOT] BETWEEN begin AND end
WHERE salary BETWEEN 1000 AND 2000
WHERE salary NOT BETWEEN 100 AND 200

WHERE BillingDueDate 
BETWEEN GetDate() AND GetDate() + 30

WHERE BillingDate 
BETWEEN '2002-05-01' AND '2002-05-31'

WHERE M1+M2 BETWEEN 200 AND 500
IN(list)
Matches any one value of list based on equality.

An IN operator is equivalent to a series of conditions, connected with one or more OR operators. But it does not work with NULL value.

WHERE eid in (1000, 2000, 3000)
= WHERE eid=1000 OR eid=2000 OR  eid=3000

WHERE BankerID IN
      (  SELECT BankerID
         FROM Billings
          WHERE BillingDate = '2002-05-01')

SOME(list)
Matches more than 1 value.
WHERE salary  =SOME(1000,2000,3000)
WHERE salary > SOME (1000,2000,3000)
ANY(list)
Same as IN but works with non-equality (<, >, != etc)

Condition                   Equivalent expression
---------------------------------------------------------
x > ANY (12)                x > 1
x < ANY (12)                x < 2
x = ANY (12)                (x = 1) OR (x = 2)
x <> ANY (12)              (x <> 1) OR (x <> 2)

WHERE salary = ANY(100,200,300)
= WHERE salary IN(100,200,300)
Note: Difference: ANY allows arithmetic operators.

WHERE salary >=(select salary from employee)

ALL(list)
Matches all values of list.

Table: How ALL works:

Condition        Equivalent expression
x > ALL(12)         x > 2
x < ALL(12)         x < 1
x = ALL(12)         (x = 1) AND (x = 2)
x <>ALL(12)        (x<>1) AND (x<>2)

Finding Maximum of salary
SELECT   salary FROM   employee
WHERE m1>=ALL(select salary from employee)
= SELECT MAX(salary) FROM employee

Note: use <= for minimum. But it would not work with NULL values.
EXISTS(list)
Matches rows.
(similar to IN but matches entire rows
not a single value).

WHERE [NOT] EXISTS (subquery)

Use the EXISTS operator to test  that one or more rows are returned by the 
Subquery .When you use the EXISTS operator 
with a subquery, the subquery doesn't 
actually return any rows.Instead, it returns an 
indication of whether any rows meet the specified condition



SELECT ID, First_Name, Last_Name
FROM Employee
WHERE EXISTS
 (SELECT FROM Title
  WHERE ID = 1).


Note: For EXISTS, no need of same type of columns in main query and sub-query as it is compulsory for set operations.
NOTE:  1) IN, SOME, ALL,ANY, EXISTS operators does not works with NULL values.

               2) Except IN operator, other operators such as SOME, ALL, ANY, EXISTS operators needs
                   sub-query not direct value for execution. Example:
   WHERE col1 =>ALL(1000,200) ; // Error but    WHERE col1 =>ALL(Select col1 from Table1); // O.K.
5: NULL CHECKING OPERATOR
IS [NOT] NULL
Instead of “=”, use “IS” operator to check NULL values.
WHERE name IS NULL
WHERE name IS NOT NULL
6: SET OPERATORS

UNION
Set UNION operation.

Returns records of both queries eliminating duplicates.

Note:
(1)     For all set operations such as UNION, UNIONALL, INTERSET,  & EXCEPT, both queries must be select identical columns, otherwise operation cannot be performed.

(2)     Syntax for all set operations is
(Query1) OPERATOR(Query2).
Example1:  UNION same type of columns from different tables

SELECT CompanyName, Address,  
       City,  Region,Country
FROM Customers
UNION
SELECT CompanyName, Address,
       City, Region,Country
FROM Supplier

Example2: OR operator can be used instead of the UNION operator

SELECT first_name
FROM employee WHERE id = '1'
UNION
SELECT first_name
FROM employee WHERE id = '2'
=
SELECT first_name
FROM employee
WHERE id ='1' OR id = '2'


UNION ALL
Returns records of both the queries without eliminating duplicates.

Note: ORDER BY clause is not allowed with in query prior to UNION ALL
E.g., inserting multiple records simultaneously

INSERT INTO table1(id, name)
SELECT 101,’aaa’
UNION ALL
SELECT 102,’bbbb’.
INTERSECT
It returns common records available in both the result sets.

Used for Returning Distinct or Matching Rows.
SELECT ID,first_Name
FROM employee where id > 3
INTERSECT
SELECT 
ID,first_Name
FROM employee where id > 4;
=
SELECT ID,first_name
FROM employee
Where id>3 AND id>4
EXCEPT
(Like Minus)
Returns un-common records from first results.

SELECT id FROM employee
EXCEPT
SELECT id FROM title

SELECT ID,first_Name
FROM employee where id > 2
EXCEPT
SELECT ID,first_Name
FROM employee where id > 4;
=
SELECT ID, first_Name
FROM employee
Where id>2 AND id<=4.

Note: You can use “NOT” operator with many other operators. E.g., NOT IN, NOT EXISTS,
           NOT BETWEEN, and NOT LIKE, NOT = etc.





4. SELECT-FROM-WHERE-GROUP By-HAVING

  1. GROUP BY clause groups the records according to given criteria.
    Example: If you do grouping based on primary key then No. of groups = No. of records.

    SELECT Id
    FROM Employee
    GROUP BY id
  2. Displaying unique names (without using DISTINCT )

    SELECT Name
    FROM student
    GROUP BY name
  3. To display the on duplicate names in a table.

    SELECT Name
    FROM Student
    GROUP BY name HAVING (COUNT(name) > 1)
  4. You can do grouping with JOIN operations

    SELECT BankerState,
              BankerCity,
              COUNT(*) AS BillingQty,
              AVG(BillingTotal) AS BillingAvg
    FROM Billings
              JOIN Bankers ON Billings.BankerID = Bankers.BankerID
    GROUP BY BankerState, BankerCity
  5. GROUPING IS DONE after the filtering rows in WHERE clause.

    SELECT Name
    FROM student
    WHERE (RNo > 4)
    GROUP BY name
  6. Use aggregate functions by GROUPING.

    SELECT City, SUM(Salary)
    FROM Employee
    GROUP BY city
  7. You can GROUPING based on multiple columns.

    SELECT CustomerID, EmployeeID, COUNT(*)
    FROM Orders
    GROUP BY CustomerID, EmployeeID

5. SELECT-FROM-ORDER By

Syntax:

ORDER BY expression [ASC|DESC] [, expression [ASC|DESC]] ...
  • An ORDER BY clause sorts a column in ascending sequence
  • The default order is ASC.

Examples

  1. Simple ORDER BY

  2. SELECT Ename
    FROM Employee  
    ORDER BY Ename DESC
  3. Complex ORDER BY (ORDERING MULTIPLE COLUMNS).

    If ordering is as ORDER BY col1, col2, then result is based on first col1, and then col2 for duplicate values in col1. Example:
    Col1       Col2                 
    65           50
    66           40
    67           20
    67           30
    86           10                  


    SELECT *
    FROM Table1 
    ORDER BY Col1, Col2;
  4. Alias in ORDER BY

    Select M1+M2+M3 as Total
    FROM Student 
    ORDER BY Total
  5. Inline View Alias in ORDER BY

    Select *
    FROM (Select ename, id From Employee) AS t1 
    ORDER BY t1.id
  6. Expression in ORDER BY clause.

    SELECT *
     FROM Student  
    ORDER BY M1+M2+M3
  7. ORDERING aggregate results.

    SELECT region
    FROM Employee
    GROUP BY region  
    ORDER BY region