Friday, May 31, 2013

Find Duplicate Record in Sql Server


Introduction

In this article i will explain how to find or count duplicate record in SQL Server.

In this example i have a datatable and this table doesn't contain any primary key because of that duplicate records inserted in table.

First i will create table without contain any primary key

Query


CREATE TABLE [dbo].[Emp_Info]
(
      [Emp_Id] [int] NULL,
      [Name] [varchar](50) NULL,
      [Salary] [int] NULL,
      [City] [varchar](50) NULL

)
Now insert some duplicate record in this table.




Now count how many duplicate record exits in datatable with the help of below query

Query


select Emp_Id, Name, Count(*) as DuplicateRecord
From Emp_Info
Group By Emp_Id, Name, City

Having Count(*) >1 Order By Emp_Id


Wednesday, May 29, 2013

Find vowels character in a String

Introduction

Some one ask a question to me, how to find all vowels from a string without repeating vowels ( means you can find out vowels from a string but if one is come out like 'E' from that string, then next time it is not included with the output.

Now I write a code for Find out vowels from a given string.

Example

using System;
class Program
{
    static void Main()
    {
        string value1 = RemoveDuplicateChars("AERTJEIUO");
        for (int i = 0; i <= value1.Length - 1; i++)
        {
                // code that checks the char is vowel or not
            if (65 == (int)value1[i] || 69 == (int)value1[i] || 73 == (int)value1[i] || 79 == (int)value1[i] || 85 == (int)value1[i]) 
            {
                Console.WriteLine(value1[i]);
            }
        }
        Console.ReadKey();
    }
 
    // method for removing the duplicate characters
        static string RemoveDuplicateChars(string key)
    {
        string val1 = "";
        string result = "";
        foreach (char value in key)
        {
            if (val1.IndexOf(value) == -1)
            {
                val1 += value;
                result += value;
            }
        }
        return result;
    }
}

Output


Monday, May 27, 2013

SQL interview questions:Part1


1-  Difference in Delete truncate and Drop
  1. Truncate
     
    • Truncate is faster and used by fewer system.
    • Truncate can not be roll back.
    • It is DDL (Data Definition Language) command.
    • You can not use where clause with  Truncate command.
    • Truncate table statement deletes all data from a table without deleting the definition of table.
  2. Delete 
     
    •  Delete removes rows once at a time, means you can use delete with and without where clause.
    • Delete can be roll back.
    • It is DML (Data Manipulation Language) command.
    • DELETE does not reset the identity of the table.
  3. Drop
     
    • Drop command removes table definition including indexer, trigger, constraints.
2-  Difference between clustered and non-clustered index
  1. A clustered index
     
    • A clustered index is a type of index where the table records are physically re-ordered to math the index. A table can have only one clustered index. One of the most important point about clustered index is that "Primary Key has to be Clustered Index".
  2. A non-clustered index
     
    •  A non-clustered index is a special type of index in which the logical order of the index does not match physical order of the rows on disk.
3-  Important Data Types in SQL
Data Type Syntax
integer integer
numeric numeric (p,s)
decimal decimal (p,s)
float float (p,s)
character char (x)
date date
time time
bit bit (x)
real real
smallint smallint
4-  Create, Drop or Delete, Select and show Database
  1. Create a Database
     
    Syntax
    CREATE DATABASE  databaseName

    Example

    SQL>CREATE DATABASE test;
     
  2. Delete or drop a Database

    syntax

    DROP DATABASE dataBaseName

    Example

    SQL>DROP DATABASE TEST;
     
  3. Select a Database

    syntax

    USE DataBaseName;

    Example

    SQL> USE Test;
     
  4. Show all Databases
    Example

    For SQL
    sp_databases

    And For mysql

    mysql>Show DATABASES;

Thursday, May 23, 2013

Difference between Primary key and Unique Key in Sql


Difference between Unique key and Primary key

A UNIQUE constraint and PRIMARY key both are similar and it provide unique enforce uniqueness of the column on which they are defined.
Some are basic differences between Primary Key and Unique key are as follows.

Primary key
  1. Primary key cannot have a NULL value.
  2. Each table can have only single primary key.
  3. Primary key is implemented as indexes on the table. By default this index is clustered index.
  4. Primary key can be related with another table's as a Foreign Key.
  5. We can generated ID automatically with the help of Auto Increment field. Primary key supports Auto Increment value. 
Unique Constraint
  1. Unique Constraint may have a NULL value.
  2. Each table can have more than one Unique Constraint.
  3. Unique Constraint is also implemented as indexes on the table. By default this index is Non-clustered index.
  4. Unique Constraint can not be related with another table's as a Foreign Key.
  5. Unique Constraint doesn't supports Auto Increment value.
Define Primary and Unique Key

Create table Student
(
StuId int primary key, ---- Define Primary Key
StuName varchar(50) Not Null,
ContactNo int Unique --- Define Unique Key
)
Insert some data in table


Now you will see above fig. StuId can not have NULL value but ContactNo can have NULL value.

Wednesday, May 22, 2013

Gridview checkbox validation using JavaScript in Asp.net


Introduction

In this article i will explain check the checkbox selected status in gridview using JavaScript in Asp.net.

The following example check whether checkboxes selected in gridview or not. In this example I have taken one gridview with checkboxes and I have taken one button If user click on button without check any checkbox it rise validation.

Firstly I am created a database EmpDetail. and Now I am created a table in this database. 

Query Code

CREATE TABLE [dbo].[Emp_Info](
      [Emp_Id] [int] NULL,
      [Name] [varchar](50) NULL,
      [Salary] [int] NULL,
      [City] [varchar](50) NULL
) ON [PRIMARY]
Now Insert some Data in Emp_Info table. Then use the following procedure.
Complete Program

Checkbox_Validation.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Checkbox_Validation.aspx.cs" Inherits="Checkbox_Validation" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript">
        function CheckBox_Validation()
        {
            var valid = false;
            var gdvw = document.getElementById('<%= gdview.ClientID %>');
            for (var i = 1; i < gdvw.rows.length; i++)
            {
                var value = gdvw.rows[i].getElementsByTagName('input');
                if (value != null)
                {
                    if (value[0].type == "checkbox")
                    {
                        if (value[0].checked)
                        {
                            valid = true;
                            alert("Checkbox selected successfully");
                            return true;
                        }
                    }
                }
            }
            alert("Please select atleast one checkbox");
            return false;
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:GridView ID="gdview" CssClass="Gridview" runat="server" AutoGenerateColumns="false" HeaderStyle-BackColor="#669999"
HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="Black" HeaderStyle-BorderColor="#333300" HeaderStyle-BorderWidth="1">
        <Columns>
            <asp:TemplateField>
                <HeaderTemplate>
                    <asp:CheckBox ID="chkbox" runat="server" />         
                </HeaderTemplate>
                <ItemTemplate>
                    <asp:CheckBox ID="chkboxchild" runat="server" />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField HeaderText="Emp_Id" DataField="Emp_Id" HeaderStyle-HorizontalAlign="Left" >
<HeaderStyle HorizontalAlign="Left"></HeaderStyle>
            </asp:BoundField>
            <asp:BoundField HeaderText="Name" DataField="Name" HeaderStyle-HorizontalAlign="Left" >
<HeaderStyle HorizontalAlign="Left"></HeaderStyle>
            </asp:BoundField>
            <asp:BoundField HeaderText="Salary" DataField="Salary" HeaderStyle-HorizontalAlign="Left" >
<HeaderStyle HorizontalAlign="Left"></HeaderStyle>
            </asp:BoundField>
            <asp:BoundField HeaderText="City" DataField="City" HeaderStyle-HorizontalAlign="Left" >
<HeaderStyle HorizontalAlign="Left"></HeaderStyle>
            </asp:BoundField>
        </Columns>
        <EditRowStyle Font-Bold="False" Font-Italic="False" />
<HeaderStyle BackColor="#99CCFF" Font-Bold="True" ForeColor="Black" BorderColor="#663300" BorderWidth="1px"></HeaderStyle>
    </asp:GridView>
        <br />
        <asp:Button runat="server" Text="Submit" OnClientClick="javascript:CheckBox_Validation()" Font-Bold="True" Font-Italic="False" />
    </div>
    </form>
</body>
</html>

Checkbox_Validation.aspx.cs

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;
 
public partial class Checkbox_Validation : System.Web.UI.Page
{
    SqlConnection con;
    SqlDataAdapter da;
    DataSet ds;
    protected void Page_Load(object sender, EventArgs e)
    {
        using (con = new SqlConnection("Data Source=.;Initial Catalog=EmpDetail;Integrated Security=True"))
        {
            using (da = new SqlDataAdapter("Select * from Emp_Info", con))
            {
                ds = new DataSet();
                da.Fill(ds);
                gdview.DataSource = ds.Tables[0];
                gdview.DataBind();
            }
        }
    }
}
Output 1


Click on "Submit" button without any check checkbox


Output 2

Click on "Submit" button after check checkboxes