 |
| Part 5 |
Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER
SALARY NUMBER
What is the correct syntax for an inline view?
A. SELECT a.last_name, a.salary, a.department_id, b.maxsal
FROM employees a,
(SELECT department_id, max(salary)maxsal FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id AND a.salary < b.maxsal;
B. SELECT a.last name, a.salary, a.department_id FROM employees a
WHERE a.department_id IN (SELECT department_id FROM employees b
GROUP BY department_id having salary = (SELECT max(salary) from employees))
C. SELECT a.last_name, a.salary, a.department_id FROM employees a
WHERE a.salary = (SELECT max(salary) FROM employees b
WHERE a.department _ id = b.department _ id);
D. SELECT a.last_name, a.salary, a.department_id FROM employees a
WHERE (a.department_id, a.salary) IN (SELECT department_id, a.salary) IN (SELECT department_id max(salary) FROM employees b
GROUP BY department_id ORDER BY department _ id);
Answer: A
Explanation:
This SQL statement shows correct syntax to build inline views. You must enclose the query text for the inline view in parentheses and also give a label for the inline view so that columns in it can be referenced later. In answer A inline view is marked as B.
Incorrect Answers
B: This SQL statement will fail because it is not correct syntax for inline views. There is no a label for this inline view also.
C: This SQL statement will fail because it is not correct syntax for inline views. There is no a label for this inline view also.
D: This SQL statement will fail because it is not correct syntax for inline views. There is no a label for this inline view also.
QUESTION 2:
Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater then 5000?
A. ORDER BY SALARY > 5000
B. GROUP BY SALARY > 5000
C. HAVING SALARY > 5000
D. WHERE SALARY > 5000
Answer: D
Explanation:
You need to use the WHERE clause to limit the display to those employees whose salary is greater then 5000. Incorrect Answers
A: The ORDER BY clause will just sort data, but it will not limit them.
B: You cannot use the GROUP BY clause with conditions. Oracle error will be generated.
C: The HAVING clause may be used only in conjunction with the GROUP BY clause. OCP
QUESTION 3:
When should you create a role? (Choose two)
A. To simplify the process of creating new users using the CREATE USER xxx IDENTIFIED by yyy statement.
B. To grant a group of related privileges to a user.
C. When the number of people using the database is very high.
D. To simplify the process of granting and revoking privileges.
E. To simplify profile maintenance for a user who is constantly traveling.
Answer: C, D
Explanation:
You should use roles to grant a group of privileges to a user. You grant the appropriate privileges to the role and after that grant this role to specific users. By granting to or revoking privileges from the role you can simplify procedure of users privileges maintainance: you don't need to grant/revoke privileges to/from each user. It's especially very helpful when you are experiencing a high number of people using the database. Incorrect Answers
A: Roles have nothing to do with simplifying the process of creating new users.
B: You can use roles to grant a group of privileges to a user, but they can be not related at all.
E: Roles have nothing to do with user who is constantly traveling.
QUESTION 4:
Which three statements about subqueries are true? (Choose three)
A. A single row subquery can retrieve only one column and one row.
B. A single row subquery can retrieve only one row but many columns.
C. A multiple row subquery can retrieve multiple rows and multiple columns.
D. A multiple row subquery can be compared by using the ">" operator.
E. A single row subquery can use the IN operator.
F. A multiple row subquery can use the "=" operator.
Answer: B, C, D
Explanation:
A single row sub-query can retrieve only one row but many columns. A multiple row sub-query can retrieve one row or multiple rows and multiple columns. A multiple row sub-query can be compared by using the ">" operator.
Incorrect Answers
A: A single row sub-query can retrieve only one row, but many columns.. E: A single row sub-query cannot use the IN operator.
F: A multiple row sub-query cannot use the "=" operator.
QUESTION 5:
Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER NOT NULL EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) DEFAULT 'SA_REP' SAL NUMBER
COMM_PCT NUMBER MGR_ID NUMBER DEPARTMENT_ID NUMBER
You need to update the records of employees 103 and 115. The UPDATE statement you specify should update the rows with the values specified below:
JOB_ID: Default value specified for this column definition. SAL: Maximum salary earned for the job ID SA_REP.
COMM_PCT: Default value specified for this commission percentage column, if any. If no default value is specified for the column,
the value should be NULL. DEPARTMENT_ID: Supplied by the user during run time through substitution variable. Which UPDATE statement meets the requirements?
A. UPDATE employees SET job_id = DEFAULT
AND Sal = (SELECT MAX(sal) FROM employees
WHERE job_id = 'SA_REP') AND comm_pct = DEFAULT AND department_id = &did
WHERE employee _id IN (103,115);
B. UPDATE employees
SET job_id = DEFAULT AND Sal = MAX(sal)
AND comm_pct = DEFAULT OR NULL AND department_id = &did
WHERE employee_id IN (103,115) AND job _ id = 'SA_ REP';
C. UPDATE employees SET job_id = DEFAULT, Sal = (SELECT MAX(sal) FROM employees
WHERE job_id = 'SA_REP'), comm_pct = DEFAULT, department_id = &did
WHERE employee_id IN (103,115);
D. UPDATE employees SET job_id = DEFAULT, Sal = MAX(sal), comm_pct = DEFAULT, department_id = &did
WHERE employee_id IN (103,115) AND job _ id = 'SA_ REP';
E. UPDATE employees
SET job_id = DEFAULT, Sal = (SELECT MAX(sal) FROM employees
WHERE job_id = 'SA_REP') comm_pct = DEFAULT OR NULL, department_id = &did
WHERE employee_id IN (103,115);
Answer: C
Explanation:
This UPDATE statement is correct to receive desired results. Correct syntax is UPDATE table_name SET column_name1 = value, column_name2 = value2.
You can also use the DEFAULT keyword to set a column value to its specified default value in update statements as well.
Incorrect Answers
A: You cannot use syntax like UPDATE table_name SET column_name1 = value AND column_name2 = value2.
Correct syntax is UPDATE table_name SET column_name1 = value, column_name2 = value2.
B: You cannot use syntax like UPDATE table_name SET column_name1 = value AND column_name2 = value2.
Correct syntax is UPDATE table_name SET column_name1 = value, column_name2 = value2.
D: Group function is not allowed to use in the SET clause of the UPDATE command.
E: You cannot set column to value DEFAULT OR NULL:
QUESTION 6:
Which two statements about sequences are true? (Choose two)
A. You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value.
B. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.
C. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence.
D. You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column.
E. If a sequence starting from a value 100 and incremented by 1 is used by more then one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence.
F. You use REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.
Answer: B, C
Explanation:
You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence.
Incorrect Answers
A: You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence.
D: You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.
E: This statement is not correct. There is no limitation like that in Oracle.
F: You use CYCLE clause, not REUSE, when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.
QUESTION 7:
Which four are correct guidelines for naming database tables? (Choose four)
A. Must begin with either a number or a letter.
B. Must be 1-30 characters long.
C. Should not be an Oracle Server reserved word.
D. Must contain only A-Z, a-z, 0-+, _, *, and #.
E. Must contain only A-Z, a-z, 0-9, _, $, and #.
F. Must begin with a letter.
Answer: B, C, E, F
Explanation:
Oracle database object must begin with a letter and can usually be between 1 and 30 characters long, except for databases (which have a maximum of eight characters) and database links (with a maximum of 128 characters). Name cannot be an Oracle Server reserved word. Name must contain only A-Z, a-z, 0-9, _, $, and #.
Incorrect Answers
A: Database tables may not begin with number. D: It cannot contain symbols "+" or "*".
QUESTION 78:
Examine the structure of the EMPLOYEES table: Column name Data type Remarks
EMPLOYEE_ID NUMBER NOT NULL, Primary Key LAST_NAME VARCNAR2(30)
FIRST_NAME VARCNAR2(30) JOB_ID NUMBER
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER
You need to create an index called NAME_IDX on the first name and last name fields of the EMPLOYEES table. Which SQL statement would you use to perform this task?
A. CREATE INDEX NAME _IDX (first_name, last_name);
B. CREATE INDEX NAME _IDX (first_name, AND last_name)
C. CREATE INDEX NAME_IDX
ON (First_name, last_name);
D. CREATE INDEX NAME_IDX
ON employees (First_name, AND last_name);
E. CREATE INDEX NAME_IDX
ON employees (First_name, last_name);
F. CREATE INDEX NAME_IDX
FOR employees (First_name, last_name);
Answer: E
Explanation:
Answer E provides correct syntax to create index: CREATE INDEX index_name ON table_name(list of columns).
Incorrect Answers
A: You need to use keyword ON also to create index.
B: You cannot use keyword AND to build a list of columns for index. Also this statement is missing the table name on which the index is creating.
C: This statement is missing the table name on which the index is creating.
D: You cannot use keyword AND to build a list of columns for index.
F: You cannot use keyword FOR to create an index.
QUESTION 9:
Which operator can be used with a multiple-row subquery?
A. =
B. LIKE
C. BETWEEN
D. NOT IN
E. IS
F. <>
Answer: D
Explanation:
Only NOT IN operator can be used with a multi-row sub-query. All others may be used with single-row sub-query only.
Incorrect Answers
A: When sub-queries are linked to the parent by equality comparisons, the parent query expects only one row of data from the sub-query,
so "=" operator cannot be used with a multiple-row sub-query.
B: Operator LIKE will work only with single-row sub-queries.
C: Operator BETWEEN will not work with a multi-row sub-queries.
E: Operator LIKE is used only for single-row sub-queries.
F: When sub-queries are linked to the parent by equality comparisons, the parent query expects only one row of data from the sub-query,
so "<>" operator cannot be used with a multiple-row sub-query.
