.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 n^th  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