单选题The EMPLOYEE table contains the following information: EMPNO NAME WORKDEPT 101 SAM A11 102 JOHN C12 103 JANE -104 PAT Remote 105 ANNE -106 BOB A11 The MANAGER table contains the following information: MGRID NAME DEPTNO EMPCOUNT 1 WU B01 - 2 JONES A11 -

第1题：

第2题：

Given the following function: CREATE FUNCTION emplist （　） RETURNS TABLE ( id CHAR(6) , firstname VARCHAR(12) , lastname VARCHAR(15) ) LANGUAGE SQL BEGIN ATOMIC RETURN SELECT EMPNO, FIRSTNME, LASTNAME FROM EMPLOYEE WHERE WORKDEPT IN ('A00', 'B00'); END How can this function be used in an SQL statement?

• A、SELECT TABLE(EMPLIST()) FROM EMPLOYEE
• B、SELECT TABLE(EMPLIST()) AS t FROM EMPLOYEE
• C、SELECT EMPLIST(id, firstname, lastname) FROM EMPLOYEE
• D、SELECT id, firstname, lastname FROM TABLE(EMPLIST()) AS t

第3题：

第4题：

Which three statements inserts a row into the table?（）

• A、INSERT INTO employees   VALUES ( NULL, ‘John’,‘Smith’);
• B、INSERT INTO employees( first_name, last_name)   VALUES(‘John’,‘Smith’);
• C、INSERT INTO employees   VALUES (‘1000’,‘John’,NULL);
• D、INSERT INTO employees(first_name,last_name, employee_id)   VALUES ( 1000, ‘John’,‘Smith’);
• E、INSERT INTO employees (employee_id)   VALUES (1000);
• F、INSERT INTO employees (employee_id, first_name, last_name)   VALUES ( 1000, ‘John’,‘’);

第5题：

Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) Which three statements inserts a row into the table? （）

• A、INSERT INTO employees VALUES (NULL, 'JOHN','Smith');
• B、INSERT INTO employees( first_name, last_name) VALUES ('JOHN','Smith');
• C、INSERT INTO employees VALUES ('1000','JOHN','NULL');
• D、INSERT INTO employees(first_name,last_name, employee_id) VALUES ('1000, 'john','Smith');
• E、INSERT INTO employees (employee_id) VALUES (1000);
• F、INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, 'john',");

第6题：

What type of constraint is used to ensure that each row inserted into the EMPLOYEE table with a value in the WORKDEPT column has a row with a corresponding value in the DEPTNO column of the DEPARTMENT table?（）

• A、A check constraint on the EMPLOYEE table
• B、A unique constraint on the EMPLOYEE table WORKDEPT column
• C、A foreign key reference from the DEPARTMENT tables DEPTNO column to the WORKDEPT column of the EMPLOYEE table
• D、A foreign key reference from the EMPLOYEE tables WORKDEPT column to the DEPTNO column of the DEPARTMENT table

第7题：

Given the following requirements: Create a table to contain employee data, with a unique numeric identifier automatically assigned when a row is added, has an EDLEVEL column that permits only the values 'C', 'H' and 'N', and permits inserts only when a corresponding value for the employee's department exists in the DEPARTMENT table. Which of the following CREATE statements will successfully create this table?（）

• A、CREATE TABLE emp ( empno SMALLINT NEXTVAL GENERATED ALWAYS AS IDENTITY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, workdept CHAR(3) NOT NULL, edlevel CHAR(1), PRIMARY KEY emp_pk (empno), FOREIGN KEY emp_workdept_fk ON (workdept) REFERENCES department (deptno), CHECK edlevel_ck VALUES (edlevel IN ('C','H','N')), );
• B、CREATE TABLE emp ( empno SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, workdept CHAR(3), edlevel CHAR(1), CONSTRAINT emp_pk PRIMARY KEY (empno), CONSTRAINT emp_workdept_fk FOREIGN KEY (workdept) REFERENCES department (deptno), CONSTRAINT edlevel_ck CHECK edlevel VALUES ('C','H','N') );
• C、CREATE TABLE emp ( empno SMALLINT NEXTVAL GENERATED BY DEFAULT AS IDENTITY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, workdept CHAR(3) NOT NULL, edlevel CHAR(1) CHECK IN ('C','H','N')), CONSTRAINT emp_pk PRIMARY KEY (empno), CONSTRAINT emp_workdept_fk FOREIGN KEY department (deptno) REFERENCES (workdept) );
• D、CREATE TABLE emp ( empno SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, workdept CHAR(3), edlevel CHAR(1), CONSTRAINT emp_pk PRIMARY KEY (empno), CONSTRAINT emp_workdept_fk FOREIGN KEY (workdept) REFERENCES department (deptno), CONSTRAINT edlevel_ck CHECK (edlevel IN ('C','H','N')) );

第8题：

第9题：

EMPLOYEES and DEPARTMENTS data: EMPLOYEES DEMP_NAME DEPT_ID MGR_ID JOB_ID SALARY EMPLOYEE_I 101 Smith 20 120 SA_REP 4000 102 Martin 10 105 CLERK 2500 103 Chris 20 120 IT_ADMIN 4200 104 John 30 108 HR_CLERK 2500 105 Diana 30 108 IT_ADMIN 5000 106 Smith 40 110 AD_ASST 3000 108 Jennifer 30 110 HR_DIR 6500 110 Bob 40 EX_DIR 8000 120 Ravi 20 110 SA_DIR 6500 DEPARTMENTS DEPARTMENT_ID DEPARTMENT_NAME 10 Admin 20 Education 30 IT 40 Human Resources On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID managers and refers to the EMPLOYEE_ID. On the DEPARTMENTS table DEPARTMENT_ID is the primary key. Evaluate this UPDATE statement. UPDATE employees SET mgr_id = (SELECT mgr_id FROM employees WHERE dept_id= (SELECT department_id FROM departments WHERE department_name = 'Administration')), Salary = (SELECT salary FROM employees WHERE emp_name = 'Smith') WHERE job_id = 'IT_ADMIN'; What happens when the statement is executed?（）

• A、The statement executes successfully, leaves the manager ID as the existing value, and changes the salary to 4000 for the employees with ID 103 and 105.
• B、The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105.
• C、The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105.
• D、The statement fails because there is more than one row matching the employee name Smith.
• E、The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES table.
• F、The statement fails because there is no 'Administration' department in the DEPARTMENTS table.

第10题：

You discover that your Recycle Bin contains two tables with the same name, MY_TABLE. You also have a table named MY_TABLE in your schema. You execute the following statement: FLASHBACK TABLE my_table TO BEFORE DROP RENAME TO my_table2; What will be the result of executing this statement?（）

• A、One of the tables is recovered from the Recycle Bin using a First In First Out (FIFO) approach.
• B、One of the tables is recovered from the Recycle Bin using a Last In First Out (LIFO) approach.
• C、Both the tables are recovered from the Recycle Bin with one table renamed to MY_TABLE2 and the other to a system-generated name.
• D、None of the tables are recovered from the Recycle Bin, and the statement returns an error.