tag:blogger.com,1999:blog-90888715950050336602024-03-13T21:33:38.688-07:00Sql2DotnetKrishna Patilhttp://www.blogger.com/profile/01315946500940726214noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-9088871595005033660.post-53957402017899939432021-09-25T09:56:00.000-07:002021-09-25T09:56:18.620-07:00<p style="text-align: center;"><span style="font-size: large;"><span style="font-family: arial;"> </span><span style="font-family: courier;"><b>D</b><b>ifference between break and continue in c#</b></span></span></p><p style="text-align: center;"></p><ul><li><span style="font-family: georgia;"><b>break :</b> key word used when we want to terminate code execution or terminate execution of loop let see below example and understand<span style="color: red;">.</span></span></li></ul><div style="text-align: center;"><span style="color: red; font-family: georgia;"> for (int k = 0; k < 10; k++) </span></div><div style="text-align: center;"><span style="font-family: georgia;"><span style="color: red;"> { </span><span style="color: red;"> </span></span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;"> if (k == 5) </span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;"> </span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;"> { </span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;"> </span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;"> break; </span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;"><br /></span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;"> } </span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;"><br /></span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;"> } </span></div><div style="text-align: center;"><span style="font-family: georgia;"> As seen in above code for loop will terminate execution when value of k reach five(5).</span></div><div style="text-align: center;"><span style="font-family: georgia;">As we can see k value will generate up-to 10 so loop will run 10 cycle but as we use break key word loop will run up-to 5 cycle and complier terminate loop execution.</span></div><blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;"><blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;"><div style="text-align: center;"><span style="font-family: georgia;"><span style="color: red;"> </span></span></div></blockquote></blockquote><ul><li><span style="font-family: georgia;"><b>continue </b>: key word used when we want to keep loop execution continue</span></li></ul><p></p><p style="text-align: center;"><span><b><span style="color: red; font-family: georgia;"><br /></span></b></span></p><div style="text-align: center;"><span style="color: red; font-family: georgia;">for (int k = 0; k < 10; k++) </span></div><div style="text-align: center;"><span style="font-family: georgia;"><span style="color: red;"> { </span><span style="color: red;"> </span></span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;"> if (k == 5) </span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;"> </span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;"> { </span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;"> </span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;"> break; </span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;"><br /></span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;"> }</span></div><div style="text-align: center;"><span style="font-family: georgia;"><span style="color: red;">elseif</span><span style="color: red;">(k < 5)</span></span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;">{</span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;">continue;</span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;">} </span></div><div style="text-align: center;"><span style="color: red; font-family: georgia;"><br /></span></div><p style="text-align: center;"><span style="font-family: georgia;"></span></p><div style="text-align: center;"><span style="color: red; font-family: georgia;"> }</span></div><p style="text-align: center;"><br /></p>Krishna Patilhttp://www.blogger.com/profile/01315946500940726214noreply@blogger.com0tag:blogger.com,1999:blog-9088871595005033660.post-39379581560156546712014-09-28T10:08:00.000-07:002014-09-28T10:09:46.677-07:00Alter,Add,modify,delete columns- sql server<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<h2 style="text-align: left;">
Query to Alter,Add,modify,delete columns in table - sql server</h2>
<br />
<span style="color: #f6b26b;">Introduction:</span><br />
<br />
how to write SQL Query to add new column to existing table in sql server and query to delete column from table in sql and query to modify column in existing table.<br />
<br />
<br />
<span style="color: #e69138;">Query to add new column to table</span><br />
<br />
If we want to add new column to existing table that syntax will be like this<br />
<br />
<br />
ALTER TABLE Table_Name ADD COLUMN_NAME DATATYPE<br />
Ex:<br />
<br />
<br />
ALTER TABLE emp_table ADD Manager_Name VARCHAR(50)<br />
From the above query declaration new column “Manager_Name” will add to emp_table with data type VARCHAR(50)<br />
<br />
<span style="color: #f6b26b;">Query to Drop or delete column from existing table</span><br />
<br />
If we want to drop column from existing table that syntax will be like this<br />
<br />
<br />
ALTER TABLE Table_Name DROP COLUMN COLUMN_NAME<br />
Ex:<br />
<br />
<br />
ALTER TABLE emp_table DROP COLUMN Manager_Name<br />
From the above query declaration column “Manager_Name” will drop from emp_table<br />
<br />
<span style="color: #f6b26b;">Query to modify existing column datatype in table</span><br />
<br />
If we want to modify existing column datatype from data table that syntax will be like this<br />
<br />
<br />
ALTER TABLE Table_Name ALTER COLUMN COLUMN_NAME DATATYPE<br />
Ex:<br />
<br />
<br />
ALTER TABLE emp_table ALTER COLUMN Manager_Name nvarchar(max)<br />
From the above query declaration I changed “Manager_Name” column datatype from VARCHAR(50) to nvarchar(max)</div>
Krishna Patilhttp://www.blogger.com/profile/01315946500940726214noreply@blogger.com0tag:blogger.com,1999:blog-9088871595005033660.post-74099768285266946562014-04-06T02:30:00.000-07:002014-09-28T10:10:43.126-07:00Sql Queries asked in job interviews<div dir="ltr" style="text-align: left;" trbidi="on">
<table border="0" cellpadding="0" class="MsoNormalTable" style="mso-cellspacing: 1.5pt; mso-yfti-tbllook: 1184; width: 100%px;">
<tbody>
<tr>
<td style="padding: .75pt .75pt .75pt .75pt;"><div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">This article
demonstrates some commonly asked SQL queries in a .NET/SQL job interview.<o:p></o:p></span></div>
</td>
</tr>
<tr style="height: 7.5pt; mso-yfti-irow: 1;">
<td style="height: 7.5pt; padding: .75pt .75pt .75pt .75pt;"></td>
</tr>
<tr>
<td style="padding: .75pt .75pt .75pt .75pt;"><div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">Let’s create Employee
table and insert data as shown below </span></b></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">to perform basic sql query operation.</span></b><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">CREATE</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">TABLE</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> #Employee<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">EmpID </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">INT</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Identity</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">Empname </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Varchar</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">100</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">),</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">EmpSal </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Decimal</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">10</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">2</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">),</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">DEPARTMENT </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">varchar</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">100</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">),</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">JOINING_DATE </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">varchar</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">100</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">)</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">)</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: 12.0pt;">
<br /></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">INSERT</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">INTO</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> #Employee </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">VALUES</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'Krishna'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">50000</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'System Administrator'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'10/01/2011'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">);</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">INSERT</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">INTO</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> #Employee </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">VALUES</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'John'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">30000</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'Network Engineer'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'23/08/2011'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">);</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">INSERT</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">INTO</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> #Employee </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">VALUES</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'Vicky'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">22000</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'Help Desk'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'23/01/2012'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">);</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">INSERT</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">INTO</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> #Employee </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">VALUES</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'Mayur'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">35000</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'System Administrator'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'05/04/2012'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">);</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">INSERT</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">INTO</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> #Employee </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">VALUES</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'Nitin'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">25000</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'Help Desk'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'20/05/2011'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">);</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">INSERT</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">INTO</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> #Employee </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">VALUES</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'Sagar'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">25000</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'Network Engineer'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'25/01/2011'</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">);</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: 12.0pt;">
<br /></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">One of Basic query<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;"> (1) How can we
find the </span><b><i><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">highest</span></i></b><span style="font-family: Arial, sans-serif; font-size: 12pt;"> salary in a table?<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><u><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">Answer</span></u></b><b><u><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">:</span></u></b><span style="font-family: Arial, sans-serif; font-size: 12pt;">The aggregate function
SQL MAX() is used to find the maximum </span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">value or highest value of a certain
column or expression. This function is useful to </span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">determine the largest of all selected values
of a column.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">SELECT</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: magenta; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">MAX</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">Empsal</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">)</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">as</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> salary </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">FROM</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> #Employee<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">(2) SQL Query to find
second</span></b><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;"> </span></b><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">highest
salary</span></b><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;"> </span></b><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">of
Employee</span></b><span style="font-family: Arial, sans-serif; font-size: 12pt;">.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><u><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">Answer:</span></u></b><span style="font-family: Arial, sans-serif; font-size: 12pt;"> There are many ways to find second highest
salary of Employee in SQL,</span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;"> you can either use SQL Join or Sub
query to solve this problem. Here is SQL query using </span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">Sub query.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">SELECT</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: magenta; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">MAX</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">Empsal</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">)</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">as</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> Salary </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">FROM</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> #Employee<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">where</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> EmpSal </span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">not</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">in(</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">SELECT</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: magenta; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">MAX</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">Empsal</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">)</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">FROM</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> #Employee</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">)</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">(3) Query to find Max
Salary from each</span></b><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;"> </span></b><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">department.</span></b><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><u><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">Answer:</span></u></b><span style="font-family: Arial, sans-serif; font-size: 12pt;"> For this use Group By clause is used
for grouping the records of the database </span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">table(s).This clause creates a single row
for each group and this process is called aggregation.</span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt; line-height: 20.25pt;">To use group by clause
we have to use at least one aggregate function in Select statement. </span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt; line-height: 20.25pt;">We can
use group by clause without where clause.</span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">SELECT</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> DEPARTMENT</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: magenta; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">MAX</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">empsal</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">)</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">as</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> salary </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">FROM</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> #Employee </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">GROUP</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">BY</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">DEPARTMENT<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">(4) Write an SQL Query</span></b><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;"> find number </span></b><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">of employees whose DOJ is </span></b></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">between 01/01/2011
to 31/12/2012.</span></b><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><u><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">Answer:</span></u></b><b><u><span style="font-family: Arial, sans-serif; font-size: 13.5pt;"> </span></u></b><span style="font-family: Arial, sans-serif; font-size: 12pt;">Use BETWEEN operator selects values
within a range. </span><br />
<span style="font-family: Arial, sans-serif; font-size: 12pt;">The values can be numbers, text, or dates.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">SELECT</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: magenta; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">COUNT</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(*)</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">from</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> #Employee </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">WHERE</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> JOINING_DATE </span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">BETWEEN</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'01/01/2011'</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><br />
<span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">AND</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'31/12/2012'</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">(5) Write an SQL Query</span></b><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;"> </span></b><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">find number</span></b><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;"> </span></b><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">of employees according to</span></b><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;"> </span></b></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">Department</span></b><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;"> </span></b><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">whose DOJ is between 01/01/2011 to 31/12/2012.</span></b><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">SELECT</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: magenta; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">COUNT</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(*)</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">as</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> total</span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">DEPARTMENT </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">from</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> #Employee </span><br />
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">WHERE</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> JOINING_DATE </span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">BETWEEN</span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'01/01/2011'</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">AND</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'31/12/2012'</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><br />
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">GROUP</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">BY</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> DEPARTMENT<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">(6) Write an SQL Query
to find employee whose Salary is equal or </span></b></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">greater than 30000</span></b><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">SELECT</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> EmpName </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">FROM</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> #Employee </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">WHERE</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> EmpSal </span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">>=</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">30000<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">(7) SQL Query to</span></b><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;"> find name </span></b><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">of employee whose name Start with ‘K’</span></b><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><u><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">Answer:</span></u></b><b><u><span style="font-family: Arial, sans-serif; font-size: 13.5pt;"> </span></u></b><span style="font-family: Arial, sans-serif; font-size: 12pt;">The LIKE operator is used to search for a
specified pattern in a column.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">SELECT</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">*</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">FROM</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> #Employee </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">WHERE</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> EmpName </span><span style="color: grey; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">like</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'k%'</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><i><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">“I HOPE THIS WILL HELP
YOU”</span></i></b><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
</td>
</tr>
</tbody></table>
<br />
<div class="MsoNormal">
<br /></div>
</div>
Krishna Patilhttp://www.blogger.com/profile/01315946500940726214noreply@blogger.com0tag:blogger.com,1999:blog-9088871595005033660.post-2720111785850945582014-04-06T02:13:00.000-07:002014-09-28T10:11:35.537-07:00Merge cells in Asp.net Gridview Footer as per requirement<div dir="ltr" style="text-align: left;" trbidi="on">
<h1 class="post-title" style="background-color: white; border-bottom-color: rgb(0, 0, 0); border-bottom-style: dotted; border-bottom-width: 0px; border-top-color: rgb(0, 0, 0); border-top-style: dotted; border-top-width: 0px; margin: 0.25em 0px 0px; padding: 0px 0px 4px;">
<table border="0" cellpadding="0" class="MsoNormalTable" style="mso-cellspacing: 1.5pt; mso-yfti-tbllook: 1184; width: 100%px;">
<tbody>
<tr>
<td style="padding: .75pt .75pt .75pt .75pt;"><div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">Here I will explain
how to merge cells in asp.net grid view Footer as per requirement.<o:p></o:p></span></div>
<table border="0" cellpadding="0" class="MsoNormalTable" style="width: 100%px;"><tbody>
<tr><td style="padding: .75pt .75pt .75pt .75pt;"><div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt; line-height: 20.25pt;">write code like as
shown below in your aspx page</span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">html</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">xmlns</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="http://www.w3.org/1999/xhtml"></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">head</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">runat</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="server"></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">title</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;">merger Footer
programmatically as per requirement</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">title</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">head</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">body</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">form</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">id</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="form1"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">runat</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="server"></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">div</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">GridView</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">ID</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="GridView1"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">runat</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="server"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">AutoGenerateColumns</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="false" </span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">OnRowDataBound</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="GridView1_RowDataBound"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">ShowFooter</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="true"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">BackColor</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="#339966"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Columns</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">TemplateField</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">HeaderText</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="Employee
Name"></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">ItemTemplate</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Label</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">ID</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="lblEname"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">runat</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="server"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Text</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">='</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><%#Eval("First_Name") %></span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Label</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">ItemTemplate</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">TemplateField</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">TemplateField</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">HeaderText</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="Salary"></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">ItemTemplate</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Label</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">ID</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="lblsalary"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">runat</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="server"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Text</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">='</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><%#Eval("salary") %></span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Label</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">ItemTemplate</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">TemplateField</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">TemplateField</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">HeaderText</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="City"></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">ItemTemplate</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Label</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">ID</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="lblcity"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">runat</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="server"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Text</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">='</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><%#Eval("city") %></span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Label</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">ItemTemplate</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">TemplateField</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">TemplateField</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">HeaderText</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="Designation"></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">ItemTemplate</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Label</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">ID</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="lblDescription"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">runat</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">="server"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: red; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Text</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">='</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><%#Eval("Description") %></span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">'></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Label</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">ItemTemplate</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">TemplateField</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Columns</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">asp</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">GridView</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">div</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">form</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">body</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"></</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">html</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">></span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;"><br /><br /><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;"> <b>After that write
the following code in code behind</b><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: teal; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">SqlConnection</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> connection=</span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">new</span><span style="color: teal; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">SqlConnection</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">(</span><span style="color: teal; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">ConfigurationManager</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">.ConnectionStrings[</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">"ConnectionString"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">].</span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">ConnectionString.ToString());<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">protected</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">void</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> Page_Load(</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">object</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> sender, </span><span style="color: teal; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">EventArgs</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> e)<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">{<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: teal; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">DataSet</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> ds = </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">new</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: teal; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">DataSet</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">();<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">connection.Open();<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: teal; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">SqlDataAdapter</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> da = </span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">new</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: teal; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">SqlDataAdapter</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">(</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">"SELECT * FROM
dbo.Employee"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">, connection);<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">da.Fill(ds);<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">GridView1.DataSource =
ds;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">GridView1.DataBind();<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">connection.Close();<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">}<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">protected</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">void</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> GridView1_RowDataBound(</span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">object</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> sender, </span><span style="color: teal; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">GridViewRowEventArgs</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> e)<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">{<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">if</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> (e.Row.RowType == </span><span style="color: teal; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">DataControlRowType</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">.Footer)<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">{<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: teal; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">DataSet</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> ds = </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">new</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: teal; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">DataSet</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">();<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;"><br /><br /><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: teal; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">SqlDataAdapter</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> da = </span><span style="color: blue; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">new</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span><span style="color: teal; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">SqlDataAdapter</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">(</span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">"SELECT
Sum(salary) FROM dbo.Employee"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;">, connection);<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="color: green; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">// connection.Open();</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">da.Fill(ds);<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">e.Row.Cells[0].ColumnSpan
= 3;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">e.Row.Cells.RemoveAt(1);<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">e.Row.Cells.RemoveAt(2);<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">e.Row.Cells[0].Text = </span><span style="color: maroon; font-family: "Arial","sans-serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">"<b>Total Payee Salary Of This
Month:-</b>"</span><span style="font-family: Arial, sans-serif; font-size: 12pt;"> +
ds.Tables[0].Rows[0][0];<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">connection.Close();<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">}<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">}<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;"><br /></span></div>
<div class="MsoNormal" style="line-height: 20.25pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: Arial, sans-serif; font-size: 12pt;">Cells mergening logic
are implemented in </span><b><span style="font-family: Arial, sans-serif; font-size: 13.5pt;">GridView1_RowDataBound</span></b><span style="font-family: Arial, sans-serif; font-size: 12pt;"> </span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Arial, sans-serif; font-size: small;"><span style="line-height: 20.25pt;">as you see three cells are </span></span><span style="font-family: Arial, sans-serif;"><span style="line-height: 27px;">merged</span></span><span style="font-family: Arial, sans-serif; font-size: small;"><span style="line-height: 20.25pt;"> to show Employee
total payee.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Arial, sans-serif; font-size: small;"><span style="line-height: 20.25pt;"><br /></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Arial, sans-serif; font-size: small;"><span style="line-height: 20.25pt;"><b>DEMO</b></span></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWdEWemu9khg56QUw0yQPufR2KaCe6WIBZFAG_XnkbDCKRDV0MGOGTAHNhuuK9wg20gR1hK_4eFEN7MTPYnQdCsHjqSiryeddKNg17sT5jT1LwntjFgw_hfS3dkpMHFYFkMnl2K3GTtkhv/s1600/60e378a8-664d-49a3-9f92-0717f1f7d8e0.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWdEWemu9khg56QUw0yQPufR2KaCe6WIBZFAG_XnkbDCKRDV0MGOGTAHNhuuK9wg20gR1hK_4eFEN7MTPYnQdCsHjqSiryeddKNg17sT5jT1LwntjFgw_hfS3dkpMHFYFkMnl2K3GTtkhv/s1600/60e378a8-664d-49a3-9f92-0717f1f7d8e0.jpg" height="93" width="320" /></a></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Arial, sans-serif; font-size: small;"><span style="line-height: 20.25pt;"><b><br /></b></span></span></div>
</td></tr>
</tbody></table>
</td>
</tr>
</tbody></table>
</h1>
</div>
Krishna Patilhttp://www.blogger.com/profile/01315946500940726214noreply@blogger.com0tag:blogger.com,1999:blog-9088871595005033660.post-6637331582575827882013-04-23T17:53:00.003-07:002014-09-28T10:10:43.120-07:00SqlServer-Query-Optimization-Tips<div dir="ltr" style="text-align: left;" trbidi="on">
<h2 style="text-align: center;">
<span style="background-color: white; background-position: initial initial; background-repeat: initial initial; font-family: Arial, sans-serif; font-size: 10.5pt; line-height: 115%;">SqlServer Query Optimization Tips</span></h2>
<div>
<span style="background-color: white; background-position: initial initial; background-repeat: initial initial; font-family: Arial, sans-serif; font-size: 10.5pt; line-height: 115%;"><br /></span></div>
<div>
<table border="0" cellpadding="0" class="MsoNormalTable" style="background: white; mso-cellspacing: 1.5pt; mso-yfti-tbllook: 1184; width: 100%px;">
<tbody>
<tr>
<td style="padding: .75pt .75pt .75pt .75pt;"><div class="MsoNormal" style="margin-left: 0in; text-align: justify; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: Arial, sans-serif; font-size: 9.5pt;">1.<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><!--[endif]--><b><span style="font-family: Verdana, sans-serif; font-size: 9.5pt;">Tip 1: </span></b><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">Always use WHERE Clause in
SELECT Queries while we don’t need all the rows to be returned. This will
help to narrow the return rows else it will perform a whole table scan and
waste the Sql server resources with increasing the network traffic. While
scanning the whole it will lock the Table which may prevent other users to
access the table.</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<b><span style="font-family: Verdana, sans-serif; font-size: 9.5pt;">Tip 2: </span></b><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">It is seen many times
developers use codes like</span><span style="font-family: Verdana, sans-serif; font-size: 9.5pt;"> <b> </b></span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<b><span style="color: navy; font-family: "Courier New"; font-size: 9.5pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">SELECT * FROM OrderTable WHERE LOWER(UserName)='telsa'</span></b><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">Instead of writing it like the below</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<b><span style="color: navy; font-family: "Courier New"; font-size: 9.5pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">SELECT * FROM OrderTable WHERE UserName='telsa'</span></b><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">Infact both the queries does the same work but the 2nd one is
better and retrieves rows more speedly than the first query. Because Sql
Server is not case sensitive</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<b><span style="font-family: Verdana, sans-serif; font-size: 9.5pt;">Tip 3: </span></b><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">While running a query, the
operators used with the WHERE clause directly affect the performance. The
operators shown below are in their decreasing order of their performance.</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: 0in; text-align: justify; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: Arial, sans-serif; font-size: 9.5pt;">1.<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><!--[endif]--><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">=</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: 0in; text-align: justify; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: Arial, sans-serif; font-size: 9.5pt;">2.<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><!--[endif]--><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">>,>=,<, <=</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: 0in; text-align: justify; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: Arial, sans-serif; font-size: 9.5pt;">3.<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><!--[endif]--><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">LIKE</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: 0in; text-align: justify; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: Arial, sans-serif; font-size: 9.5pt;">4.<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><!--[endif]--><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;"><></span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"> </span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<b><span style="font-family: Verdana, sans-serif; font-size: 9.5pt;">Tip 4 : </span></b><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">When we are writing queries
containing NOT IN, then this is going to offer poor performance as the
optimizer need to use nested table scan to perform this activity. This can be
avoided by using EXISTS or NOT EXISTS.</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">When there is a choice to use IN or EXIST, we should go with
EXIST clause for better performance.</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<b><span style="font-family: Verdana, sans-serif; font-size: 9.5pt;">Tip 5: </span></b><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">It is always best practice to
use the Index seek while the columns are covered by an index, this will force
the Query Optimizer to use the index while using IN or OR clauses as a part
of our WHERE clause. </span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<b><span style="color: navy; font-family: "Courier New"; font-size: 9.5pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">SELECT * FROM OrderTable WHERE Status = 1 AND OrderID IN
(406,530,956)</span></b><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">Takes more time than </span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<b><span style="color: navy; font-family: "Courier New"; font-size: 9.5pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">SELECT * FROM OrderTable (INDEX=IX_OrderID) WHERE Status = 1 AND
OrderID IN (406,530,956)</span></b><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
</td>
</tr>
<tr>
<td style="padding: .75pt .75pt .75pt .75pt;"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<b><span style="font-family: Verdana, sans-serif; font-size: 9.5pt;">Tip 6:</span></b><span style="font-family: Verdana, sans-serif; font-size: 9.5pt;"> </span><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">While we use IN, in the sql query it better to use one or more
leading characters in the clause instead of using the wildcard character at
the starting.</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: none windowtext 1.0pt; color: blue; font-family: "Courier New"; font-size: 9.5pt; mso-border-alt: none windowtext 0in; mso-fareast-font-family: "Times New Roman"; padding: 0in;">SELECT * FROM CustomerTable
WHERE CustomerName LIKE 'm%'</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: none windowtext 1.0pt; color: blue; font-family: "Courier New"; font-size: 9.5pt; mso-border-alt: none windowtext 0in; mso-fareast-font-family: "Times New Roman"; padding: 0in;">SELECT * FROM CustomerTable
WHERE CustomerName LIKE '%m'</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">In the first query the Query optimizer is having the ability to
use an index to perform the query and there by reducing the load on sql
server. But in the second query, no suitable index can be created while
running the query.</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<b><span style="font-family: Verdana, sans-serif; font-size: 9.5pt;">Tip 7:</span></b><span style="font-family: Verdana, sans-serif; font-size: 9.5pt;"> </span><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">While there is case to use IN or BETWEEN clause in the query, it
is always advisable to use BETWEEN for better result.</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: none windowtext 1.0pt; color: blue; font-family: "Courier New"; font-size: 9.5pt; mso-border-alt: none windowtext 0in; mso-fareast-font-family: "Times New Roman"; padding: 0in;">SELECT * FROM CustomerTable
WHERE CustomerID BETWEEN (5000 AND 5005)</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">Performs better than</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: none windowtext 1.0pt; color: blue; font-family: "Courier New"; font-size: 9.5pt; mso-border-alt: none windowtext 0in; mso-fareast-font-family: "Times New Roman"; padding: 0in;">SELECT * FROM CustomerTable
WHERE CustomerID IN (5000,5001,5002,5003,5004,5005)</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<b><span style="font-family: Verdana, sans-serif; font-size: 9.5pt;">Tip 8: </span></b><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">Always avoid the use of
SUBSTRING function in the query.</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: none windowtext 1.0pt; color: blue; font-family: "Courier New"; font-size: 9.5pt; mso-border-alt: none windowtext 0in; mso-fareast-font-family: "Times New Roman"; padding: 0in;">SELECT * FROM CustomerTable
WHERE CustomerName LIKE 'n%'</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">Is much better than writing</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: none windowtext 1.0pt; color: blue; font-family: "Courier New"; font-size: 9.5pt; mso-border-alt: none windowtext 0in; mso-fareast-font-family: "Times New Roman"; padding: 0in;">SELECT * FROM CustomerTable
WHERE SUBSTRING(CustomerName,1,1)='n'</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<b><span style="font-family: Verdana, sans-serif; font-size: 9.5pt;">Tip 9 : </span></b><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">The queries having WHERE
clause connected by AND operators are evaluated from left to right in the
order they are written. So certain things should be taken care of like</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: 0in; text-align: justify; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: 'Courier New'; font-size: 10pt;">o<span style="font-family: 'Times New Roman'; font-size: 7pt;"> </span></span><!--[endif]--><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9pt; padding: 0in;">Provide the least likely true
expressions first in the AND. By doing this if the AND expression is false at
the initial stage the clause will end immediately. So it will save execution
time</span><span style="font-family: Arial, sans-serif; font-size: 9pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: 0in; text-align: justify; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: 'Courier New'; font-size: 10pt;">o<span style="font-family: 'Times New Roman'; font-size: 7pt;"> </span></span><!--[endif]--><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9pt; padding: 0in;">If all the parts of the AND
expression are equally like being false then better to put the Complex
expression first. So if the complex works are false then less works to be
done.</span><span style="font-family: Arial, sans-serif; font-size: 9pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<b><span style="font-family: Verdana, sans-serif; font-size: 9.5pt;">Tip 10: </span></b><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">Its sometimes better to
combine queries using UNION ALL instead of using many OR clauses.</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: none windowtext 1.0pt; color: blue; font-family: "Courier New"; font-size: 9.5pt; mso-border-alt: none windowtext 0in; mso-fareast-font-family: "Times New Roman"; padding: 0in;">SELECT CustomerID, FirstName,
LastName FROM CustomerTable</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: none windowtext 1.0pt; color: blue; font-family: "Courier New"; font-size: 9.5pt; mso-border-alt: none windowtext 0in; mso-fareast-font-family: "Times New Roman"; padding: 0in;">WHERE City = 'Wichita' or ZIP
= '67201' or State= 'Kansas'</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">The above query to use and index, it is required to have indexes
on all the 3 columns.</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">The same query can be written as</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: none windowtext 1.0pt; color: blue; font-family: "Courier New"; font-size: 9.5pt; mso-border-alt: none windowtext 0in; mso-fareast-font-family: "Times New Roman"; padding: 0in;">SELECT CustomerID, FirstName,
LastName FROM CustomerTable WHERE City = 'Wichita'</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: none windowtext 1.0pt; color: blue; font-family: "Courier New"; font-size: 9.5pt; mso-border-alt: none windowtext 0in; mso-fareast-font-family: "Times New Roman"; padding: 0in;">UNION ALL</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: none windowtext 1.0pt; color: blue; font-family: "Courier New"; font-size: 9.5pt; mso-border-alt: none windowtext 0in; mso-fareast-font-family: "Times New Roman"; padding: 0in;">SELECT CustomerID, FirstName,
LastName FROM CustomerTable WHERE ZIP = '67201'</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: none windowtext 1.0pt; color: blue; font-family: "Courier New"; font-size: 9.5pt; mso-border-alt: none windowtext 0in; mso-fareast-font-family: "Times New Roman"; padding: 0in;">UNION ALL</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: none windowtext 1.0pt; color: blue; font-family: "Courier New"; font-size: 9.5pt; mso-border-alt: none windowtext 0in; mso-fareast-font-family: "Times New Roman"; padding: 0in;">SELECT CustomerID, FirstName,
LastName FROM CustomerTable WHERE State= 'Kansas'</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">Both the queries will provide same results but if there is only
an index on City and no indexes on the zip or state, then the first query
will not use the index and a table scan is performed. But the 2nd one will
use the index as the part of the query.</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<b><span style="font-family: Verdana, sans-serif; font-size: 9.5pt;">Tip 11: </span></b><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;"> While the select
statement contains a HAVING clause, its better to make the WHERE clause to do
most of the works (removing the undesired rows) for the Query instead of letting
the HAVING clause to do the works.</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;"> e.g. in a SELECT statement with GROUP BY and HAVING
clause, things happens like first WHERE clause will select appropriate rows
then GROUP BY divide them to group of rows and finally the HAVING clause have
less works to perform, which will boost the performance.</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<b><span style="font-family: Verdana, sans-serif; font-size: 9.5pt;">Tip 12: </span></b><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">Let’s take 2 situations</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: 0in; text-align: justify; text-indent: -0.25in;">
<span style="font-family: Courier New; font-size: x-small;"> </span><span style="font-family: 'Times New Roman'; font-size: 7pt;"> </span><!--[endif]--><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9pt; padding: 0in;">A query that takes 30 seconds
to run, and then displays all of the required results.</span><span style="font-family: Arial, sans-serif; font-size: 9pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: 0in; text-align: justify; text-indent: -0.25in;">
<span style="font-family: Courier New; font-size: x-small;"> </span><span style="font-family: 'Times New Roman'; font-size: 7pt;"> </span><!--[endif]--><span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9pt; padding: 0in;">A query that takes 60 seconds
to run, but displays the first screen full of records in less than 1 second.</span><span style="font-family: Arial, sans-serif; font-size: 9pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="border: 1pt none windowtext; font-family: Verdana, sans-serif; font-size: 9.5pt; padding: 0in;">By looking at the above 2 situations a developer may choose
to follow the 1st option, as it uses less resources and faster in
performance. But actually the 2nd one is more acceptable by a DBA. An
application may provide immediate feedback to the user, but actually this may
not be happening at the background.</span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 6.25pt; text-align: justify;">
<span style="font-family: Arial, sans-serif; font-size: 9.5pt;">We can use a hint like<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 6.25pt; text-align: justify;">
<span style="font-family: Arial, sans-serif; font-size: 9.5pt;">SELECT * FROM
CustomerTable WHERE City = 'Wichita' OPTION(FAST n)<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 6.25pt; text-align: justify;">
<span style="font-family: Arial, sans-serif; font-size: 9.5pt;">where n = number of
rows that we want to display as fast as possible. This hint helps to return
the specified number of rows as fast as possible without bothering about the
time taken by the overall query.<o:p></o:p></span></div>
</td>
</tr>
</tbody></table>
</div>
</div>
Krishna Patilhttp://www.blogger.com/profile/01315946500940726214noreply@blogger.com0tag:blogger.com,1999:blog-9088871595005033660.post-25204831147942267422013-04-21T18:33:00.000-07:002014-09-28T10:10:43.113-07:00 ROLLUP and CUBE.<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="aboveUnitContent" style="background-color: white; margin-bottom: 15px; margin-top: 15px;">
<div class="userContentWrapper">
<h2 style="color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 11px; line-height: 14px; text-align: left;">
<span class="userContent"><b>Let me give you one example where we can use ROLLUP and CUBE.</b></span></h2>
<div class="_wk">
<span class="userContent"><span style="color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 18px;"> I am developing the Employee base application and administrator wants the report which shows Employee Payment. You will say that’s really easy and can write the query as shown below,</span><br /></span><br />
<div style="color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 18px; text-align: left;">
<span class="userContent">create table EMP</span></div>
<span class="userContent">
<div style="color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 18px; text-align: left;">
( id int not null identity(1,1),</div>
<div style="color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 18px; text-align: left;">
Employeeno varchar(max) NOT NULL,</div>
<div style="color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 18px; text-align: left;">
PAYMENT_DATE varchar(max) NOT NULL,</div>
<div style="color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 18px; text-align: left;">
Amount DECIMAL(7,2) NOT NULL);</div>
<div style="text-align: left;">
<span style="color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 18px;"><br /></span></div>
<span style="color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 18px;">INSERT INTO EMP VALUES ( 6, '1980-12-08', 100);</span><br /><span class="text_exposed_show" style="color: #333333; display: inline; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 18px;">INSERT INTO EMP VALUES ( 44, ' 1981-05-05', 75);<br />INSERT INTO EMP VALUES ( 104, ' 1984-12-08',50);<br />INSERT INTO EMP VALUES ( 44, '1984-11-12', 75);<br />INSERT INTO EMP VALUES ( 8, ' 1980-12-08', 25);<br />INSERT INTO EMP VALUES ( 44, ' 1980-12-08',25);<br />INSERT INTO EMP VALUES ( 27, ' 1984-11-12 ', 75);<br />INSERT INTO EMP VALUES ( 44, ' 1982-12-30', 30);<br /><br />Fig – (1) Group By clause.<br />SELECT Employeeno,PAYMENT_DATE,SUM(Amount) as total<br />FROM EMP GROUP BY Employeeno,PAYMENT_DATE<br />Which will returns the result as shown Fig – (1)<br /><br />Here ROLLUP and CUBE comes into the picture and help us. The shown Fig – (2) result is generated using ROLLUP. ROLLUP adds new row for each column used in GROUP BY clause.<br /><br /><br />CASE WHEN GROUPING(Employeeno)=1 THEN 'TOTAL PAYEE'<br />ELSE Employeeno END Employeeno,<br />CASE WHEN GROUPING(PAYMENT_DATE)=1 THEN 'SUB TOTAL'<br />ELSE PAYMENT_DATE END PAYMENT_DATE,<br />SUM(Amount)<br />FROM EMP GROUP BY Employeeno,PAYMENT_DATE WITH ROLLUP<br /><br />In fig – 2 we have TOTAL PAYEE by employeeno, lets assume you want the TOTAL PAYEE by PAYMENT_DATE also. Here you have to use CUBE as shown below See Result In fig – 3 ,<br /><br />SELECT<br />CASE WHEN GROUPING(Employeeno)=1 THEN 'TOTAL PAYEE'<br />ELSE Employeeno END Employeeno,<br />CASE WHEN GROUPING(PAYMENT_DATE)=1 THEN 'SUB TOTAL'<br />ELSE PAYMENT_DATE END PAYMENT_DATE,<br />SUM(Amount)<br />FROM EMP GROUP BY Employeeno,PAYMENT_DATE WITH CUBE</span></span></div>
<div class="_wk">
<span class="userContent"><span class="text_exposed_show" style="color: #333333; display: inline; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 18px;"><br /></span></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4Ki0Qjo8ZoW3SQemXTs9jKabytzkItcIjhgT-kGX7BH2xUQ72WZFlzVJG7rOcd-T5g8-BqCRNf5-K4kFZZf1cikqpvQZidesVafeZDKfglZ8B67_o0r6Q6RVEZS_539WmgqwR66miYE3w/s1600/602792_539627092722079_1515070497_n.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="236" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4Ki0Qjo8ZoW3SQemXTs9jKabytzkItcIjhgT-kGX7BH2xUQ72WZFlzVJG7rOcd-T5g8-BqCRNf5-K4kFZZf1cikqpvQZidesVafeZDKfglZ8B67_o0r6Q6RVEZS_539WmgqwR66miYE3w/s640/602792_539627092722079_1515070497_n.jpg" width="640" /></a></div>
<div class="_wk">
<span class="userContent"><span class="text_exposed_show" style="color: #333333; display: inline; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 18px;"><br /></span></span></div>
<div style="color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 11px; line-height: 14px;">
<span class="userContent"><span class="text_exposed_show" style="display: inline;"><br /></span></span></div>
</div>
</div>
<div class="photoUnit clearfix" style="background-color: white; color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 11px; line-height: 14px; margin: 0px -15px; position: relative; zoom: 1;">
<div class="_53s uiScaledThumb photo photoWidth1" data-ft="{"tn":"E"}" data-gt="{"fbid":"539627092722079"}" style="float: left; position: relative;">
<a ajaxify="http://www.facebook.com/photo.php?fbid=539627092722079&set=a.218762991475159.63633.218760331475425&type=1&relevant_count=1&src=https%3A%2F%2Ffbcdn-sphotos-g-a.akamaihd.net%2Fhphotos-ak-prn1%2F602792_539627092722079_1515070497_n.jpg&size=851%2C315&theater" class="_6i9" href="http://www.facebook.com/photo.php?fbid=539627092722079&set=a.218762991475159.63633.218760331475425&type=1&relevant_count=1" rel="theater" style="color: #3b5998; cursor: pointer; text-decoration: none;"></a></div>
</div>
</div>
Krishna Patilhttp://www.blogger.com/profile/01315946500940726214noreply@blogger.com0tag:blogger.com,1999:blog-9088871595005033660.post-77123487392439627052013-04-21T18:14:00.000-07:002014-09-28T10:11:35.539-07:00Interview Question<div dir="ltr" style="text-align: left;" trbidi="on">
<h2 style="text-align: center;">
<span class="userContent">Interview Questions </span></h2>
<div>
<span class="userContent"><div>
What is an Index in SQL Server?</div>
<div>
<br /></div>
<div>
An index is a physical structure containing pointers to the data. Indexes are created in an existing table to locate the data more efficiently and quickly (Read more Here)</div>
<div>
<br /></div>
<div>
What is the Difference between Clustered and a Non-clustered Index in SQL Server?</div>
<div>
<br /></div>
<div>
The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db. (Read more Here)</div>
<div>
<br /></div>
<div>
What is Collation?</div>
<div>
<br /></div>
<div>
Collation refers to a set of rules that determine how the data is sorted and compared (Read more Here)</div>
<div>
<br /></div>
<div>
What are Primary Keys and Foreign Keys in SQL Server?</div>
<div>
<br /></div>
<div>
Primary key is used to identify records in table uniquely and it won’t allow null values and duplication of data. Only one primary key is allowed for each table ()</div>
<div>
Foreign key in one table points to primary key in another table. Foreign key is used to prevent the actions that would destroy the link between two tables. </div>
<div>
<br /></div>
<div>
What’s the Difference between a Primary Key and a Unique Key? </div>
<div>
<br /></div>
<div>
Primary key is used to identify records in table uniquely and it won’t allow null values and duplication of data. Only one primary key is allowed for each table.</div>
<div>
Unique key is same as primary key only the difference is unique key allow null values.</div>
</span></div>
</div>
Krishna Patilhttp://www.blogger.com/profile/01315946500940726214noreply@blogger.com0tag:blogger.com,1999:blog-9088871595005033660.post-17023360215212727792013-04-20T22:16:00.000-07:002014-09-28T10:10:43.107-07:00First Char is in Uppercase<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<h2 style="text-align: left;">
To Find string is completely in uppercase?<br /> or<br /><div style="text-align: center;">
if First Char is in Uppercase</div>
<div style="text-align: center;">
<br /></div>
<div style="text-align: center;">
<blockquote class="tr_bq">
<span style="font-weight: normal;">Im my previous Project i need to find that string first char are upper or not and according that need to make that row active or in active</span></blockquote>
Let me Show you How to do that<br />
<br />
<br />
<span style="font-weight: normal;">---Create Table</span><br />
<span style="font-weight: normal;"><br /></span>
<span style="font-weight: normal;">create table duplicatetest</span><span style="font-weight: normal;">(</span><br />
<span style="font-weight: normal;">id int identity(1,1),</span><br />
<span style="font-weight: normal;">name varchar(max),</span><br />
<span style="font-weight: normal;">Active int </span>)<br />
<br />
<br />
<br />
<span style="font-weight: normal;"> ---Insert Some Record</span><br />
<span style="font-weight: normal;"><br /></span>
<span style="font-weight: normal;"> insert into duplicatetest(name,Active) values('krishna patil',1)</span><br />
<span style="font-weight: normal;"> insert into duplicatetest(name,</span><span style="font-weight: normal;">Active</span><span style="font-weight: normal;">) values('Mitesh patil',1)</span><br />
<span style="font-weight: normal;"> insert into duplicatetest(name</span><span style="font-weight: normal;">,Active</span><span style="font-weight: normal;">) values('Rahul Joshi',1)</span><br />
<span style="font-weight: normal;"> insert into duplicatetest(name</span><span style="font-weight: normal;">,Active</span><span style="font-weight: normal;">) values('krishna patil',1)</span><br />
<span style="font-weight: normal;"> insert into duplicatetest(name</span><span style="font-weight: normal;">,Active</span><span style="font-weight: normal;">) values('sagar Patel',1)</span><br />
<span style="font-weight: normal;"> insert into duplicatetest(name</span><span style="font-weight: normal;">,Active</span><span style="font-weight: normal;">) values('Mitesh patil',1)</span><br />
<span style="font-weight: normal;"><br /></span>
<span style="font-weight: normal;">To check first char is in Upper use following Query</span><br />
<span style="font-weight: normal;"><br /></span>
<span style="font-weight: normal;">update </span><span style="font-weight: normal;">duplicatetest</span><span style="font-weight: normal;"> set active=0 where ASCII(substring(name,1,1))=ASCII(upper(substring(name,1,1) ))</span><br />
<blockquote class="tr_bq">
<br /></blockquote>
</div>
</h2>
<div style="text-align: left;">
<br /></div>
</div>
Krishna Patilhttp://www.blogger.com/profile/01315946500940726214noreply@blogger.com0tag:blogger.com,1999:blog-9088871595005033660.post-37684978782663561922013-04-18T00:33:00.000-07:002014-09-28T10:10:43.115-07:00difference between DDL, DML and DCL commands<div dir="ltr" style="text-align: left;" trbidi="on">
<h2 style="text-align: left;">
<span style="font-weight: normal;">What are the difference between DDL, DML and DCL commands?</span></h2>
<div>
<div>
DDL</div>
<div>
<br /></div>
<div>
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:</div>
<div>
CREATE - to create objects in the database</div>
<div>
ALTER - alters the structure of the database</div>
<div>
DROP - delete objects from the database</div>
<div>
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed</div>
<div>
COMMENT - add comments to the data dictionary</div>
<div>
RENAME - rename an object</div>
<div>
<br /></div>
<div>
DML</div>
<div>
<br /></div>
<div>
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:</div>
<div>
SELECT - retrieve data from the a database</div>
<div>
INSERT - insert data into a table</div>
<div>
UPDATE - updates existing data within a table</div>
<div>
DELETE - deletes all records from a table, the space for the records remain</div>
<div>
MERGE - UPSERT operation (insert or update)</div>
<div>
CALL - call a PL/SQL or Java subprogram</div>
<div>
EXPLAIN PLAN - explain access path to data</div>
<div>
LOCK TABLE - control concurrency</div>
<div>
DCL</div>
<div>
<br /></div>
<div>
Data Control Language (DCL) statements. Some examples:</div>
<div>
GRANT - gives user's access privileges to database</div>
<div>
REVOKE - withdraw access privileges given with the GRANT command</div>
<div>
TCL</div>
<div>
<br /></div>
<div>
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.</div>
<div>
COMMIT - save work done</div>
<div>
SAVEPOINT - identify a point in a transaction to which you can later roll back</div>
<div>
ROLLBACK - restore database to original since the last COMMIT</div>
<div>
SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use</div>
</div>
</div>
Krishna Patilhttp://www.blogger.com/profile/01315946500940726214noreply@blogger.com1tag:blogger.com,1999:blog-9088871595005033660.post-18050968990010874722013-04-16T22:33:00.000-07:002014-09-28T10:10:43.110-07:00 Query To Get Count of Duplicate Records<div dir="ltr" style="text-align: left;" trbidi="on">
<h2 style="text-align: center;">
Query To Get Count of Duplicate Records</h2>
<h3 style="text-align: left;">
Here I will explain How to find duplicate records in SQL server.</h3>
<div>
<br /></div>
<div>
<div>
---Create Table</div>
<div>
<br /></div>
<div>
<br />
<span style="color: blue;">create table</span> duplicatetest<br />
(<br />
id<span style="color: blue;"> int identity(1,1)</span>,<br />
<span style="color: blue;">name varchar(max)</span><br />
)<br />
</div>
</div>
<div>
<br /></div>
<div>
<div>
---Insert Some Record</div>
<div>
<br /></div>
<div>
<span style="color: blue;"> insert into</span> duplicatetest(name) <span style="color: blue;">values</span>('krishna patil')</div>
<div>
<span style="color: blue;"> insert into</span> duplicatetest(name) <span style="color: blue;">values</span>('Mitesh patil')</div>
<div>
<span style="color: blue;"> insert into</span> duplicatetest(name) <span style="color: blue;">values</span>('Rahul Joshi')</div>
<div>
<span style="color: blue;"> insert into</span> duplicatetest(name) <span style="color: blue;">values</span>('krishna patil')</div>
<div>
<span style="color: blue;"> insert into</span> duplicatetest(name) <span style="color: blue;">values</span>('Sagar Patel')</div>
<div>
<span style="color: blue;"> insert into</span> duplicatetest(name) <span style="color: blue;">values</span>('Mitesh patil')</div>
</div>
<div>
<br /></div>
<div>
<div>
<br /></div>
<div>
<span style="color: blue;"> select </span>* <span style="color: blue;">from</span> duplicatetest<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnKjTjNHqn6ZnAeXquFrr3Ob8gUl2ctGyelES3NCE3fWTWvWkgXchRXTQYMUEmR2KDb8O64-_a96sySdvs9fBQNgQrCPkFv174p2EYuJlq4wxX7dfqDGFcGzhpQ4Zs_Jz7GTzIWpt-yhZA/s1600/1.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnKjTjNHqn6ZnAeXquFrr3Ob8gUl2ctGyelES3NCE3fWTWvWkgXchRXTQYMUEmR2KDb8O64-_a96sySdvs9fBQNgQrCPkFv174p2EYuJlq4wxX7dfqDGFcGzhpQ4Zs_Jz7GTzIWpt-yhZA/s1600/1.bmp" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
Now I want to know the duplicate records count that means how many times each duplicate record exists in datatable for that I written a query like this</div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both;">
<span style="color: blue;">SELECT </span>Name,<span style="color: blue;"> COUNT</span>(*) as duplicate</div>
<div class="separator" style="clear: both;">
<span style="color: blue;">FROM </span>duplicatetest</div>
<div class="separator" style="clear: both;">
<span style="color: blue;">GROUP BY</span> Name</div>
<div class="separator" style="clear: both;">
<span style="color: blue;">HAVING COUNT</span>(*) > 1 <span style="color: blue;">Order By</span> Name</div>
<div class="separator" style="clear: both;">
<br /></div>
<div class="separator" style="clear: both;">
<br /></div>
<div class="separator" style="clear: both;">
</div>
<div class="separator" style="clear: both;">
<br /></div>
<div class="separator" style="clear: both;">
After run above query we will get each record duplicate count that would be like this</div>
<div class="separator" style="clear: both;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggPkN4XG99r3EGogTkAeggC_F19vNAq4ON_UX-weM1Yn8l1GF0U2-S6dFPODFO5FXBw-PLGpb2w_9RKthXJpIZOoSy4n7E4z-a4IXaUslDPrgl2GKHJCkQvQFZoYRY3eOI2B1FhW8NpAal/s1600/2.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggPkN4XG99r3EGogTkAeggC_F19vNAq4ON_UX-weM1Yn8l1GF0U2-S6dFPODFO5FXBw-PLGpb2w_9RKthXJpIZOoSy4n7E4z-a4IXaUslDPrgl2GKHJCkQvQFZoYRY3eOI2B1FhW8NpAal/s1600/2.bmp" /></a></div>
<div class="separator" style="clear: both;">
<br /></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
</div>
</div>
</div>
Krishna Patilhttp://www.blogger.com/profile/01315946500940726214noreply@blogger.com0tag:blogger.com,1999:blog-9088871595005033660.post-71346523870531796372013-04-15T00:00:00.002-07:002014-09-28T10:10:43.122-07:00PATINDEX Example <div dir="ltr" style="text-align: left;" trbidi="on">
<h2 style="text-align: left;">
<b>Let me Show Use of PATINDEX in Sql</b></h2>
<div>
<br /></div>
<div>
<div>
<b>PATINDEX Example</b> </div>
<div>
<br /></div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span>DECLARE @myString varchar(100);</div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span>DECLARE @largestInt int;</div>
<div>
<br /></div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span>SET @myString = 'R2D2456778'</div>
<div>
<br /></div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span>SET @mystring = @myString;</div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span>select left(@myString, PATINDEX('%[a-z]%',substring(@myString,1,1)))</div>
</div>
</div>
Krishna Patilhttp://www.blogger.com/profile/01315946500940726214noreply@blogger.com0