Sql Server Interview Questions and Answers (1758) - Page 65

Latest and authentic Interview questions. You can also post an interview question and win monthly prizes as well as gain community credit points.

1758 records found.

Post |

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

What is the difference between Where Clause and Having Clause?

1). Where clause filters the rows before grouping i.e. aggregation is performed,where as Having clause does after the grouping is performed.

2). Where clause can be used with insert,update and delete statement,where as having clause is only used with select statement.

3). Aggregate function can not be used in the Where clause,where as in the Having clause we can use aggregate function.

Which statement is True about Group By clause?

NOTE: This is objective type question, Please click question title for correct answer.

What do we mean by Check Constraint in Sql Server?

A check constraint is a rule that identifies acceptable column values for data in a row within a SQL Server table.In other words, we can say that,the Check Constraint is used to validate or limit the value range given that can be placed or marked on a column. Actually a CHECK constraints determine the valid values from a logical expression that is not based on data in another column.
Note:-1).If we define a Check Constraint on a single column,then it allows only certain values for this column.
2).If we define a Check Constraint on a table,then it can limit the values in certain columns based on values in other columns in the row.

How to define a Check Constraint while creating Tables in MySql?

Following script creates a Check Constraint on Employee_Master table :-

Create Table Employee_Master

(

  Employee_Id int not null,

  First_Name varchar(100) not null,

  Middle_Name varchar(100),

  Last_Name varchar(100) not null,

  Employee_Name varchar(100) not null,

  PAN_Number varchar(10) not null,

  LDAP varchar(10) not null,

  Check ([PAN_Number] Is Not Null)

)

Here, I have used a Check clause that is associated with the PAN_Number column,which indicates that Pan Number should not accept any null values.

Can we give or assign more than one column a Check Constraint?

Yes.We can provide check constraints on multiple columns as shown below example:

Create Table Employee_Master

(

  Employee_Id int not null,

  First_Name varchar(100) not null,

  Middle_Name varchar(100),

  Last_Name varchar(100) not null,

  Employee_Name varchar(100) not null,

  PAN_Number varchar(10) not null,

  LDAP varchar(10) not null,

  Check ([Employee_Id] > 0 

  And [PAN_Number] Is Not Null  

  And [LDAP] Is Not Null)

)

Here,Check Constraints are applied on Employee_Id,Pan_Number and LDAP columns which says that employee id must be greated than zero(0) and
PAN number and LDAP column can not have Null values.

How to define Check Constraint while creating Tables in Sql Server,Oracle and Ms-Access database?

Following script creates a Check Constraint on Employee_Master table :-

Create Table Employee_Master

(

  Employee_Id int not null Check ([PAN_Number] Is Not Null),

  First_Name varchar(100) not null,

  Middle_Name varchar(100),

  Last_Name varchar(100) not null,

  Employee_Name varchar(100) not null,

  PAN_Number varchar(10) not null,

  LDAP varchar(10) not null

  )

)

How to assign check constraint a Name for multiple columns in Sql Server?

Refer below script:-

Create Table Employee_Master

(

  Employee_Id int not null Constraint ck_employee Check([PAN_Number] Is Not Null),

  First_Name varchar(100) not null,

  Middle_Name varchar(100),

  Last_Name varchar(100) not null,

  Employee_Name varchar(100) not null,

  PAN_Number varchar(10) not null,

  LDAP varchar(10) not null

  )

)

Which statement is correct for creating Check Constraint on an existing tables?

NOTE: This is objective type question, Please click question title for correct answer.

How to assign a name to Check Constraint on existing tables?

We can refer below script.
We have to use Constraint keyword in case of Sql Server,Oracle and MS-Access.

Alter Table Employee_Master

Add Constraint ck_employee Check([PAN_Number] Is Not Null)

Here,ck_employee will be used as a constraints name.

How to remove a Check Constraint from column in Sql Server,Oracle and Ms-Access?

Use below script for dropping a constraint from a table:-

Alter Table Employee_Master

Drop Constraint ck_employee

How to remove a Check Constraint from column in MySql database?

Use below script:-

Alter Table Employee_Master

Drop Check ck_employee

Note:-There is only one difference between dropping constraint in Sql server and MySql database is Constarint and Check keyword.

How to validate Pan number which should not be greater than 10 digits with the help of check constraint?

We can write below script to validate PAN Number as:

Alter Table Employee_Master

Add Constraint CK_PAN_Number_Length

Check (Len(PAN_Number) Between 1 And 10)

How to validate Zip Code which should not exceed more that 5 digits with check constraint?

We can write below script to validate Zip code as:

Alter Table Employee_Master

Add Constraint CK_Zip_Code

Check ([ZIP_Code] Like '[0-9][0-9][0-9][0-9][0-9]')

How to validate Employee Age which should be greater than or equal to 18 years with check constraint?

We can write below script to validate Age as:

Alter Table Employee_Master

Add Constraint CK_Age

Check ([Age] >= 18)

Found this useful, bookmark this page to the blog or social networking websites.

Bookmark It

Interview Questions and Answers Categories

Data Structures & Algorithms

Design Pattern & Practices

DotNetFunda.Com

Entity Framework

Error and Solution

Function Points (FPA)

MSBI (SSIS, SSRS, SSAS)

Web Services, Remoting