4 Nov 2017

sql join with example


Complete sql join with example:-

CREATE DATABASE Join_Concepts

USE Join_Concepts

CREATE TABLE Courses 
(
CID INT UNIQUE,
CName VARCHAR(30) NOT NULL,
C_dur SMALLINT CHECK (C_dur = 180 OR C_dur = 240)
)

INSERT INTO Courses VALUES (101, 'Ethical Haking', 180), (102, 'Networking',180)
INSERT INTO Courses VALUES (103, 'SQL_DBA', 240)

select * from Courses

TABLE 2:-

CREATE TABLE STUDENTS 
(
S_ID INT UNIQUE,
Sname VARCHAR(30) NOT NULL,
Sage TINYINT, 
Sgender CHAR CHECK (Sgender IN ('M','F')),
S_Courses_id INT REFERENCES Courses(CID)  


INSERT INTO STUDENTS VALUES (10001, 'Pooja', 23, 'F', 101), 
 (10002, 'Shivani', 31, 'F', 101), (10003, 'Palak', 23, 'F', 102),
  (10004, 'Neelam', 31, 'F', 102), (10005, 'Monika', 23, 'F', 102),
   (10006, 'Varsha', 31, 'F', 102), (10007, 'Rekha', 23, 'F', 101),
    (10008, 'Manju', 31, 'F', 101), (10009, 'Shikha', 23, 'F', 101)

select * from STUDENTS

TABLE 3: 

CREATE TABLE STAFF
(
STF_ID INT IDENTITY (100001,1) PRIMARY KEY,
STF_name VARCHAR(30) NOT NULL,
STF_age TINYINT, 
STF_gender CHAR CHECK (STF_gender IN ('M','F')),
STF_CID INT REFERENCES Courses(CID) 
)



INSERT INTO STAFF VALUES ('Nisha', 33, 'F', 101), 
 ('Jeetu', 31, 'M', 101),('Shiva', 39, 'M', 101)



 SELECT * FROM Courses -- 3R
 SELECT * FROM STUDENTS -- 9R
 SELECT * FROM STAFF -- 3R


 REQUREMENT #1: Report list of all courses, respective students

 SELECT * FROM Courses 
 SELECT * FROM STUDENTS 

  SELECT * FROM Courses, STUDENTS   -- 27 ROWS. [ 3 COURSES, 9 STUDENTS]


    SELECT * FROM Courses, STUDENTS 
WHERE
Courses.CID = STUDENTS.S_Courses_id

SELECT * FROM Courses, STUDENTS 
WHERE
Courses.CID = STUDENTS.S_Courses_id
ORDER BY Courses.CID ASC

CROSS JOIN :-

SELECT * FROM Courses CROSS JOIN STUDENTS 
WHERE -- THIS CONDITION IS EVALUATED / CHECKED AFTER JOIN. SEQUENTIAL
Courses.CID = STUDENTS.S_Courses_id
ORDER BY Courses.CID ASC


ALTERNATIVE SOLUTION TO ABOVE QUERY:

SELECT * FROM Courses INNER JOIN STUDENTS 
ON  -- THIS CONDITION IS EVALUATED / CHECKED DURING JOIN. PARALLEL
Courses.CID= STUDENTS.S_Courses_id
ORDER BY Courses.CID ASC
option (MERGE JOIN)



REQUREMENT #2: Report list of all courses without students

SELECT * FROM Courses LEFT OUTER JOIN STUDENTS 
ON -- All left table + matching Right table. 
-- Non match right is NULL
Courses.CID = STUDENTS.S_Courses_id
ORDER BY Courses.CID ASC


SELECT * FROM Courses LEFT OUTER JOIN STUDENTS 
ON -- All left table + matching Right table. 
-- Non match right is NULL
Courses.CID = STUDENTS.S_Courses_id
WHERE STUDENTS.S_Courses_id IS NULL  -- EVALUATED AFTER JOIN
ORDER BY Courses.CID ASC

SELECT Courses.* FROM Courses LEFT OUTER JOIN STUDENTS 
ON -- All left table + matching Right table. 
-- Non match right is NULL
Courses.CID = STUDENTS.S_Courses_id
WHERE STUDENTS.S_Courses_id IS NULL
ORDER BY Courses.CID ASC


SELECT * FROM STUDENTS RIGHT OUTER JOIN  Courses
ON
Courses.CID = STUDENTS.S_Courses_id
ORDER BY Courses.CID ASC


WE CAN USE EITHER LEFT OUTER JOIN OR RIGHT OUTER JOIN OR FULL OUTER JOIN.

LEFT OUTER JOIN: all left table + matching right table. non match right is null

SELECT Courses.* FROM Courses LEFT OUTER JOIN STUDENTS
ON
Courses.CID = STUDENTS.S_Courses_id
WHERE STUDENTS.S_Courses_id IS NULL 

RIGHT OUTER JOIN: all right table + matching left table. non match left is null

SELECT Courses.* FROM STUDENTS RIGHT OUTER JOIN Courses
ON
Courses.CID = STUDENTS.S_Courses_id
WHERE STUDENTS.S_Courses_id IS NULL 

FULL OUTER JOIN: combined output of LEFT outer join & RIGHT outer join

SELECT * FROM STUDENTS FULL OUTER JOIN Courses
ON
Courses.CID = STUDENTS.S_Courses_id


===============================================



Q: HOW TO REPORT LIST OF ALL COURSES WITHOUT ANY STUDENTS.
-- MEANS, WE NEED TO LOOK FOR MISSING DATA. HENCE WE NEED TO USE : OUTER JOINS
-- WE CAN USE EITHER LEFT OUTER JOIN OR RIGHT OUTER JOIN OR FULL OUTER JOIN.
----------------------------------------------------------------------------------
LEFT OUTER JOIN: all left table + matching right table. non match right is null

SELECT C.* FROM Courses AS C LEFT OUTER JOIN STUDENTS AS S
ON
C.CID = S.S_Courses_id
WHERE S.S_Courses_id IS NULL 
-----------------------------------------------------------------------------------

 RIGHT OUTER JOIN: all right table + matching left table. non match left is null

SELECT CRS.* FROM STUDENTS AS ST RIGHT OUTER JOIN Courses AS CRS
ON
CRS.CID = ST.S_Courses_id
WHERE ST.S_Courses_id IS NULL 
------------------------------------------------------------------------------------

FULL OUTER JOIN: combined output of LEFT outer join & RIGHT outer join

SELECT * FROM STUDENTS AS S FULL JOIN Courses C
ON
C.CID = S.S_Courses_id
------------------------------------------------------------------------------------

ALIAS :-
                    A temporary name given to a table/column/query for easy representation. 

aliias is not permenant. temporary. operates at statement level only



Read Complete

3 Nov 2017

What is join and types of join in sql server


What is join and types of join in SQL server:- 

JOINS:
      A MECHANISM TO COMPARE ONE OR MORE TABLES.


PURPOSE : FOR EASY REPORTING AND DATA GENERATION.


EX: -
             ASSUME ALL PRODUCTS IN ONE TABLE, SALES IN ANOTHER TABLE


A "JOIN" CAN REPORT LIST OF ALL PRODUCTS & THEIR SALES

A "JOIN" CAN REPORT LSIT OF ALL PRODUCTS WITHOU SALES.

TYPES OF JOINS:

1. CROSS JOIN: Used to report all possible combinations

2. INNER JOIN: Used to report only MATCHING data.

3. OUTER JOIN: Used to report MATCHING & MISSING data.

Left Outer Join

Right Outer Join

Full Outer Join

4. SELF JOIN : Used to compare columns within same table.

5.  HASH JOIN : Applicable for tables with unknown size. Dynamic Tables

6.  MERGE JOIN: Applicable for tables with LARGE data. in MBs, GBs

7.  LOOP JOIN: Applicable for tables with SMALL data. in KBs




















Read Complete

1 Nov 2017

Client Side Validation with JavaScript in ASP.NET


Client Side Validation with JavaScript in ASP.NET :-


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Employee.aspx.cs" Inherits="Validation.Employee" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">

////java script code///

    <script type="text/javascript">
        function validation() {
            var errorsummary = "";
            errorsummary += checkname();
            errorsummary += checkpassword();
            if(errorsummary !="")
            {
                alert(errorsummary);
                return false;
            }
        }
        function checkname() {
            var TN = document.getElementById('txtname'); 
            var exp = /^[a-zA-Z ]+$/
            if (TN.value== "") {
                return 'please enter your name !!\n';
            }
            else if (exp.test(TN.value))
            {
                return "";
            }
            else {
                return 'plaeese enter only alphabets !!\n';
            }
        }
        function checkpassword() {
            var TP = document.getElementById('txtpassword');
            if (TP.value == "") {
                return 'please enter your password !!\n';
            }
            else {
                return "";
            }
        }
    </script>




    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <table>
                <tr>
                    <td>Name :</td>
                    <td><asp:TextBox ID="txtname" runat="server"></asp:TextBox></td>
                </tr>
                 <tr>
                    <td>Password :</td>
                    <td><asp:TextBox ID="txtpassword" runat="server"></asp:TextBox></td>
                </tr>
                 <tr>
                    <td></td>
                    <td><asp:Button ID="btnsave" runat="server" Text="Save" OnClientClick="return validation()" OnClick="btnsave_Click" /></td>
                </tr>
            </table>
        </div>
    </form>
</body>
</html>

Read Complete

Grid View with Image and File upload Controls in ASP.Net

Registration.aspx page code:-

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Registration.aspx.cs" Inherits="Registration_31nov.Registration" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="Ajax" %>
<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <Ajax:ToolkitScriptManager ID="DOB" runat="server"></Ajax:ToolkitScriptManager>
    <div>
    <table>
        <tr>
            <td>Name:</td>
            <td><asp:TextBox ID="Textname" runat="server"></asp:TextBox></td>
        </tr>

        <tr>
            <td>Country:</td>
            <td><asp:DropDownList ID="ddlcountry" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlcountry_SelectedIndexChanged"></asp:DropDownList></td>
        </tr>

        <tr>
            <td>State:</td>
            <td><asp:DropDownList ID="ddlstate" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlstate_SelectedIndexChanged"></asp:DropDownList></td>
        </tr>

        <tr>
            <td>Gender:</td>
            <td><asp:RadioButtonList ID="rblgender" runat="server" RepeatColumns="3">
                <asp:ListItem Value="1" Text="Male"></asp:ListItem>
                <asp:ListItem Value="2" Text="Female"></asp:ListItem>
                <asp:ListItem Value="3" Text="Other"></asp:ListItem>
                </asp:RadioButtonList></td>
        </tr>
        <tr>
            <td>Email:</td>
            <td><asp:TextBox ID="Textemail" runat="server"></asp:TextBox></td>
        </tr>

         <tr>
            <td>DOB:</td>
            <td><asp:TextBox ID="Textdob" runat="server"></asp:TextBox></td>
             <Ajax:CalendarExtender ID="clender" runat="server" PopupButtonID="Textdob" PopupPosition="BottomRight" TargetControlID="Textdob"></Ajax:CalendarExtender>
        </tr>

        <tr>
            <td>Password:</td>
            <td><asp:TextBox ID="Textpassword" runat="server"></asp:TextBox></td>
        </tr>
        <tr>
            <td>CPassword:</td>
            <td><asp:TextBox ID="Textcpassword" runat="server"></asp:TextBox></td>
        </tr>
        <tr>
            <td>Hobbies:</td>
            <td><asp:CheckBoxList ID="cblhobbies" runat="server" RepeatColumns="3">
                <asp:ListItem Value="1" Text="Football"></asp:ListItem>
                <asp:ListItem Value="2" Text="Music"></asp:ListItem>
                <asp:ListItem Value="3" Text="Game"></asp:ListItem>
                <asp:ListItem Value="4" Text="Books"></asp:ListItem>
                <asp:ListItem Value="5" Text="Movies"></asp:ListItem>
                <asp:ListItem Value="6" Text="Swmming"></asp:ListItem>
                </asp:CheckBoxList></td>
        </tr>
        <tr>
            <td>File_Upload</td>
            <td><asp:FileUpload ID="TextFN" runat="server" /></td>
        </tr>

         <tr>
            <td>Active:</td>
            <td><asp:CheckBox ID="cblactive" runat="server" /></td>
        </tr>

        <tr>
            <td></td>
            <td><asp:Button ID="btnsave" runat="server" Text="Save" OnClick="btnsave_Click" /></td>
        </tr>

        <tr>
            <td></td>
            <td><asp:GridView ID="grd" runat="server" AutoGenerateColumns="false" OnRowCommand="grd_RowCommand">
                <Columns>
                    <asp:TemplateField HeaderText="Emp Name">
                        <ItemTemplate>
                            <%# Eval("Name") %>
                        </ItemTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Emp Country">
                        <ItemTemplate>
                            <%# Eval("Cname") %>
                        </ItemTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Emp State">
                        <ItemTemplate>
                            <%# Eval("Sname") %>
                        </ItemTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Emp Gender">
                        <ItemTemplate>
                            <%# Eval("Gender").ToString()== "1"?"Male":Eval("Gender").ToString()=="2"?"Female":"Other" %>
                        </ItemTemplate>
                    </asp:TemplateField>


                    <asp:TemplateField HeaderText="DOB">
                        <ItemTemplate>
                            <%# Eval("DOB") %>
                        </ItemTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Emp Hobbies">
                        <ItemTemplate>
                            <%# Eval("Hobbies") %>
                        </ItemTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Enable/Diseble">
                        <ItemTemplate>
                            <%# Eval("Active") %>
                        </ItemTemplate>
                    </asp:TemplateField>


                    <asp:TemplateField HeaderText="File_Upload">
                        <ItemTemplate>
                            <asp:Image ID="img" runat="server" ImageUrl='<%#Eval("File_Upload","~/File_Upload/{0}") %>' Width="70px" Height="60px" />
                        </ItemTemplate>
                    </asp:TemplateField>

                     <asp:TemplateField HeaderText="">
                        <ItemTemplate>
                            <asp:LinkButton ID="lnkbtndelete" runat="server" CommandName="DEL" Text="Delete" CommandArgument='<%# Eval("Empid") %>'></asp:LinkButton>
                        </ItemTemplate>
                    </asp:TemplateField>

                     <asp:TemplateField HeaderText="">
                        <ItemTemplate>
                            <asp:LinkButton ID="lnkbtnedit" runat="server" CommandName="EDT" Text="Edit" CommandArgument='<%# Eval("Empid") %>'></asp:LinkButton>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
                </asp:GridView></td>

        </tr>
    </table>
    </div>
    </form>
</body>
</html>



Registration Page .CS page code:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;

namespace Registration_31nov
{
    public partial class Registration : System.Web.UI.Page
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString);
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Fill_Country();
                ddlstate.Items.Insert(0, new ListItem("--Select--", "0"));
                Fill_grd();
            }
        }
        public void Fill_Country()
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("usp_Country_get", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            con.Close();
            if (ds.Tables[0].Rows.Count > 0)
            {
                ddlcountry.DataValueField = "Cid";
                ddlcountry.DataTextField = "Cname";
                ddlcountry.DataSource = ds;
                ddlcountry.DataBind();
                ddlcountry.Items.Insert(0, new ListItem("--Select--", "0"));
            }
        }
        public void Fill_state(string Cid)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("usp_state_get", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Cid", Cid);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            con.Close();
            if(ds.Tables[0].Rows.Count>0)
            {
                ddlstate.DataValueField = "Sid";
            ddlstate.DataTextField="Sname";
            ddlstate.DataSource=ds;
            ddlstate.DataBind();
            ddlstate.Items.Insert(0,new ListItem("--Select--","0"));
            }
        }
        public void Fill_grd()
        {
            SqlCommand cmd = new SqlCommand("usp_employee_get", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
            {
                grd.DataSource = ds;
                grd.DataBind();
            }
            else
            {
                grd.DataSource = null;
                grd.DataBind();
            }
            
        }
        protected void ddlcountry_SelectedIndexChanged(object sender, EventArgs e)
        {
            Fill_state(ddlcountry.SelectedValue);
        }

        protected void ddlstate_SelectedIndexChanged(object sender, EventArgs e)
        {
            cblactive.Checked = true;
        }
        protected void btnsave_Click(object sender, EventArgs e)
        {
            string HOB = "";
            for (int i = 0; i < cblhobbies.Items.Count; i++)
            {
                if (cblhobbies.Items[i].Selected == true)
                {
                    HOB += cblhobbies.Items[i].Text + ',';
                }
            }
            HOB = HOB.TrimEnd(',');

            string FN = "";

            FN = Path.GetFileName(TextFN.PostedFile.FileName);
            TextFN.SaveAs(Server.MapPath("File_upload" + "\\" + FN));
            if (btnsave.Text == "Save")
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("ups_Employee_insert", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Name", Textname.Text);
                cmd.Parameters.AddWithValue("@Country", ddlcountry.SelectedValue);
                cmd.Parameters.AddWithValue("@State", ddlstate.SelectedValue);
                cmd.Parameters.AddWithValue("@Gender", rblgender.SelectedValue);
                cmd.Parameters.AddWithValue("@Email", Textemail.Text);
                cmd.Parameters.AddWithValue("@DOB", Textdob.Text);
                cmd.Parameters.AddWithValue("@Password", Textpassword.Text);
                cmd.Parameters.AddWithValue("@CPassword", Textcpassword.Text);
                cmd.Parameters.AddWithValue("@Hobbies", HOB);
                cmd.Parameters.AddWithValue("@File_Upload", FN);
                cmd.Parameters.AddWithValue("@Active", cblactive.Checked == true ? 1 : 0);
                cmd.ExecuteNonQuery();
                Fill_grd();
                con.Close();
            }
            else if (btnsave.Text == "Update")
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("usp_employee_update", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Empid", ViewState["vid"]);
                cmd.Parameters.AddWithValue("@Name", Textname.Text);
                cmd.Parameters.AddWithValue("@Country", ddlcountry.SelectedValue);
                cmd.Parameters.AddWithValue("@State", ddlstate.SelectedValue);
                cmd.Parameters.AddWithValue("@Gender", rblgender.SelectedValue);
                cmd.Parameters.AddWithValue("@Email", Textemail.Text);
                cmd.Parameters.AddWithValue("@DOB", Textdob.Text);
                cmd.Parameters.AddWithValue("@Password", Textpassword.Text);
                cmd.Parameters.AddWithValue("@CPassword", Textcpassword.Text);
                cmd.Parameters.AddWithValue("@Hobbies", HOB);
                cmd.Parameters.AddWithValue("@File_Upload", FN);
                cmd.Parameters.AddWithValue("@Active", cblactive.Checked == true ? 1 : 0);
                cmd.ExecuteNonQuery();
                Fill_grd();
                con.Close();
            }
        }
        protected void grd_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "DEL")
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("usp_employee_delete", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Empid", e.CommandArgument);
                cmd.ExecuteNonQuery();
                con.Close();
                Fill_grd();
            }
            else if (e.CommandName == "EDT")
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("usp_employee_edit",con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Empid", e.CommandArgument);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                con.Close();
                if (ds.Tables[0].Rows.Count > 0)
                {
                    Textname.Text = ds.Tables[0].Rows[0]["Name"].ToString();
                    ddlcountry.SelectedValue = ds.Tables[0].Rows[0]["Country"].ToString();
                    Fill_state(ddlcountry.SelectedValue);
                    ddlstate.SelectedValue = ds.Tables[0].Rows[0]["State"].ToString();
                    rblgender.SelectedValue = ds.Tables[0].Rows[0]["Gender"].ToString();
                    Textemail.Text = ds.Tables[0].Rows[0]["Email"].ToString();
                    Textdob.Text = ds.Tables[0].Rows[0]["DOB"].ToString();
                    Textpassword.Text = ds.Tables[0].Rows[0]["Password"].ToString();
                    Textcpassword.Text = ds.Tables[0].Rows[0]["CPassword"].ToString();

                    string[] arg = ds.Tables[0].Rows[0]["Hobbies"].ToString().Split(',');
                    for (int i = 0; i < cblhobbies.Items.Count; i++)
                    {
                        for (int j = 0; j < arg.Length; j++)
                        {
                            if (cblhobbies.Items[i].Text == arg[j])
                            {
                                cblhobbies.Items[i].Selected = true;
                                break;
                            }
                        }
                    }
                   
                    btnsave.Text = "Update";
                    ViewState["vid"] = e.CommandArgument;
                    
                }
            }
        }
    }
}

Web.config:-

<?xml version="1.0"?>

<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->

<configuration>
    <system.web>
      <compilation debug="true" targetFramework="4.5" />
      <httpRuntime targetFramework="4.5" />
    </system.web>
  <connectionStrings>
    <add name="DBCS" connectionString="data source=Shiva;integrated security=true;initial catalog=Registration_1nov"/>
  </connectionStrings>
</configuration>

Read Complete

Popular Posts

Popular Posts

Translate

Total Pageviews