Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seql START WITH 100 INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE;
The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seql.nextval FROM dual;
What is displayed by the SELECT statement?
A. 1
B. 10
C. 100
D. an error
Answer: A
Q2. The first DROP operation is performed on PRODUCTS table using the following command:
DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command:
FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?
A. It recovers only thetablestructure.
B. It recovers thetablestructure,data,andtheindexes.
C. It recovers thetablestructure anddatabutnotthe related indexes.
D. It is not possible to recover the table structure, data, or the related indexes.
Answer: D
Q3. Which mandatory clause has to be added to the following statement to successfully create an external table called EMPDET?
CREATE TABLE empdet
(empno CHAR(2), ename CHAR(5), deptno NUMBER(4))
ORGANIZATION EXTERNAL
(LOCATION ('emp.daf));
A. TYPE
B. REJECTLIMIT
C. DEFAULT DIRECTORY
D. ACCESS PARAMETERS
Answer: C
Q4. Which statement correctly grants a system privilege?
A. GRANT EXECUTE ON prod TO PUBLIC;
B. GRANT CREATE VIEW ON tablel TO User1;
C. GRANT CREATE TABLE TO used,user2;
D. GRANT CREATE SESSION TO ALL;
Answer: C
Q5. Which statement correctly differentiates a system privilege from an object privilege?
A. System privileges can be granted only by the DBA whereas object privileges can be granted by DBAs or the owner of the object.
B. System privileges give the rights to only create user schemas whereas object privileges give rights to manipulate objects in a schema.
C. Users require system privileges to gain access to the database whereas they require object privileges to create objects in the database.
D. A system privilege is the right to perform specific activities in a database whereas an object privilege is a right to perform activities on a specific object in the database.
Answer: D
Q6. Evaluate the following SQL statements that are issued in the given order:
CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
enameVARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_FK?
A. It wouldbeautomatically enabled and deferred.
B. It wouldbeautomaticallyenabledand immediate.
C. Itwouldremaindisabled and has to beenabled manuallyusingthe ALTER TABLEcommand.
D. Itwould remain disabled and can be enabled only by droppingtheforeign key constraint andre-creating it.
Answer: ABD
Q7. View the Exhibit and examine the structure of the PRODUCT INFORMATION table.
Which two queries would work? (Choose two.)
A. SELECT product_name
FROM product_information
WHERE list_price = (SELECT AVG(list_price)
FROM product_information);
B. SELECT product_status
FROM product_information
GROUP BY product_status
WHERE list_price < (SELECT AVG(list_price)
FROM product_information);
C. SELECT product_status
FROM product_information
GROUP BY product_status
HAVING list_price > (SELECT AVG(list_price)
FROM product_information);
D. SELECT product_name FROM product_jnformation WHERE list_price < ANY(SELECT AVG(list_price) FROM product_jnformation GROUP BY product_status);
Answer: AD
Q8. View the Exhibit and examine the description of the EMPLOYEES table.
You want to calculate the total remuneration for each employee. Total remuneration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission.
Which SOL statement would you execute to get the desired output?
A. SELECTfirst_name, salary, salary*12+salary*commission_pct "Total"
FROM EMPLOYEES;
B. SELECTfirst_name,salary, salary*12+NVL((salary*commission_pct), 0) "Total"
FROMEMPLOYEES;
C. SELECTfirst_name, salary, salary*12 + NVL(salary, O)*commission_pct "Total"
FROM EMPLOYEES;
D. SELECTfirst_name, salary, salary*12+(salary*NVL2(commission_pct,
salary,salary+commission_pct))"Total"
FROM EMPLOYEES;
Answer: B
Q9. Which statement is true regarding the ROLLUP operator specified in the GROUP BY clause of a SQL statement?
A. It produces only the subtotals for the groups specified in the GROUP BY clause.
B. It produces only the grand totals for the groups specified in the GROUP BY clause.
C. It produces higher-level subtotals, moving from right to left through the list of grouping columns specified in the GROUP BY clause.
D. It produces higher-level subtotals, moving in all the directions through the list of grouping columns specified in the GROUP BY clause.
Answer: C
Q10. Which two statements are true? (Choose two.)
A. The USER_SYNONYMSviewcan provide information about private synonyms.
B. The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.
C. All the dynamic performance views prefixed with V$ are accessible to all the database users.
D. The USER_OBJECTS view can provide information about the tables and views created by the user only.
E. DICTIONARY is a view thatcontains thenamesof allthe datadictionary views that theuser can access.
Answer: AE
Q11. The following are the steps for a correlated subquery, listed in random order: 1) The WHERE clause of the outer query is evaluated.
2) The candidate row is fetched from the table specified in the outer query.
3) The procedure is repeated for the subsequent rows of the table, till all the rows are processed.
4) Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query. Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery.
A. 4,2,1,3
B. 4,1,2,3
C. 2,4,1,3
D. 2,1,4,3
Answer: C
Q12. Which two statements are true regarding operators used with subqueries? (Choose two.
A. The NOT IN operator is equivalent to IS NULL
B. The <ANY operator means less than the maximum.
C. =ANY and =ALL operators have the same functionality.
D. The IN operator cannot be used in single-row subqueries.
E. TheNOT operator can be used with IN, ANY and ALL operators.
Answer: BE
Q13. View the Exhibit and examine the details of the PRODUCT_INFORMATION table.
Evaluate the following SQL statement:
SELECT TO_CHAR(list_price ,'$9,999") FROM product_information; Which two statements would be true regarding the output for this SQL statement? (Choose two.)
A. TheLIST_PRICEcolumn having value1123.90wouldbedisplayed as $1,124.
B. The LIST_PRICEcolumn having value 1123.90 would be displayed as$1,123.
C. The LIST_PRICE columnhavingvalue 11235.90would bedisplayed as $1,123.
D. The LIST_PRICE column having value 11235.90wouldbe displayed as #######.
Answer: AD
Q14. View the Exhibit and examine the description of the EMPLOYEES table.
You want to display the EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYEE_ID 104. To accomplish the task, you execute the following SQL statement:
SELECT employee_id, first_name, department_id
FROM employees
WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees WHERE employee_id = 104)
AND employee_id <> 104;
When you execute the statement it does not produce the desired output. What is the reason for this?
A. The WHERE clause condition in the main query is using the = comparison operator, instead of EXISTS.
B. The WHERE clause condition in the main query is using the = comparison operator, instead of the IN operator.
C. The WHERE clause condition in the main query is using the = comparison operator, instead of the = ANY operator.
D. The columns in the WHERE clause condition of the main query and the columns selected in the subquery should be in the same order.
Answer: D
Q15. View the Exhibit and examine the details of the EMPLOYEES table.
Evaluate the following SQL statements:
Statement 1:
SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH
employee_id = 101
CONNECT BY PRIOR employee_id = manager_id AND manager_id != 108;
Statement 2:
SELECT employee_id, last_name, job_id, manager_id
FROM employees
WHERE manager_id != 108
START WITH employee_id = 101
CONNECT BY PRIOR employee_id = manager_id;
Which two statements are true regarding the above SQL statements? (Choose two.)
A. Statement 2 would not execute because theWHEREclause condition is not allowed in a statementthathas the START WITH clause.
B. Theoutput forstatement1 would displaytheemployee withMANAGERJD108 and all the employeesbelowhim or herin thehierarchy.
C. The output of statement 1 wouldneitherdisplay the employeewithMANAGERJD 108 nor any employee below him or herinthe hierarchy.
D. The output for statement 2 would not displaytheemployee with MANAGERJD 108 but it would display all the employees belowhimor her in the hierarchy.
Answer: CD