# SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – All about SQL Constraints – Day 22 of 35

Answer simple quiz at the end of the blog post and –

Every day one winner from India will get Joes 2 Pros Volume 4.

Every day one winner from United States will get Joes 2 Pros Volume 4.

## Check Constraints

My old track coach would tell us to give 110% effort. However, had my math teacher heard this, he would have explained that a percentage value exceeding 100% in this context is not possible. For the coach it was a fun way that implies that you will give all you have, but then somehow you will give 10% more than “all.” Does that mean 110% is always an incorrect value? No, it depends on the situation. For example, this year’s sales could be 110% of last year’s sales. The range of acceptable values defined for a field depends upon the context.

Let’s consider negative numbers for a moment. If a medical form asked for my height in inches, and I wrote negative -67.5, you would think this was either a typo or a joke.  -67.5 would be easily accepted by a decimal or float data type, but in the context of height, a negative number simply isn’t acceptable. A person’s height will always be a positive number, never a negative number. For that reason, a “Height” field in a database should be restricted to only positive values. If you want to add your own logic on top of a data type, you can use what is known as a check constraint.

In the StateList the “District of Columbia” record should consist of 68 square miles of land. However, we see a negative LandMass value for that record and a few other records.

Similar to the concept of height, there really is no such thing as a negative land mass (coastal erosion anomalies aside).  We know that the absolute value for each LandMass number in our table is correct, so we essentially just need to clean up any negative figures to positive numbers (e.g., -68 should be 68).  The UPDATE statement below multiplies the LandMass value by -1 where the existing LandMass value is < 0 (less than zero). The SELECT query in the below Figure shows that no negative LandMass values remain following the update.

### Creating Check Constraints

Our LandMass data has been scrubbed and now we want to create the check constraint to maintain this field’s data integrity during future inserts or updates. We want SQL Server to say “Yes” to legitimate LandMass values (even if 2 states are the same size) but “No” to any update or insert resulting in a negative LandMass values. The code below will check to see if the value is positive before accepting the transaction. The preferred naming convention for the check constraint is CK for check (constraint) plus the table name and the fieldname, with each part separated by an underscore: CK_StateList_LandMass.

```ALTER TABLE StateList ADD CONSTRAINT CK_StateList_LandMass CHECK(LandMass >=0)```

To prove that our check constraint will prevent DML statements which try to introduce negative LandMass numbers, we will run a quick test. Below you see an UPDATE statement which attempts to change some of the LandMass values into negative numbers. The CK_StateList_LandMass check constraint guards against updates which would result in invalid LandMass values.

In the figure below we see the CK_StateList_LandMass check constraint that we just added. In Object Explorer, expand the “Tables” folder of the JProCo database and expand Constraints.  Note: You may need to right-click to refresh the Constraints folder and see newly created items.

Let’s consider other errors which could occur in our LandMass data. Our largest current LandMass is Alaska at just over 650,000 square miles. A typo adding one or two extra zeros would really be a problem (i.e., distorting Alaska’s LandMass to either 6 million or 65 million miles).  Let’s attempt a bad UPDATE to help us recognize that our check constraint isn’t currently protecting our table from these kinds of mistakes.

```UPDATE StateList SET LandMass = 65642500 WHERE StateID = 'AK' ```

Since the UPDATE succeeded, our StateList table now incorrectly indicates that Alaska’s LandMass exceeds 65 million square miles. While constraints can’t guard against all potential data integrity problems, we should constrain the upper limit of the LandMass field in this example to disallow such glaringly large, erroneous values. Before proceeding, let’s reset Alaska’s LandMass back to its proper value:

```UPDATE StateList SET LandMass = 656425 WHERE StateID = 'AK' ```

### Changing Existing Check Constraints

We want to add another condition to CK_StateList_LandMass, our existing check constraint for the LandMass field. There isn’t a T-SQL statement which allows us to directly modify our existing check constraint.  But we can easily accomplish our purpose by dropping the current constraint object and then rebuilding it with both conditions (values for LandMass must be non-negative and less than 2 million square miles).  Re-create CK_StateList_LandMass with expressions covering both of the conditions which we want the constraint to prevent (LandMass >= 0 and LandMass < 2000000) with the following formula.

