Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. 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
Q2. View the Exhibit and examine the details of the EMPLOYEES table.
You want to generate a hierarchical report for all the employees who report to the employee whose EMPLOYEE_ID is 100.
Which SQL clauses would you require to accomplish the task? (Choose all that apply.)
A. WHERE
B. HAVING
C. GROUP BY
D. START WITH
E. CONNECT BY
Answer: ADE
Q3. Which statement correctly grants a system privilege?
A. GRANT EXECUTE ON prod TO PUBLIC;
B. GRANT CREATE VIEW ON tablel TO used;
C. GRANT CREATE TABLE TO used ,user2;
D. GRANT CREATE SESSION TO ALL;
Answer: C
Q4. Which three statements indicate the end of a transaction? (Choose three.)
A. after a COMMIT is issued
B. aftera ROLLBACK is issued
C. afteraSAVEPOINTis issued
D. afteraSELECT statement is issued
E. aftera CREATE statementis issued
Answer: ABE
Q5. View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.
Which DELETE statement would execute successfully?
A. DELETEorder_idFROMorders WHEREorder_total< 1000;
B. DELETEordersWHERE order_total < 1000;
C. DELETE FROM orders WHERE (SELECTorder_id FROM order_items);
D. DELETE orders o, order_itemsi WHEREo.order id = i.order id;
Answer: B
Q6. View the Exhibit and examine the data in the PRODUCTS table.
Which statement would add a column called PRICE, which cannot contain NULL?
A. ALTER TABLE products
ADD price NUMBER(8,2) NOT NULL;
B. ALTER TABLE products
ADD price NUMBER(8,2) DEFAULT NOT NULL;
C. ALTER TABLE products
ADD price NUMBER(8,2) DEFAULT 0 NOT NULL;
D. ALTER TABLE products
ADD price NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;
Answer: C
Q7. Which statement is true regarding the SESSION_PRIVS dictionary view?
A. It contains the current object privileges available in the user session.
B. It contains the current system privileges available in the user session.
C. It contains the object privileges granted to other users by the current user session.
D. It contains the system privileges granted to other users by the current user session.
Answer: B
Q8. View the Exhibit and examine the structure of the ORDERS table.
The ORDER_ID column is the PRIMARY KEY in the ORDERS table. Evaluate the following
CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)
AS
SELECT order_id.order_date.customer_id
FROM orders;
Which statement is true regarding the above command?
A. The NEW_ORDERS table would not get created because the DEFAULT value can not be specified in the column definition.
B. The NEW_ORDERS table would get created and only the NOT NULL constraint definedon the specified columns would bepassed to the new table.
C. The NEW_ORDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
D. The NEW_ORDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.
Answer: B
Q9. Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)
A. You can use column alias in the GROUP BY clause.
B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause.
Answer: DE
Q10. Which two statements best describe the benefits of using the WITH clause? (Choose two.)
A. It enables users to store the results of a query permanently.
B. It enables users to store the query block permanently in the memory and use it to create complex queries.
C. It enables users to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
D. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the user's temporary tablespace.
Answer: CD
Q11. Which two statements are true regarding roles? (Choose two.)
A. A role can be granted to itself.
B. A role can be granted to PUBLIC.
C. A user can be granted only one role at any point of time.
D. The REVOKE command can be used to remove privileges but not roles from other users.
E. Roles are named groups of related privileges that can be granted to users or other roles.
Answer: B,E
Q12. Which CREATE TABLE statement is valid?
A. CREATE TABLE ord_details
(ord_no NUMBER(2) PRIMARY KEY,
item_no NUMBER(3)PRIMARY KEY,
ord_date date NOT NULL);
B. CREATE TABLE ord_details
(ord_no NUMBER(2) UNIQUE, NOT NULL,
item_no NUMBER(3),
ord_date date DEFAULT SYSDATE NOT NULL);
C. CREATE TABLE ord_details
(ord_no NUMBER(2) ,
item_no NUMBER(3),
ord_date date DEFAULT NOT NULL,
CONSTRAINT ord_uq UNIQUE (ord_no),
CONSTRAINT ord_pk PRIMARY KEY (ord_no));
D. CREATE TABLE ord_details
(ord_no NUMBER(2),
item_no NUMBER(3),
ord_date date DEFAULT SYSDATE NOT NULL,
CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));
Answer: D
Q13. 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 with MANAGER_ID108 and
all the employeesbelowhim or herin thehierarchy.
C. The output of statement 1 wouldneither display the employee with MANAGER_ID 108 nor any
employee below him or herinthe hierarchy.
D. The output for statement 2 would not display theemployee with MANAGER_ID 108 but it
would display all the employees belowhimor her in the hierarchy.
Answer: CD
Q14. View the Exhibit and examine the description of the EMPLOYEES table.
You executed the following SQL statement:
SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, first_name, salary desc;
Which two statements are true regarding the output of the above query? (Choose two.)
A. The valuesinall the columns wouldbe sortedin the descending order.
B. Thevalues in theSALARYcolumn wouldbesorted indescendingorder for all the employees havingthesame valueinthe DEPARTMENT_ID column.
C. The values intheFIRST_NAME column would be sortedinascending order for alltheemployees having the same valueinthe DEPARTMENT_ID column.
D. Thevalues in the FIRST_NAME column would be sorted in the descendingorder forall the employees having the same valueinthe DEPARTMENT_ID column.
E. Thevalues in the SALARY column wouldbe sortedin descending order foralltheemployeeshaving thesame value intheDEPARTMENT_IDandFIRST_NAME column.
Answer: CE
Q15. Which two statements are true about the GROUPING function? (Choose two.)
A. Itis used to find the groups forming the subtotal in a row.
B. It is used to identify the NULL value in the aggregate functions.
C. It is used to form the group sets involved in generating the totals and subtotals.
D. It can only be used with ROLLUP and CUBE operators specified in the GROUP BY clause.
Answer: A,D