Brainscape's Knowledge GenomeTM

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


See full index

RPT101i SQL I > Logical Expressions > Flashcards

Logical Expressions Flashcards

1
Q

How does the WHERE clause evaluate rows of data?

A

The WHERE clause evaluates each row of data separately.

4.5

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

What do logical expressions ensure?

A

That only needed data, not unnecessary data, is returned by the query.

(4.6)

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

The most common form of a logical expression is: value1 operator value2

What do the values represent?

A

The values can be constants or columns from the database.

4.6

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

What four rules must logical expressions follow?

A
  1. Are made up of comparisons
  2. Evaluate to TRUE, FALSE, or UNKNOWN
  3. Can be combined with other expressions
  4. Must evaluate to TRUE to satisfy a condition

(4.6)

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

What are logical expressions made up of (literally)?

A

One or more values and an operator.

4.6

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

True or False:

Date/time columns are stored as a number of units since a specific time far in the past.

A

True.

Therefore, as far as comparisons are concerned, date A is considered less than date B if date A occurred before date B.

(4.6)

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

What type of quote should enclose string and date constants?

A

Single quotes.

4.6

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

What are the 6 two-value comparisons listed in the training manual?

A
  1. value1 = value 2 (Equal)
  2. value 1 < > value 2 (Not equal)
  3. value 1 < value 2 (Less than)
  4. value1 <= value 2 (Less than or equal)
  5. value1 > value 2 (Greater than)
  6. value1 >= value (Greater than or equal)

(4.7)

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

What is Three-Value Logic?

A

SQL uses a logical system known as three-value logic: all logical expressions evaluate to TRUE, FALSE, or UNKNOWN.

(4.8)

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

When do statements evaluate to UNKNOWN?

A

When there is insufficient data to make the comparison (if the value is NULL), the statement evaluates to UNKNOWN.

(4.9)

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

Is NULL equal to anything? What does a NULL mean?

A

No, not even itself.

When NULL appears in a comparison, there isn’t enough data to compute the expression.

PATIENT.CITY = NULL
This expression is never TRUE and never FALSE; it is always UNKNOWN.

(4.9)

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

What operator should you use to find NULL values?

A

The IS operator is used to check if a value is NULL.

The expression value IS NULL will always evaluate to TRUE for NULL values and FALSE for populated values.

(4.9)

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

When working with logical expressions, what keyword can be added to reverse TRUE and FALSE statements?

A

NOT

4.10

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

How would you reverse IS NULL?

A

Use IS NOT NULL

4.10

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

What does the statement NOT FALSE evaluate to?

A

TRUE

4.10

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

What does the statement NOT UNKNOWN evaluate to?

A

UNKNOWN

4.10

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

How would the Complex Comparison look for the following description:
• Greater than or equal to value2 and less than or equal to value3

A

value1 BETWEEN value2 AND value3

4.8

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

How would the Complex Comparison look for the following description:
• Less than value2 or greater than value3

A

value1 NOT BETWEEN value2 AND value3

4.8

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

How would the Complex Comparison look for the following description:
• Equal to one of several values

A

value1 IN (value2, value3, … )

4.8

20
Q

How would the Complex Comparison look for the following description:
• Not equal to any of several values

A

value1 NOT IN (value2, value3, … )

4.8

21
Q

How would the Complex Comparison look for the following description:
• Matches a pattern. The pattern can include an underscore “_” meaning “any character” and a percent sign “%” meaning “any string of characters”

A

value LIKE pattern

4.8

22
Q

How would the Complex Comparison look for the following description:
• Does not match a pattern

A

value NOT LIKE pattern

4.8

23
Q

What does the CASE statement allow you to do?

A

The CASE statement allows you to use logical expressions to determine which values to return in your results.

(4.19)

24
Q

What does the CASE statement return?

A

The CASE statement will return the value associated with the first TRUE logical_expression.

(4.19)

25
Q

When the CASE statement is in the SELECT clause, does the resulting column have a column name?

A

No. By default the resulting column will not have a column name. Therefore, it is recommended to use column aliases to ensure that every column in the result has a name.

(4.19)

26
Q

Where in the CASE statement do you name the resulting column?

A

After the END keyword

4.19

27
Q

There are two formats for a CASE statement. What is the syntax for the format that involves choosing each value based on a separate logical expression?

A

CASE WHEN logical_expression1 THEN value1
WHEN logical_expression2 then value2
ELSE default_value END

