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

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

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;
   

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.

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