Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. View the Exhibit and examine the ORDERS table.
The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?
A. ALTER TABLE orders
ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
B. ALTER TABLE orders
MODIFY customer_id CONSTRAINT orders_cust_id_nn NOT NULL;
C. ALTER TABLE orders
MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
D. ALTER TABLE orders
ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;
Answer: B
Q2. 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
Q3. View the Exhibit and examine the structure of the MARKS_DETAILS and MARKStables.
Which is the best method to load data from the MARKS DETAILStable to the MARKStable?
A. Pivoting INSERT
B. Unconditional INSERT
C. Conditional ALL INSERT
D. Conditional FIRST INSERT
Answer: A
Q4. 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
Q5. Which statement is true regarding external tables?
A. The default REJECT LIMIT for external tables is UNLIMITED.
B. The data and metadata for an external table are stored outside the database.
C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.
D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.
Answer: D
Q6. 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
Q7. 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
Q8. Which statement is true regarding external tables?
A. The default REJECT LIMIT for external tables is UNLIMITED.
B. The data and metadata for an external table are stored outside the database.
C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.
D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.
Answer: D
Q9. View the Exhibit and examine the description of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECTfirst_name, employee_id, NEXr_DAY(ADD_MONTHS(hire_date, 6), 1) "Review" FROM employees;
The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees. The review date is the first Monday after the completion of six months of the hiring. The
NLS_TERRITORY parameter is set to AMERICA in the session. Which statement is true regarding this query?
A. The query would execute to give the desired output.
B. The query would not execute because date functions cannot be nested.
C. The query would execute but the output would give review dates that are Sundays.
D. The query would not execute because the NEXT_DAY function accepts a string as argument.
Answer: C
Q10. View the Exhibit and examine the description of the EMPLOYEES table.
Your company decided to give a monthly bonus of $50 to all the employees who have completed five years in the company. The following statement is written to display the LAST_NAME, DEPARTMENT_ID, and the total annual salary:
SELECT last_name, departmentjd, salary450*12 "Annual Compensation" FROM employees WHERE MONTHS_BETWEEN(SYSDATE, hire_date)/12 >= 5;
When you execute the statement, the "Annual Compensation" is not computed correctly. What changes would you make to the query to calculate the annual compensation correctly?
A. Change the SELECT clause to SELECT last_name, department_id, salary*12+50 "Annual Compensation".
B. Change the SELECT clause to SELECT last_name, department_id, salary+(50*12) "Annual Compensation".
C. Change the SELECT clause to SELECT last_name, department_id, (salary +50)*12 "Annual Compensation".
D. Change the SELECT clause to SELECT last_name, department_id, (salary*12)+50 "Annual Compensation".
Answer: C
Q11. View the Exhibit and examine the structure of the EMPLOYEES table.
You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees.
Which SQL statement would you execute?
A. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE m.manager_id=100;
B. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE e.managerjd=100;
C. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON e.employee_id = m.manager_id
WHERE m.manager_id=100;
D. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
WHERE m.employee_id = e.manager_id AND e.managerjd=100;
Answer: B
Q12. View the Exhibit and examine the description of the CUSTOMERS table.
You want to add a constraint on the CUST_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers.
Which SOL statement would you use to accomplish the task?
A. ALTER TABLE CUSTOMERS
ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name1'^A-Z’))NOVALIDATE;
B. ALTER TABLE CUSTOMERS
ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'^[0-9]’))NOVALIDATE;
C. ALTER TABLE CUSTOMERS
ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:alpha: ]]’))NOVALIDATE;
D. ALTER TABLE CUSTOMERS
ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:digit: ]]’))NOVALIDATE ;
Answer: C
Q13. Which SQL statement would display the view names and definitions of all the views owned by you?
A. SELECTviewjiame, text FROM user_view;
B. SELECTviewjiame, text FROM user_object;
C. SELECTviewjiame, text FROM user_objects;
D. SELECTview_name, text FROM user views;
Answer: D
Q14. 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
Q15. 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 statementwouldprovide the desired results.
B. Thestatement wouldnotexecute because the ON clause iswritten twice.
C. Thestatement wouldnot execute because the WHERE clause is wrongly placed.
D. The statement wouldnot execute because the self join usestheON clauseinsteadof the USING clause.
Answer: C