SQL allows some useful functions. SQL Functions are already defined words that can perfrom actions. For example if we want to check that how many total rows we have in our table, the function used is COUNT. Some of the SQL functions are explained here.
SQL Count Function
SYNTAX:
COUNT(column)
EXAMPLES:
SELECT count(*) FROM Items_shahid3
SELECT count(ItemName) FROM Items_shahid3
SELECT count( DISTINCT itemname ) FROM items_shahid3
DESCRIPTION:
COUNT is used for counting. It can count rows if we type * or any column name in count function. It can also give us unique rows if we type keyword DISTINCT before column name. For example count(*) will show us all the products from our items table. But may be we have one product many times in our table. In that case, if we use Count(Distinct ItemName), it will give us unique total number, Where every product will be counted only once.
SQL AVG Function
SYNTAX:
AVG(Column)
EXAMPLES:
SELECT AVG( ItemPrice ) FROM Items_shahid3
DESCRIPTION:
AVG is used for average of a specified column
SQL SUM Function
SYNTAX:
SUM(Column)
EXAMPLES:
SELECT SUM( ItemPrice ) FROM Items_shahid3
DESCRIPTION:
SUM is used for SUM of all values in a column
SQL MIN Function
SYNTAX:
MIN(Column)
EXAMPLES:
SELECT MIN( ItemPrice ) FROM Items_shahid3
DESCRIPTION:
Minumum of values specified in a column
SQL MAX Function
SYNTAX:
MAX(Column)
EXAMPLES:
SELECT MAX( ItemPrice ) FROM Items_shahid3
DESCRIPTION:
Maximum of values specified in a column
MS SQL Server Stored Procedures
SQL Server provides some usefule stored procedures. Some of the SQL Stored procedures are given here.
View table specification:
sp_help Product
Rename table:
sp_rename 'currentName', 'newName'
Rename table column from Ptype into Pcat:
sp_rename 'Product.[Pcat]', 'Pcategory', 'COLUMN'