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

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:

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 (1, 2)                x > 1 x < ANY (1, 2)                x < 2 x = ANY (1, 2)                (x = 1) OR (x = 2) x <> ANY (1, 2)              (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(1, 2)         x > 2 x < ALL(1, 2)         x < 1 x = ALL(1, 2)         (x = 1) AND (x = 2) x <>ALL(1, 2)        (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

SELECT Ename
FROM Employee  
ORDER BY Ename DESC

2. 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;

3. Alias in ORDER BY

   Select M1+M2+M3 as Total
   FROM Student 
   ORDER BY Total

4. Inline View Alias in ORDER BY

   Select *
   FROM (Select ename, id From Employee) AS t1 
   ORDER BY t1.id

5. Expression in ORDER BY clause.

   SELECT *
    FROM Student  
   ORDER BY M1+M2+M3

6. ORDERING aggregate results.

   SELECT region
   FROM Employee
   GROUP BY region  
   ORDER BY region