Sample Exam Questions

From the objective of OSS-DB Exam Silver
S3.2 Functions and operators (String operators)

Sample Question


Choose three appropriate descriptions of string handling in PostgreSQL.

  1. You can use || to concatenate strings. For example, 'abc' || 'xyz' becomes 'abcxyz'.
  2. You can use + to concatenate strings. For example, 'abc' + 'xyz' becomes 'abcxyz'.
  3. LIKE, which is used for string comparison, is case-insensitive. Therefore, 'ABC' LIKE 'abc' is true.
  4. In string comparison with LIKE, _ and % are wildcards. For example, 'abcde' LIKE 'a_c%' is true.
  5. SIMILAR TO, which is used for comparison with regular expressions, supports wildcards _ and %, unlike POSIX regular expressions. For example, 'abcde' SIMILAR TO 'a_c%' is true.

※This sample exam is different from those that appear in the actual OSS-DB Exam.

Answer and Explanation

String handling is one of the most important features in an RDBMS, so it's crucial to understand it well.
The standard SQL operator used to concatenate strings is ||, and PostgreSQL follows this convention. While many programming languages use + for string concatenation, this cannot be used for string concatenation in SQL as it implies arithmetic operations.

Partial matches in strings can be compared using the = operator by extracting a substring with functions like substring. A similar technique to how filenames are matched using wildcards like ? and * in an OS shell can also be used. It's important to note that when using SQL's LIKE, the wildcard _ matches any single character, and % matches any string.

Furthermore, both LIKE and = distinguish between uppercase and lowercase in string comparisons, so 'ABC' and 'abc' do not match. If you don't want to distinguish between them, you can use PostgreSQL's extension ILIKE, or use the upper or lower functions to convert to uppercase or lowercase before comparing.

LIKE only allows wildcards, but for more flexible string matching, PostgreSQL provides a comparison called SIMILAR TO. This incorporates POSIX regular expression features such as alternation with |, grouping with parentheses, and specifying the number of repetitions of the preceding item using *, +, ?, {}. However, it uses the same _ and % wildcards as standard SQL. Note that in POSIX regular expressions, any single character is represented by a period (.) and any string is represented by a period and an asterisk (.*).

Therefore, the correct answers are A, D, and E.