```ALTER TABLE StateList DROP CONSTRAINT CK_StateList_LandMass GO ALTER TABLE StateList ADD CONSTRAINT CK_StateList_LandMass CHECK(LandMass >=0 AND LandMass < 2000000) GO```

To test whether our revised constraint works, let’s reattempt our earlier UPDATE statement. Our second requirement (LandMass < 2000000) is similarly enforced by the revised check constraint. Values in excess of 2 million (square miles) are disallowed for the LandMass field. You may also wish to perform additional testing to observe the check constraint allows valid transactions into the StateList table.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLProgrammingChapter1.0Setup.sql script from Volume 4.

## Question 22

You have a table named Feedback that contains every record of how a customer felt about their purchase. One field is called Complaint, where 0 is no complaint and 1 is a complaint. You also have a field called Rating that ranges from 0 to 100. If a customer complains they should not be giving a perfect rating of 100. If they complain then they can enter a score between 0 and 90. If they don’t then it can be between 1 and 100. Which check constraint would you use?

1. CHECK (Rating BETWEEN 1 and 100)
2. CHECK (Rating <=90 AND Complaint = 1)
3. CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )
4. CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1)
AND ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

### Rules:

Every day one winner will be announced from United States.
Every day one winner will be announced from India.
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

## 80 thoughts on “SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – All about SQL Constraints – Day 22 of 35”

1. None of the options given will answer the question. Because all constraints shows the conditions from 1 to 100 except for the 2nd one. But even that doesn’t contain 1 to 100 if complaint=0 condition.

Sathya
India

Like

2.CHECK (Rating <=90 AND Complaint = 1)
syed
fairfax,usa

Like

3. Kalyanasundaram.K |

3) CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1)
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Answer 1 : It only checks rating column value
Answer 2 : It checks rating column value, if complaint = 1
Answer 3 : This check based on both complaint value 0 or 1.
Answer 4 : This check based on both complaint value 0 and 1.(Its always false)

Like

4. Reddy |

None of the options above returns correct results

The correct query is :
CHECK ( (Rating BETWEEN 0 and 90 AND Complaint = 1)
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

1.CHECK (Rating BETWEEN 1 and 100)

– This option is incorrect, it always validates Rating between 1 and 100 irrespective of if there is a complaint/not a complaint

2.CHECK (Rating <=90 AND Complaint = 1)

– This option is incorrect, it does not validate if there is not a complaint and also range is not specified if there is a complaint
(i.e., this query allows to enter negative rating values if there is a complaint)

3.CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

– This option is incorrect because if there is a complaint, it should check between 0 and 90 not between 1 and 90

4.CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1)
AND ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

– This options is incorrect because this validates the Rating field
when Complaint is 1 then Rating must be between 0 and 90
when Complaint is 0 then Rating must be between 1 and 100

Thanks.

Country – India

Like

5. Answer is #3. you have to include the OR in the constraints.

US

Like

6. Rene Alberto Castro Velasquez |

Correct answer is No. 3, because we want that the users introduce a score between 0 and 100 if they do not complain, but if they complain, then they should not be able to give a perfect rating of 100 and they can can enter a score between 0 and 90
Rene Castro

Like

7. Uday Bhoopalam |

Uday Bhooapalm
USA

Like

8. Prerana Sharma |

CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Explanation: As in case of complaint (1), Rating value can be between 1-90
and in case of non – complaint(0), rating value can be anywhere between 1-100.

There are some examples where only one of the above condition can be true, so it is achieved by OR condition in the CHECK constraint.
Example : Complaint = 1 and rating = 91 — Invalid
Complaint = 0 and rating = 91 — valid

Resident : USA

Like

9. Karthikeyan.S |

3.CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Like

3) CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

This will check that if complaint =1 , then rating is between 1 and 90(coded and an AND operation)
or if Complaint =0 , rating is between 1 and 100 (again coded with AND operation)

Both these conditions are the possible conditions. hence the OR contion

Like

11. Question 22
Ans :
CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Chennai, INDIA

Like

12. Sudhir Chawla |

Hi,

Option 2 is correct because for complaint the Rating must be <=90.

Thanks

Sudhir Chawla
New Delhi, India

Like

13. Hi, as per description Option 3 is the right Answer as:
“CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )”

Thanks….
Rajneesh Verma
(INDIA)

Like

14. Manoj Sahoo |

