DEPARTMENT 表包含以下列： DEPT_ID NUMBER, Primary Key DEPT_ABBR VAR

Examine the data in the EMPLOYEES and DEPARTMENTS tables:

EMPLOYEES

EMP_NAME DEPT_ID MGR_ID JOB_ID SALARY

EMPLOYEE_ID

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

Also examine the SQL statements that create the EMPLOYEES and DEPARTMENTS tables:

CREATE TABLE departments

(department_id NUMBER PRIMARY KEY,

department _ name VARCHAR2(30));

CREATE TABLE employees

(EMPLOYEE_ID NUMBER PRIMARY KEY,

EMP_NAME VARCHAR2(20),

DEPT_ID NUMBER REFERENCES

departments(department_id),

MGR_ID NUMBER REFERENCES

employees(employee id),

MGR_ID NUMBER REFERENCES

employees(employee id),

JOB_ID VARCHAR2(15).

SALARY NUMBER);

ON the EMPLOYEES,

On the EMPLOYEES table, EMPLOYEE_ID is the primary key.

MGR_ID is the ID of managers and refers to the EMPLOYEE_ID. DEPT_ID is foreign key to DEPARTMENT_ID column of the DEPARTMENTS table. On the DEPARTMENTS table, DEPARTMENT_ID is the primary key.

Examine this DELETE statement:

DELETE

FROM departments

WHERE department id = 40;

What happens when you execute the DELETE statement?（）

Click the Exhibit button and examine the data in the EMPLOYEES table.On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID. The JOB_ID column is a NOT NULL column.Evaluate this DELETE statement:DELETE employee_id, salary, job_idFROM employeesWHERE dept_id = 90;Why does the DELETE statement fail when you execute it?（）

A.There is no row with dept_id 90 in the EMPLOYEES table.

B.You cannot delete the JOB_ID column because it is a NOT NULL column.

C.You cannot specify column names in the DELETE clause of the DELETE statement.

D.You cannot delete the EMPLOYEE_ID column because it is the primary key of the table.

Examine the description of the EMPLOYEES table:EMP_ID NUMBER(4) NOT NULLLAST_NAME VARCHAR2(30) NOT NULLFIRST_NAME VARCHAR2(30)DEPT_ID NUMBER(2)JOB_CAT VARCHARD2(30)SALARY NUMBER(8,2)Which statement shows the maximum salary paid in each job category of each department? （）

A. SELECT dept_id, job_cat, MAX(salary) FROM employees WHERE salary > MAX (salary);

B. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id,job_cat;

C. SELECT dept_id, job_cat, MAX(salary) FROM employees;

D. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id;

E. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept _ id job _ cat salary;

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

阅读以下说明，回答问题1至问题3，将答案写在对应栏内。

【说明】

关于一位花商有以下一些事实。

(1)销售在不同地区生长的花，这些地区一年的量低温度在一定范围内变化。

(2)想用编号来表示发货类型。

(3)要出售某些类型的花。

假定已经通过SQL语句建立了基本表：

CREATE TABLE Zone

(

ID Char(2) PRIMARY KEY，

LowerTemp Number (3)，

UpperTemp Number (3)

)；

CREATE TABLE Delivery

(

ID char(2)PRIMARY KEY，

Category VarChar (5)，

DelSize Number (5，3)

)：

CREATE TABLE FlowerInfo

(

ID Char(3) CONSTRAINT

Flowerinfo_ id _ pk PRIMARY KEY，

ComName VarChar (25)，

LatName VarChar (30)，

Czone Number (3)，

Hzone Number (3)，

Delivered Number (3)，

SunNeed Char (3)，

PRIMARY KEY (ID)

)：

地区(ID，最高温度，最低温度)

发货(ID，发货类型，发货规格)

花的信息(ID，普通名，拉丁名，花能够生长的最冷地区，花能够生长的最热地区，发货类型，日光需求)

写出语句，将(ID=1，Category=pot，DelSize=1.5)的数据插入DELIVERY表中。