Friday 28 December 2012

.NET and SQL Server 2005 Interview Questions



Q 1.  See the below Scenerio:

Table : Product

Prod_Name
Prod_code
Vegon-R
A1
Swift
A1
ZEN
A1
Indica
A2
Manza
A2
Xylo
A3
Xuv
A3
Logan
A3
Jeep
A3


Now find the output as given below:

Prod_Name
Prod_code
Vegon-R
1
Swift
2
ZEN
3
Indica
1
Manza
2
Xylo
1
Xuv
2
Logan
3
Jeep
4

Ans:-

Select Prod_name,(ROW_NUMBER() OVER(PARTITION BY p1.Prod_code ORDER BY p1.Prod_code)) as Prod_count from Product p1



Q 2. What is the difference between SQL Server 2005 and SQL Server 2008R2 ?

Ans:-

s.no
SQL SERVER 2005
SQL SERVER 2008 R2
1
Xml data type is introduced
XML Data type is used
2
Cannot encrypt the entire Database
Can Encrypt the entire database introduced in 2008
3
Datetime is used for both date and time
Date and time are separately used for date and time
4
No table datatype is include
Table datatype introduced
5
SSIS is started using
SSIS avails in this versions.
6
CMS is not available
Central Management Server(CMS) is introduced.







Q3. What is difference between Constant and Read only variables ?

Ans:-

Read Only:-  A read only field can be initialized either at declaration time in the constructor. The read only field can have different values depending on the constructor used. The read only fields can be used for run time constants. It makes the fields as unchangeable but the property can be changed inside the constructor of the class.

Constants:-  A constant field can only be initialized at the declaration of the field. The constant field can be used for compile time constant and it cannot be change at run time. If a program where ever we are using constant, then that value will be replaced by the compiler.

Syntax:-

Public const int ConstValue = 10;

Public static readonly int ReadOnlyValue = 20;




Q 3. See the given below scenario ?

Table : Emp                                      Table : Emp_Leave

Emp_id
Emp_name
001
A
002
B
003
C
004
D

Emp_id
Emp_leave
001
01/01/2012
001
02/01/2012
002
14/03/2012
003
02/12/2012
003
03/02/2012


Find out the below output from the above tables:

Emp_id
Emp_name
Total_Leaves
001
A
2
002
B
1
003
C
2
004
D
0



Ans:-

Select Distinct e.emp_id, e.Emp_name,(Select count(ee.Emp_id) from emp_leave ee where ee.emp_id = e1.emp_id) as Total_Leaves from Employee e LEFT OUTER JOIN Emp_leave e1 on e.emp_id = e1.emp_id.




Q4. What is Run-time or Dynamic Polymorphism   ?

Ans:- Please see below example for Run – time polymorphism :


class Shape1
    {
        public virtual void Draw1()
        {

        }
    }

    class Ractangel1:Shape1
    {
        public override void Draw1()
        {
            Console.WriteLine("Rectangle Drawn ");
        }

    }

     class Circle1:Shape1
    {
        public override void  Draw1()
        {
            Console.WriteLine("Circle Drawn ");
           
        }

    }
     class Traingle1:Shape1
    {
        public override void Draw1()
        {
            Console.WriteLine("Triangle Drawn ");
           
        }
    }

  static void Main(string[] args)
        {
            /* Testing Polymorphism */

            Shape1[] s = new Shape1[3];

            /* Polymorphic Objects */
            /* creating Array with Different types of Objects */

            s[0] = new Circle1();
            s[1] = new Ractangel1();
            s[2] = new Traingle1();

      Console.WriteLine("\n\nRuntime polymorphism test\n\n");

            for (int i = 0; i < 3; i++)
            {
                s[i].Draw1();
            }

            Console.ReadKey();
        }



Q 5. What is difference between IN and ANY Clause ?

Ans:-          We cannot put operator in IN Clause like (<=,>=,!=,== etc.). But we can put Operator in ANY Clause.




