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. Given below is a list of functions and the tasks performed by using these functions, in random order.
Function Usage
1) LPAD a) Used to truncate a column, expression, or value to n decimal places 2) TRUNC b) Used to remove heading or trailing or both characters from the character string 3) DECODE c) Pads the character value right-justified to a total width of n character positions 4) TRIM d) Used to return the numeric value for position of a named character from the character string 5) INSTR e) Used to translate an expression after comparing it with each search value
Which option correctly matches the function names with their usage?
A. 1-c,2-b,3-e,4-a,5-d
B. 1-e,2-b,3-c,4-a,5-d
C. 1-e,2-a,3-c,4-d,5-b
D. 1-c,2-a,3-e,4-b,5-d
Answer: D
Q3. Which two statements are true about sequences created in a single instance database? (Choose two.)
A. The numbers generated by a sequence can be used only for one table.
B. DELETE <sequencename> would remove a sequence from the database.
C. CURRVAL is used to refer to the last sequence number that has been generated.
D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.
E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted.
Answer: CD
Q4. View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the composite PRIMARY KEY is (ORD_NO, ITEM_NO).
Which two CREATE INDEX statements are valid? (Choose two.)
A. CREATE INDEX ord_idx ON ord(ord_no);
B. CREATE INDEX ord_idx ON ord_items(ord_no);
C. CREATE INDEX ord_idx ON ord_items(item_no);
D. CREATE INDEX ord_idx ON ord,ord_items(ord_no, ord_date,qty);
Answer: BC
Q5. Evaluate the following SQL statements in the given order:
DROP TABLE dept;
CREATE TABLE dept
(deptno NUMBER(3) PRIMARY KEY,
deptname VARCHAR2(10));
DROP TABLE dept;
FLASHBACK TABLE dept TO BEFORE DROP;
Which statement is true regarding the above FLASHBACK operation?
A. It recovers only the firstDEPTtable.
B. Itrecovers onlythesecondDEPTtable.
C. It does not recoveranyof the tables becauseFLASHBACKis not possible in this case.
D. Itrecovers both the tables but the names would be changed to the ones assigned intheRECYCLEBIN.
Answer: B
Q6. 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_IDRDERS table would not get created because the DEFAULT value can not be specified in the column definition.
B. The NEW_IDRDERS table would get created and only the NOTNULL constraint defined on the specified columns would be passed to the new table.
C. The NEW_IDRDERS table would not get created because the column names in the CREATE TABLE commandand the SELECT clause do not match.
D. The NEW_IDRDERS 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
Q7. View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the composite PRIMARY KEY is (ORD_NO, ITEM_NO).
Which two CREATE INDEX statements are valid? (Choose two.)
A. CREATE INDEX ord_idx
ON ord(ord_no);
B. CREATE INDEX ord_idx
ON ord_items(ord_no);
C. CREATE INDEX ord_idx
ON ord_items(item_no);
D. CREATEINDEX ord_idx
ON ord,ord_items(ord_no, ord_date,qty);
Answer: BC
Q8. 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
Q9. View the Exhibit and examine the data in the EMPLOYEES tables.
Evaluate the following SQL statement: SELECT employee_id, department_id FROM employees WHERE department_id= 50 ORDER BY department_id UNION SELECT employee_id, department_id FROM employees WHERE department_id= 90 UNION SELECT employee_id, department_id FROM employees WHERE department_id= 10; What would be the outcome of the above SQL statement?
A. The statementwouldexecute successfullyanddisplay all the rows intheascending order of DEPARTMENT_ID.
B. Thestatement would execute successfullybutitwillignoretheORDERBYclause and display the rows in random order.
C. The statementwouldnot execute because the positional notationinstead of thecolumn name shouldbeusedwiththe ORDER BY clause.
D. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.
Answer: D
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