Oracle 1Z0-051 real exam prep : Oracle Database: SQL Fundamentals I

Which two statements are true regarding the ORDER BY clause? (Choose two.)

• A. You can specify a combination of numeric positions and column names in this clause.
• B. It is executed first in the query execution.
• C. You cannot specify a column name followed by an expression in this clause.
• D. It cannot be used in a SELECT statement containing a HAVING clause.
• E. It must be the last clause in the SELECT statement.

Examine the structure proposed for the TRANSACTIONS table:

Which two statements are true regarding the storage of data in the above table structure? (Choose two.)

• A. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.
• B. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.
• C. The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
• D. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
NEW_EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which MERGE statement is valid?

• A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);
• B. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
• C. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id, e.first_name ||', '||e.last_name);
• D. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);

EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2(30)
JOB_ID NUMBER\
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of theDEPARTMENTS table
You created a sequence called EMP_ID_SEQ in order to populate sequential values for the EMPLOYEE_ID column of the EMPLOYEES table.
Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.)

• A. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column.
• B. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.
• C. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column.
• D. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column.
• E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table.
• F. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.

You are the DBA for an academic database. You need to create a role that allows a group of users to modify existing rows in the STUDENT_GRADES table.
Which set of statements accomplishes this?

• A. CREATE NEW ROLE registrar; GRANT ALL ON student_grades TO registrar; GRANT registrar to user1, user2, user3
• B. CREATE registrar; GRANT CHANGE ON student_grades TO registrar; GRANT registrar;
• C. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT ROLE registrar to user1, user2, user3
• D. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3;
• E. CREATE ROLE registrar; GRANT MODIFY ON student_grades TO registrar; GRANT registrar to user1, user2, user3