Which of the following comparison operators means Between these two values
|
The presence of NULL in a SELECT 3 NOT BETWEEN 2 AND 6;0 or SELECT 3 NOT BETWEEN 2 AND 6;2 statement will result in the statement evaluating to NULL: Show
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null The SELECT 3 NOT BETWEEN 2 AND 6;0 and SELECT 3 NOT BETWEEN 2 AND 6;2 operators can also be used to evaluate string arguments: SELECT 'Paul' BETWEEN 'John' AND 'Ringo'; -- true Note that the value, min, and max parameters to SELECT 3 NOT BETWEEN 2 AND 6;0 and SELECT 3 NOT BETWEEN 2 AND 6;2 must be the same type. For example, Presto will produce an error if you ask it if John is between 2.3 and 35.2. IS NULL and IS NOT NULLThe SELECT 3 NOT BETWEEN 2 AND 6;9 and SELECT 3 < 2 OR 3 > 6;0 operators test whether a value is null (undefined). Both operators work for all data types. Using SELECT 3 < 2 OR 3 > 6;1 with SELECT 3 NOT BETWEEN 2 AND 6;9 evaluates to true: select NULL IS NULL; -- true But any other constant does not: SELECT 3.0 IS NULL; -- false IS DISTINCT FROM and IS NOT DISTINCT FROMIn SQL a SELECT 3 < 2 OR 3 > 6;1 value signifies an unknown value, so any comparison involving a SELECT 3 < 2 OR 3 > 6;1 will produce SELECT 3 < 2 OR 3 > 6;1. The SELECT 3 < 2 OR 3 > 6;6 and SELECT 3 < 2 OR 3 > 6;7 operators treat SELECT 3 < 2 OR 3 > 6;1 as a known value and both operators guarantee either a true or false outcome even in the presence of SELECT 3 < 2 OR 3 > 6;1 input: SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true In the example shown above, a SELECT 3 < 2 OR 3 > 6;1 value is not considered distinct from SELECT 3 < 2 OR 3 > 6;1. When you are comparing values which may include SELECT 3 < 2 OR 3 > 6;1 use these operators to guarantee either a SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null3 or SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null4 result. The following truth table demonstrate the handling of SELECT 3 < 2 OR 3 > 6;1 in SELECT 3 < 2 OR 3 > 6;6 and SELECT 3 < 2 OR 3 > 6;7: a b a = b a <> b a DISTINCT b a NOT DISTINCT b SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null8 SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null8 SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null3 SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null4 SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null4 SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null3 SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null8 SELECT 'Paul' BETWEEN 'John' AND 'Ringo'; -- true5 SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null4 SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null3 SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null3 SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null4 SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null8 SELECT 3 < 2 OR 3 > 6;1 SELECT 3 < 2 OR 3 > 6;1 SELECT 3 < 2 OR 3 > 6;1 SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null3 SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null4 SELECT 3 < 2 OR 3 > 6;1 SELECT 3 < 2 OR 3 > 6;1 SELECT 3 < 2 OR 3 > 6;1 SELECT 3 < 2 OR 3 > 6;1 SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null4 SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null3 GREATEST and LEASTThese functions are not in the SQL standard, but are a common extension. Like most other functions in Presto, they return null if any argument is null. Note that in some other databases, such as PostgreSQL, they only return null if all arguments are null. The following types are supported: SELECT 3.0 IS NULL; -- false2, SELECT 3.0 IS NULL; -- false3, SELECT 3.0 IS NULL; -- false4, SELECT 3.0 IS NULL; -- false5, SELECT 3.0 IS NULL; -- false6, SELECT 3.0 IS NULL; -- false7greatest(value1, value2, ..., valueN) → [same as input] Returns the largest of the provided values. least(value1, value2, ..., valueN) → [same as input]Returns the smallest of the provided values. Quantified Comparison Predicates: ALL, ANY and SOMEThe SELECT 3.0 IS NULL; -- false8, SELECT 3.0 IS NULL; -- false9 and SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true0 quantifiers can be used together with comparison operators in the following way: expression operator quantifier ( subquery ) For example: SELECT 3 >= 2 AND 3 <= 6;0 Here are the meanings of some quantifier and comparison operator combinations: Expression Meaning SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true1 Evaluates to SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true2 when SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true3 is equal to all values. SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true4 Evaluates to SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true2 when SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true3 doesn’t match any value. SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true7 Evaluates to SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true2 when SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true3 is smaller than the smallest value. expression operator quantifier ( subquery )0 Evaluates to SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true2 when SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true3 is equal to any of the values. This form is equivalent to expression operator quantifier ( subquery )3. expression operator quantifier ( subquery )4 Evaluates to SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true2 when SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true3 doesn’t match one or more values. expression operator quantifier ( subquery )7 Evaluates to SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true2 when SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true3 is smaller than the biggest value. SELECT 3.0 IS NULL; -- false9 and SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true0 have the same meaning and can be used interchangeably. LIKEThe LIKE operator is used to match a specified character pattern in a string. Patterns can contain regular characters as well as wildcards. Wildcard characters can be escaped using the single character specified for the ESCAPE parameter. Matching is case sensitive. Which of the following functions counts the number of records that have a value in the field?Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A1:A20: =COUNT(A1:A20).
Which of the following gets its value from a calculation instead of a value that is entered into the field quizlet?A calculated field is a field that gets its value from a calculation instead of a value that is entered into the field.
Which of the following represents the correct order of operations?The order is PEMDAS: Parentheses, Exponents, Multiplication, and Division (from left to right), Addition and Subtraction (from left to right).
Which of the following wildcard characters matches any single character in a specific position?Examples of wildcard characters. |
