单选题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

第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题：

Given the following stored procedure:CREATE PROCEDURE increase_salary ( IN p_workdept CHAR(6), OUT p_sum DECIMAL(9,2) ) SET p_sum = (SELECT SUM(salary) FROM employee WHERE workdept=p_workdept);How can this stored procedure be called from the Command Line Processor?（）

• A、CALL increase_salary('A00')
• B、VALUES increase_salary('A00')
• C、CALL increase_salary('A00', ?)
• D、VALUES increase_salary('A00', ?)

第4题：

The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4) LAST_NAME VARCHAR2 (25) JOB_ID VARCHAR2(10) You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use?（）

• A、SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA/_%' ESCAPE '/';
• B、SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_';
• C、SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' ESCAPE "/";
• D、SELECT employee_id, last_name, job_id FROM employees WHERE job_id = '%SA_';

第5题：

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 DELETE statement is valid?（）

• A、DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees);
• B、DELETE * FROM employees WHERE employee_id = (SELECT employee_id FROM new_ employees);
• C、DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = 'carrey');
• D、DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = 'carrey');

第6题：

Evaluate the following function code：   CREATE FUNCTION get_dept_avg（dept_id NUMBER） RETURN NUMBER RESULT_CACHE RELIES_ON （EMPLOYEES） IS avgsal NUMBER（6）； BEGIN  SELECT AVG（SALARY）INTO avgsal FROM EMPLOYEES   WHERE DEPARTMENT_ID = dept_id； RETURN avgsal； END get_dept_avg；   Which statement is true regarding the above function？（）

• A、 The cached result becomes invalid when any structural change is done to the EMPLOYEES table. 
• B、 If the function execution results in an unhandled exception，the exception result is also stored in the cache.
• C、 Each time the function is invoked in a different session，the current result in the result cache gets overwritten.
• D、 If the function is invoked with a different parameter value，the existing result in the result cache gets overwritten by the latest value.

第7题：

第8题：

第9题：

第10题：

第11题：

第12题：

在Oracle 中，下列语句中哪些可以合法地创建一个函数？ (1.0分) [多选] A. CREATE FUNCTION func_name(cdcode NUMBER) RETURN CHAR IS DECLARE dis_cd CHAR(15); BEGIN .. END; B. CREATE FUNCTION func_name(cdcode NUMBER) RETURN CHAR IS dis_cd CHAR(15); BEGIN .. END; C. CREATE FUNCTION func_name(cdcode NUMBER) IS BEGIN .. END; D. CREATE FUNCTION func_name(cdcode NUMBER) RETURN CHAR IS BEGIN .. END; E. CREATE FUNCTION func_name(cdcode NUMBER) RETURN CHAR IS DECLARE dis_cd STRING (15); BEGIN .. END;
答案: B  D

第13题：

现有表Employee，字段：id（int）、firstname（varchar）、lastname（varchar）；以下sql语句错误的是（）

• A、select firstname+’.’+lastnameas’name’from employee
• B、select firstname+’.’+lastname=’name’from employee
• C、select’name’=firstname+’.’+lastnamefrom employee
• D、select firstname，lastname from employee

第14题：

How should the following UDF be invoked in order to convert US currency values stored in the EXPENSES table into Canadian currency?CREATE FUNCTION getratews11 ( country1 VARCHAR(100), country2 VARCHAR(100) ) RETURNS DOUBLE LANGUAGE SQL CONTAINS SQL EXTERNAL ACTION NOT DETERMINISTIC BEGIN ... END（）

• A、CALL getratews11('USA','CANADA')
• B、CALL expenses.getratews11('USA','CANADA')
• C、SELECT getratews11('USA','CANADA') FROM expenses
• D、SELECT * FROM TABLE(getratews11('USA','CANADA')) AS convert_currency

第15题：

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')) );

第16题：

Examine the structure if the EMPLOYEES table: Column name Data Type Remarks EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30) JOB_ID VARCHAR2(20) NOT NULL SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of the DEPARTMENTS table You need to create a view called EMP_VU that allows the user to insert rows through the view. Which SQL statement, when used to create the EMP_VU view, allows the user to insert rows? （）

• A、CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, department_id FROM employees WHERE mgr_id IN (102, 120);
• B、CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id department_id FROM employees WHERE mgr_id IN (102, 120);
• C、CREATE VIEW emp_Vu AS SELECT department_id, SUM(sal) TOTALSAL FROM employees WHERE mgr_id IN (102, 120) GROUP BY department_ id;
• D、CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, DISTINCT department_id FROM employees;

第17题：

Examine the structure if the EMPLOYEES table: Column name Data Type Remarks EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30) JOB_ID VARCHAR2(20) NOT NULL SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of the DEPARTMENTS table You need to create a view called EMP_VU that allows the user to insert rows through the view. Which SQL statement, when used to create the EMP_VU view, allows the user to insert rows?（）

• A、CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, department_id FROM employees WHERE mgr_id IN (102, 120);
• B、CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id department_id FROM employees WHERE mgr_id IN (102, 120);
• C、CREATE VIEW emp_Vu AS SELECT department_id, SUM(sal) TOTALSAL FROM employees WHERE mgr_id IN (102, 120) GROUP BY department_ id;
• D、CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, DISTINCT department_id FROM employees;

第18题：

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 DELETE statement is valid?（）

• A、DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees);
• B、DELETE * FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees);
• C、DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name ='Carrey');
• D、DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE last_name ='Carrey');

第19题：

第20题：

第21题：

第22题：

第23题：