Evaluate the following statements：   CREATE TABLE purchase_orders （po_id NUMBER（4），  po_date TIMESTAMP， supplier_id NUMBER（6）， po_total NUMBER（8，2），  CONSTRAINT order_pk PRIMARY KEY（po_id）） PARTITION BY RANGE（po_date）  （PARTITION Q1 VALUES LESS THAN （TO_

第1题：

You need to create a partitioned table to store historical data and you issued the following command：   CREATE TABLE purchase_interval PARTITION BY RANGE （time_id） INTERVAL （NUMTOYMINTERVAL（1，’month’）） STORE IN （tbs1，tbs2，tbs3） （ PARTITION p1 VALUES LESS THAN（TO_DATE（’1-1-2005’， ’dd-mm-yyyy’））， PARTITION p2 VALUES LESS THAN（TO_DATE（’1-1- 2007’， ’dd-mm-yyyy’）））  AS SELECT * FROM purchases WHERE time_id < TO_DATE（’1-1-2007’，’dd-mm-yyyy’）；   What is the outcome of the above command？（）  

• A、 It returns an error because the range partitions P1 and P2 should be of the same range.
• B、 It creates two range partitions （P1， P2）. Within each range partition， it creates monthwise subpartitions.
• C、 It creates two range partitions of varying range. For data beyond ’1-1-2007，’ it creates partitions with a width of one month each.
• D、 It returns an error because the number of tablespaces （TBS1，TBS2，TBS3）specified does not match the number of range partitions （P1，P2） specified.

第2题：

评估此CREATE TABLE语句的执行结果： CREATE TABLE part( part_id NUMBER, part_name VARCHAR2(25), manufacturer_id NUMBER(9), retail_price NUMBER(7,2) NOT NULL, CONSTRAINT part_id_pk PRIMARY KEY(part_id), CONSTRAINT cost_nn NOT NULL(cost), CONSTRAINT FOREIGN KEY (manufacturer_id) REFERENCES manufacturer(id)); 哪一行会导致产生错误（）

• A、6
• B、7
• C、8
• D、9

第3题：

Evaluate the CREATE TABLE statement：   CREATE TABLE products   （product_id NUMBER （6）  CONSTRAINT prod_id_pk PRIMARY KEY，  product_name VARCHAR2 （15））；   Which statement is true regarding the PROD_ID_PK constraint？（）

• A、 It would be created only if a unique index is manually created first.
• B、 It would be created and would use an automatically created unique index.
• C、 It would be created and would use an automatically created nonunique index.
• D、 It would be created and remains in a disabled state because no index is specified in the command.

第4题：

Which is a valid CREATE TABLE statement? （）

• A、CREATE TABLE EMP9$# AS (empid number(2));
• B、CREATE TABLE EMP*123 AS (empid number(2));
• C、CREATE TABLE PACKAGE AS (packid number(2));
• D、CREATE TABLE 1EMP_TEST AS (empid number(2));

第5题：

Which SQL statement defines the FOREIGN KEY constraint on the DEPTNO column of the EMP table?（）

• A、CREATE TABLE EMP (empno NUMBER(4), ename VARCNAR2(35), deptno NUMBER(7,2) NOT NULL CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno);
• B、CREATE TABLE EMP (empno NUMBER(4), ename VARCNAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
• C、CREATE TABLE EMP (empno NUMBER(4) ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));
• D、CREATE TABLE EMP (empno NUMBER(4), ename VARCNAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp deptno fk REFERENCES dept (deptno));

第6题：

Examine the following command: CREATE TABLE (prod_id number(4), Prod_name varchar2 (20), Category_id number(30), Quantity_on_hand number (3) INVISIBLE); Which three statements are true about using an invisible column in the PRODUCTS table?（）

• A、The %ROWTYPE attribute declarations in PL/SQL to access a row will not display the invisible column in the output.
• B、The DESCRIBE commands in SQL *Plus will not display the invisible column in the output.
• C、Referential integrity constraint cannot be set on the invisible column.
• D、The invisible column cannot be made visible and can only be marked as unused.
• E、A primary key constraint can be added on the invisible column.

第7题：

Which SQL statement defines a FOREIGN KEY constraint on the DEPTNO column of the EMP table?（）