Q 6. What is the diff. between IN and WHERE Clause ?

Ans:- IN Clause can use multiple values but WHERE clause one condition with Operator.




Q 7. Consider a table with 8 rows. 4 row contains 0 and 4 row contains 1, now write a single query to make all 0’s as 1 and all 1’s as 0.

Ans:-          update mytable set mycolumn = (mycolumn+1)%2



Q 8.  How to swap data of two columns in a table. Both the columns containing varchar value?

Ans:-          Update table set Column1 = Column2, Column2 = Column1;



Q 9.  Can a Primary Key has multiple fields ?

Ans:- Yes, A Primary key can have multiple fields of a table, But a table cannot have multiple Primary Key. A Primary key can be made with the combination of multiple fields.

Exp:-

Create table Emp(Eid in NOT NULL, Name VARCHAR(50) NOT NULL,
Address VARCHAR(100), CONSTRAINT EID_PK PRIMARY KEY(EID,Name));




Q 10. What is the difference between FULL OUTER JOIN and UNION ALL ?

Ans:- FULL OUTER JOIN :- It will show both or nth  no of tables records according to condition.

UNION ALL:- It can show the records with or without condition and both of the tables OR Views should have same Number of Columns






Q 11. What is difference between Stored Procedure and Function ?

Ans :-

S.No
Function
Stored Procedure
1
A function is always return a value using return statement
A Procedure may return one or more value through parameter or may or return values at all
2
Function are normally used for computation
Procedure are normally used for execution business logics.
3
Function return type could be scalar, table or table values.
Stored procedure always return integer values by default zero.
4
A function can be used inside the Query
But Stored procedure cannot be used onside select Query.




Q 12. What is difference between check and Rule?

Ans:-

S.No
Check
Rules
1
Check constraints are a part of the tables definition
Rules are not part of table definition.
2
We can create check constraint using create table or alter table clause
Rule are create independently of the table and can be bound to multiple columns of diff. tables.
3
If the check constraint are create suing alter table clause they will check on the column where they are defined
Rule do not check the existing data




Q 13. How to create table with identity columns?

Ans:- Create table IdentityDemo(idno indentity(100,1),IdName varchar(50));


How to insert:- insert IdentityDemo(‘Jeetu’)



Q 14. How to save and copy output in another table using SQL?

Ans:- Select * into myTable from(select * from emp Cross Join Sales) A

Note: A is a temporary variable



Q 15. How to Drop and Add column in a table using Alter SQL ?

Ans :-

ADD

Sysntax:

Alter table <Table_name> add <New_Attribute1/Column_name1> <data type>, <New_Attribute2/Column_name2> <data Type>……;


Exp:-

Alter table Emp add Address varchar(100);


DELETE


Sysntax:

Alter table <Table_name> drop column <Column_Name>

Exp:-

Alter Table Emp drop Column Phone




Q 16. What is sealed Keyword?

Ans:- To Prevent a class from inheritance and function from overriding, we use Sealed keyword.



Q 17.  What is the use of virtual keyword?

Ans:-  Virtual keyword is used with class function just because the definition of same function name and parameter can be possible by another class with diff. behaviors.



Q 18. What is Polymorphism?

Ans:- Polymorphism is the concept by which we can have diff methods or properties with the same name either with difference signature or implementation.



Q 19. What is interface?

Ans:- It is a reference type similar to a class and includes some methods and properties which can only be implemented by inheriting the interface through classes or structure.

All behavior of interface becomes public and override by default.



Q 20. What are the Class Library ?

Ans:- Library Class is an application which program are also called the component programs. These Programs becomes inside the .dll file which we calls assembly file of Class Library.



Q 21. What are the Static Classes?
Ans:-  The Classes are defined by static Modifier. The Static class can be usefull if we do not need any inheritance and need static members only.
We cannot create instance of a static class and we cannot inherit static class.
Example:-
Static Class jeet
{                 
static void Abc()
          {
           Console.WriteLine(“Static Class”) ;
           }
static void Main()
           {
           Abc();        // No need to create Class jeet instance;
           }
}

