Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Interview Prep - SQL > Filtering Data > Flashcards

Filtering Data Flashcards

1
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

How does the NOT operator treat nulls?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is the proper form of a predicate using LIKE?

A

column LIKE pattern

SQL Server 70-461 03-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

How can you combine predicates in the WHERE clause?

A
  • AND
  • OR

SQL Server 70-461 03-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Explain Three Valued Logic

A

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

SQL Server 70-461 03-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Explain Two-Valued Logic

A

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

SQL Server 70-461 03-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

How does the % wildcard work?

A
  • 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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

How does the _ wildcard work?

A
  • 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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How does the character list wildcard work?

A
  • 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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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

A

A search argument

SQL Server 70-461 03-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A
  • 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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

The predicate evaluates to false and nulls are not included.

SQL Server 70-461 03-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How does the character range wildcard work?

A
  • 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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is a predicate?

A

An expression that is either true or false.

SQL Server 70-461 03-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

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

SQL Server 70-461 03-01

17
Q

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

A
  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

18
Q

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

A

The predicate expression is true and nulls are not included.

SQL Server 70-461 03-01

19
Q

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

A

The predicate expression is true and nulls are not included.

SQL Server 70-461 03-01

20
Q

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

A
  • 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

21
Q

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

A

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

22
Q

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

A
  1. ON
  2. HAVING
  3. WHERE

SQL Server 70-461 03-01

23
Q

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

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

SQL Server 70-461 03-01

24
Q

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

A

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

25
Q

What does a Search Argument look like?

A

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

SQL Server 70-461 03-01

26
Q

How are expressions in the WHERE clause evaluated?

A
  • 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.

SQL Server 70-461 03-01

27
Q

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

A
  • 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.

SQL Server 70-461 03-01

28
Q

What does a WHERE clause return?

A

Only those rows for which the predicate is true. It discards both false and unknown.

SQL Server 70-461 03-01

29
Q

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

A

SQL Server can potentially use an index on the filtererd column. If a LIKE statement starts with a wildcard, this is not the case.

SQL Server 70-461 03-01

30
Q

What are the performance benefits of using the WHERE filter?

A
  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

SQL Server 70-461 03-01

31
Q

Why do Search Argumrents make a query more efficient?

A

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.

SQL Server 70-461 03-01

32
Q

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

A
  1. ‘20070213’
  2. ‘2007-02-12’
  3. ‘02/12/07’

SQL Server 70-461 03-01

33
Q

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

A

‘20070212’

SQL Server 70-461 03-01

34
Q

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

A

‘02/12/07’

SQL Server 70-461 03-01

35
Q

Can you negate an IN predicate?

A

Yes. You can use NOT IN

SQL Server 70-461 04-02a

Interview Prep - SQL (26 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions