Difference bettwen DBMS and RDBMS and about KEY

DBMS

Transaction with TRY-Catch

BEGIN TRY

BEGIN TRANSACTION transABC

INSERT INTO TABLEA (col1,col2,col3) VALUES ('a','b','c')

INSERT INTO TABLEB (col1,col2,col3) VALUES ('a','b','c')

UPDATE TABLEA SET col2='abcde' WHERE col1 = 'a'

COMMIT TRANSACTION transABC

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK TRANSACTION transABC --RollBack in case of Error

-- Raise an error with the details of the exception

DECLARE @ErrMsg nvarchar(4000), @ErrSeverity INT

SELECT @ErrMsg = ERROR_MESSAGE(),

@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)

END CATCH

//////////

DBMS vs. RDBMS

• Relationship among tables is maintained in a RDBMS whereas this not the case DBMS as it is used to manage the database.

• DBMS accepts the ‘flat file’ data that means there is no relation among different data whereas RDBMS does not accepts this type of design.

• DBMS is used for simpler business applications whereas RDBMS is used for more complex applications.

• Although the foreign key concept is supported by both DBMS and RDBMS but its only RDBMS that enforces the rules.

• RDBMS solution is required by large sets of data whereas small sets of data can be managed by DBMS.

///////////////////////////////////////////////////////////////////

A key is a single or combination of multiple fields in a table. Its is used to fetch or retrieve records/data-rows from data table according to the condition/requirement. Keys are also used to create relationship among different database tables or views.

Types of SQL Keys

We have following types of keys in SQL which are used to fetch records from tables and to make relationship among tables or views.

1.             Super Key

Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table.Example : Primary key, Unique key, Alternate key are subset of Super Keys.

2.            Candidate Key

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Example: In below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key.

3.            Primary Key

Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It can not accept null, duplicate values. Only one Candidate Key can be Primary Key.

4.            Alternate key

A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.

Example: In below diagram RollNo and EnrollNo becomes Alternate Keys when we define ID as Primary Key.

5.            Composite/Compound Key

Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.

6.            Unique Key

Uniquekey is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values

7.            Foreign Key

Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values.

Example : We can have a DeptID column in the Employee table which is pointing to DeptID column in a department table where it a primary key.

Defined Keys -

CREATE TABLE Department1
 (
  DeptID int PRIMARY KEY,
   Name varchar (50) NOT NULL,
  Address varchar (200) NOT NULL, )
 CREATE TABLE Student
 (
   ID int PRIMARY KEY,
   RollNo varchar(10) NOT NULL,
 Name varchar(50) NOT NULL,
 EnrollNo varchar(50) UNIQUE,
Address varchar(200) NOT NULL,
 DeptID int FOREIGN KEY REFERENCES Department1(DeptID))

Sum Function with Group By Clause 
                                      Table Name=S3
 
                                              Table Name=S4
 
 Inner Join for Sum of Mark

SELECT        s.Sid, s.SName, e.TOT
FROM            S3 AS s INNER JOIN
                             (SELECT        Sid, SUM(Mark) AS TOT
                               FROM            S4
                               GROUP BY Sid) AS e ON e.Sid = s.Sid 

 Inner Join for Max Mark 
SELECT        s.Sid, s.SName, e.ww
FROM            S3 AS s INNER JOIN
                             (SELECT        Sid, MAX(Mark) AS ww
                               FROM            S4 AS b
                               GROUP BY Sid) AS e ON e.Sid = s.Sid

in pressing the Enter key how the user go to Next Contol using JQuery.

Source Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridviewKeydown.aspx.cs" Inherits="GridviewKeydown" %>

<!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>
<%--    <!-- required -->
<script type="text/javascript" src="js/jquery.js"></script>
<!-- optional -->
<script type="text/javascript" src="js/jquery.shadow.js"></script>
<script type="text/javascript" src="js/jquery.ifixpng.js"></script>
<script type="text/javascript" src="js/jquery.fancyzoom.min.js"></script>--%>

  <script src="Scripts/jquery-1.4.1.js" type="text/javascript"></script>
    <script src="Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
     <script language="javascript" type="text/javascript">
         $(document).ready(function ()
          {
              $(':input').bind("keydown", function (e)
             {
                 var n = $(":input").length;
                 if (e.keyCode == 13)
                 {
                     e.preventDefault();
                     var nextIndex = $(':input').index(this) + 1;
                     if (nextIndex < n)
                         $(':input')[nextIndex].focus();
                 }
             });
         });
     </script>
                                                              OR
