NEW QUESTION 1
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, ename VARCHAR2(15),
salary NUMBER (8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp(emp_no)); 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_PK?

• A. It would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it.
• B. It would remain disabled and has to be enabled manually using the ALTER TABLE command.
• C. It would be automatically enabled and immediate.
• D. It would be automatically enabled and deferred.

Answer: B

NEW QUESTION 2
View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.

The CUSTOMERS table contains the current location of all currently active customers.
The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.
You need to find those customers who have never changed their address. Which SET operator would you use to get the required output?

• A. INTERSECT
• B. UNION ALL
• C. MINUS
• D. UNION

Answer: C

NEW QUESTION 3
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: B

Explanation: References:
https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT038

NEW QUESTION 4
Examine the structure of the BOOKS_TRANSACTIONS table:

You want to display the member IDs, due date, and late fee as $2 for all transactions. Which SQL statement must you execute?

• A. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS;
• B. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;
• C. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS;
• D. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS;

Answer: C

NEW QUESTION 5
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: BE

Explanation: References:
http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authorization.htm#autoId28

NEW QUESTION 6
View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.


Evaluate the following SQL statement: SQL> SELECT prod_id FROM costs
WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL
(SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_date- promo_begin_date)));
What would be the outcome of the above SQL statement?

• A. It displays prod IDs in the promo with the lowest cost.
• B. It displays prod IDs in the promos with the lowest cost in the same time interval.
• C. It displays prod IDs in the promos with the highest cost in the same time interval.
• D. It displays prod IDs in the promos which cost less than the highest cost in the same time interval.

Answer: D

NEW QUESTION 7
Examine the business rule:
Each student can work on multiple projects and each project can have multiple students.
You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK
Which two statements are true in this scenario?

• A. The ERD must have a 1:M relationship between the STUDENTS and PROJECTS entities.
• B. The ERD must have a M:M relationship between the STUDENTS and PROJECTS entities that must be resolved into 1:M relationships.
• C. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.
• D. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
• E. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.

Answer: BE

Explanation: References:
http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61sql-512021.html

NEW QUESTION 8
Evaluate the following ALTER TABLE statement:
ALTER TABLE orders
SET UNUSED (order_date); Which statement is true?

• A. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.
• B. The ORDER_DATE column should be empty for the ALTER TABLE command to execute succsessfully.
• C. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
• D. The DESCRIBE command would still display the ORDER_DATE column.

Answer: A

NEW QUESTION 9
Which statement is true regarding the default behaviour of the ORDER by clause?

• A. Numeric values are displayed in descending order if they have decimal positions.
• B. Only columns that are specified in the SELECT list can be used in the ORDER by clause.
• C. In a character sort, the values are case-sensitive.
• D. NULLs are not including in the sort operation

Answer: C

NEW QUESTION 10
View the Exhibit and examine the structure in the EMPLOYEES tables.

Evaluate the following SQL statement: SELECT employee_id, department_id FROM employees
WHERE department_id= 50 ORDER BY department_id UNION
SELECT employee_id, department_id FROM employees
WHERE department_id=90 UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id=10;
What would be the outcome of the above SQL statement?

• A. The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.
• B. The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.
• C. The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.
• D. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.

Answer: D

NEW QUESTION 11
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: AE

NEW QUESTION 12
Examine the structure of the SALES table. (Choose two.)

Examine this statement:
SQL > CREATE TABLE sales1 (prod_id, cust_id, quantity_sold, price) AS
SELECT product_id, customer_id, quantity_sold, price FROM sales
WHERE 1 = 2;
Which two statements are true about the SALES1 table?

• A. It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
• B. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
• C. It will not be created because of the invalid WHERE clause.
• D. It is created with no rows.
• E. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.

Answer: BD

NEW QUESTION 13
Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables:

You want to generate a list of all department IDs that do not exist in the COURSE_DETAILS table. You execute the SQL statement:

What is the outcome?

• A. It fails because the join type used is incorrect.
• B. It executes successfully and displays the required list.
• C. It executes successfully but displays an incorrect list.
• D. It fails because the ON clause condition is not valid.

Answer: B

NEW QUESTION 14
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:
SQL>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.

Answer: A

NEW QUESTION 15
You issued the following command: SQL> DROP TABLE employees; Which three statements are true?

• A. All uncommitted transactions are committed.
• B. All indexes and constraints defined on the table being dropped are also dropped.
• C. Sequences used in the employees table become invalid.
• D. The space used by the employees table is reclaimed immediately.
• E. The employees table can be recovered using the rollback command.
• F. The employees table is moved to the recycle bin

Answer: ABF

NEW QUESTION 16
Which task can be performed by using a single Data Manipulation Language (DML) 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: C

NEW QUESTION 17
Which two statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose two.)

• A. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
• B. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
• C. The WHERE clause can be used to exclude rows after dividing them into groups.
• D. The HAVING clause can be used with aggregate functions in subqueries.
• E. The WHERE clause can be used to exclude rows before dividing them into groups.

Answer: CD

NEW QUESTION 18
Which two statements are true regarding constraints?

• A. A table can have only one primary key and one foreign key.
• B. A table can have only one primary key but multiple foreign keys.
• C. Only the primary key can be defined at the column and table levels.
• D. The foreign key and parent table primary key must have the same name.
• E. Both primary key and foreign key constraints can be defined at both column and table levels.

Answer: BE