Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. View the Exhibit and examine DEPARTMENTS and the LOCATIONS tables.
Evaluate the following SOL statement:
SELECT location_id, city
FROM locations
I WHERE NOT EXISTS (SELECT location_id
FROM departments
WHERE location_id <> I. location_id);
This statement was written to display LOCATIONJD and CITY where there are no departments located. Which statement is true regarding the execution and output of the command?
A. The statement would execute and would return the desired results.
B. The statement would not execute because the = comparison operator is missing in the WHERE clause of the outer query.
C. The statement would execute but it will return zero rows because the WHERE clause in the inner query should have the = operator instead of <>.
D. The statement would not execute because the WHERE clause in the outer query is missing the column name for comparison with the inner query result.
Answer: C
Q2. Given below is the list of meta character syntaxes and their descriptions in random order: Meta character syntax Description 1) ^a) Matches character not in the list
2) [^...] b) Matches character when it occurs at the beginning of a line 3) | c) Treats the subsequent meta character as a literal 4) \ d) Matches one of the characters such as the OR operator Identify the option that correctly matches the meta character syntaxes with their descriptions.
A. 1-b,2-a,3-d.4-c
B. 1-a, 2-b,3-d.4-c
C. 1-d, 2-b,3-a.4-c
D. 1-b,2-c,3-d,2-a
Answer: A
Q3. Which statements are true regarding the usage of the WITH clause in complex correlated subqueries? (Choose all that apply.)
A. It can be used only with the SELECT clause.
B. The WITH clause can hold more than one query.
C. If the query block name and the table name were the same, then the table name would take precedence.
D. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block.
Answer: ABD
Q4. Which statement is true regarding synonyms?
A. Synonyms can be created for tables but not views.
B. Synonyms are used to reference only those tables that are owned by another user.
C. A public synonym and a private synonym can exist with the same name for the same table.
D. The DROP SYNONYM statement removes the synonym, and the status of the table on which the synonym has been created becomes invalid.
Answer: C
Q5. Which view would you use to display the column names and DEFAULT values for a table?
A. DBA_TABLES
B. DBA_COLUMNS
C. USER_COLUMNS
D. USER TAB COLUMNS
Answer: D
Q6. View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables.
The query should display the employee IDs of all the employees who have held the job SA_MAN at any time during their tenure.
Choose the correct SET operator to fill in the blank space and complete the following query.
SELECT employee_id
FROM employees
WHERE job_id = 'SA_MAN'
SELECT employee_id
FROMjob_history
WHERE job_id='SA_MAN'
A. UNION
B. MINUS
C. INTERSECT
D. UNIONALL
Answer: AD
Q7. SCOTT is a user in the database.
Evaluate the commands issued by the DBA:
1 - CREATE ROLE mgr;
2 - GRANT CREATE TABLE, SELECT
ON oe. orders
TO mgr;
3 - GRANT mgr, create table TO SCOTT;
Which statement is true regarding the execution of the above commands?
A. Statement 1 would not execute because the WITH GRANT option is missing.
B. Statement 1 would not execute because the IDENTIFIED BY <password> clause is missing.
C. Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement.
D. Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command.
Answer: D
Q8. Which statement is true regarding the CUBE operator in the GROUP BY clause of a SQL statement?
A. It produces only aggregates for the groups specified in the GROUP BY clause.
B. It finds all the NULL values in the superaggregates for the groups specified in the GROUP BY clause.
C. It produces 2 n possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause.
D. It produces n+1 possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause.
Answer: C
Q9. 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
Q10. The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following
GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What correction needs to be done to the above statement?
A. PUBLICshould bereplacedwithspecific usernames.
B. ALL should be replaced with a list of specific privileges.
C. WITH GRANTOPTIONshould beaddedto the statement.
D. Separate GRANT statements are required for ORDERSandORDER_ITEMS tables.
Answer: D
Q11. 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
Q12. View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables.
In the ORDERS table, ORDER_ID is the PRIMARY KEY and ORDER_DATE has the DEFAULT value as SYSDATE.
Evaluate the following statement:
UPDATE orders
SET order_date=DEFAULT
WHERE order_id IN (SELECT order_id FROM order_items
WHERE qty IS NULL);
What would be the outcome of the above statement?
A. The UPDATEstatementwould not work because the main queryandthe subquery usedifferenttables.
B. The UPDATEstatement would not work becausetheDEFAULTvaluecan be used only in INSERT statements.
C. TheUPDATEstatementwould changeall ORDER_DATE values to SYSDATE provided the current ORDER_DATE is NOT NULLand QTYis NULL
D. The UPDATE statement would change all the ORDER_DATE values to SYSDATE irrespective of what the current ORDER_DATE value is for all orders where QTY is NULL
Answer: D
Q13. Which statement is true regarding synonyms?
A. Synonyms can be created for tables but not views.
B. Synonyms are used to reference only those tables that are owned by another user.
C. A public synonym and a private synonym can exist with the same name for the same table.
D. The DROP SYNONYM statement removes the synonym, and the status of the table on which the synonym has been created becomes invalid.
Answer: C
Q14. Which statement is true regarding the CUBE operator in the GROUP BY clause of a SQL statement?
A. It produces only aggregates for the groups specified in the GROUP BY clause.
B. It finds all the NULL values in the superaggregates for the groups specified in the GROUP BY clause.
C. It produces 2 n possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause.
D. It produces n+1 possible superaggregatecombinations, if the n columns and expressions are specified in the GROUP BY clause.
Answer: C
Q15. View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Order Amount"
FROM order_items oi JOIN orders o
ON oi.order_id = o.order_id
GROUP BY CUBE (o.customer_id, oi.product_id);
Which three statements are true regarding the output of this SQL statement? (Choose three.)
A. t wouldreturn thesubtotals for theOrder Amount of every CUSTOMER_ID.
B. twould returnthesubtotals fortheOrderAmountfor every PRODUCT_ID.
C. twould return the subtotals fortheOrder Amount of every PRODUCT_IDandCUSTOMER_ID asonegroup.
D. t would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.
E. t wouldreturnonly thegrandtotal for theOrderAmount ofeveryCUSTOMER_ID and PRODUCT_ID as onegroup.
Answer: ABD