<%--
         <script type="text/javascript"
        src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.2.min.js">
    </script>
  
    <script type="text/javascript">
        $(function () {
            $('input:text:first').focus();
            var $inp = $('input:text');
            $inp.bind('keydown', function (e) {
                //var key = (e.keyCode ? e.keyCode : e.charCode);
                var key = e.which;
                if (key == 13) {
                    e.preventDefault();
                    var nxtIdx = $inp.index(this) + 1;
                    $(":input:text:eq(" + nxtIdx + ")").focus();
                }
            });
        });
    </script>--%>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:gridview ID="Gridview1" runat="server" AutoGenerateColumns="False">
    <Columns>
    <asp:TemplateField>
    <ItemTemplate>
         <asp:TextBox ID="TextBox1" runat="server" Text='<%#Eval("LedgerID")%>'></asp:TextBox>
    </ItemTemplate>
    </asp:TemplateField>

    <asp:TemplateField>
    <ItemTemplate>
         <asp:TextBox ID="TextBox1" runat="server" Text='<%#Eval("LedgerName")%>'></asp:TextBox>
    </ItemTemplate>
    </asp:TemplateField>

    <asp:TemplateField>
    <ItemTemplate>
        <asp:TextBox ID="TextBox1" runat="server" Text='<%#Eval("CompanyID")%>'></asp:TextBox>
    </ItemTemplate>
    </asp:TemplateField>
    </Columns>
    </asp:gridview>
    </div>
    </form>
</body>
</html>

Page Design
 
C# 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;


public partial class GridviewKeydown : System.Web.UI.Page
{
    SqlConnection con;
    SqlCommand cmd;
    DataTable dt;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GetData();
        }
    }
    public void GetData()
    {
        cone();
        SqlDataAdapter da = new SqlDataAdapter("Select * from Rakesh", con);
        dt = new DataTable();
        da.Fill(dt);
        Gridview1.DataSource = dt;
        Gridview1.DataBind();
    }
    public void cone()
    {
        con = new SqlConnection(ConfigurationManager.ConnectionStrings["Satya"].ToString());
        con.Open();
    }
}

How to retrive More than one Select Statement in a single Command.And Show in a GridView.Using Normal query and Storeprocedure.

Page Design

Source Code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>

<!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>
    <style type="text/css">
        .style1
        {
            background-color: #66CCFF;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:gridview ID="Gridview1" runat="server"></asp:gridview>
    <asp:gridview ID="Gridview2" runat="server"></asp:gridview>
        <asp:Button ID="BtnReader" runat="server" Text="Data Reader"
            onclick="BtnReader_Click" style="position:absolute; top: 310px; left: 325px;"
            CssClass="style1" />
        <asp:Button ID="BtnAdapter" runat="server" Text="Data Adapter"
            onclick="BtnAdapter_Click" style="position:absolute; top: 312px; left: 191px;"
            CssClass="style1"  />
        <asp:Button ID="BtnStored" runat="server" Text="Stored Procedure"
            style="position:absolute; top: 311px;" onclick="BtnStored_Click"
            CssClass="style1" />
    </div>
    </form>
</body>
</html>

C# 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;

public partial class Default3 : System.Web.UI.Page
{
    SqlConnection con;
    SqlDataAdapter da;
    SqlCommand cmd;
    DataSet ds;
    SqlDataReader dr;
    protected void Page_Load(object sender, EventArgs e)
    {
        con = new SqlConnection(ConfigurationManager.ConnectionStrings["Satya"].ConnectionString);
        con.Open();
        if (!IsPostBack)
        {
        }
    }
    public void satya_DA()
    {
        da = new SqlDataAdapter("Select * from Rakesh;Select * from Demo", con);
        ds = new DataSet();
        da.Fill(ds);
        Gridview1.DataSource = ds.Tables[0].DefaultView;
        Gridview1.DataBind();
        Gridview2.DataSource = ds.Tables[1].DefaultView;
        Gridview2.DataBind();
    }
    public void satya_Reader()
    {
        using (cmd = new SqlCommand("Select * from Rakesh;Select * from Demo", con))
        {
            using (dr = cmd.ExecuteReader())
            {
                Gridview1.DataSource = dr;
                Gridview1.DataBind();
                dr.NextResult();
                Gridview2.DataSource = dr;
                Gridview2.DataBind();
            }
        }
    }
    public void satya_SP()
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
    //Create StoreProcedure and Call Here
        cmd.CommandText = "exec Select_Employee_Sallary_Qualification exec Select_Employee_Sallary_Qualification";
        da = new SqlDataAdapter(cmd);
        ds = new DataSet();
        da.Fill(ds);
        Gridview1.DataSource = ds.Tables[0].DefaultView;
        Gridview1.DataBind();
        Gridview2.DataSource = ds.Tables[1].DefaultView;
        Gridview2.DataBind();
        da.Dispose();
        con.Close();
    }
    protected void BtnReader_Click(object sender, EventArgs e)
    {
        satya_Reader();
    }
    protected void BtnAdapter_Click(object sender, EventArgs e)
    {
        satya_DA();
    }
    protected void BtnStored_Click(object sender, EventArgs e)
    {
        satya_SP();
    }
}