Q 22. What are the partial Classes?
Ans:- The Classes are defined by the partial modifier and have got the same name. Partial classes are treated as a single class and we can access all the members of different partial classes by using a single objects reference partial classes are used in window/ Web applications.

Example:-
partial Class Calc
{
int a;
int area()
       {
        return a*a;
       }
}
partial class Calc
{
void dd()
     {
     Console.WriteLine(“Welcome”);
     }
}
partial class Calc
{
        void Abc()
            {
            Console.WriteLine(“Welcome”);
            }
static void Main()
    {
    Calc c = new Calc();
    c.abc();
    c.a=40;
    Console.WriteLine(c.area());
    c.dd();
    }
}

Q 23. What is the difference between SOME,ANY and ALL Keywords?

Ans:- SOME:  This operator compares the value in the query with the values in the sub query. It returns true if the condition matches with any of the result in the sub Query.

Example:-
If 2 > (select ID from Employee)
print ‘yes’
else
print ‘No’

ANY:- SOME and ANY are the same thing.

ALL:- This operator compares the values in the query with values in the sub Query. It returns true if the condition matches with all of the result in the sub Query.

Exp:-
if 4 > ALL(select ID from Employee)
Print ‘return True’
else
Print ‘return False’

Q 24. What is difference between UNION and UNION ALL?

Ans:-  UNION ALL:- In UNION ALL the rows will not be distinct or can be duplicate. It means more than one row of same records can be shown.
UNION:- In UNION only distinct rows will be shown. Duplicate rows will not be shown here.

Q 25.  What is difference between Custom control and User control?

Ans:-
S.No
Custom Control
User Control
1
Custom control are assemblies that can be used in multiple applications
User control is page file with .ascx extension that can be used within a single application.
2
Custom control can be added to Toolbox
User Control cannot be added to the toolbar of .net
3
Custom control can be viewed during design time.
User control cannot viewed during run time due to becoming generic.
4
A custom control developed in C# can be used in a project developed in VB.NET or any other managed code and vice versa
But this is not possible with user control. These are Language specific.


Q 26. What is difference between string and String Builder?

Ans:- String : It is a immutable class. Means we cannot make changes in the particular location of the memory where data is assigned.
It means that every time, we assign a new data, the variable will point to a new location memory.

Example :-           String s= “hello”;
                     s + = “good”;
                     s + =  “Welcome”;
New location in the memory will pointed to by s variable string is more beneficial where we want formatting of string (e.g. padleft(), Upper(), Lower(), Substring() methods are there in string)

String Building:-  It is a class defined by System.Text name Space.
It is mutable. It means we can take change on one object StringBuilder is more beneficial where we want frequent string manipulation.


Q 27. What is the difference between Candidate Key and Composite Key?
Ans:-  Candidate Key : A attribute that is Uniquely identify a row in a table is key as Primary key. All candidate keys which are not chosen as “Primary key” are alternative keys. The key which uniquely identify the rows of the table and which is made up more than one attribute is called Composite key.

Composite Key:-  A Key formed by combining at least two or more columns is called composite key.


Q 28. How to Convert Your Table data into XML Format?
Ans:-    Select * from employee for xml auto

Q 29. How to create Foreign key? Given an query Example?

Ans:-
Table : CATEGORIES
Create table Categories(id int primary key, Cname varchar(50))

Table : PRODUCT
Create table product(pid int, pname varchar(20), catid int constraint fk foreign key reference categoriest id);


Q 30. What is difference between Delete and Truncate?

Ans:-
S.No
Delete
Truncate
1
Delete records do not save until they are committed and they can be rollback.
The record will be removed permanently by the truncate command.
2
It will effect Stored Triggers
Will not affect stored triggers.
3
Process Slow
Process fast as compare to delete