Sunday, 6 April 2014

This article demonstrates some commonly asked SQL queries in a .NET/SQL job interview.
Let’s create Employee table and insert data as shown below 
to perform basic sql query operation.
CREATE TABLE #Employee
(
EmpID INT Identity,
Empname Varchar(100),
EmpSal Decimal (10,2),
DEPARTMENT varchar(100),
JOINING_DATE varchar(100)
)

INSERT INTO #Employee VALUES ('Krishna',50000,'System Administrator','10/01/2011');
INSERT INTO #Employee VALUES ('John',30000,'Network Engineer','23/08/2011');
INSERT INTO #Employee VALUES ('Vicky',22000,'Help Desk','23/01/2012');
INSERT INTO #Employee VALUES ('Mayur',35000,'System Administrator','05/04/2012');
INSERT INTO #Employee VALUES ('Nitin',25000,'Help Desk','20/05/2011');
INSERT INTO #Employee VALUES ('Sagar',25000,'Network Engineer','25/01/2011');

One of Basic query
 (1) How can we find the highest salary in a table?
Answer:The aggregate function SQL MAX() is used to find the maximum 
value or highest value of a certain column or expression. This function is useful to 
determine the largest of all selected values of a column.
SELECT MAX(Empsal) as salary FROM #Employee

(2) SQL Query to find second highest salary of Employee.
Answer: There are many ways to find second highest salary of Employee in SQL,
 you can either use SQL Join or Sub query to solve this problem. Here is SQL query using 
Sub query.
SELECT MAX(Empsal) as Salary FROM #Employee
where EmpSal not in(SELECT MAX(Empsal) FROM #Employee)

(3) Query to find Max Salary from each department.
Answer:  For this use Group By clause is used for grouping the records of the database 
table(s).This clause creates a single row for each group and this process is called aggregation.
To use group by clause we have to use at least one aggregate function in Select statement. 
We can use group by clause without where clause.
SELECT DEPARTMENT, MAX(empsal) as salary FROM #Employee GROUP BY 
DEPARTMENT

(4) Write an SQL Query find number of employees whose DOJ is 
between 01/01/2011 to 31/12/2012.
Answer: Use BETWEEN operator selects values within a range. 
The values can be numbers, text, or dates.
SELECT COUNT(*) from #Employee WHERE JOINING_DATE BETWEEN '01/01/2011' 
AND '31/12/2012'

(5) Write an SQL Query find number of employees according to 
Department whose DOJ is between 01/01/2011 to 31/12/2012.
SELECT COUNT(*) as total,DEPARTMENT from #Employee 
WHERE JOINING_DATE BETWEEN'01/01/2011' AND '31/12/2012' 
GROUP BY DEPARTMENT

(6) Write an SQL Query to find employee whose Salary is equal or 
greater than 30000
SELECT EmpName FROM #Employee WHERE EmpSal >=30000

(7) SQL Query to find name of employee whose name Start with ‘K’
Answer: The LIKE operator is used to search for a specified pattern in a column.
SELECT * FROM #Employee WHERE EmpName like 'k%'

“I HOPE THIS WILL HELP YOU”


Here I will explain how to  merge cells in asp.net grid view Footer  as per requirement.
write code like as shown below in your aspx page
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>merger Footer programmatically as per requirement</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" 
OnRowDataBound="GridView1_RowDataBound" ShowFooter="true" BackColor="#339966" >
<Columns>
<asp:TemplateField HeaderText="Employee Name">
<ItemTemplate>
<asp:Label ID="lblEname" runat="server" Text='<%#Eval("First_Name") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Salary">
<ItemTemplate>
<asp:Label ID="lblsalary" runat="server" Text='<%#Eval("salary") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<ItemTemplate>
<asp:Label ID="lblcity" runat="server" Text='<%#Eval("city") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Designation">
<ItemTemplate>
<asp:Label ID="lblDescription" runat="server" Text='<%#Eval("Description") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>


 After that write the following code in code behind
SqlConnection connection=
 newSqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].
ConnectionString.ToString());
protected void Page_Load(object sender, EventArgs e)
{
DataSet ds = new DataSet();
connection.Open();
SqlDataAdapter da = 
new SqlDataAdapter("SELECT * FROM dbo.Employee", connection);
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
connection.Close();
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Footer)
{
DataSet ds = new DataSet();


SqlDataAdapter da = new SqlDataAdapter("SELECT Sum(salary) FROM dbo.Employee", connection);
// connection.Open();
da.Fill(ds);
e.Row.Cells[0].ColumnSpan = 3;
e.Row.Cells.RemoveAt(1);
e.Row.Cells.RemoveAt(2);
e.Row.Cells[0].Text = "<b>Total Payee Salary Of This Month:-</b>" + ds.Tables[0].Rows[0][0];
connection.Close();
}
}

Cells mergening logic are implemented in GridView1_RowDataBound 
as you see three cells are merged to show Employee total payee.

DEMO