Option 3 is th Correct Answer which is

CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Manoj Sahoo

India

Like

The correct answer is option 3 that is

CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

As if Complain is set to 1 we need to give rating only between 1 and 90 or if complain is 0 then rating should be tetween 1 and 100.

Of course we also need to add constrain to allow user only to enter complain either 0 or 1.

INDIA

Like

16. ghanshyam |

CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Ghanshyam
India

Like

17. shekhar gurav |

as all the condition check can be done by this option.

shekhar gurav.
country : India

Like

18. santosh.s |

The correct answer is option 3)
Because option:
1) checks for Rating , but it does not take into consideration whether Complaint is 0 or not.
So this is not correct one.
2) This checks if the complaint is 1, then it takes the Rating below 90. But this does not check for complaint =0.So this option is also not the right one.
3) This is the correct one. Because it checks both the conditions .
4) This is a wrong one. We cannot create this constraint with “AND”.
It gives an exception – “Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint “CK_Validate”(or which ever is the constraint name ).

Thanks & Regards
Santosh.S
Bangalore, India

Like

19. Kumar Harsh |

Kumar Harsh
Delhi, India

Like

20. Ritesh Choksi |

option 3rd is correct
India

Like

Reason: Only one of the Complaint conditions will be true – Either a customer complaints or not.

Country of residence: India

Like

22. Partha Pratim Dinda |

Ans: 3
CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Others are incorrect according to query requirements.
1st one only check the rating field value in between 1 and 100
neither check complaint 1 nor complaint 0,
2nd one only check the value if complaint is 1 and it also allow user to put negative value.
3rd one check both condition of complaint and also its corresponding rating.
4th one check both condition and its rating all time whether complaint 0 or 1.but this condition never satisfy because at time complain may not be 0 and 1
.

so 3rd one is only write answer.

Partha
India.

Like

Option 3 is Correct

Arjun
India

Like

24. Correct Ans:3

3.CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Regards
Rajesh
From India

Like

25. Correct answer is option #3.

3.CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

New Delhi
India

Like

3.CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Country : India

Like

27. Narendra |

The Correct Answer Is Option – 3

Thanks,
Narendra(India).

Like

option #3

Like

29. None will be right, but still if we have to select

2.CHECK (Rating <=90 AND Complaint = 1)

as we need to have check constraint on rating to 90 only when there is a complaint, if we have no complaint then we will have no checks on rating so that we can enter untill 100.

but still it doesn't satisfy the for Complain=0 condition i.e. between 1 and 100 and even option 3 is not be correct as for Complaint=1 it takes values between 1 and 90, but as mentioned in the question it should be between 0 and 90

Hemanth

Like

3) CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )
Romania

Like

31. Anish Shenoy.P |

Hi Sir,

None of the above options are correct.

According to the question
Point no 1 : If they complain then they can enter a score between 0 and 90.
That is the Rating should be between 0 and 90 when Complaint = 1

Point No 2 : If they don’t then it can be between 1 and 100.
That is the Rating should be between 1 and 100 when Complaint = 0

So the correct check constraint would be :

