Oracle 1z0-071 問題集

質問 1：
The PROD_ID column is the foreign key in the SALES table.Which references the PRODUCTS table.
Similarly,the CUST_ID and TIME_ID columns are Also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales(prod_id, I cust_id, order_date DEFAULT SYSDATE)
AS SELECT I prod_id,cust_id,time_id FROM sales.
Which statement is true regarding the above command?
A. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause I do not match.
B. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table
C. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
D. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
正解：C

質問 2：
Examine these statements:
CREATE TABLE dept (
deptno NUMBER PRIMARY KEY,
diname VARCHAR2(10) ,
mgr NUMBER ,
CONSTRAINT dept_fkey FOREIGN KEY(mgr) REFERENCES emp (empno));
CREATE TABLE emp (
Empno NUMBER PRIMARY KEY,
Ename VARCHAR2 (10) ,
deptno NUMBER,
CONSTRAINT emp_fkey FOREIGN KEY (deptno) REFERENCES dept (deptno) DISABLE); ALTER TABLE emp MODIFY CONSTRAINT emp_fkey ENABLE; Which two are true?
A. The Deptno column in the emp table will be able to contain nulls values.
B. The DEFT FKEY constraint definition must be removed from the CREATE TABLE DEF statement.and be added with an AITER TABLE statement once both tables are created, for the two CREATE TABLE statements to execute successfully in the order shown.
C. The MGR column in the DEPT table will not be able to contain NULL values.
D. All three statements execute successfully in the order shown
E. The CREATE TABLE EMP statement must precede the CREATE TABLE DEPT statement for all threestatements to execute successfully.
F. Both foreign key constraint definitions must be removed from the CREATE TABLE statements, andbe added with ALTER TABLE statements once both tables are created, for the two CREATE TABLEstatements to execute successfully in the order shown.
正解：A,B

質問 3：
Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY.
Only the EMPLOYEES_ID column is indexed.
Rows exist for employees 100 and 200.
Examine this statement:
UPDATE employees
SET (job_id, salary) =
(SELECT job_id, salary
FROM employees
WHERE employee_id = 200)
WHERE employee id=100;
Which two statements are true?
A. Employee 200 will have SALARY set to the same value as the SALARY of employee 100.
B. Employee 100 will have SALARY set to the same value as the SALARY of employee 200.
C. Employees 100 and 200 will have the same JOB ID as before the update command.
D. Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200.
E. Employees 100 and 200 will have the same SALARY as before the update command.
F. Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100
正解：B,D

質問 4：
Which three are key components of an Entity Relationship Model?
A. an activity
B. an entity
C. a table
D. a relationship
E. an attribute
F. a unique identifier
正解：B,D,E

質問 5：
Which three are true aboutprivileges and roles?
A. System prilgese always set prilge for an entire database.
B. A user has all object privileges for every object in their schema by default.
C. PUBIIC can be revoked from a user.
D. PUBLIC acts as a default role granted to every user in a database.
E. All roles are owned by the sYs schema.
F. A role is owned by the user who created it.
G. A role can contain a combination of several privileges and roles.
正解：B,D,G

質問 6：
You have the privileges to create any type of synonym.
Which stalement will create a synonym called EMP for the HCM.EMPLOYEE_RECORDS table that is accesible to all users?
A. CREATE SYNONYM emp FOR hcm.employee_records;
B. CREATE SYNONYM SYS.emp FOR hcm.employee_records;
C. CREATE GLOBAL SYNONYM emp FOR hcm.employee_records;
D. CREATE PUBLIC SYNONYM emp FOR hcm. employee_records;
E. CREATE SYNONYM PUBLIC.emp FOR hcm.employee_records;
正解：D

質問 7：
You execute these commands:
CREATE TABLE customers (customer id INTEGER, customer name VARCHAR2 (20)); INSERT INTO customers VALUES (1,'Custmoer1 '); SAVEPOINT post insert; INSERT INTO customers VALUES (2, 'Customer2 ');<TODO> SELECTCOUNT (*) FROM customers; Which two, used independently, can replace <TODO> so the query retums 1?
A. ROLLEBACK TO post_ insert;
B. COMMIT;
C. CONOIT TO SAVEPOINT post_ insert;
D. ROLIBACK TO SAVEPOINT post_ insert;
E. ROLLBACK;
正解：A,D

質問 8：
Examine the description of the EMPLOYEES table:

Which two statements will run successfully?
A. SELECT 'The first_name is ''' ||first_name||''' FROM employees ;
B. SELECT 'The first_name is '' || first_name || '' FROM employees ;
C. SELECT 'The first_name is '''||first_name ||'''' FROM employees ;
D. SELECT 'The first_name is '|| first_name|| '' FROM employees;
E. SELECT 'The first_name is \'' || first_name || '\'' FROM employees;
正解：C,D

質問 9：
Examine this SQL statement
DELETE FROM employees e
WHERE EXISTS
(SELECT' dummy'
FROM emp history
WHERE employee_ id= e. employee id);
Which two are true?
A. All existing rows in the EMPLOYEES table are deleted,
B. The DELETE statement executes successfully even if the subquery selects multiple rows.
C. The subquery is not a correlated subquery.
D. The subquery is executed for every row in the EMPLOYEES table.
E. The subquery is executed before the DELETE statement is executed.
正解：B,D