SQL JOINS:

                SQL joins has five type of  keywords.

namely,

      INNER  JOIN

      OUTER  JOIN

      LEFT  JOIN

      RIGHT  JOIN

      FULL  JOIN

INNER JOIN:

The INNER JOIN keyword return rows when there is at least one match in both tables.

Syntax:

SELECT column_name(s) FROM tablename1 as t1 INNER JOIN tablename2 as t2 ON t1.column_name=t2.column_name

SQL INNER JOIN Example

The table “Customer” is,

Id	Name	Address	City
1	Vaishnavi	10,Anna Nagar	Bangalore
2	Shajil	23,T.Nagar	Chennai
3	Vinoth	20,Sivaji Street	Chennai

The  table “Order” is,

O_Id	OrderNo	Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Query:

SELECT C.Name, C.City, O.OrderNo FROM Customer as C INNER JOIN Orders as O ON C.Id=O.Id

The result-set will be:

Name	City	OrderNo
Vaishnavi	Bangalore	22456
Vaishnavi	Bangalore	24562
Vinoth	Chennai	77895
Vinoth	Chennai	44678

Note that, the INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows in "Customer" that do not have matches in "Orders", those rows will NOT be listed.

To Find Second Highest Salary from Employee Table in SQL

Here we are going to find the second highest salary in the Table

Employess:

EmpId EmpName Salary 1 sajil 5000 2 vinoth 5865 3 viji 5265 4 Rajiv 20000 5 Mathan 7890

SQL Statement:

SELECT MIN(Salary)

FROM Employees

WHERE EmpID IN

(

SELECT TOP 2 EmpID

FROM Employees

ORDER BY Salary Desc

)

The same query can be re-written using a derived table, as shown below, and it performs twice
as fast as the above query:

SELECT MIN(Salary)
FROM
(
SELECT TOP 2 Salary
FROM Employees
ORDER BY Salary DESC
) AS A

Result LookLike This:

EmpId	EmpName	Salary
5	Mathan	7890

SQL Join - Statement:

SQL Join - Statement:

          SQL joins are used to gather and manipulate data across several tables based on a relationship between certain columns in these tables.

Note: These tables should have common columns.

For Example:

 The tables Order and Part shown in below,

Order Table:

ORDERDATE	NAME	PARTNUM	REMARKS
15-MAY-1996	TRUE WHEEL	42	PAID
19-MAY-1996	TRUE WHEEL	76	PAID
30-MAY-1996	BIKE SPEC	54	PAID
17-JAN-1996	LE SHOPPE	76	PAID
1-JUN-1996	AAA BIKE	10	PAID

Part Table:

PARTNUM	DESCRIPTION	PRICE
54	PEDALS	54.25
42	SEATS	24.50
46	TIRES	15.25
23	MOUNTAIN BIKE	350.45
76	ROAD BIKE	530.00
10	TANDEM	1200.00

Now join PART and ORDERS:

SELECT O.ORDERDATE, O.NAME, O.PARTNUM, P.PARTNUM, P.DESCRIPTION

FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM

The Result Set is,

ORDERDATE	NAME	PARTNUM	PARTNUM	DESCRIPTION
15-MAY-1996	TRUE WHEEL	42	42	SEATS
30-MAY-1996	BIKE SPEC	54	54	PEDALS
1-JUN-1996	AAA BIKE	10	10	TANDEM

[Note that PARTNUM fields that are common to both tables]

Stored Procedure in SQL with Simple Select Query in C#

Here we are going to learn how to use select query in asp.net  with c# using stored procedure,first we have to create table which we going to use in the stored procedure as shown below.We  Are using 3 tier architecture
CREATE TABLE TBL_IEB_User
(
UserID INT IDENTITY(1,1) PRIMARY KEY,
FullName VARCHAR(250) NOT NULL,
EmailAddress VARCHAR(500),
UserName VARCHAR(50) NOT NULL,
Password VARCHAR(50)
)
CREATE Procedure SP_TBL_IEB_User

