Oracle Database Programming with SQL Section 1
1. Which of the following are true? (Choose Two) (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
♦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;
♦SELECT last_name, salary
FROM employees
WHERE salary < 3500;
♦LAST_NAME SALARY
King 5000
King 5000
♦LAST_NAME SALARY
Rajas 3500
Rajas 3500
♦LAST_NAME SALARY
Davies 3100
(*)
Davies 3100
(*)
♦All of the above
3. How can you write “not equal to” in the WHERE-clause?
♦!=
♦^=
♦<>
♦All of the above (*)
♦!=
♦^=
♦<>
♦All of the above (*)
4. Which query would give the following result?
LAST_NAME FIRST_NAME DEPARTMENT_ID
King Steven 90
LAST_NAME FIRST_NAME DEPARTMENT_ID
King Steven 90
♦SELECT last_name, first_name, department_id
FROM employees
WHERE last_name LIKE ‘k%’;
FROM employees
WHERE last_name LIKE ‘k%’;
♦SELECT last_name, first_name, department_id
FROM employees
WHERE last_name LIKE ‘KING’;
FROM employees
WHERE last_name LIKE ‘KING’;
♦SELECT last_name, first_name, department_id
FROM employees
WHERE last_name = ‘King’;
(*)
FROM employees
WHERE last_name = ‘King’;
(*)
♦SELECT last_name, first_name, department_id
FROM employees C
WHERE last_name = ‘KING’;
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?
♦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;
♦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?
♦WHERE
♦SELECT
♦NONE
♦FROM (*)
♦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?
♦WHERE (*)
♦FROM
♦SELECT
♦IS
♦WHERE (*)
♦FROM
♦SELECT
♦IS
8. The concatenation operator …
♦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 (*)
♦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?
♦LIKE
♦BETWEEN…AND… (*)
♦IN
♦IS NULL
♦LIKE
♦BETWEEN…AND… (*)
♦IN
♦IS NULL
10. Which comparison condition would you use to select rows that match a character pattern?
♦LIKE (*)
♦IN
♦ALMOST
♦SIMILAR
♦LIKE (*)
♦IN
♦ALMOST
♦SIMILAR
11. Which of the following WHERE clauses would not select the number 10?
♦WHERE hours <= 10
♦WHERE hours IN (8,9,10)
♦WHERE hours <>10 (*)
♦WHERE hours BETWEEN 10 AND 20
♦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?
♦True (*)
♦False
♦True (*)
♦False
13. Which of the following are examples of comparison operators used in the WHERE clause?
♦=, >, <, <=, >=, <>
♦between ___ and ___
♦in (..,..,.. )
♦like
♦is null
♦All of the above (*)
♦=, >, <, <=, >=, <>
♦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?
♦True
♦False (*)
♦True
♦False (*)
15. The EMPLOYEES table contains these columns:
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
EMAIL VARCHAR2(50)
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;
FROM employees;
Which WHERE clause should you use to complete this statement?
♦WHERE email != NULL;
♦WHERE email IS NOT NULL; (*)
♦WHERE email = NULL;
♦WHERE email IS NULL;
♦WHERE email IS NOT NULL; (*)
♦WHERE email = NULL;
♦WHERE email IS NULL;
16. 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?
♦BETWEEN..AND..
♦!=
♦=
♦IN (*)
♦BETWEEN..AND..
♦!=
♦=
♦IN (*)
17. 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?
♦IN
♦BETWEEN
♦AND
♦LIKE (*)
♦IN
♦BETWEEN
♦AND
♦LIKE (*)
18. Evaluate this SELECT statement:
SELECT *
FROM employees
WHERE department_id IN(10, 20, 30)
AND salary > 20000;
SELECT *
FROM employees
WHERE department_id IN(10, 20, 30)
AND salary > 20000;
Which values would cause the logical condition to return TRUE?
♦DEPARTMENT_ID = 20 and SALARY = 20000
♦DEPARTMENT_ID = 10 and SALARY = 20000
♦DEPARTMENT_ID = 10 and SALARY = 20001 (*)
♦DEPARTMENT_ID = null and SALARY = 20001
♦DEPARTMENT_ID = 10 and SALARY = 20000
♦DEPARTMENT_ID = 10 and SALARY = 20001 (*)
♦DEPARTMENT_ID = null and SALARY = 20001
19. How can you write “not equal to” in the WHERE-clause?
♦!=
♦^=
♦<>
♦All of the above (*)
♦!=
♦^=
♦<>
♦All of the above (*)
20. Which of the following are true? (Choose Two) (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
♦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
21. 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?
♦True
♦False (*)
♦True
♦False (*)
22. The EMPLOYEES table contains these columns:
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
EMAIL VARCHAR2(50)
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;
FROM employees;
Which WHERE clause should you use to complete this statement?
♦WHERE email IS NOT NULL; (*)
♦WHERE email IS NULL;
♦WHERE email = NULL;
♦WHERE email != NULL;
♦WHERE email IS NULL;
♦WHERE email = NULL;
♦WHERE email != NULL;
23. When using the “LIKE” operator, the % and _ symbols can be used to do a pattern-matching, wild card search. True or False?
♦True (*)
♦False
♦True (*)
♦False
24. Which of the following WHERE clauses would not select the number 10?
♦WHERE hours <= 10
♦WHERE hours <>10 (*)
♦WHERE hours BETWEEN 10 AND 20
♦WHERE hours IN (8,9,10)
♦WHERE hours <= 10
♦WHERE hours <>10 (*)
♦WHERE hours BETWEEN 10 AND 20
♦WHERE hours IN (8,9,10)
25. Which two statements would select salaries that are greater than or equal to 2500 and less than or equal to 3500? (Choose two) (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 (*)
♦WHERE salary BETWEEN 3500 AND 2500
♦WHERE salary BETWEEN 2500 AND 3500 (*)
♦WHERE salary <=2500 AND salary >= 3500
♦WHERE salary >= 2500 AND salary <= 3500 (*)
26. You need to display employees with salaries that are at least 30000 or higher. Which comparison operator should you use?
♦”=>”
♦!=
♦>
♦>= (*)
♦”=>”
♦!=
♦>
♦>= (*)
27. The concatenation operator …
♦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 (*)
♦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 (*)
28. You need to display employees whose salary is in the range of 30000 and 50000. Which comparison operator should you use?
♦BETWEEN…AND… (*)
♦IN
♦IS NULL
♦LIKE
♦BETWEEN…AND… (*)
♦IN
♦IS NULL
♦LIKE
29. 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?
♦DISTINCTROW
♦ONLY
♦UNIQUEONE
♦DISTINCT (*)
♦DISTINCTROW
♦ONLY
♦UNIQUEONE
♦DISTINCT (*)
30. 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?
♦WHERE department_id < 50
AND salary BETWEEN 10000 AND 25000
AND salary BETWEEN 10000 AND 25000
♦WHERE department_id = 50
AND salary BETWEEN 25001 AND 10001
AND salary BETWEEN 25001 AND 10001
♦WHERE department_id = 50
AND salary BETWEEN 10000 AND 25000
(*)
AND salary BETWEEN 10000 AND 25000
(*)
♦WHERE department_id > 50
AND salary BETWEEN 10000 AND 25000
31. Which of the following are true? (Choose Two) (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
♦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
32. Which of the following would be returned by this SELECT statement:
SELECT last_name, salary
FROM employees
WHERE salary < 3500;
SELECT last_name, salary
FROM employees
WHERE salary < 3500;
♦LAST_NAME SALARY
King 5000
King 5000
♦LAST_NAME SALARY
Rajas 3500
Rajas 3500
♦LAST_NAME SALARY
Davies 3100
(*)
Davies 3100
(*)
♦All of the above
33. How can you write “not equal to” in the WHERE-clause?
♦!=
♦^=
♦<>
♦All of the above (*)
♦!=
♦^=
♦<>
♦All of the above (*)
34. Which query would give the following result?
LAST_NAME FIRST_NAME DEPARTMENT_ID
King Steven 90
LAST_NAME FIRST_NAME DEPARTMENT_ID
King Steven 90
♦SELECT last_name, first_name, department_id
FROM employees
WHERE last_name LIKE ‘k%’;
FROM employees
WHERE last_name LIKE ‘k%’;
♦SELECT last_name, first_name, department_id
FROM employees
WHERE last_name LIKE ‘KING’;
FROM employees
WHERE last_name LIKE ‘KING’;
♦SELECT last_name, first_name, department_id
FROM employees
WHERE last_name = ‘King’;
(*)
FROM employees
WHERE last_name = ‘King’;
(*)
♦SELECT last_name, first_name, department_id
FROM employees C
WHERE last_name = ‘KING’;
FROM employees C
WHERE last_name = ‘KING’;
35. Which SELECT statement will display both unique and non-unique combinations of the MANAGER_ID and DEPARTMENT_ID values from the EMPLOYEES table?
♦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;
♦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;
36. Where in a SQL statement can you not use arithmetic operators?
♦WHERE
♦SELECT
♦NONE
♦FROM (*)
♦WHERE
♦SELECT
♦NONE
♦FROM (*)
37. Which clause would you include in a SELECT statement to restrict the data returned to only the employees in department 10?
♦WHERE (*)
♦FROM
♦SELECT
♦IS
♦WHERE (*)
♦FROM
♦SELECT
♦IS
38. The concatenation operator …
♦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 (*)
♦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 (*)
39. You need to display employees whose salary is in the range of 30000 and 50000. Which comparison operator should you use?
♦LIKE
♦BETWEEN…AND… (*)
♦IN
♦IS NULL
♦LIKE
♦BETWEEN…AND… (*)
♦IN
♦IS NULL
40. Which comparison condition would you use to select rows that match a character pattern?
♦LIKE (*)
♦IN
♦ALMOST
♦SIMILAR
♦LIKE (*)
♦IN
♦ALMOST
♦SIMILAR
41. Which of the following WHERE clauses would not select the number 10?
♦WHERE hours <= 10
♦WHERE hours IN (8,9,10)
♦WHERE hours <>10 (*)
♦WHERE hours BETWEEN 10 AND 20
♦WHERE hours IN (8,9,10)
♦WHERE hours <>10 (*)
♦WHERE hours BETWEEN 10 AND 20
42. When using the “LIKE” operator, the % and _ symbols can be used to do a pattern-matching, wild card search. True or False?
♦True (*)
♦False
♦True (*)
♦False
43. Which of the following are examples of comparison operators used in the WHERE clause?
♦=, >, <, <=, >=, <>
♦between ___ and ___
♦in (..,..,.. )
♦like
♦is null
♦All of the above (*)
♦=, >, <, <=, >=, <>
♦between ___ and ___
♦in (..,..,.. )
♦like
♦is null
♦All of the above (*)
44. 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?
♦True
♦False (*)
♦True
♦False (*)
45. The EMPLOYEES table contains these columns:
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
EMAIL VARCHAR2(50)
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;
FROM employees;
Which WHERE clause should you use to complete this statement?
♦WHERE email != NULL;
♦WHERE email IS NOT NULL; (*)
♦WHERE email = NULL;
♦WHERE email IS NULL;
♦WHERE email IS NOT NULL; (*)
♦WHERE email = NULL;
♦WHERE email IS NULL;
46. 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?
♦BETWEEN..AND..
♦!=
♦=
♦IN (*)
♦BETWEEN..AND..
♦!=
♦=
♦IN (*)
47. 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?
♦IN
♦BETWEEN
♦AND
♦LIKE (*)
♦IN
♦BETWEEN
♦AND
♦LIKE (*)
48. Evaluate this SELECT statement:
SELECT *
FROM employees
WHERE department_id IN(10, 20, 30)
AND salary > 20000;
SELECT *
FROM employees
WHERE department_id IN(10, 20, 30)
AND salary > 20000;
Which values would cause the logical condition to return TRUE?
♦DEPARTMENT_ID = 20 and SALARY = 20000
♦DEPARTMENT_ID = 10 and SALARY = 20000
♦DEPARTMENT_ID = 10 and SALARY = 20001 (*)
♦DEPARTMENT_ID = null and SALARY = 20001
♦DEPARTMENT_ID = 10 and SALARY = 20000
♦DEPARTMENT_ID = 10 and SALARY = 20001 (*)
♦DEPARTMENT_ID = null and SALARY = 20001
49. How can you write “not equal to” in the WHERE-clause?
♦!=
♦^=
♦<>
♦All of the above (*)
♦!=
♦^=
♦<>
♦All of the above (*)
50. Which of the following are true? (Choose Two) (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
♦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
51. 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?
♦True
♦False (*)
♦True
♦False (*)
52. The EMPLOYEES table contains these columns:
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
EMAIL VARCHAR2(50)
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;
FROM employees;
Which WHERE clause should you use to complete this statement?
♦WHERE email IS NOT NULL; (*)
♦WHERE email IS NULL;
♦WHERE email = NULL;
♦WHERE email != NULL;
♦WHERE email IS NULL;
♦WHERE email = NULL;
♦WHERE email != NULL;
53. When using the “LIKE” operator, the % and _ symbols can be used to do a pattern-matching, wild card search. True or False?
♦True (*)
♦False
♦True (*)
♦False
54. Which of the following WHERE clauses would not select the number 10?
♦WHERE hours <= 10
♦WHERE hours <>10 (*)
♦WHERE hours BETWEEN 10 AND 20
♦WHERE hours IN (8,9,10)
♦WHERE hours <= 10
♦WHERE hours <>10 (*)
♦WHERE hours BETWEEN 10 AND 20
♦WHERE hours IN (8,9,10)
55. Which two statements would select salaries that are greater than or equal to 2500 and less than or equal to 3500? (Choose two) (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 (*)
♦WHERE salary BETWEEN 3500 AND 2500
♦WHERE salary BETWEEN 2500 AND 3500 (*)
♦WHERE salary <=2500 AND salary >= 3500
♦WHERE salary >= 2500 AND salary <= 3500 (*)
56. You need to display employees with salaries that are at least 30000 or higher. Which comparison operator should you use?
♦”=>”
♦!=
♦>
♦>= (*)
♦”=>”
♦!=
♦>
♦>= (*)
57. The concatenation operator …
♦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 (*)
♦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 (*)
58. You need to display employees whose salary is in the range of 30000 and 50000. Which comparison operator should you use?
♦BETWEEN…AND… (*)
♦IN
♦IS NULL
♦LIKE
♦BETWEEN…AND… (*)
♦IN
♦IS NULL
♦LIKE
59. 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?
♦DISTINCTROW
♦ONLY
♦UNIQUEONE
♦DISTINCT (*)
♦DISTINCTROW
♦ONLY
♦UNIQUEONE
♦DISTINCT (*)
60. 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?
♦WHERE department_id < 50
AND salary BETWEEN 10000 AND 25000
♦WHERE department_id < 50
AND salary BETWEEN 10000 AND 25000
♦WHERE department_id = 50
AND salary BETWEEN 25001 AND 10001
AND salary BETWEEN 25001 AND 10001
♦WHERE department_id = 50
AND salary BETWEEN 10000 AND 25000
(*)
AND salary BETWEEN 10000 AND 25000
(*)
♦WHERE department_id > 50
AND salary BETWEEN 10000 AND 25000