Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. View the Exhibit and examine the data in the LOCATIONS table.
Evaluate the following SOL statement:
SELECT street_address
FROM locations
WHERE
REGEXP_INSTR(street_address,'[^[: alpha:]]’) = 1;
Which statement is true regarding the output of this SOL statement?
A. It would displayallthe street addresses thatdo nothaveasubstring 'alpha'.
B. It would displayallthestreetaddresseswhere the first character isaspecial character.
C. It would display allthe streetaddresses wherethefirst character is aletterofthealphabet.
D. It would displayall thestreet addresses where the first character isnota letter of the alphabet.
Answer: D
Q2. View the Exhibit and examine the description for EMPLOYEES and DEPARTMENTS tables.
Evaluate the following SQL statement:
SELECT e.department_id, e.job_id, d.location_id, sum(e. salary) total FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY CUBE (e.department_id, e.job_id, d.location_id);
Which two statements are true regarding the output of this command? (Choose two.)
A. Theoutputwould display thetotal salaryforallthedepartments.
B. Theoutputwoulddisplay the total salaryforall theJOB_IDsinadepartment.
C. The output would display only the grand total ofthesalary for allJOB_IDsin a LOCATION_ID.
D. Theoutput would displaythegrandtotalof thesalaryfor only the groups specified in the GROUP BY clause.
Answer: AB
Q3. 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_name,,^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
Q4. View the Exhibit and examine the descriptions of ORDER_ITEMS and ORDERS tables.
You want to display the CUSTOMER_ID, PRODUCT_ID, and total (UNIT_PRICE multiplied by QUANTITY) for the order placed. You also want to display the subtotals for a CUSTOMER_ID as well as for a PRODUCT ID for the last six months.
Which SQL statement would you execute to get the desired output?
A. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total FROM order_items oi JOIN orders o ON oi.order_id=o.order_id GROUP BY ROLLUP (o.customer_id, oi.product_id) WHERE MONTHS_BETWEEN(order_ date,SYSDATE)<=6;
B. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total FROM order_items oi JOIN orders o ON oi.order_id=o.order_id GROUP BY ROLLUP (o.customer_id, oi.product_id) HAVING MONTHS_BETWEEN(order_ date,SYSDATE)<=6;
C. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total FROM order_items oi JOIN orders o ON oi.order_id=o.order_id GROUP BY ROLLUP (o.customer_id, oi.product_id) WHERE MONTHS_BETWEEN(order_ date,SYSDATE)>=6;
D. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total FROM order_items oi JOIN orders o ON oi.order_id=o.order_id WHERE MONTHS_BETWEEN(order date,SYSDATE)<=6 GROUP BY ROLLUP (o.customer_id, oi.product_id);
Answer: D
Q5. View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables.
You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of items in each order.
Which CREATE VIEW statement would create the view successfully?
A. CREATE OR REPLACE VIEW ord_vu (order_id,order_date)
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date;
B. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date;
C. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
FROM orders o JOIN order_items i ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date;
D. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)ll’ NO OF ITEMS'
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date
WITH CHECK OPTION;
Answer: B
Q6. Which statements are true? (Choose all that apply.)
A. The data dictionary is created and maintained by the database administrator.
B. The data dictionary views can consist of joins of dictionary base tables and user-defined tables.
C. The usernames of all the users including the database administrators are stored in the data dictionary.
D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
E. Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user.
F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary
Answer: CDF
Q7. In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause?
A. to find the groups forming the subtotal in a row
B. to create group-wise grand totals for the groups specified within a GROUP BY clause
C. to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals
D. to create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is cross-tabular report for calculating the subtotals
Answer: C
Q8. View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Order Amount"
FROM orde_items oi JOIN orders o
ON oi.order_id = o.order_id
GROUP BY CUBE (o.customer_id, oi.product_id);
Which three statements are true regarding the output of this SQL statement? (Choose three.)
A. It would return the subtotals for the Order Amount of every CUSTOMER_ID.
B. It would return the subtotals for the Order Amount for every PRODUCT_ID.
C. It would return the subtotals for the Order Amount of every PRODUCT_ID and CUSTOMER_ID as one group.
D. It would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.
E. It would return only the grand total for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.
Answer: ABD
Q9. View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table.
Evaluate the following SQL statement:
ALTER TABLE emp DROP COLUMN first_name;
Which two statements are true regarding the above command? (Choose two.)
A. The FIRST_NAME column would be dropped provided it does not contain any data.
B. The FIRST_NAME column would be dropped provided at least one or more columns remain in the table.
C. The FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the above SQL statement.
D. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is used.
Answer: BD
Q10. 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
Q11. View the Exhibit and examine the description of the EMPLOYEES table.
You want to display the EMPLOYE_ID, FIRST_NAME, and DEPARTMEN_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYE_ID 104. To accomplish the task, you execute the following SQL statement:
SELECT employee_id, first_name, department_id
FROM employees
WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees WHERE employee_id = 104)
AND employee_id <> 104;
When you execute the statement it does not produce the desired output. What is the reason for this?
A. The WHERE clause condition in the main query is using the = comparison operator, instead of EXISTS.
B. TheWHERE clause condition in themainquery is usingthe= comparison operator,insteadof theINoperator.
C. The WHERE clause condition in themainquery is using the=comparison operator,insteadof the =ANYoperator.
D. The columns in the WHERE clause condition of the main query andthecolumns selected inthesubquery should be in the same order.
Answer: D
Q12. 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
Q13. 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
Q14. Which two statements are true regarding the types of table joins available in Oracle Database 10g? (Choose two.)
A. You can use the JOIN clause to join only two tables.
B. You can explicitly provide the join condition with a NATURAL JOIN.
C. You can use the USING clause to join tables on more than one column.
D. You can use the ON clause to specify multiple conditions while joining tables.
Answer: CD
Q15. Which statement is true regarding Flashback Version Query?
A. It returns versions of rows only within a transaction.
B. It can be used in subqueries contained only in a SELECT statement.
C. It will return an error if the undo retention time is less than the lower bound time or SCN specified.
D. It retrieves all versions including the deleted as well as subsequently reinserted versions of the rows.
Answer: D