(  
@UserName varchar(50),
@Password varchar(50)
)
AS
BEGIN
 Select * From TBL_IEB_User Where UserName=@UserName AND Password=@Password
END

Presentation Layer:

C#:
Login.cs:

 protected void btnSubmit_Click(object sender, EventArgs e)
        {
            DataTable dtLogin = new DataTable();
            LoginBL objloginBL = new LoginBL();
            dtLogin = objloginBL.getLogIn(txtUserName.Text, txtPassword.Text);
            if (dtLogin.Rows.Count > 0)
            {
                Session["UserID"] = dtLogin.Rows[0]["UserID"];
                Session["UserName"] = dtLogin.Rows[0]["UserName"];
                Session["FullName"] = dtLogin.Rows[0]["FullName"];
                Session["EmailAddress"] = dtLogin.Rows[0]["Emailaddress"];
                Response.Redirect("Home.aspx");
            }
            else {
                lblErrorMsg.Text = "Invalid UserName/Password !";
            }
         

        }
bussiness Layer
LoginBL.cs:

public class LoginBL
    {
        public DataTable getLogIn(string UserName,string Password)
        {
            ExpensesDL objExpDL = new ExpensesDL();
            return objExpDL.LogIn(UserName, Password);

        }
    }

LoginDL.cs:



 public DataTable LogIn(string UserName, string Password)
        {
            using (SqlConnection objCon = new SqlConnection(new Common().GetConnectionString()))
            {
                objCon.Open();
                DataSet ds = new DataSet();
                using (SqlCommand objCmd = new SqlCommand("SP_TBL_IEB_User", objCon))
                {
                    objCmd.CommandType = CommandType.StoredProcedure;
                    objCmd.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value = UserName;
                    objCmd.Parameters.Add("@Password", SqlDbType.VarChar, 50).Value = Password;
                 
                    SqlDataAdapter Da = new SqlDataAdapter(objCmd);
                    Da.Fill(ds);
                    return ds.Tables[0];

                }
            }
        }

Make menu using hover(css) in asp.net

<%@ Master Language="C#" AutoEventWireup="true" CodeBehind="IEB.master.cs" Inherits="IEB.IEB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<asp:ContentPlaceHolder ID="head" runat="server">
</asp:ContentPlaceHolder>
<link href="CSS/IEBStyleSheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
     <form id="form1" runat="server">
          <div class="header">
        <div class="logo">IEB</div>
</div>
    <div class="welcome"><span id="spWelcomeMsg" runat="server"></span><span>&nbsp;&nbsp;&nbsp;[&nbsp; <%=DateTime.Now.ToLongDateString() %>&nbsp;]&nbsp;&nbsp;</span></div>

    <div class="bodycontent">

        <div class="leftmenu">

            <ul class="sidemenu">

                <a href="Home.aspx"><li>Home</li></a>

                <a href="#"><li>Income</li></a>

                <a href="Expense.aspx"><li>Expenses</li></a>

                <a href="Budget.aspx"><li>Budget</li></a>

                <a href="#"><li>Logout</li></a>

            </ul>

        </div>

        <div class="content">

            <asp:ContentPlaceHolder ID="ContentPlaceHolder1" runat="server">

            </asp:ContentPlaceHolder>

        </div>

    </div>

    </form>

</body>

</html>

Style sheet:

.sidemenu
{
  padding:15px 5px 5px 5px;
}
.sidemenu a li
{
    list-style:none;

    margin:5px 5px 5px 5px;

    padding:5px 5px 5px 3px;

    text-align:left;

    text-decoration:none;

    border:1px solid white;

    color:White;

    cursor:pointer;

}
.sidemenu li:hover
{
   background-color:White;

    color:#25C2EB !important;

}

.sidemenu a
{
      color:White;

    font-weight:bold;

    text-decoration:none;

}