Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. View the Exhibit and examine the data in EMP and DEPT tables.
In the DEPT table, DEPTNO is the PRIMARY KEY.
In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing
the DEPTNO column in the DEPT table.
What would be the outcome of the following statements executed in the given sequence?
DROP TABLE emp;
FLASHBACK TABLE emp TO BEFORE DROP;
INSERT INTO emp VALUES (2.COTT 10);
INSERT INTO emp VALUES (3,ING 55);
A. Both the INSERT statements would fail because all constraints are automatically retrieved when the table is flashed back.
B. Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back.
C. Only the first INSERT statement would succeed because all the constraints except the primary key constraint are automatically retrieved after a table is flashed back.
D. Only the second INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.
Answer: D
Q2. 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_id
FROMorders
WHEREorder_total< 1000;
B. DELETEorders
WHERE 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
Q3. View the Exhibit and examine the data in the DEPARTMENTS tables.
Evaluate the following SQL statement:
SELECT department_id "DEPT_ID", department_name , 'b' FROM departments WHERE department__id=90 UNION SELECT department_id, department_name DEPT_NAME, 'a' FROM departments WHERE department_id=10
Which two ORDER BY clauses can be used to sort the output of the above statement? (Choose two.)
A. ORDERBY 3;
B. ORDER BY'b'
C. ORDER BYDEPT_ID;
D. ORDER BY DEPTNAME;
Answer: AC
Q4. View the Exhibit and examine the structure of the ORDERS table.
You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those orders that were placed after the last order placed by the customer whose CUSTOMER_ID is 101
Which query would give you the desired output?
A. SELECT order_id, order_date FROM orders
WHERE order_date > ALL (SELECT MAX(order_date)
FROM orders) AND
Customer_id = 101;
B. SELECT order_id, order_date FROM orders
WHERE order_date > ANY (SELECT order_date
FROM orders
WHERE customer_id = 101);
C. SELECT order_id, order_date FROM orders
WHERE order_date > ALL (SELECT order_date
FROM orders
WHERE customer_id = 101);
D. SELECT order_id, order_date FROM orders
WHERE order_date IN (SELECT order_date
FROM orders
WHERE customer id = 101);
Answer: C
Q5. 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
Q6. View the Exhibit and examine the structure of the ORDERS table:
The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the NOT NULL constraint.
Evaluate the following statement:
INSERT INTO (SELECT order_id.order_date.customer_id
FROM ORDERS
WHERE order_total = 1000
WITH CHECK OPTION)
VALUES (13, SYSDATE, 101);
What would be the outcome of the above INSERT statement?
A. It would execute successfully and the new row would be inserted into a new temporary table created by the subquery.
B. It would execute successfully and the ORDER_TOTAL column would have the value 1000 inserted automatically in the new row.
C. It would not execute successfully because the ORDER_TOTAL column is not specified in the SELECT list and no value is provided for it.
D. It would not execute successfully because all the columns from the ORDERS table should have been included in the SELECT list and values should have been provided for all the columns.
Answer: C
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. View the Exhibit and examine the description of the EMPLOYEES and DEPARTMENTS tables.
You want to display the LAST_NAME for the employees, LAST_NAME for the manager of the employees, and the DEPARTMENT_NAME for the employees having 100 as MANAGER_ID. The following SQL statement was written:
SELECT m.last_name "Manager", e.last_name "Employee", department_name "Department"
FROM employees m JOIN employees e
ON (m.employee_id = e.manager_id)
WHERE e.manager_id=100
JOIN departments d
ON (e.department_id = d.department_id);
Which statement is true regarding the output of this SQL statement?
A. The statement would provide the desired results.
B. The statement would not execute because the ON clause is written twice.
C. The statement would not execute because the WHERE clause is wrongly placed.
D. The statement would not execute because the self join uses the ON clause instead of the USING clause.
Answer: C
Q9. 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
Q10. View the Exhibit and examine the structure of the ORDERS table.
You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those orders that were placed after the last order placed by the customer whose CUSTOMER_ID is 101
Which query would give you the desired output?
A. SELECT order id, order_date FROM orders
WHERE order_date > ALL (SELECT MAX(order_date)
FROM orders)AND
Customer_id = 101;
B. SELECT order id, order_date FROM orders
WHERE order_date > ANY (SELECT order_date
FROM orders
WHERE customer_id = 101);
C. SELECT order _id, order_date FROM orders WHERE order_date > ALL (SELECT order_date FROM orders WHERE customer_id = 101);
D. SELECT order id, order_date FROM orders WHERE order_date IN (SELECT order_date FROM orders WHERE customer id = 101);
Answer: C
Q11. Which three statements are true regarding single-row functions? (Choose three.)
A. They can accept only one argument.
B. They can be nested up to only two levels.
C. They can return multiple values of more than one data type.
D. They can be used in SELECT, WHERE, and ORDER BY clauses.
E. They can modify the data type of the argument that is referenced.
F. They can accept a column name, expression, variable name, or a user-supplied constant as arguments.
Answer: DEF
Q12. 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
Q13. Which statements are true regarding the hierarchical query in Oracle Database 10g? (Choose all that apply.)
A. It is possible to retrieve data only in top-down hierarchy.
B. Itis possible to retrieve data in top-down or bottom-up hierarchy.
C. It is possible to remove an entire branch from the output of the hierarchical query.
D. You cannot specify conditions when you retrieve data by using a hierarchical query.
Answer: BC
Q14. View the Exhibit and examine the structure of the ORDERS table.
NEW_IDRDERS is a new table with the columns ORD_ID, ORD_DATE, CUST_ID, and ORD_TOTAL that have the same data types and size as the corresponding columns in the ORDERS table.
Evaluate the following INSERT statement:
INSERT INTO new_orders (ord_id, ord_date, cust_id, ord_total) VALUES
(SELECT order_id.order_date.customer_id.order_total FROM orders WHERE order_date > ‘31-dec-1999’);
Why would the INSERT statement fail?
A. because column names in NEWORDERS and ORDERS tables do not match
B. because the VALUES clause cannot be used in an INSERT with a subquery
C. because the WHERE clause cannot be used in a subquery embedded in an INSERT statement
D. because the total number of columns in the NEW ORDERS table does not match the total number of columns in the ORDERS table
Answer: B
Q15. View the Exhibit and examine the descriptions of the DEPT and LOCATIONS tables.
You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.
Which SOL statement would you execute to accomplish the task?
A. UPDATE deptd
SETcity=ANY(SELECT city
FROMlocations I);
B. UPDATE deptd
SET city=(SELECT city
FROM locations I)
WHERE d.location_id = l.location_id;
C. UPDATE dept d
SETcity =(SELECTcity
FROM locations I
WHEREd.location_id = l.location_id);
D. UPDATE deptd SET city =ALL(SELECT city FROM locations I WHEREd.location_id = l.location_id);
Answer: C