SELECT PROV_ID,
IS_RESIDENT,
CASE WHEN IS_RESIDENT = ‘Y’ THEN ‘Yes’
WHEN IS_RESIDENT = ‘N’ THEN ‘No’
ELSE ‘Unknown’ END “Resident”
FROM CLARITY_SER

(4.19)

28
Q

There are two formats for a CASE statement. What is the syntax for the format that limits you to comparing values against only a single value? The implicit operator for the comparison is =.

A

CASE value0
WHEN value1a THEN value1b
WHEN value2a THEN value2b
ELSE default_value END

SELECT PROV_ID,
               IS_RESIDENT,
          CASE IS_RESIDENT
                     WHEN 'Y' THEN 'Yes'
                     WHEN 'N' THEN 'No'
          ELSE 'Unknown' END "Resident"
FROM CLARITY_SER

(4.20)

29
Q

What would you use if you wanted to know if a column was populated?

A

The non-null value check
(value IS NOT NULL)

(4.8)

30
Q

What does the WHERE clause do?

A

Filters rows from your results.

4.22

31
Q

How many logical expressions can the WHERE clause hold?

A

One

4.22

32
Q

What happens if an expression evaluates to FALSE or UNKNOWN?

A

The data will be excluded from your results.

4.22

33
Q

In SQL, can you use an equal sign (=) to find NULL values?

A

No, you should use the IS operator instead.

4.25

34
Q

Can you use column aliases in the WHERE clause? Why or why not?

A

No, because the WHERE clause is processed before the SELECT clause.

(4.26)

35
Q

Is it possible to combine logical expressions with logical operators (AND, OR and NOT) to make a more complex logical expression?

A

Yes.

A common mistake is to try to combine multiple values using logical operators, for example PATIENT.SEX_C = ‘1’ OR ‘2’. This is not correct. Logical operators can only be used to combine whole logical expressions. The correct syntax here would be PATIENT.SEX_C = ‘1’ OR PATIENT.SEX_C = ‘2’ .

(4.27)

36
Q

When should you use the AND operator?

A

When a query needs multiple filters to be respected simultaneously. Rows will only be returned from the query if the statements on both sides of AND evaluate to TRUE for that record.

/* A query that returns all males born before 1984 */
SELECT PAT_ID,
              PAT_NAME
FROM PATIENT
‐‐Patient is Male
WHERE SEX_C = '2'
‐‐Patient was born before 1984
AND BIRTH_DATE < '01 JAN 1984'

(4.27)

37
Q

When should a query use the OR operator?

A

When a query needs at least one filter among a set to be respected.

SELECT PatientKey,
              EncounterDateKey,
              MinutesInRoom
FROM VisitFact
‐‐Not yet open
WHERE Closed IS NULL
‐‐Open
OR Closed = 0

(4.28)

38
Q

How would you read NOT (A OR B) ?

A

( NOT A ) AND ( NOT B )

4.32

39
Q

How would you read NOT ( A AND B ) ?

A

( NOT A ) OR ( NOT B )

4.32

40
Q

What does “level of detail” mean when comparing dates?

A

Precision: is the data stored with a time?

How precise is the time? To the hour? Minute? Second? Fraction of a second? Regardless of the precision, whatever is missing is considered to be zero.

(4.32)

41
Q

What time is it if a date is stored without a time?

A

Midnight (12:00:00.000 AM)

4.32

42
Q

For consistency and simplicity, what is the best way of comparing a date/time column with a range?

A

”<= AND

43
Q

What does “level of detail” mean when comparing numbers?

A

Scale: how many digits are stores to the right of the decimal point? Regardless of the scale, whatever is missing is considered to be zero.

(4.33)

44
Q

Which of the following is less granular?

  1. PATIENT - one row per patient
  2. PatientDim - one row per patient per data range
A

Because the PATIENT table has one row per patient and the PatientDim table may have more than one row per patient, the PATIENT table is said to be less granular than the PatientDim table.

(4.35)

45
Q

How would you use the WHERE clause to reduce the granularity of a result?

A

Filter on a condition that matches the granularity of the desired query result.

Example:
• PatientDim.IsCurrent holds a 1 for the patient’s most recent date range
Because each patient has exactly one “most recent” date range, then filtering on IsCurrent =
1 will reduce the granularity of the query result to “patient”.

(4.35)

46
Q

What are the two things that a proper query result must have?

A
  1. Correct Columns
  2. Correct Granularity

There may be cases where you’ll need to change the granularity of a query result.

(4.35)

RPT101i SQL I (6 decks)

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