Filtering Data

Question 1

What is returned if you only specify a date you want to filter for from a column but the column is DATETIME type?

Answer 1

When you only specify a date when filtering a DATETIME column, time of midnight is assumed. So, if asked for ‘20070212’, only records on that date with a time stamp of midnight would be returned.

SQL Server 70-461 03-01

Question 2

How does the NOT operator treat nulls?

Answer 2

Nulls will be excluded unless IS NULL, IS NOT NULL or some similar expression is used.

For example, NOT region=’WA’ will only return rows where the region is filled in and doesn’t equal WA. Since a null has an unknown value, the query cannot decide if it doesn’t equal WA and excludes it.

SQL Server 70-461 03-01

Question 3

What is the proper form of a predicate using LIKE?

Answer 3

column LIKE pattern

SQL Server 70-461 03-01

Question 4

How can you combine predicates in the WHERE clause?

Answer 4

• AND
• OR

SQL Server 70-461 03-01

Question 5

Explain Three Valued Logic

Answer 5

Predicate can evaluate to true, false or unknown since NULLS are involved.

SQL Server 70-461 03-01

Question 6

Explain Two-Valued Logic

Answer 6

Predicate expression evaluates to true or false, if NULLS are not possible.

SQL Server 70-461 03-01

Question 7

How does the % wildcard work?

Answer 7

• The percent sign stands for a string of any size, including an empty string.
• Example: ‘D%’ This will find all strings that start with a D and follow with pretty much anything else

SQL Server 70-461 03-01

Question 8

How does the _ wildcard work?

Answer 8

• The _ stands for any one character.
• Example: So, if you wanted to find a name that started with any character but wanted to ensure the second character was D you would use ‘_D%’. % standing for a string of any number of characters, even an empty string.

SQL Server 70-461 03-01

Question 9

How does the character list wildcard work?

Answer 9

• Between square brackets, [ ], list the acceptable characters.
• Example: ‘[ABC]%’ This will return all names that start with A, B or C and have anything after that.

SQL Server 70-461 03-01

Question 10

What form does a predicate need to be in for the query to filter efficiently?

Answer 10

A search argument

SQL Server 70-461 03-01

Question 11

What does not work when trying to include NULLS in a WHERE clause?

Answer 11

• Region=NULL
• Two nulls are not considered equal to each other since null is an unknown value.
• Region <>’WA’ will not work either because it will not include regions that are NULL.

SQL Server 70-461 03-01

Question 12

What does it mean for a WHERE clause to evaluate to false?

Answer 12

The predicate evaluates to false and nulls are not included.

SQL Server 70-461 03-01

Question 13

How does the character range wildcard work?

Answer 13

• Between square brackets, [ ], indicate a range of letters or numbers. Examples, [A-E] or [0-9].
• Example: ‘[A-E]%’ would indicate that the first letter of the name start with A, B, C, D or E and have anything after that.

SQL Server 70-461 03-01

Question 14

How does the NOT IN character list or range wild card work?

Answer 14

It works just like the character list and character range wildcards except you put a ^ after the left bracket to indicate not in the list or range.

SQL Server 70-461 03-01

Question 15

What is a predicate?

Answer 15

An expression that is either true or false.

SQL Server 70-461 03-01

Question 16

How can null values be included or excluded in a WHERE clause?

Answer 16

• Use the operator IS NULL or IS NOT NULL
• Example: Region IS NULL

SQL Server 70-461 03-01

Question 17

When combining predicates in a WHERE clause, what order of precedence holds?

Answer 17

1. NOT
2. AND
3. OR

Parenthesis actually trump all of these so if you use parenthesis you can make sure your statement is evaluated the way you want it to be.

SQL Server 70-461 03-01

Question 18

What does it mean for a WHERE clause to evaluate to true?

Answer 18

The predicate expression is true and nulls are not included.

SQL Server 70-461 03-01

Question 19

What does it mean for a WHERE clause to evaluate to true?

Answer 19

The predicate expression is true and nulls are not included.

SQL Server 70-461 03-01

Question 20

How can you look for a character that is used as a wildcard?

Answer 20

• Decide on an escape character to use.
• One could use !. Put ! before the character that is considered a wildcard but that you want to search for. Then put ESCAPE ‘!’ after your wildcard expression so it knows you are using ! for your escape character.
• Example: col1 LIKE ‘!_%’ ESCAPE ‘!’ This looks for strings that start with an underscore.

SQL Server 70-461 03-01

Question 21

When a string starts with a known prefix, why should you use LIKE to search for it?

Answer 21

Because LIKE ‘ABC%’ is considered a search argument. Using LEFT(col, 3)=’ABC’ is not considered a search argument and therefore wouldn’t be as efficient.

SQL Server 70-461 03-01

Question 22

What three query clauses allow you to filter data based on predicates?

Answer 22

1. ON
2. HAVING
3. WHERE

SQL Server 70-461 03-01

Question 23

What are the types of wildcards to be used with LIKE?

Answer 23

• %
• _
• Character List
• Character Range
• NOT IN character list or range

SQL Server 70-461 03-01

Question 24

What would be an example of filtering for a particular date using a search argument? What would not be a search argument?

Answer 24

Search Argument
- orderdate>=’20070201’ AND orderdate<’20070301’

Not a Search Argument
- YEAR(orderdate)=2007 AND MONTH(orderdate)=2
- This is not a search argument because functions are used on the column being searched.

SQL Server 70-461 03-01

Question 25

What does a Search Argument look like?

Answer 25

**Search Argument** - - A predicate in the form of column operator value or value operator column. - **Examples:** Col1=10 or col>10 are two examples **Not a Search Argument** - If a column is manipulated, it most likely will not be a Search Argument. - **Examples:** are F(col1)=10, where F is some function, and Col1-1<=10. The last example could be changed to a search argument by making it col1<=10+1 ## Footnote SQL Server 70-461 03-01

Question 26

How are expressions in the WHERE clause evaluated?

Answer 26

- Conceptually, not taking into account precedence rules. They are evaluated at the same time. One *cannot* assume left to right evaluation. - **Example:** Where propertytype='INT' AND CAST(propertyval AS INT)>10. - One cannot assume that the left side of the AND will be evaluated before the right side. SQL Server will determine which one it should evaluate first. ## Footnote SQL Server 70-461 03-01

Question 27

When searching for a date range, why use (>=) and (<) instead of between?

Answer 27

- Because orderdate>='20070201' AND orderdate<'20070301' will work correctly with all date and time data types, whether the data type includes a time portion or not. - If you use between, you must specify the end date and time with the correct precision for the time or it could be rounded up, essesntially making the end date the day after what you wanted. ## Footnote SQL Server 70-461 03-01

Question 28

What does a WHERE clause return?

Answer 28

Only those rows for which the predicate is true. It discards both false and unknown. ## Footnote SQL Server 70-461 03-01

Question 29

What is the benefit of a LIKE statement starting with a known prefix? LIKE 'ABC%' for example?

Answer 29

SQL Server can potentially use an index on the filtererd column. If a LIKE statement starts with a wildcard, this is not the case. ## Footnote SQL Server 70-461 03-01

Question 30

What are the performance benefits of using the WHERE filter?

Answer 30

1. You reduce network traffic by filtering in the database server instead of in the client 2. You can potentially use indexes to avoid full scans of the tables ## Footnote SQL Server 70-461 03-01

Question 31

Why do Search Argumrents make a query more efficient?

Answer 31

When the predicate is in the form of a search argument, SQL Server can determine whether to use indexes to get the data more efficiently without having to scan the whole table. ## Footnote SQL Server 70-461 03-01

Question 32

What are three date formats you could use to filter by?

Answer 32

1. '20070213' 2. '2007-02-12' 3. '02/12/07' ## Footnote SQL Server 70-461 03-01

Question 33

Which date format is language neutral for all date/time data types?

Answer 33

'20070212' ## Footnote SQL Server 70-461 03-01

Question 34

Which date/time data format is language dependant for ALL data types?

Answer 34

'02/12/07' ## Footnote SQL Server 70-461 03-01

Question 35

Can you negate an IN predicate?

Answer 35

Yes. You can use NOT IN ## Footnote SQL Server 70-461 04-02a