SQL- Shortcut way to delete duplicate records from a table

I have discussed earlier in one of my post how to delete duplicate records from a table . But recently i came up with even shorter way to delete duplicate records.

Consider a StudentInfo Table as shown  below for demonstration.

SELECT * FROM StudentInfo

StudentInfo Table

Step1:
 First we will create a temporary table '#temp' with same structure  as that of original table 
'StudentInfo' and insert all distinct rows from the 'StudentInfo' table into temporary table as follows:

SQL- Temporary table in SQL SERVER

Temporary table in SQL SERVER

Temporary table: We create temporary table for temporary storing of data as the name suggests. It is very similar to normal tables and one can perform all operations on temporary 
table.Temprory tables are created inside tempdb database.The lifetime or the scope of 
the temporary table is limited.There are two kinds of temporary table which are as 
follows:

Local temporary table: It is prefixed with # to the table name.It is visible  only to the one who has created it till the session  exists. Table gets automatically deleted when the user log out from the session. The user can also explicitly drop the temporary table by using DROP command.
   Following below query shown is an example to create local temporary  table.

Creating Local Temporary Table

CREATE TABLE #Temp
(
  ID INT,
  Name VARCHAR(30),
  Age INT

  )

Inserting values into Local Temporary Table

INSERT INTO #Temp VALUES(1,'Sunil',24)
INSERT INTO #Temp VALUES(2,'Shweta',22)
INSERT INTO #Temp VALUES(3,'Rohit',25)
INSERT INTO #Temp VALUES(4,'Sohan',21)
INSERT INTO #Temp VALUES(5,'Mohan',21)

Let us see the data in #temp

SQL- Difference between Local temporary table and Global temporary table

Q. What is the difference between Local temporary table and Global temporary  table ?

Local temporary table: It is prefixed with # to the table name.It is visible  only to the one who has created it till the session  exists. Table gets automatically deleted when the user log out from the session. The user can also explicitly drop the temporary table by using DROP command.
   Following below query shown is an example to create local temporary  table.

CREATE TABLE #Temp
(
  ID INT,
  Name VARCHAR(30),
  Age INT

  )

Global temporary table: It is prefixed with ## to the table name. It is visible to the multiple users once the connection or session has been created.Table gets automatically deleted when all the user referencing to that table logs out from the session.The user can also explicitly drop the temporary table by using DROP command.
      Following below query shown is an example to create global temporary  table.

CREATE TABLE ##Temp1
(
  ID INT,
  Name VARCHAR(30),
  Age INT

  )

Click for detail

SQL Questions PART-2

Q What are different types of constraint in SQL ?

    Following are the different types of constraints in SQl:-

•     Primary Key Constraint
•     Foreign Key Constraint
•     Unique Constraint
•     Check Constraint
•     Not Null Constraint

Q What is self referencing Foreign Key ?
        When a foreign key refers to the column of the same table then it is  called self referencing foreign key.

Q what is aggregate function ?

       Aggregate function returns single value after performing calculation on        set of values.Aggregate functions are generally used with the GROUP BY        clause of the SELECT statement.

•     AVG() - It is used to find the average value
•     COUNT() - It is used to find the number of rows
•     MAX() - It is used to find the largest value
•     MIN() - It is used to find the smallest value
•     SUM() - It is used to find the sum

SQL-Deleting Duplicate Records from a Table

Q How to delete duplicate records from a table ?
      This is one of the famous question asked by  an interviewer. Lets us see how to to delete duplicate records from a table. Table StudentInfo has duplicate records as shown below.

SudentInfo Table

Step 1: First we will create a temporary table say temp and copy all original table (StudentInfo Table) data into this very temporary table.

SQL- Nth Highest Salary

Q How to find the nth highest salary from a table in SQL Server ?
     Consider below the EmployeeDetails table.

EmployeeDetails Table

Let us target to find 2nd highest salary from above table which is 40000.
We can achieve this in the following two ways:

By using TOP clause

SELECT TOP 1 Salary  FROM  
   (SELECT DISTINCT TOP 2 Salary
       FROM EmployeeDetails 
       ORDER BY Salary DESC) A
      ORDER BY Salary 

