INSERT INTO employee VALUES (2, 'Smith', 80, 'Mgr', '09/03/2006', 80000, NULL)
INSERT INTO employee VALUES (4, 'Smith', 86, 'Mgr', '07/14/2003', 90000, NULL)
INSERT INTO employee VALUES (1, 'Smith', 55, 'Sales', '07/14/2003', NULL, NULL)
INSERT INTO employee VALUES (3, 'Smith', 33, 'Analyst', '11/26/2006', 90000, NULL)
第1题：
A table was created using the following DDL:CREATE TABLE employee (id SMALLINT NOT NULL, name VARCHAR(9), dept SMALLINT CHECK (dept BETWEEN 10 AND 100),job CHAR(10) CHECK (job IN (‘Sales‘,‘Mgr‘,‘Clerk‘)), hiredate DATE, salary DECIMAL(7,2), comm DECIMAL(7,2), PRIMARY KEY (id), CONSTRAINT yearsal CHECK (YEAR(hiredate) > 2004 OR salary > 80500) );Which of the following INSERT statements will fail?（）
A.INSERT INTO employee VALUES (2, ‘Smith‘, 80, ‘Mgr‘, ‘09/03/2006‘, 80000, NULL)
B.INSERT INTO employee VALUES (4, ‘Smith‘, 86, ‘Mgr‘, ‘07/14/2003‘, 90000, NULL)
C.INSERT INTO employee VALUES (1, ‘Smith‘, 55, ‘Sales‘, ‘07/14/2003‘, NULL, NULL)
D.INSERT INTO employee VALUES (3, ‘Smith‘, 33, ‘Analyst‘, ‘11/26/2006‘, 90000, NULL)
第2题：
Given the following requirements:Create a table named TESTTAB, which has an identity column named ACTIVITYNO. Define the identity column to generate the values for the column by default. Start the values at 10 and increment by 10. Make the identity column unique. Which of the following CREATE statements will successfully create this table?（）
A.CREATE TABLE TESTTAB (ACTIVITYNO SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 10), ACTKWD CHAR(6) NOT NULL, ACTDESC VARCHAR(20) NOT NULL, UNIQUE(ACTIVITYNO))
B.CREATE TABLE TESTTAB (ACTIVITYNO SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 10), ACTKWD CHAR(6) NOT NULL, ACTDESC VARCHAR(20) NOT NULL, UNIQUE(ACTNO))
C.CREATE TABLE TESTTAB (ACTIVITYNO SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 1), ACTKWD CHAR(6) NOT NULL, ACTDESC VARCHAR(20) NOT NULL, UNIQUE(ACTIVITYNO))
D.CREATE TABLE TESTTAB (ACTIVITYNO SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 10), ACTKWD CHAR(6) NOT NULL, ACTDESC VARCHAR(20) NOT NULL, UNIQUE(ACTIVITYNO))
第3题：
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;
第4题：
Examine the data in the EMPLOYEES table. 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 EK_DIR 8000 120 Revi 20 110 SA_DIR 6500 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_id FROM employees WHERE dept_id = 90; Why does the DELETE statement fail when you execute it?（）
第5题：
A table was created using the following DDL: CREATE TABLE employee (id SMALLINT NOT NULL, name VARCHAR(9), dept SMALLINT CHECK (dept BETWEEN 10 AND 100), job CHAR(10) CHECK (job IN ('Sales','Mgr','Clerk')), hiredate DATE, salary DECIMAL(7,2), comm DECIMAL(7,2), PRIMARY KEY (id), CONSTRAINT yearsal CHECK (YEAR(hiredate) > 2004 OR salary > 80500) ); Which of the following INSERT statements will fail?（）
第6题：
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‘)) );
第7题：
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.
第8题：
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?（）
第9题：
DEPARTMENT 表包含以下列： DEPT_ID NUMBER, Primary Key DEPT_ABBR VARCHAR2(4) DEPT_NAME VARCHAR2(30) MGR_ID NUMBER EMPLOYEE 表包含以下列： EMPLOYEE_ID NUMBER EMP_LNAME VARCHAR2(25) EMP_FNAME VARCHAR2(25) DEPT_ID NUMBER JOB_ID NUMBER MGR_ID NUMBER SALARY NUMBER(9,2) HIRE_DATE DATE 请评估以下语句： ALTER TABLE employee ADD CONSTRAINT REFERENTIAL (mgr_id) TO department(mgr_id); 是以下哪个说法（）
第10题：
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?（）