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;