Friday, May 31, 2013

Find Duplicate Record in Sql Server


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


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


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


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.


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]) 
    // 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;


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
    CREATE DATABASE  databaseName


  2. Delete or drop a Database


    DROP DATABASE dataBaseName


  3. Select a Database


    USE DataBaseName;


    SQL> USE Test;
  4. Show all Databases

    For SQL

    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


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

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
Now Insert some Data in Emp_Info table. Then use the following procedure.
Complete Program


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Checkbox_Validation.aspx.cs" Inherits="Checkbox_Validation" %>
<!DOCTYPE html>
<html xmlns="">
<head runat="server">
    <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;
    <form id="form1" runat="server">
    <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">
                    <asp:CheckBox ID="chkbox" runat="server" />         
                    <asp:CheckBox ID="chkboxchild" runat="server" />
            <asp:BoundField HeaderText="Emp_Id" DataField="Emp_Id" HeaderStyle-HorizontalAlign="Left" >
<HeaderStyle HorizontalAlign="Left"></HeaderStyle>
            <asp:BoundField HeaderText="Name" DataField="Name" HeaderStyle-HorizontalAlign="Left" >
<HeaderStyle HorizontalAlign="Left"></HeaderStyle>
            <asp:BoundField HeaderText="Salary" DataField="Salary" HeaderStyle-HorizontalAlign="Left" >
<HeaderStyle HorizontalAlign="Left"></HeaderStyle>
            <asp:BoundField HeaderText="City" DataField="City" HeaderStyle-HorizontalAlign="Left" >
<HeaderStyle HorizontalAlign="Left"></HeaderStyle>
        <EditRowStyle Font-Bold="False" Font-Italic="False" />
<HeaderStyle BackColor="#99CCFF" Font-Bold="True" ForeColor="Black" BorderColor="#663300" BorderWidth="1px"></HeaderStyle>
        <br />
        <asp:Button runat="server" Text="Submit" OnClientClick="javascript:CheckBox_Validation()" Font-Bold="True" Font-Italic="False" />


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();
                gdview.DataSource = ds.Tables[0];
Output 1

Click on "Submit" button without any check checkbox

Output 2

Click on "Submit" button after check checkboxes