Use of HAVING clause in SQL

Overview : 

Having clause in SQL is used to place aggregate functions  to filter data because where condition does not allow the use of aggregate function.

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;

Demo :

Suppose we have a database of items as shown below. 

Now if we need to find out if there is any duplicate bar-code in these items then we can use having clause.

Query :

select count(item_code), item_name, item_purchase_rate, item_sale_rate from item group by item_code, item_name, item_purchase_rate, item_sale_rate having count(item_code)  > 0

Using PIVOT in SQL

Overview : PIVOT turns the unique values from one column in the expression to multiple columns in result, and applies aggregation on remaining value found in resultant output.

Example: lets create a table for this as follows –

create table demo(id int identity(1,1), item nvarchar(max), category nvarchar(max), qty int)

now lets insert some items in this table –

insert into demo(item, category, qty) values('item01', 'cat01', 5)

insert into demo(item, category, qty) values('item02', 'cat03', 5)

insert into demo(item, category, qty) values('item02', 'cat01', 5)

insert into demo(item, category, qty) values('item01', 'cat02', 5)

insert into demo(item, category, qty) values('item03', 'cat01', 5)

insert into demo(item, category, qty) values('item01', 'cat01', 5)

insert into demo(item, category, qty) values('item02', 'cat03', 5)

insert into demo(item, category, qty) values('item02', 'cat01', 5)

insert into demo(item, category, qty) values('item03', 'cat01', 5)

insert into demo(item, category, qty) values('item02', 'cat02', 5)

Lets apply our PIVOT query on this –

select * from(select item, category, qty from demo

)datatable

 PIVOT

 (

   sum(qty) for category  IN ([cat01], [cat02], [cat03])

 )

 Pivottable

Result :

Avoid Refreshing of Master Page

The refreshing of master page is a big problem for .NET developers, if you have placed a tree kind of menu in your website than if your master page refreshes every time than this is not a good thing for your website. So, to overcome this problem you can use frameset and iframe.

1  <frameset>

2       <div style="width:100%;">

3       <div style="width:35%; height:auto; float:left;">

4        <iframe src="Default2.aspx" height="auto" width="250px;"></iframe>

5        </div>

6        <div style="height:auto; width:65%; float:left;"></div>

7        <iframe id="iframe2" name="iframe2">

8         

9         </iframe>

1        </div>

1      </frameset>

If you a designing the menu in left side of your page than the first iframe should contain the src of the page which contains menu and in the page which contains menu will have target="iframe2" in anchor tag’s property like this :

<a href="Default3.aspx" id="dd" title="page" target="iframe2" style="color:Navy;">Title</a>

Master Page and IFrame

Master Page :

Master page reduces the code of a programmer because it comes with such features that we do not need to write the same code on many pages. If your website is using the same design on most of the pages than you can write the design code and programming on master page and place a content placeholder for other pages, by using this there is no need to write repetative codes. All the other pages are going to be shown in that content placeholder, you just simply need to call the master page on other pages.


IFrame :

Iframes are completely HTML part, it is also a very useful feature. If you are using the same kind of design an more than one page, than write the commom code on a separate page and call that page using iframe like this -

<iframe src="page.htm" height="55px" width="100%" frameborder="0"></iframe>

Note : Sometimes when we are using tree menu then we see that our master page gets refresh each time when we click on the menu and the menu goes in it's starting stage. To avoid these kind of problem it is usefull to use iframe by placing the menu in separate iframe.