Important Functions used in sql server 2008/2005

The TOP Clause

The TOP clause is used to specify the number of records to return.
The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.

 Syntax

SELECT TOP number|percent column_name(s)
FROM table_name

The LIKE Operator

The LIKE operator is used to search for a specified pattern in a column.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

 Example:-

SELECT * FROM Employess
WHERE City LIKE 's%'

The IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

 Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

The BETWEEN Operator

The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.

 Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

SQL Alias

You can give a table or a column another name by using an alias. This can be a good thing to do if you have very long or complex table names or column names.
An alias name could be anything, but usually it is short.

Syntax for Tables

SELECT column_name(s)
FROM table_name
AS alias_name

SQL Alias Syntax for Columns

SELECT column_name AS alias_name
FROM table_name 

SQL JOIN

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

SQL INNER JOIN Keyword

The INNER JOIN keyword return rows when there is at least one match in both tables.

 Syntax

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

Syntax

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

 Syntax

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SQL FULL JOIN Keyword

The FULL JOIN keyword return rows when there is a match in one of the tables.

Syntax

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

The AVG() Function

The AVG() function returns the average value of a numeric column.

Syntax

SELECT AVG(column_name) FROM table_name

SQL COUNT(column_name) Syntax

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(column_name) FROM table_name

SQL COUNT(*) Syntax

The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name

The FIRST() Function

The FIRST() function returns the first value of the selected column.

 Syntax

SELECT FIRST(column_name) FROM table_name

The LAST() Function

The LAST() function returns the last value of the selected column.

 Syntax

SELECT LAST(column_name) FROM table_name

The MAX() Function

The MAX() function returns the largest value of the selected column.

Syntax

SELECT MAX(column_name) FROM table_name

The MIN() Function

The MIN() function returns the smallest value of the selected column.

Syntax

SELECT MIN(column_name) FROM table_name

  

The SUM() Function

The SUM() function returns the total sum of a numeric column.

Syntax

SELECT SUM(column_name) FROM table_name

The GROUP BY Statement

 The GROUP BY Statement The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

 Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name 

The HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

 Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value 

The UCASE() Function

The UCASE() function converts the value of a field to uppercase.

Syntax

SELECT UCASE(column_name) FROM table_name

Syntax for SQL Server

SELECT UPPER(column_name) FROM table_name

The LCASE() Function

The LCASE() function converts the value of a field to lowercase.

Syntax

SELECT LCASE(column_name) FROM table_name

Syntax for SQL Server

SELECT LOWER(column_name) FROM table_name

The MID() Function

The MID() function is used to extract characters from a text field.

 Syntax

SELECT MID(column_name,start[,length]) FROM table_name

The LEN() Function

The LEN() function returns the length of the value in a text field.

 Syntax

SELECT LEN(column_name) FROM table_name

The ROUND() Function

The ROUND() function is used to round a numeric field to the number of decimals specified.

 Syntax

SELECT ROUND(column_name,decimals) FROM table_name

The NOW() Function

The NOW() function returns the current system date and time.

Syntax

SELECT NOW() FROM table_name

The FORMAT() Function

The FORMAT() function is used to format how a field is to be displayed.

Syntax

SELECT FORMAT(column_name,format) FROM table_name