Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)
A. You can use column alias in the GROUP BY clause.
B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause.
Answer: D,E
Q2. Evaluate the following SQL query;
What would be the outcome?
A. 200
B. 16
C. 160
D. 150
E. 100
Answer: C
Explanation: Function Purpose
ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.)
TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero
Q3. Evaluate the following SELECT statement and view the Exhibit to examine its output:
SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints WHERE table_name = ORDERS
Which two statements are true about the output? (Choose two.)
A. In the second column, indicates a check constraint.
B. The STATUS column indicates whether the table is currently in use.
C. The R_CONSTRAINT_NAME column gives the alternative name for the constraint.
D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.
Answer: A,D
Q4. Which two statements are true regarding roles? (Choose two.)
A. A role can be granted to itself.
B. A role can be granted to PUBLIC.
C. A user can be granted only one role at any point of time.
D. The REVOKE command can be used to remove privileges but not roles from other users.
E. Roles are named groups of related privileges that can be granted to users or other roles.
Answer: B,E
Q5. Which two statements are true regarding multiple-row subqueries? (Choose two.)
A. They can contain group functions.
B. They always contain a subquery within a subquery.
C. They use the < ALL operator to imply less than the maximum.
D. They can be used to retrieve multiple rows from a single table only.
E. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.
Answer: A,E
Q6. View the Exhibit and examine the details of the PRODUCT_INFORMATION table.
You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
SELECT product_name, list_price FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; Which statement is true regarding the execution of the query?
A. It would execute but the output would return no rows.
B. It would execute and the output would display the desired result.
C. It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.
D. It would not execute because the same column has been used in both sides of the AND logical operator to form the condition.
Answer: A
Q7. Which two statements are true about sequences created in a single instance database? (Choose two.)
A. CURRVAL is used to refer to the last sequence number that has been generated
B. DELETE <sequencename> would remove a sequence from the database
C. The numbers generated by a sequence can be used only for one table
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: A,D
Explanation:
Gaps in the Sequence
Although sequence generators issue sequential numbers without gaps, this action occurs independent of a commit or rollback. Therefore, if you roll back a statement containing a sequence, the number is lost.
Another event that can cause gaps in the sequence is a system crash. If the sequence caches values in memory, those values are lost if the system crashes.
Because sequences are not tied directly to tables, the same sequence can be used for multiple tables.
However, if you do so, each table can contain gaps in the sequential numbers.
Modifying a Sequence
If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are allocated and you will receive an error indicating that the sequence exceeds the MAXVALUE. To continue to use the sequence, you can modify it by using the ALTER SEQUENCE statement
To remove a sequence, use the DROP statement:
DROP SEQUENCE dept_deptid_seq;
Q8. You execute the following commands:
For which substitution variables are you prompted for the input?
A. None, because no input required
B. Both the substitution variables 'hiredate' and 'mgr_id\
C. Only 'hiredate'
D. Only 'mgr_id'
Answer: B
Q9. Evaluate the following ALTER TABLE statement:
ALTER TABLE orders SET UNUSED order_date;
Which statement is true?
A. The DESCRIBE command would still display the ORDER_DATE column.
B. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
C. The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully.
D. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.
Answer: D
Q10. Which two statements are true about Data Manipulation Language (DML) statements?
A. AH INSERT INTO. . .VALUES. . statement can add multiple rows per execution to a table.
B. An UPDATE...SET... statement can modify multiple rows based on multiple conditions on a table.
C. A DELETE FROM ..... statement can remove rows based on only a single condition on a table.
D. An INSERT INTO...VALUES..... statement can add a single row based on multiple conditions on a table.
E. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.
F. An UPDATE...SET.... statement can modify multiple rows based on only a single condition on a table.
Answer: A,C
Q11. Evaluate the following SQL statements that are issued in the given order:
CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, enameVARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE; ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_FK?
A. It would be automatically enabled and deferred.
B. It would be automatically enabled and immediate.
C. It would remain disabled and has to be enabled manually using the ALTER TABLE command.
D. It would remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.
Answer: A,B,D
Q12. View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.
You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id;
What would happen when the above statement is executed?
A. The statement would execute successfully to produce the required output.
B. The statement would not execute because inline views and outer joins cannot be used together.
C. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.
D. The statement would not execute because the GROUP BY clause cannot be used in the inline view.
Answer: A
Q13. See the Exhibit and examine the structure of the PROMOTIONS table: Exhibit:
Using the PROMOTIONS table, you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements: Exhibit:
What would be the outcome?
A. It generates an error because multiple conditions cannot be specified for the WHEN clause
B. It executes successfully and gives the required result
C. It generates an error because CASE cannot be used with group functions
D. It generates an error because NULL cannot be specified as a return value
Answer: B
Explanation: CASE Expression
Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr]
END
Q14. See the Exhibit and Examine the structure of the CUSTOMERS table:
Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?
A. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT" FROM customers;
B. SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT" FROM customers;
C. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT" FROM customers;
D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT" FROM customers;
Answer: D
Explanation: NVL Function
Converts a null value to an actual value:
Data types that can be used are date, character, and number. Data types must match:
– NVL(commission_pct,0)
– NVL(hire_date,'01-JAN-97')
– NVL(job_id,'No Job Yet')
Q15. Which statement is true regarding the INTERSECT operator?
A. It ignores NULL values
B. The number of columns and data types must be identical for all SELECT statements in the query
C. The names of columns in all SELECT statements must be identical
D. Reversing the order of the intersected tables the result
Answer: B
Explanation:
INTERSECT Returns only the rows that occur in both queries’ result sets, sorting them and removing duplicates.
The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.
Q16. When does a transaction complete? (Choose all that apply.)
A. When a PL/SQL anonymous block is executed
B. When a DELETE statement is executed
C. When a data definition language statement is executed
D. When a TRUNCATE statement is executed after the pending transaction
E. When a ROLLBACK command is executed
Answer: C,D,E
Q17. Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)
A. The outer query stops evaluating the result set of the inner query when the first value is found.
B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.
Answer: A,C
Q18. You are designing the structure of a table in which two columns have the specifications:
COMPONENT_ID - must be able to contain a maximum of 12 alphanumeric characters and uniquely identify the row
EXECUTION_DATETIME - contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons between components.
Which two options define the data types that satisfy these requirements most efficiently?
A. The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.
B. The EXECUTION _DATETIME must be of TIMESTAMP data type.
C. The EXECUTION_DATATIME must be of DATE data type.
D. The COMPONENT_ID must be of ROWID data type.
E. The COMPONENT_ID must be of VARCHAR2 data type.
F. The COMPONENT_ID column must be of CHAR data type.
Answer: C,E