Q1. 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
Q2. View the Exhibit and examine the structure of ORDER_ITEMS and ORDERS tables.
You need to remove from the ORDER_ITEMS table those rows that have an order status of 0 or 1 in the ORDERS table.
Which DELETE statements are valid? (Choose all that apply.)
A. DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE order_status in (0,1));
B. DELETE * FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE order_status IN (0,1));
C. DELETE FROM order_items i WHERE order_id = (SELECT order_id FROM orders o WHERE i. order_id = o. order_id AND order_status IN (0,1));
D. DELETE FROM (SELECT* FROM order_items i.orders o WHERE i.order_id = o.order_id AND order_status IN (0,1));
Answer: ACD
Q3. 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
Q4. Which statements are correct regarding indexes? (Choose all that apply.)
A. When a table is dropped, the corresponding indexes are automatically dropped.
B. For each DML operation performed, the corresponding indexes are automatically updated.
C. Indexes should be created on columns that are frequently referenced as part of an expression.
D. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
Answer: ABD
Q5. Evaluate the following DELETE statement:
DELETE FROM orders; There are no other uncommitted transactions on the ORDERS table. Which statement is true about the DELETE statement?
A. Itremovesallthe rows in thetable andallows ROLLBACK
B. It would not removetherows if thetablehasaprimary key.
C. It removesallthe rows as well as the structure of the table.
D. Itremoves all the rows inthetable and doesnotallow ROLLBACK
Answer: A
Q6. You need to create a table for a banking application with the following considerations:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and subtracted with 3) date type data without using the conversion functions.
4) The maximum period of the credit provision in the application is 30 days.
5) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?
A. INTERVAL YEAR TOMONTH
B. INTERVALDAYTO SECOND
C. TIMESTAMP WITHTIMEZONE
D. TIMESTAMP WITH LOCAL TIME ZONE
Answer: B
Q7. View the Exhibit and examine the description of the EMPLOYEES table.
Your company wants to give 5% bonus to all the employees on their annual salary. The SALARY column stores the monthly salary for an employee. To check the total for annual salary and bonus amount for each employee, you issued the following SQL statement:
SELECTfirst_name, salary, salary*!2+salary*12*.05 "ANNUAL SALARY + BONUS" FROM employees;
Which statement is true regarding the above query?
A. It would execute and give you the desired output.
B. It would not execute because the AS keyword is missing between the column name and the alias.
C. It would not execute because double quotation marks are used instead of single quotation marks for assigning alias for the third column.
D. It would execute but the result for the third column would be inaccurate because the parentheses for overriding the precedence of the operator are missing.
Answer: A
Q8. Which three statements are true regarding group functions? (Choose three.)
A. They can be used on columns or expressions.
B. They can be passed as an argument to another group function.
C. They can be used only with a SQL statement that has the GROUP BY clause.
D. They can be used on only one column in the SELECT clause of a SQL statement.
E. They can be used along with the single-row function in the SELECT clause of a SQL statement.
Answer: ABE
Q9. 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: AD
Q10. View the Exhibit and examine the structure of the EMPLOYEES table.
You want to know the FIRST_NAME and SALARY for all employees who have the same manager as that of the employee with the first name 'Neena' and have salary equal to or greater than that of'Neena'.
Which SQL statement would give you the desired result?
A. SELECTfirst_name, salary FROM employees WHERE (manager_id, salary) >= ALL (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' AND first_name <> 'Neena'
B. SELECT first_name, salary FROM employees WHERE (manager_id, salary) >= (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena') AND first_name <> 'Neena'
C. SELECT first_name, salary FROM employees WHERE (manager_id, salary) >= ANY (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' AND first_name <> 'Neena'
D. SELECT first_name, salaryFROM employees WHERE (manager_id = (SELECT manager_id FROM employees WHERE first_name = 'Neena') AND salary >= (SELECT salary FROM employees WHERE first_name = 'Neena')) AND first name <> 'Neena'
Answer: D