• A、CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno);
• B、CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
• C、CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));
• D、CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

第8题：

第9题：

第10题：

第11题：

第12题：

第13题：

Which is a restriction on a list partitioned table？（）

• A、You cannot create global range partitioned indexes on the table. 
• B、The optimizer will not execute partition wise joins on the table’s partitions. 
• C、You must include at least one element in the value list of each partition of the table, even if that element is the keyword NULL. 
• D、Partition pruning will not occur during query optimizer if a range of values is included in the query predicate.

第14题：

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

第15题：

Which two statements are true about the primary key constraint in a table？ （）

• A、It is not possible to disable the primary key constraint.
• B、It is possible to have more than one primary key constraint in a single table.
• C、The primary key constraint can be referred by only one foreign key constraint.
• D、The primary key constraint can be imposed by combining more than one column.
• E、The non-deferrable primary key constraint creates an unique index on the primary key column if it is not already indexed.

第16题：

You need to design a student registration database that contains several tables storing academic information. The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key. You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?（）

• A、CREATE TABLE student_grades (student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY student (student_id));
• B、CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id));
• C、CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES student (student_id));
• D、CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id));

第17题：

Examine the command that is used to create a table：  SQL> CREATE TABLE orders（oid NUMBER（6） PRIMARY KEY， odate DATE， ccode NUMBER（6）， oamt NUMBER（10，2）） TABLESPACE users；  Which two statements are true about the effect of the above command） （）

• A、A CHECK constraint is created on the OID column.
• B、A NOT NULL constraint is created on the OID column.
• C、The ORDERS table is the only object created in the USERS tablespace.
• D、The ORDERS table and a unique index are created in the USERS tablespace.
• E、The ORDERS table is created in the USERS tablespace and a unique index is created on the OIDcolumn in the SYSTEM tablespace.

第18题：

The INV_HISTORY table is created using the command：   SQL>CREATE TABLE INV_HISTORY （inv_no NUMBER（3）， inv_date DATE， inv_amt NUMBER（10，2）） partition by range （inv_date） interval （numtoyminterval（1，’month’）） （partition p0  values less than （to_date（’01-01-2005’，’dd-mm-yyyy’））， partition p1 values less than （to_date（’01-01-2006’，’dd-mm-yyyy’）））；   The following data has been inserted into the INV_HISTORY table ：   INV_NO INV_DATE INV_AMT 1 30-dec-2004 1000 2 30-dec-2005 2000 3 1-feb-2006 3000 4 1-mar-2006 4000 5 1-apr-2006 5000   You would like to store the data belonging to the year 2006 in a single partition and issue the command：   SQL> ALTER TABLE inv_history MERGE PARTITIONS  FOR（TO_DATE（’15-feb-2006’，’dd-mon-yyyy’））， FOR（TO_DATE（’15-apr-2006’）） INTO PARTITION sys_py；  What would be the outcome of this command？（）

• A、 It executes successfully，and the transition point is set to ’1-apr-2006’.
• B、 It executes successfully，and the transition point is set to ’15-apr-2006’.
• C、 It produces an error because the partitions specified for merging are not adjacent.
• D、 It produces an error because the date values specified in the merge do not match the date values stored in the table.

第19题：

Evaluate the following SQL statement used to create the PRODUCTS table：   CREATE TABLE products （product_id NUMBER（3） PRIMARY KEY， product_desc VARCHAR2（25）， qtyNUMBER（8，2）， rate NUMBER（10，2）， total_value AS （ qty * rate）） PARTITION BY RANGE （total_value） （PARTITION p1 VALUES LESS THAN （100000）， PARTITION p2 VALUES LESS THAN  （150000）， PARTITION p3 VALUES LESS THAN （MAXVALUE））  COMPRESS FOR ALL OPERATIONS；   Which statement is true regarding this command？（）  

• A、 It executes successfully but partition pruning cannot happen for this partition key.
• B、 It produces an error because the TOTAL_VALUE column cannot be used as a partition key.
• C、 It produces an error because compression cannot be used for the TOTAL_VALUE partition key. 
• D、 It executes successfully but the values in the TOTAL_VALUE column would not be physically stored in the partitions.

第20题：

第21题：

第22题：

第23题：