Oracle AcademyTest: Final Exam Semester 1 Section 16 - Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
 |
| oracle academy test |
Semester 1 Final Exam covers Sections 11-17 of Database Design.
Section 16
(Answer all questions in this section)
11. The PLAYERS table contains these columns:
PLAYER_ID NUMBER(9)
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2 (20)
TEAM_ID NUMBER (4)
MANAGER_ID NUMBER (9)
POSITION_ID NUMBER (4)
Which SELECT statement should you use if you want to display unique combinations of the TEAM_ID and MANAGER_ID columns?
Mark for Review
(1) Points
SELECT * FROM players;
SELECT team_id, manager_id FROM players;
SELECT DISTINCT team_id, manager_id FROM players; (*)
SELECT team_id, DISTINCT manager_id FROM players;
SELECT team_id, manager_id DISTINCT FROM players;
Incorrect. See Section 16 Lesson 2.
12. 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 = 10 and SALARY = 20000
DEPARTMENT_ID = 20 and SALARY = 20000
DEPARTMENT_ID = null and SALARY = 20001
DEPARTMENT_ID = 10 and SALARY = 20001 (*)
Incorrect. See Section 16 Lesson 2.
13. Which of the following commands will display the last name concatenated with the job ID from the employees table, separated by a comma and space, and label the resulting column "Employee and Title"? Mark for Review
(1) Points
SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM employees;
SELECT last_name||', '|| job_id "Employee and Title" FROM employees; (*)
SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM emp;
SELECT last_name||","|| job_id "Employee and Title" FROM employees;
Incorrect. See Section 16 Lesson 2.
14. What does the DISTINCT keyword do when it is used in a SELECT clause? Mark for Review
(1) Points
Hides NULL values
Eliminates all unique values and compares values
Eliminates duplicate rows in the result (*)
Eliminates only unique rows in the result
Incorrect. See Section 16 Lesson 1.
15. When using the LIKE condition, which symbol represents any sequence of none, one or more characters? Mark for Review
(1) Points
_
% (*)
#
&
Incorrect. See Section 16 Lesson 1.
16. Which of the following elements cannot be included in a WHERE clause? Mark for Review
(1) Points
A column alias (*)
A column name
A comparison condition
A constant
Correct.
17. Which comparison operator searches for a specified character pattern? Mark for Review
(1) Points
IN
LIKE (*)
BETWEEN...AND...
IS NULL
Incorrect. See Section 16 Lesson 1.
18. Where in a SQL statement can you not use arithmetic operators? Mark for Review
(1) Points
SELECT
FROM (*)
WHERE
NONE
Incorrect. See Section 16 Lesson 1.
19. Which comparison condition would you use to select rows that match a character pattern? Mark for Review
(1) Points
IN
LIKE (*)
ALMOST
SIMILAR
Incorrect. See Section 16 Lesson 1.
20. The Concatenation Operator does which of the following? Mark for Review
(1) Points
Links rows of data together inside the database.
Links two or more columns or literals to form a single output column (*)
Is represented by the asterisk (*) symbol
Separates columns.
Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 16 Quiz
(Answer all questions in this section)
1. Which of the following best describes the function of an index?
An index can run statement blocks when DML actions occur against a table.
An index can reduce the time required to grant multiple privileges to users.
An index can prevent users from viewing certain data in a table.
An index can increase the performance of SQL queries that search large tables. (*)
2. Unique indexes are automatically created on columns that have which two types of constraints?
UNIQUE and FOREIGN KEY
PRIMARY KEY and FOREIGN KEY
UNIQUE and PRIMARY KEY (*)
NOT NULL and UNIQUE
3. Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created?
A PRIMARY KEY constraint
A FOREIGN KEY constraint
An index (*)
A CHECK constraint
4. Indexes can be used to speed up queries. True or False?
True (*)
False
5. Which one of the following statements about indexes is true?
An index is created automatically when a PRIMARY KEY constraint is created. (*)
An index must be created by a database administrator when a PRIMARY KEY constraint is created.
An index cannot be created before a PRIMARY KEY constraint is created.
An index is never created for a unique constraint.
Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 16 Quiz
(Answer all questions in this section)
6. User Mary's schema contains an EMP table. Mary has Database Administrator privileges and executes the following statement:
CREATE PUBLIC SYNONYM emp FOR mary.emp;
User Susan now needs to SELECT from Mary's EMP table. Which of the following SQL statements can she use?
(Choose two)
(Choose all correct answers)
SELECT * FROM emp; (*)
SELECT * FROM emp.mary;
CREATE SYNONYM marys_emp FOR mary(emp);
SELECT * FROM mary.emp; (*)
7. The EMPLOYEES table contains these columns:
EMP_ID NOT NULL, Primary Key
SSNUM NOT NULL, Unique
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPT_ID NUMBER Foreign Key to DEPT_ID column of the DEPARTMENTS table
SALARY NUMBER(8,2)
You execute this statement:
CREATE INDEX emp_name_idx
ON employees(last_name, first_name);
Which statement is true?
The statement creates a function-based index.
The statement fails because of a syntax error.
The statement creates a composite unique index.
The statement creates a composite non-unique index. (*)
8. In order to be able to generate primary key values that are not likely to contain gaps, which phrase should be included in the sequence creation statement?
CACHE
MAXVALUE
NOCACHE (*)
9. Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE order_id_seq NOCYCLE NOCACHE; Which statement is true?
The sequence has no maximum value.
The sequence will start with 1. (*)
The sequence preallocates values and retains them in memory.
The sequence will continue to generate values after reaching its maximum value.
10. To see the most recent value that you fetched from a sequence named my_seq you should reference:
my_seq.(lastval)
my_seq.currval (*)
my_seq.nextval
my_seq.(currval)
Incorrect Incorrect. Refer to Section 16 Lesson 1.
Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 16 Quiz
(Answer all questions in this section)
11. Evaluate this statement:
SELECT po_itemid_seq.CURRVAL
FROM dual; What does this statement accomplish?
It displays the next available value of the PO_ITEM_ID_SEQ sequence.
It displays the current value of the PO_ITEM_ID_SEQ sequence. (*)
It resets the current value of the PO_ITEM_ID_SEQ sequence.
It sets the current value of the PO_ITEM_ID_SEQ sequence to the value of the PO_ITEMID column.
12. Why do gaps in sequences occur?
A rollback is executed
The system crashes
The sequence is used in another table
All of the above (*)
13. A sequence is a database object. True or False?
True (*)
False
14. Evaluate this statement:
CREATE SEQUENCE line_item_id_seq
MINVALUE 100 MAXVALUE 130 INCREMENT BY -10 CYCLE;
What will be the first five numbers generated by this sequence?
130120110100130
The CREATE SEQUENCE statement will fail because a START WITH value was not specified. (*)
The fifth number cannot be generated.
100110120130100
15. Examine the code for creating this sequence:
CREATE SEQUENCE track_id_seq
INCREMENT BY 10
START WITH 1000 MAXVALUE 10000
What are the first three values that would be generated by the sequence?
0, 1, 2
1100, 1200, 1300
1000, 1010, 1020 (*)
100010011002
