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 two statements are true regarding subqueries? (Choose two.)
A. Only two subqueries can be placed at one level.
B. A subquery can be used to access data from one or more tables or views.
C. If the subquery returns 0 rows, then the value returned by the subquery expression is NULL. D. The columns in a subquery must always be qualified with the name or alias of the table used. E. A subquery in the WHERE clause of a SELECT statement can be nested up to three levels only.
Answer: BC
Q3. 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
Q4. 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
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)| 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. 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. Thestatement would execute successfullytoproducetherequired output.
B. Thestatement wouldnotexecute because inlineviewsandouterjoins cannot be usedtogether.
C. The statementwouldnot execute because the ITEM_CNT alias cannotbedisplayedintheouter query.
D. The statement wouldnot execute because the GROUP BYclausecannot be used intheinline view.
Answer: A
Q7. Which statement is true regarding the ROLLUP operator specified in the GROUP BY clause of a SQL statement?
A. It produces only the subtotals for the groups specified in the GROUP BY clause.
B. It produces only the grand totals for the groups specified in the GROUP BY clause.
C. It produces higher-level subtotals, moving from right to left through the list of grouping columns specified in the GROUP BY clause.
D. It produces higher-level subtotals, moving in all the directions through the list of grouping columns specified in the GROUP BY clause.
Answer: C
Q8. View the Exhibit and examine the structure of the ORD table.
Evaluate the following SQL statements that are executed in a user session in the specified order: CREATE SEQUENCE ord_seq;
SELECT ord_seq.nextval FROM dual;
INSERT INTO ord
VALUES (ord_seq.CURRVAL, 25-jan-2007',101);
UPDATE ord
SET ord_no= ord_seq.NEXTVAL
WHERE cust_id =101;
What would be the outcome of the above statements?
A. All the statements would execute successfullyandthe ORD_NO column would containthevalue 2 for the CUSTJD101.
B. TheCREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.
C. The CREATE SEQUENCE command wouldnotexecute because the starting value of the sequence and the incrementvaluehave not been specified.
D. Allthe statementswould execute successfully and the ORD_NO column wouldhave the value 20 for the CUST_ID101becausethedefault CACHE value is 20.
Answer: A
Q9. The details of the order ID, order date, order total, and customer ID are obtained from the ORDERS table. If the order value is more than 30000, the details have to be added to the LARGE_DRDERS table. The order ID, order date, and order total should be added to the ORDER_HISTORY table, and order ID and customer ID should be added to the CUST_HISTORY table. Which multitable INSERT statement would you use?
A. Pivoting INSERT
B. Unconditional INSERT
C. ConditionalALLINSERT
D. Conditional FIRST INSERT
Answer: C
Q10. 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
Q11. View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the
PRIMARY KEY in the ORDERS table.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)
AS
SELECT order_id.order_date,customer_id
FROM orders;
Which statement is true regarding the above command?
A. The NEW_IDRDERS table would not get created because the DEFAULT value can not be specified in the column definition.
B. The NEW_IDRDERS table would get created and only the NOTNULL constraint defined on the specified columns would be passed to the new table.
C. The NEW_IDRDERS table would not get created because the column names in the CREATE TABLE commandand the SELECT clause do not match.
D. The NEW_IDRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.
Answer: B
Q12. Which three statements are true? (Choose three.)
A. Only one LONG column can be used per table.
B. ATIMESTAMP data type column stores only time values with fractional seconds.
C. The BLOB data type column is used to store binary data in an operating system file.
D. The minimum column width that can be specified for a varchar2 data type column is one.
E. The value for a CHAR data type column is blank-padded to the maximum defined column width.
Answer: ADE
Q13. View the Exhibit and examine the structure of the EMP table.
You executed the following command to add a primary key to the EMP table:
ALTER TABLE emp
ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
USING INDEX emp_id_idx;
Which statement is true regarding the effect of the command?
A. The PRIMARY KEY is created along with a new index.
B. The PRIMARY KEY is created and it would use an existing unique index.
C. The PRIMARY KEY would be created in a disabled state because it is using an existing index.
D. The statement produces an error because the USING clause is permitted only in the CREATE TABLE command.
Answer: B
Q14. Which two statements are true regarding views? (Choose two.)
A. A simple view in which column aliases have been used cannot be updated.
B. A subquery used in a complex view definition cannot contain group functions or joins.
C. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
D. Rows added through a view are deleted from the table automatically when the view is dropped.
E. TheOR REPLACE option is used to change the definition of an existing view without dropping and re-creating it.
F. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.
Answer: CE
Q15. View the Exhibit and examine the structure of the EMPLOYEES and DEPARTMENTS tables.
Which SET operator would you use in the blank space in the following SQL statement to list the departments where all the employees have managers?
SELECT department_id FROM departments
SELECT department_id FROM employees WHERE manager_id IS NULL;
A. UNION
B. MINUS
C. INTERSECT
D. UNION ALL
Answer: B