Sunday, 31 July 2011

Difference Between Where & Having Clause?

The difference between a "where" clause and a "having" clause is a "Where" clause is applied to enforce condition on select statement and a single row function and is applied prior to GROUP BY clause where as HAVING clause is applied to enforce condition of GROUP Function and is applied after GROUP BY clause in the inquiry.

When you are carrying out a summary query, you can also state a Having clause. This is similar to a Where clause, the only difference is it involves a summary value, in place of a column value.

The most basic way to understand the difference among a "Where" clause is a "Where" clause denotes the rows in your tables which have to be integrated in the summation. The "Having" clause operates after the summation has been carried out. It denotes which of the summary row are returned in your result-set


Answer in one line is : HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

Example of HAVING and WHERE in one query:

SELECT titles.pub_id, AVG(titles.price)
FROM titles INNER JOIN publishers
ON titles.pub_id = publishers.pub_id
WHERE publishers.state = 'CA'
GROUP BY titles.pub_id
HAVING AVG(titles.price) > 10


Crosstab Query

A cross tab query is a transformation of rows of data to columns.It is easier review any data from rows.

Two examples of a cross-tab is to take the above data and show how much your shops stocked in each product across. This allows for easy comparison of stock by product.

This is source data from dbo.Shop's table.


Example 1 :


Move data in column of ProductNo to column of MLR,MLB and MLC.

SELECT ShopNo,
(SELECT Stock FROM dbo.Shop WHERE ShopNo = S.ShopNo AND ProductNo = 'MLR') AS MLR,
(SELECT Stock FROM dbo.Shop WHERE ShopNo = S.ShopNo AND ProductNo = 'MLB') AS MLB,
(SELECT Stock FROM dbo.Shop WHERE ShopNo = S.ShopNo AND ProductNo = 'MLC') AS MLC
FROM dbo.Shop S GROUP BY ShopNo

Result :


Example 2 :

This is example of summary stock by product.

SELECT ShopNo,

SUM(CASE WHEN ProductNo ='MLR' THEN Stock ELSE 0 END) AS MLR,

SUM(CASE WHEN ProductNo ='MLB' THEN Stock ELSE 0 END) AS MLB,

SUM(CASE WHEN ProductNo ='MLC' THEN Stock ELSE 0 END) AS MLC,

SUM(Stock) AS Total

FROM Shop

GROUP BY ShopNo

Result :

SELECT INTO statement

The SELECT INTO statement creates the new table by copying schema and other information that is not specific to the actual algorithm.

Syntax :
SELECT [column]INTO [new table]FROM [existing table]

Example :




select * into dbo.newContacts from [OBXKites] .[dbo] .Contact;


SQL SELECT INTO - With a WHERE Clause

We can also add a WHERE clause.

The following SQL statement creates a "Persons_Backup" table with only the persons who lives in the city "Sandnes":

SELECT LastName,Firstname
INTO Persons_Backup
FROM Persons
WHERE City='Sandnes'


SQL SELECT INTO - Joined Tables

Selecting data from more than one table is also possible.
The following example creates a "Persons_Order_Backup" table contains data from the two tables "Persons" and "Orders":

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id

How to create function

CREATE FUNCTION owner_name.function_name ( @parameter_name AS data_type ,...n )
RETURNS data_type
AS
BEGIN
function_body
RETURN value
END


Examples :

CREATE FUNCTION dbo.GetNoWeek (@var Varchar(10))
RETURNS varchar(10)
BEGIN
DECLARE @result varchar(10)
IF @var = 'Ok'
SET @Result = 'Ok'
ELSE
SET @Result = 'Not Ok'
RETURN @Result
END 


Execute

Select dbo.GetNoWeek('Ok');

Stored procedures vs. functions

1. In functions you  cant use DDL , DML smts (like Insert and Update) which is possible in SPs
2. You can use function output in select smts (like Select fnTest(), a from test a)
3  You can use the function output in joins (like test a LEFT OUTER JOIN fnTest() b )
4. Sp can return multiple result-set, function can return only one resultset
5. Function can accept less parameter than procedure
6. Function can not return XML output
.

Stored Procedures are basically compiled which results in faster execution. Once executed the results are cached thus resulting in better performance  more you use it. This is not the result in case of User functions.


Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.

Stored procedure allow you to enhance application security by granting users and applications permission to use stored procedures






create procedure sp_GetAllUsers
@ContactCode Char(12)
As
Select * from [OBXKites] .[dbo] .Contact Where  [OBXKites] .[dbo] .Contact.ContactCode=@ContactCode

exec sp_GetAllUsers 102






create function dbo.getContactName(@ContactCode char(15))
returns varchar(50)
Begin
Declare @name varchar(50)

 Select @name = FirstName +LastName  from [OBXKites] .[dbo] .Contact Where  [OBXKites] .[dbo] .Contact.ContactCode=@ContactCode
return @name
end


select dbo.getContactName('102');