Some Interview Sql Query

 Table Design For Demo

1. //To only copy the Table structure of a Existing table in database

SELECT        * INTO              Demo_linkun   FROM            Demo   WHERE        (1 = 2)

2. //To only copy the data from a Existing table after creating a new table

INSERT  INTO   Demo_linkun (col1,col2…..)  SELECT        (col1,col2…..)   FROM            Demo

                                                           OR

Insert into Demo_Linkun Select * from Demo

3.//To copy both Table  structure and data from a  Existing table

SELECT        *   INTO              Demo_linkun    FROM            Demo        

4.//To retrieve the Table  structure as well data from the table

SELECT        *   FROM            Demo   WHERE        (1 = 1)

OR

SELECT        *   FROM            Demo   WHERE        (0 = 0)

5.//To retrieve the Table  structure with null value from the table

SELECT        *   FROM            Demo  WHERE        (1 = 2)

OR

SELECT        *    FROM            Demo  WHERE        (1 = 0)

4.  If we have two tables and the second table contains NULL value and we have to retrieve those records which are present in first table and not present in second table. And the First table has a Primary Key which Name Is Sid and That Sid is Foreign Key in Second Table.

1. Select Sid from St1 Where NOT EXISTS (Select * from St2 where St1.Sid=St2.Sid)

OR

2. SELECT Sid FROM St1 Left Join St2 ON St1.Sid=St2.Sid where St2.Sid IS NULL


The Table one is St1 where Primary Key is Sid.
 
The Second Table is St2 where Foreign Key is Sid

Here in Table St1 There is 6 Records but in Table St2 there is Three Records ond one Null.and there are 5 and 6 records are not Added.we want to Dispplay the Records which are not add in St2 Table and Null Value.

File upload in Database and File show in a GridView And Download the Word File

Page Design

Source Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DownloadFilein asp.aspx.cs" Inherits="DownloadFilein_asp" %>

<!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>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:FileUpload ID="FileUpload1" runat="server" />
        <asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
        <asp:Label ID="lblMessage" runat="server" Text="Label"></asp:Label>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
            onrowcommand="GridView1_RowCommand" >
        <Columns>
         <asp:BoundField HeaderText="ID" DataField="ID" />
        <asp:BoundField HeaderText="FileName" DataField="FileName" />
        <asp:ButtonField ButtonType="Link" Text="Download" CommandName="Dwn" HeaderText="Files" />
        </Columns>
        </asp:GridView>
       
    </div>
    </form>
</body>
</html>
C# Code
Add a Folder in the Website which name is Docs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Data;
using System.Web.UI.HtmlControls;

public partial class DownloadFilein_asp : System.Web.UI.Page

{
    SqlConnection con;
    protected void Page_Load(object sender, EventArgs e)
    {
        con = new SqlConnection(ConfigurationManager.ConnectionStrings["Satya"].ConnectionString);
        con.Open();
        if (!IsPostBack)
        {
            Fillgrid();
        }
        con.Close();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string name = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string location = Server.MapPath("~/Docs/" + name);
            FileUpload1.SaveAs(location);

            string connectionString = ConfigurationManager.ConnectionStrings["Satya"].ConnectionString;
            SqlConnection sqlCon = new SqlConnection(connectionString);
            string strInsert = "INSERT INTO Files(FileName) VALUES(@FileName)";
            SqlCommand command = new SqlCommand(strInsert, sqlCon);
            command.Parameters.AddWithValue("@FileName", name);
            sqlCon.Open();
            int result = command.ExecuteNonQuery();
            sqlCon.Close();

            if (result > 0)
                lblMessage.Text = "Upload Successful";
        }
        Fillgrid();

    }
    public void Fillgrid()
    {
        SqlDataAdapter da = new SqlDataAdapter("Select * from Files", con);
        DataTable dt = new DataTable();
        da.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {       
      if (e.CommandName == "Dwn")
      {
          int index = Convert.ToInt32(e.CommandArgument);
           GridViewRow row = GridView1.Rows[index];
           string fName = row.Cells[1].Text;
           Response.ContentType = "application/octet-stream";     
          Response.AddHeader("Content-Disposition", "attachment;filename=" + fName);
           Response.TransmitFile(Server.MapPath("~/Docs/" + fName));
           Response.End();
       }
    }
}

Database Design