CHECK ( (Rating BETWEEN 0 and 90 AND Complaint = 1)
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

P.Anish Shenoy,
INDIA, Bangalore, Karnataka

Like

32. santosh.s |

The correct answer is “None of the above”

All the options try to query 1 t0 100 or 90. But in the question you have asked from 0 to 100 or 0 to 90.
So none of them is the right answer

Thanks & Regards
Santhosh
India

Like

33. Sonnie Avens |

Option #3 is correct:

3.CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Sonnie Avens
New York, USA

Like

34. Shilpa Sharma |

Correct option is 3.

Shilpa
India.

Like

35. Correct answer is # 3.

CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Because we want that the users introduce a score between 0 and 100 if they do not complain.
But if they complain, then they should not be able to give a perfect rating of 100 and they can can enter a score between 0 and 90.

Here, we can consider,

Compliant = 1
Non-Compliant = 0

Gopalakrishnan Arthanarisamy
Unisys, Bangalore, India

Like

36. Nikhildas |

CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

This will check that if complaint =1 , then rating is between 1 and 90(coded and an AND operation)
or if Complaint =0 , rating is between 1 and 100 (again coded with AND operation)

Both these conditions are the possible conditions. hence the OR contion

cochin,INDIA

Like

37. Ans : 3.CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

First it will check Complaint Yes, then rating should be between 1 to 90 OR complaint 0 then rating can be 1 to 100.

Like

38. Chetan Jain |

The correct answer is “None of the Above”.

option 1 and 2 are obviously not considering all the conditions to check.

option 3 is close but it will fail when complaint =1 and rating =0 (this is specified in the question that itself)

Option 4 is using AND condition which will not allow any values to be inserted in the table. So this is totally wrong.

The ideal solution should be:

CHECK ( (Rating BETWEEN 0 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Chetan Jain (India)

Like

39. Ans: None of the Above
i thought Option 3 will be the corret ans.. as it is checking Coplaint and Ranking Range, but after reading the question agian i come to know that OPTION 3 is not correct ans

correct Ans:
3.CHECK ( (Rating BETWEEN 0 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 0 and 100 AND Complaint = 0) )

Like

CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

First Case:
Step1: One field is called Complaint, where 0 is no complaint and 1 is a complaint.

Step2: If a customer complains they should not be giving a perfect rating of 100
– So compliant is 1 and not to 100

Step3: If they complain then they can enter a score between 0 and 90.
– So still compliant is 1 and score between 0 to 90

Second Case:
Step4: If they don’t then it can be between 1 and 100
– So the no compliant is 0 and rating between 1 to 100

Step5: It can be Compliant OR no compliant

So the answer is Option 3.

-Devarajan (India).

Like

41. HI ,

Option 3 : CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

this is correct option.

I am from Gujarat,India

Like

42. Raju |

Hi,

None of the options are Correct.

CHECK ( (Rating BETWEEN 0 and 90 AND Complaint = 1)
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Because,
Rating should allow values between 0 and 90 When there is a complaint(i.e., complaint=1).

OR

Rating Should allow values between 1 and 100 When there is No Complaint(i.e., Complaint=0)

Like

43. Raju |

Hi,

None of the options are Correct.

CHECK ( (Rating BETWEEN 0 and 90 AND Complaint = 1)
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Because,
Rating should allow values between 0 and 90 When there is a complaint(i.e., complaint=1).

OR

Rating Should allow values between 1 and 100 When there is No Complaint(i.e., Complaint=0)

Raju,
India.

Like

44. Answer 3 is the closest, but it does not quite meet the specifications of the question.
CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

The first line should read “Rating BETWEEN 0 AND 90 AND Complaint = 1″ because if the customer complains, they could enter a rating of 0. Answer 3 would exclude the 0 rating by starting with a 1.

Answer 3 is also better than Answer 4 due to the “OR” criteria between the two check constraints. Answer 4 would always be wrong because Complaint can not always be 1 AND 0.

Matt Nelson, USA

Like

45. Mike Michalicek |

None of the options will create the check constraint we need here.

Question 22 stated: If a customer complains they should not be giving a perfect rating of 100. If they complain then they can enter a score between 0 and 90. If they don’t then it can be between 1 and 100. Which check constraint would you use?

Option #3 is close but not correct. It shoud look something like this:
CHECK ( (Rating BETWEEN 0 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

USA

Mike Michalicek

Like

46. Correct Option is 3
CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0)

(Sale, Nigeria)

Like

47. Bill Pepping |

Hi Pinal,

Challenge:
Question 22
You have a table named Feedback that contains every record of how a customer felt about their purchase. One field is called Complaint, where 0 is no complaint and 1 is a complaint. You also have a field called Rating that ranges from 0 to 100. If a customer complains they should not be giving a perfect rating of 100. If they complain then they can enter a score between 0 and 90. If they don’t then it can be between 1 and 100. Which check constraint would you use?

1. CHECK (Rating BETWEEN 1 and 100)
2. CHECK (Rating <=90 AND Complaint = 1)
3. CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )
4. CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1)
AND ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

The choice above that is closest to being correct is choice #2, although it falls short of the requirement.

Explanation:
Choice #1 is incorrect because it will allow a rating up to 100 even if the complaint column is 1.
Choice #3 is also incorrect becuase the requirement is to allow a user to enter a score between 0 and 90 when they mark the complaint column with a 1 (not between 1 and 90).
Choice #4 is also incorrect because it will never be possible. The Complaint column can have only one value per customer experience. It can be zero or 1, but not both. The 4th choice uses an "AND" to try to force both values, which isn't possible.
In Choice #2, if the complaint column has a value of 1, then the Rating score must be 90 or less. If the Complaint column is zero, then this constraint will not enforce a range of the Rating score.

Choice #2 should be enhanced to also constrain the range of values when the Complaint column is zero. In that case, you would want to allow a value from zero to 100. You would also want to enforce that if there is a
complaint, the value should be between zero and 90, not just less than 90. You wouldn't want a negative value to be entered and stored in the database.

It would like like this:
CHECK ( (Rating BETWEEN 0 and 90 AND Complaint = 1 ) OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Country:
United States

Thanks for the knowledge!

Regards,

Bill Pepping

Like

48. The answer is number 3.

3. CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Answers 1 and 2 only check one scenario. Answer 4 uses the AND clause; it is not possible to have a complaint and no complaint at the same time.

Answer 3 is the only one to account for both scenarios and accept only 1.

Country: USA

Like

49. Chetan |

CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Chetan – USA

Like

50. the correct answer option 3 i.e.

3.CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

India

Like

51. Hima |

The closest option is Option#3.

3.CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Maybe it is a typo, but if Option 3 has the range from “0 to 90″ then it would fir the requirement spec. Or else the spec needs to be modified to “1 to 90″

Spec – ” If a customer complains they should not be giving a perfect rating of 100. If they complain then they can enter a score between 0 and 90. If they don’t then it can be between 1 and 100. Which check constraint would you use? ”

Hima
USA

Like

52. Diljeet Kumari |

Hi Pinal Sir,

Good Question In fact very good:-

Here None of the given answer satisfies.

Here None of the answers given satisfies the given condition, Because all check constraints shows the values condition between 1 to 100 except for the #2) Option.

But even that doesn’t contain value ranges from 1 to 100 if complaint=0 condition comes.

Here None of the given answer satisfies.

Diljeet Kumari
Country : India

Like

53. Abraham Forchue |

CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

–where 0 is no complaint and 1 is a complaint
–You also have a field called Rating that ranges from 0 to 100
–If a customer complains they should not be giving a perfect rating of 100, should be 0-90
CHECK (Rating BETWEEN 1 and 90 AND Complaint = 1)

If they don’t then it can be between 1 and 100
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Abraham Forchue
Dominican Republic

Like

54. Naomi |

Option #3 is very close to the problem:
CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

However, in case they complain, we want to be able to enter score of 0 also, so the first part should be Rating between 0 and 90

All other options are not even close to the correct answer (option 4 is close, but incorrectly uses AND so it never will be true).

I am from USA

Like

55. Don |

Option 3 is the only answer that will satify both required conditions.

Country: United States

Like

56. David Brust |

If they complain then they can enter a score between 0 and 90. If they don’t then it can be between 1 and 100. Which check constraint would you use?

option 3

CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

David

Like

57. Correct Answer is: Option 3

Thanks,
Basavaraj
India

Like

58. A. Arul Prakash |

This will check that if complaint =1 , then rating is between 1 and 90(coded and an AND operation) or if Complaint =0 , rating is between 1 and 100 (again coded with AND operation)
Both these conditions are the possible conditions.

Like

59. A. Arul Prakash |

This will check that if complaint =1 , then rating is between 1 and 90(coded and an AND operation) or if Complaint =0 , rating is between 1 and 100 (again coded with AND operation)
Both these conditions are the possible conditions.
Country : USA

Like

60. Hi pinal Sir,

In the above question: none of the above answers are correct.

Explanation:

Given : Feedback table complaint and Rating two columns.

Requirement: if complaint = 0 (No complaint ) then Enter 1 to 100 the third query vary nicely provides.

But when we go for if complaint = 1 (complaint) then we will be able to enter 90 but also we are allowed to enter 1 but not 0 but as per our requirement we can enter 0 hence none of the above answers are correct.

Create statement :

create table Feedback
(
complaint int,
Rating int, CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )
)

also there is a typo in option 3 you have included one bracket extra that leads to syntax error rectified in above query.

when no complaint works fine

insert into Feedback values(0,100)
insert into Feedback values(0,1)

when complaint works fine
insert into Feedback values(1,90)

but not allowed
insert into Feedback values(1,0)

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint “CK__Feedback__4A18FC72″. The conflict occurred in database table “dbo.Feedback”.
The statement has been terminated.

Hence no option provide with the exact solution..

Dilip Kumar Jena
Country : INDIA

Like

61. Deb |

I believe you intended #3 to be the correct answer

CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

However I believe there is a typo and it should read:

CHECK ( (Rating BETWEEN 0 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Reasoning: The first two options only enforce one of the two conditions. The fourth will not work because of the AND between the two conditions. Nothing will insert because the Complaint column is either a 0 or 1 and the AND between the two conditons pretty much makes it so it needs to be both. That leaves the third option. It uses OR and has information enforcing both constraints, however you said the rating could be from 0 to 100 and the third option eliminates the possiblity of entering 0. However I would assume if someone is entering 0 then they must have a value where complaint = 1. #3 would be correct if the first 1 was a 0.

Deb – USA

Like

62. Partha Dutta Gupta |

None of the options are correct.

The first option check for rating and not complaint. Thus a value of 1 in complaint and more than 90 would be considered valid and it dos not serves our purpose.

The second option restricts complaint to 1 and rating less than =90. Thus a 0 value in complaint and value of between 91 and 100 in rating would be invalid and thus it does not server our purpose.

The third option is the most closest. but as our purpose is–> If customer complain then they can enter a score between 0 and 90. If they don’t then it can be between 1 and 100. Thus the third constraint would restrict us to put a value of Zero(0) in rating with complaint =1 . Thus this option will not work.

The fourth constraint is just the worst as the Complaint column would have to be both Zero(0) and 1 in same record which is impossible.

Country –> INDIA

Like

63. None of the above answers are correct. I think the correct option will be
CHECK ( (Rating BETWEEN 0 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Sudeepta,
India.

Like

64. Eric |

I believe 3 is the correct answer

CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0)

Eric
USA

Like

65. Raj Ejanthkar |

You have a table named Feedback that contains every record of how a customer felt about their purchase. One field is called Complaint, where 0 is no complaint and 1 is a complaint. You also have a field called Rating that ranges from 0 to 100. If a customer complains they should not be giving a perfect rating of 100. If they complain then they can enter a score between 0 and 90. If they don’t then it can be between 1 and 100. Which check constraint would you use?

CHECK (Rating BETWEEN 1 and 100)
CHECK (Rating <=90 AND Complaint = 1)
CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )
CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1)
AND ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Sol :

The correct answer is Option 3.

Option 1 : This wouldn't work since we are not checking for Complaint column
Option 2 : This wouldn't work since we are not checking for Complaint =0 and rating between 1 and 100
Option 3 : This would work since we are checking for Complaint =1 and rating between 0 and 90 or complaint = 0 and rating between 1 and 100.
Option 4 : This wouldn't work since we are checking for Complaint =1 and rating between 0 and 90 and complaint = 0 and rating between 1 and 100.
thereby checking for both the conditions which is not right.

Raj Ejanthkar
USA

Like

66. Srikanth Nallamothu |

Like

67. Pratik Raval |

3.CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Pratik Raval
India

Like

68. Gordon Kane |

3.CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Gordon
USA

Like

69. Azhar Iqbal |

Option 3 is True.
AzHAR Iqbal
Pakistan

Like

70. None of the options are “exactly” true. Based on the wording of the problem,
If the customer has no complaint (Complaint = 0) then their rating can be between 1 and 100 (Rating BETWEEN 1 AND 100). If the customer does have a complaint (Complaint = 1) then they can only answer a score between 0 and 90 (Rating BETWEEN 0 AND 90)

Option 1 disregards whether the customer has a complaint or not
Option 2 doesnt consider if the customer does not have a complaint
Option 3 is the “most” correct but doesn’t include the correct range for ratings as stated in the question
Option 4 is impossible because the customer can’t have a complant AND not have a complaint.

The correct constraint would be

CHECK ((Complaint = 0 AND Rating BETWEEN 1 and 100) OR (Complaint = 1 AND Rating BETWEEN 0 and 90))

Country of Residence: USA

Like

71. Ramya |

2.CHECK (Rating <=90 AND Complaint = 1)

Provided the Rating column can only accept between 0 and 100
and Complaint column can only accept 0 or 1.

–Ramya

Like

72. rakesh kumar |

correct option is 3.

Like