Formulas and Functions
The string operator (&) can be used in formulas to concatenate, or join, two or more strings or the contents of referenced cells. Wildcards (*,?,~) can be used in conditions to represent one or more characters.
Concatenate strings or the contents of cells
The & character is used to concatenate, or join, two or more strings or the contents of referenced cells.
Some examples of the use of the concatenation operator are:
"Abc"&"Def" returns "AbcDef".
"Abc"&A1 returns "Abc2" if cell A1 contains 2.
A1&A2 returns "12" if cell A1 contains 1 and cell A2 contains 2.
B2&", "&E2 returns "Last, First" if B2 contains "Last" and E2 contains "First".
Use a wildcard to match any single character
The ? character is used to match a single character in an expression that allows conditions.
Some examples of the use of the wildcard character ? in matching patterns are:
-
"Ea?" matches any string beginning with "Ea" and containing exactly one additional character, such as "Ea2" or "Eac".
"Th??" matches any string beginning with "Th" and containing exactly two additional characters, such as "Then" and "That".
COUNTIF(B2:E7,"?ip") returns a count of the number of cells in the range B2:E7 that contain a value that starts with a character followed by "ip", such as "rip" and "tip". It doesn’t match "drip" or "trip".
Use a wildcard to match any number of characters
The * character is used to match any number of characters, including none, in an expression that allows conditions.
Some examples of the use of the wildcard character * in matching patterns are:
"*ed" matches a string of any length ending with "ed", such as "Ted" or "Treed".
COUNTIF(B2:E7,"*it") returns a count of the number of cells in the range B2:E7 that contain a value that ends with "it" such as "bit" and "mit". It does not match "mitt".
Match a wildcard character
The ~ character is used to specify that the following character should be matched rather than used as a wildcard, in an expression that allows conditions.
Some examples of using the character ~ in matching patterns are:
"~?" matches the question mark, instead of using the question mark to match any single character.
COUNTIF(E,"~*") returns a count of the number of cells in column E that contain the asterisk character.
SEARCH("~?",B2) returns 19 if cell B2 contains "That is a question? Yes it is!" since the question mark is the 19th character in the string.
Use multiple wildcard characters in a condition
The wildcard characters (? * ~) can be used together in expressions that allow conditions. Some examples are:
"*a?" matches any expression that contains the character "a" followed by any other single character, such as "That", "Cap", and "Irregular".
COUNTIF(B2:E7,"*on?") returns a count of the number of cells in the range B2:E7 that contain a value that starts with any number of characters (including none) followed by "on" and then a single character. This matches words such as "alone", "bone", "one," and "none". This doesn’t match "only" (which has two characters after the "on") or "eon" (which has no characters after the "on").
Please don’t include any personal information in your comment.
Maximum character limit is 250.
Thanks for your feedback.
Section 2 Quiz Oracle Database Programming with SQL
1. Which of the following are true? (Choose Two)
Mark for Review
(1) Points
(Choose all correct answers)
Character values are not case-sensitive
Date values are format-sensitive (*)
Date values are enclosed in single quotation marks (*)
Character strings are enclosed in double quotation marks
2. Which of the following would be returned by this SELECT statement:
SELECT last_name, salary
FROM employees
WHERE salary < 3500;
Mark for Review
(1) Points
LAST_NAME SALARY
King 5000
LAST_NAME SALARY
Rajas 3500
LAST_NAME SALARY
Davies 3100
(*)
All of the above
3. How can you write "not equal to" in the WHERE-clause? Mark for Review
(1) Points
!=
^=
<>
All of the above (*)
4. Which query would give the following result?
LAST_NAME FIRST_NAME
DEPARTMENT_ID
King Steven 90
Mark for Review
(1) Points
SELECT last_name, first_name, department_id
FROM employees
WHERE last_name LIKE 'k%';
SELECT last_name, first_name, department_id
FROM employees
WHERE last_name LIKE 'KING';
SELECT last_name, first_name, department_id
FROM employees
WHERE last_name = 'King';
(*)
SELECT last_name, first_name, department_id
FROM employees C
WHERE last_name = 'KING';
5. Which SELECT statement will display both unique and non-unique combinations of the MANAGER_ID and DEPARTMENT_ID values from the EMPLOYEES table? Mark for Review
(1) Points
SELECT DISTINCT
manager_id, department_id FROM employees;
SELECT manager_id, DISTINCT department_id FROM employees;
SELECT manager_id, department_id FROM employees; (*)
SELECT manager_id, department_id DISTINCT FROM employees;
6. Where in a SQL statement can you not use arithmetic operators? Mark for Review
(1)
Points
WHERE
SELECT
NONE
FROM (*)
7. Which clause would you include in a SELECT statement to restrict the data returned to only the employees in department 10? Mark for Review
(1) Points
WHERE (*)
FROM
SELECT
IS
8. The concatenation operator ... Mark for Review
(1) Points
Brings together columns or character strings into other columns
Creates a resultant column that is a character expression
Is
represented by two vertical bars ( || )
All of the above (*)
9. You need to display employees whose salary is in the range of 30000 and 50000. Which comparison operator should you use? Mark for Review
(1) Points
LIKE
BETWEEN...AND... (*)
IN
IS NULL
10. Which comparison condition would you use to select rows that match a character pattern? Mark for Review
(1) Points
LIKE (*)
IN
ALMOST
SIMILAR
11. Which of the following WHERE clauses would
not select the number 10? Mark for Review
(1) Points
WHERE hours <= 10
WHERE hours IN (8,9,10)
WHERE hours <>10 (*)
WHERE hours BETWEEN 10 AND 20
12. When using the "LIKE" operator, the % and _ symbols can be used to do a pattern-matching, wild card search.
True or False? Mark for Review
(1) Points
True (*)
False
13. Which of the following are examples of comparison operators used in the WHERE clause? Mark for Review
(1) Points
=, >, <, <=, >=, <>
between ___ and ___
in (..,..,.. )
like
is null
All of the above (*)
14. If you write queries using the BETWEEN operator, it does not matter in what order you enter the values, i.e. BETWEEN low value AND high value will give the same result as BETWEEN high value and low value. True or False? Mark for Review
(1) Points
True
False (*)
15. The EMPLOYEES table contains these columns:
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
EMAIL VARCHAR2(50)
You are writing a SELECT statement to retrieve the names of employees that have an email address.
SELECT last_name||', '||first_name "Employee Name"
FROM employees;
Which WHERE clause should you use to complete this statement?
Mark for Review
(1) Points
WHERE email != NULL;
WHERE email IS NOT NULL; (*)
WHERE email = NULL;
WHERE email IS NULL;
1. You need write a SELECT statement that should only return rows that contain 34, 46, or 48 for the DEPARTMENT_ID column. Which
operator should you use in the WHERE clause to compare the DEPARTMENT_ID column to this specific list of values? Mark for Review
(1) Points
BETWEEN..AND..
!=
=
IN (*)
2. You want to retrieve a list of customers whose last names begin with the letters 'Fr' .
Which keyword should you include in the WHERE clause of your SELECT statement to achieve the desired result? Mark for Review
(1) Points
IN
BETWEEN
AND
LIKE (*)
3. Evaluate this SELECT statement:
SELECT *
FROM employees
WHERE department_id IN(10, 20, 30)
AND salary > 20000;
Which values would cause the logical condition to return TRUE?
Mark for Review
(1) Points
DEPARTMENT_ID = 20 and SALARY = 20000
DEPARTMENT_ID = 10 and SALARY = 20000
DEPARTMENT_ID = 10 and SALARY = 20001 (*)
DEPARTMENT_ID = null and SALARY = 20001
4.
How can you write "not equal to" in the WHERE-clause? Mark for Review
(1) Points
!=
^=
<>
All of the above (*)
5. Which of the following are true? (Choose Two) Mark for Review
(1) Points
(Choose all correct answers)
Date values are enclosed in single quotation marks (*)
Character strings are enclosed in double quotation marks
Date values are format-sensitive (*)
Character values are not case-sensitive
6. If you write queries using the BETWEEN operator, it does not matter in what order you enter the values, i.e. BETWEEN low value AND high value will give the same result as BETWEEN high value and low value. True or False? Mark for Review
(1) Points
True
False (*)
7. The EMPLOYEES table
contains these columns:
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
EMAIL VARCHAR2(50)
You are writing a SELECT statement to retrieve the names of employees that have an email address.
SELECT last_name||', '||first_name "Employee Name"
FROM employees;
Which WHERE clause should you use to complete this statement?
Mark for Review
(1) Points
WHERE email IS NOT NULL; (*)
WHERE email IS NULL;
WHERE email = NULL;
WHERE email != NULL;
8. When using the "LIKE" operator, the % and _ symbols can be used to do a pattern-matching, wild card search. True or False? Mark for Review
(1) Points
True (*)
False
9.
Which of the following WHERE clauses would not select the number 10? Mark for Review
(1) Points
WHERE hours <= 10
WHERE hours <>10 (*)
WHERE hours BETWEEN 10 AND 20
WHERE hours IN (8,9,10)
10. Which two statements would select salaries that
are greater than or equal to 2500 and less than or equal to 3500? (Choose two) Mark for Review
(1) Points
(Choose all correct answers)
WHERE salary BETWEEN 3500 AND 2500
WHERE salary BETWEEN 2500 AND 3500 (*)
WHERE salary <=2500 AND salary >= 3500
WHERE salary >= 2500 AND salary <= 3500 (*)
11. You need to display employees with salaries that are at least 30000 or higher. Which comparison operator should you use? Mark for Review
(1) Points
"=>"
!=
>
>= (*)
12. The concatenation operator ... Mark for Review
(1) Points
Brings together columns or character strings into other columns
Creates a resultant column that is a character expression
Is represented by two vertical bars ( || )
All of the above (*)
13. You need to display employees whose salary is in the range of 30000 and 50000. Which comparison operator should you use? Mark for Review
(1) Points
BETWEEN...AND... (*)
IN
IS NULL
LIKE
14.
You need to display only unique combinations of the LAST_NAME and MANAGER_ID columns in the EMPLOYEES table. Which keyword should you include in the SELECT clause? Mark for Review
(1) Points
DISTINCTROW
ONLY
UNIQUEONE
DISTINCT (*)
15. You need to display
employees whose salary is in the range of 10000 through 25000 for employees in department 50 . What does the WHERE clause look like? Mark for Review
(1) Points
WHERE department_id < 50
AND salary BETWEEN 10000 AND 25000
WHERE department_id = 50
AND salary BETWEEN 25001 AND 10001
WHERE department_id = 50
AND salary
BETWEEN 10000 AND 25000
(*)
WHERE department_id > 50
AND salary BETWEEN 10000 AND 25000