Q1. 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
Q2. You issue the following command to drop the PRODUCTS table: SQL>DROP TABLE products;
What is the implication of this command? (Choose all that apply.)
A. All data in the table are deleted but the table structure will remain
B. All data along with the table structure is deleted
C. All viewsand synonyms will remain but they are invalidated
D. The pending transaction in the session is committed
E. All indexes on the table will remain but they are invalidated
Answer: B,C,D
Q3. Which statement is true about transactions?
A. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.
B. Each Data Definition Language (DDL) statement executed forms a single transaction.
C. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.
D. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.
Answer: D
Q4. View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST LAST NAME is Roberts and CREDIT LIMIT is 600?
A. INSERT INTO orders
VALUES (1,'10-mar-2007', 'direct',
(SELECT customer_id FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
B. INSERT INTO orders (order_id,order_date,order_mode, (SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600) .order_total)
VALUES(1 ,'10-mar-2007', 'direct', &&customer_id, 1000);
C. INSERT INTO orders (order_id.order_date.order_mode, (SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit _limit=600) .order_total)
VALUES(1 ,'IO-mar-2007', 'direct', &customer_id, 1000);
D. INSERT INTO(SELECT o.order_id, o.order_date.o.orde_mode.c.customer_id, o.order_total FROM orders o, customers c
WHERE o.customer_id = c.customer_id
AND c.cust_last_name='Roberts'ANDc. Credit_limit=600) VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id FROM customers
WHERE cust_last_name='Roberts' AND Credit_limit=600), 1000);
Answer: A
Q5. Examine the structure of the members table:
Which query can be used to display the last names and city names only for members from the states MO and MI?
A)
B)
C)
D)
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Q6. 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
Q7. Which task can be performed by using a single Data Manipulation Language (OML) statement?
A. adding a column constraint when inserting a row into a table
B. adding a column with a default value when inserting a row into a table
C. removing all data only from one single column on which a unique constraint is defined
D. removing all data only from one single column on which a primary key constraint is defined
Answer: D
Q8. 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 cannot be specified in the column definition.
B. The NEW_IDRDERS table would get created and only the NOT NULL 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 command and 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
Q9. 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.
Q10. 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.manager_id=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.manager_id=100;
Answer: B