SQL- WHERE and HAVING

Q What is the difference between WHERE and HAVING clause ?
       WHERE clause is used to filter rows before aggregation or grouping of rows has been done. WHERE can be used in other than SELECT clause like UPDATE,DELETE etc. 
 Consider the below MarksInfo table for illustration

MarksInfo Table

In the below snapshot, the use of WHERE clause has been demonstrated. We are filtering the records based on condition in WHERE clause. Here, we are displaying only those records which have Marks greater than 80.

SQL- UNION and UNION ALL

Q What is the difference between UNION and UNION ALL ?
     UNION: It combines the results of two or more SELECT queries and it will        return only distinct records. It also displays result set in sorted order.

     Consider EmpInfo as first table.

EmpInfo Table

      Consider EmpDetails as second table.

EmpDetails

      UNION is applied on the above two tables.We see that only distinct               records are displayed out of two tables.

SQL-JOIN

         When we want to fetch data from two or more than two tables then we go for joins. Basically JOIN is an operation which combines results from two or more tables. We apply JOINS on related tables. JOINS are classified as below:

•                EQUI JOIN / INNER JOIN
•                OUTER JOIN                         

                                         LEFT OUTER JOIN
                                         RIGHT OUTER JOIN     
                                         FULL JOIN

•                 SELF JOIN 
•                 CROSS JOIN 

 INNER JOIN 

         INNER JOIN is also known as EQUI JOIN. It combines the data from two or more tables based on the JOIN conditions. It displays all the matching records and eliminates all non matching records. 

Let us consider below two tables for illustrations.

CustomerInfo Table

CustomerInfo Table

 ProdctInfo Table

ProductInfo Table

             Now , we will make a inner join between CustomerInfo Table and ProductInfo Table by writing the following below query.

SQL- Copying Structure of Table without copying Data

Q. How to copy the structure of a table without copying the data ?
           It is possible to copy the structure of a table without copying the data. The following below query will help you in achieving this.

Syntax:
SELECT * INTO <TargetTableName> FROM <SourceTableName> WHERE 1=2

 In the below snapshot Customer is a source table from where we are copying the

SQL Questions PART-1

Q How do you fetch schema information of a table in SQL ?
        The following SQL query will fetch schema of a table.

         SELECT * FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_NAME='Table Name'

Ranking function in SQL SERVER

          Ranking function helps in ranking each row after partitioning has been done.Numbering depends upon the function which is used and some rows may receive the same value as other.Rank functions are non deterministic in nature. It proves to be very useful when we need our result set to be numbered sequentially.
        Firstly, the result sets produced by FROM clause are partitioned based on the column name mentioned in the PARTITION BY expression . PARTITION BY expression is optional and if the same is absent , the function assumes all rows of the result set as a single group. Once the partition has been done , ODER BY clause orders the rows within the partition and then any ranking is applied.
      There are four RANKING functions which are discussed in detail in coming paragraph. We will be demonstrating the RANKING function with and without the use of PARTITION BY expression.Consider the below 'MarksInfo' table for illustration.

MarksInfo Table

ROW_NUMBER(): It simply Returns the sequential number of the row order by specified column.


 Syntax:ROW_NUMBER() OVER([PARTTION BY <ColumnName>] ORDER BY <ColumnName>)

A. With use of PARTITION BY clause

Q.What is a SQL ? 

      SQL is not a database management system but it is a language and a tool for communicating with the DBMS. SQL stands for Structured Query Language which organizes, manages and retrieves data from the database.It is a non procedural language. Today it is the most standard computer database language.

Q. What is DDL, DML, DCL and TCL command ?

      DDL stands for Data Definition Language.It helps in defining the structure of

SQL- COUNT(ColumnName) and COUNT(*)

Q.What is the difference between count(Column Name) and count(*)?

         Count(Column Name)- It returns the total number of rows in a table                                                    excluding NULL.

         Count(*)- It returns the total number of rows in a table  including NULL.
         
     Let us consider a below 'Customer' table. Column 'ProductId' of this table has two NULL values.

Customer Table