Test: Final Exam Semester 2 - Part I 
 
 

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

 Part I of the Semester 2 Final Exam covers Sections 8-9 of Database Programming with SQL.  


 Section 8 
      
  1.  Evaluate the structure of the EMPLOYEES table: 
EMPLOYEE_ID NUMBER(9) 
LAST_NAME VARCHAR2(25) 
FIRST_NAME VARCHAR2(25) 
DEPARTMENT_ID NUMBER(9) 
MANAGER_ID NUMBER(9) 
SALARY NUMBER(7,2) 

Which statement should you use to increase the LAST_NAME column length to 35 if the column currently contains 200 records? 
 Mark for Review 
(1) Points 
      
    ALTER employees TABLE ALTER COLUMN (last_name VARCHAR2(35));
 
  
    ALTER TABLE employees RENAME last_name VARCHAR2(35); 
 
  
    ALTER TABLE employees MODIFY (last_name VARCHAR2(35)); 
(*)
 
  
    You CANNOT increase the width of the LAST_NAME column. 
  
      
      Correct  
  
      
  2.  The PLAYERS table contains these columns: 
PLAYER_ID NUMBER(9) PRIMARY KEY 
LAST_NAME VARCHAR2(20) 
FIRST_NAME VARCHAR2(20) 
TEAM_ID NUMBER(4) 
SALARY NUMBER(9,2) 

Which statement should you use to decrease the width of the FIRST_NAME column to 10 if the column currently contains 1500 records, but none are longer than 10 bytes or characters? 
 Mark for Review 
(1) Points 
      
    ALTER players TABLE 
MODIFY COLUMN first_name VARCHAR2(10); 
 
  
    ALTER players TABLE 
MODIFY COLUMN (first_name VARCHAR2(10)); 
 
  
    ALTER TABLE players 
RENAME first_name VARCHAR2(10); 
 
  
    ALTER TABLE players 
MODIFY (first_name VARCHAR2(10));
(*) 
  
      
      Correct  
  
      
  3.  You want to issue the following command on a database that includes your company's inventory information: 
ALTER TABLE products SET UNUSED COLUMN color; 
What will be the result of issuing this command? 
 Mark for Review 
(1) Points 
      
    The column named COLOR in the table named PRODUCTS will be assigned default values. 
  
    The column named COLOR in the table named PRODUCTS will be created. 
  
    The column named COLOR in the table named PRODUCTS will be deleted. 
  
    The column named COLOR in the table named PRODUCTS will not be returned in subsequent reads of the table by Oracle, as is has been deleted logically. (*) 
  
      
      Correct  
  
      
  4.  Which command could you use to quickly remove all data from the rows in a table without deleting the table itself?  Mark for Review 
(1) Points 
      
    ALTER TABLE 
  
    DROP TABLE 
  
    MODIFY 
  
    TRUNCATE TABLE (*) 
  
      
      Correct  
  
      
  5.  Evaluate this statement: 
TRUNCATE TABLE employees; 
Which statement about this TRUNCATE TABLE statement is true? 
 Mark for Review 
(1) Points 
      
    You can produce the same results by issuing the 'DROP TABLE employee' statement. 
  
    You can issue this statement to retain the structure of the employees table. (*) 
  
    You can reverse this statement by issuing the ROLLBACK statement. 
  
    You can produce the same results by issuing the 'DELETE employees' statement. 
  
      
      Correct  
  
      
  6.  You need to remove all the rows from the SALES_HIST table. You want to release the storage space, but do not want to remove the table structure. Which statement should you use?  Mark for Review 
(1) Points 
      
    The DROP TABLE statement 
  
    The ALTER TABLE statement 
  
    The DELETE statement 
  
    The TRUNCATE TABLE statement (*) 
  
      
      Correct  
  
      
  7.  To do a logical delete of a column without the performance penalty of rewriting all the table datablocks you can issue the following command:  Mark for Review 
(1) Points 
      
    Alter table modify column 
  
    Alter table drop column 
  
    Alter table set unused (*) 
  
    Drop column "columname" 
  
      
      Correct  
  
      
  8.  You need to change the name of the EMPLOYEES table to the EMP table. Which statement should you use?  Mark for Review 
(1) Points 
      
    RENAME employees emp; 
  
    RENAME employees TO emp; (*) 
  
    ALTER TABLE employees TO emp; 
  
    ALTER TABLE employees RENAME TO emp; 
  
      
      Correct  
  
      
  9.  Examine the structure of the DONATIONS table. 
DONATIONS: 
PLEDGE_ID NUMBER 
DONOR_ID NUMBER 
PLEDGE_DT DATE 
AMOUNT_PLEDGED NUMBER (7,2) 
AMOUNT_PAID NUMBER (7,2) 
PAYMENT_DT DATE 

You need to reduce the precision of the AMOUNT_PLEDGED column to 5 with a scale of 2 and ensure that when inserting a row into the DONATIONS table without a value for the AMOUNT_PLEDGED column, a price of $10.00 will automatically be inserted. The DONATIONS table currently contains NO records. Which statement is true? 
 Mark for Review 
(1) Points 
      
    You CANNOT decrease the width of the AMOUNT_PLEDGED column. 
  
    Both changes can be accomplished with one ALTER TABLE statement. (*) 
  
    You must drop and recreate the DONATIONS table to achieve these results. 
  
    You must use the ADD OR REPLACE option to achieve these results. 
  
      
      Correct  
  
      
  10.  The previous administrator created a table named CONTACTS, which contains outdated data. You want to remove the table and its data from the database. Which statement should you issue?  Mark for Review 
(1) Points 
      
    DROP TABLE (*) 
  
    DELETE 
  
    TRUNCATE TABLE 
  
    ALTER TABLE 
  
      
      Incorrect. Refer to Section 8 Lesson 3  
  
      

Page 1 of 5   

 
  
 
 

Test: Final Exam Semester 2 - Part I 
 
 

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

 Part I of the Semester 2 Final Exam covers Sections 8-9 of Database Programming with SQL.  


 Section 8 
      
  11.  The EMPLOYEES table contains these columns: 
LAST_NAME VARCHAR2(15) NOT NULL 
FIRST_NAME VARCHAR2(10) NOT NULL 
EMPLOYEE_ID NUMBER(4) NOT NULL 
HIRE_DATE DATE NOT NULL 

You need to remove the EMPLOYEE_ID column from the EMPLOYEES table. Which statement could you use to accomplish this task? 
 Mark for Review 
(1) Points 
      
    ALTER TABLE employees 
MODIFY (employee_id NUMBER(5));
 
  
    ALTER TABLE employees 
DELETE employee_id;
 
  
    ALTER TABLE employees
DROP COLUMN employee_id;
(*)
 
  
    DELETE FROM employees 
WHERE column = employee_id;
 
  
      
      Incorrect. Refer to Section 8 Lesson 3  
  
      
  12.  You need to store the HIRE_DATE value with a time zone displacement value and allow data to be returned in the user's local session time zone. Which data type should you use?  Mark for Review 
(1) Points 
      
    DATETIME 
  
    TIMESTAMP 
  
    TIMESTAMP WITH TIME ZONE 
  
    TIMESTAMP WITH LOCAL TIME ZONE (*) 
  
      
      Correct  
  
      
  13.  You are designing a table for the Sales department. You need to include a column that contains each sales total. Which data type should you specify for this column?  Mark for Review 
(1) Points 
      
    CHAR 
  
    DATE 
  
    NUMBER (*) 
  
    VARCHAR2 
  
      
      Correct  
  
      
  14.  You are designing a table for the Human Resources department. This table must include a column that contains each employee's hire date. Which data type should you specify for this column?  Mark for Review 
(1) Points 
      
    CHAR 
  
    DATE (*) 
  
    TIMESTAMP 
  
    INTERVAL YEAR TO MONTH 
  
      
      Correct  
  
      
  15.  The TIMESTAMP data type allows what?  Mark for Review 
(1) Points 
      
    Time to be stored as an interval of years and months. 
  
    Time to be stored as a date with fractional seconds. (*) 
  
    Time to be stored as an interval of days to hours, minutes and seconds. 
  
    None of the above. 
  
      
      Incorrect. Refer to Section 8  
  
      
  16.  Which data types stores variable-length character data? Select two.  Mark for Review 
(1) Points 
      
   (Choose all correct answers)  
      
    CHAR 
  
    NCHAR 
  
    CLOB (*) 
  
    VARCHAR2 (*) 
  
      
      Correct  
  
      
  17.  Which statement about data types is true?  Mark for Review 
(1) Points 
      
    The BFILE data type stores character data up to four gigabytes in the database. 
  
    The TIMESTAMP data type is a character data type. 
  
    The VARCHAR2 data type should be used for fixed-length character data. 
  
    The CHAR data type requires that a minimum size be specified when defining a column of this type. (*) 
  
      
      Correct  
  
      
  18.  You need to store the SEASONAL data in months and years. Which data type should you use?  Mark for Review 
(1) Points 
      
    DATE 
  
    TIMESTAMP 
  
    INTERVAL YEAR TO MONTH (*) 
  
    INTERVAL DAY TO SECOND 
  
      
      Correct  
  
      
  19.  You want to create a table named TRAVEL that is a child of the EMPLOYEES table. Which of the following statements should you issue?  Mark for Review 
(1) Points 
      
    CREATE TABLE travel 
(destination_id primary key, departure_date date, return_date date, emp_id REFERENCES employees (emp_id)); 
 
  
    CREATE TABLE travel 
(destination_id number primary key, departure_date date, return_date date, t.emp_id = e.emp_id); 
 
  
    CREATE TABLE travel 
(destination_id number primary key, departure_date date, return_date date, JOIN emp_id number(10) ON employees (emp_id)); 
 
  
    CREATE TABLE travel 
(destination_id number primary key, departure_date date, return_date date, emp_id number(10) REFERENCES employees (emp_id)); 
(*)
 
  
      
      Correct  
  
      
  20.  You are creating the EMPLOYEES table. This table should contain the COMMISSION_PCT column and use a value of 10 percent if no commission value is provided when a record is inserted. Which line should you include in the CREATE TABLE statement to accomplish this task?  Mark for Review 
(1) Points 
      
    commission_pct NUMBER(4,2) DEFAULT 0.10 (*) 
  
    commission_pct NUMBER(4,2) DEFAULT = 0.10 
  
    commission_pct NUMBER(4,2) DEFAULT (0.10) 
  
    commission_pct NUMBER(4,2) (DEFAULT, 0.10) 
  
      
      Correct  
  
      

 Page 2 of 5   

 
  
 
 

Test: Final Exam Semester 2 - Part I 
 
 

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

 Part I of the Semester 2 Final Exam covers Sections 8-9 of Database Programming with SQL.  


 Section 8 
      
  21.  Which column name is valid?  Mark for Review 
(1) Points 
      
    1NUMBER 
  
    NUMBER 
  
    NUMBER_1$ (*) 
  
    1_NUMBER# 
  
      
      Incorrect. Refer to Section 8  
  
      
  22.  Which statement about table and column names is true?  Mark for Review 
(1) Points 
      
    Table and column names must begin with a letter. (*) 
  
    Table and column names can begin with a letter or a number. 
  
    Table and column names cannot include special characters. 
  
    If any character other than letters or numbers is used in a table or column name, the name must be enclosed in double quotation marks. 
  
      
      Correct  
  
      
  23.  Which CREATE TABLE statement will fail?  Mark for Review 
(1) Points 
      
    CREATE TABLE date_1 (date_1 DATE); 
  
    CREATE TABLE date (date_id NUMBER(9)); (*) 
  
    CREATE TABLE time (time_id NUMBER(9)); 
  
    CREATE TABLE time_date (time NUMBER(9)); 
  
      
      Correct  
  
      
 
      
 Section 9 
      
  24.  Which statement about a FOREIGN KEY constraint is true?  Mark for Review 
(1) Points 
      
    An index is automatically created for a FOREIGN KEY constraint. 
  
    A FOREIGN KEY constraint requires the constrained column to contain values that exist in the referenced Primary or Unique key column of the parent table. (*) 
  
    A FOREIGN KEY constraint allows that a list of allowed values be checked before a value can be added to the constrained column. 
  
    A FOREIGN KEY column can have a different data type from the primary key column that it references. 
  
      
      Correct  
  
      
  25.  Which clause could you use to ensure that cost values are greater than 1.00?  Mark for Review 
(1) Points 
      
    CONSTRAINT CHECK cost > 1.00 
  
    CONSTRAINT part_cost_ck CHECK (cost > 1.00) (*) 
  
    CHECK CONSTRAINT part_cost_ck (cost > 1.00) 
  
    CONSTRAINT CHECK part_cost_ck (cost > 1.00) 
  
      
      Correct  
  
      
  26.  Evaluate this CREATE TABLE statement: 

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)); 
Which line will cause an error? 
 Mark for Review 
(1) Points 
      
    6 
  
    7 
  
    8 (*) 
  
    9 
  
      
      Correct  
  
      
  27.  When creating the EMPLOYEES table, which clause could you use to ensure that salary values are 1000.00 or more?  Mark for Review 
(1) Points 
      
    CONSTRAINT CHECK salary > 1000 
  
    CHECK CONSTRAINT (salary > 1000) 
  
    CONSTRAINT employee_salary_min CHECK salary > 1000 
  
    CONSTRAINT employee_salary_min CHECK (salary >= 1000) (*) 
  
    CHECK CONSTRAINT employee_salary_min (salary > 1000) 
  
      
      Correct  
  
      
  28.  Evaluate the structure of the DONATIONS table. 
DONATIONS 
PLEDGE_ID NUMBER NOT NULL, Primary Key 
DONOR_ID NUMBER Foreign key to DONOR_ID column of DONORS table 
PLEDGE_DT DATE 
AMOUNT_PLEDGED NUMBER (7,2) 
AMOUNT_PAID NUMBER (7,2) 
PAYMENT_DT DATE 

Which CREATE TABLE statement should you use to create the DONATIONS table? 
 Mark for Review 
(1) Points 
      
    CREATE TABLE donations 
(pledge_id NUMBER PRIMARY KEY,
donor_id NUMBER FOREIGN KEY REFERENCES donors(donor_id), 
pledge_date DATE, 
amount_pledged NUMBER, 
amount_paid NUMBER, 
payment_dt DATE); 
 
  
    CREATE TABLE donations 
(pledge_id NUMBER PRIMARY KEY NOT NULL, 
donor_id NUMBER FOREIGN KEY donors(donor_id), 
pledge_date DATE, 
amount_pledged NUMBER(7,2), 
amount_paid NUMBER(7,2), 
payment_dt DATE);
 
  
    CREATE TABLE donations 
pledge_id NUMBER PRIMARY KEY, 
donor_id NUMBER FOREIGN KEY donor_id_fk REFERENCES donors(donor_id), 
pledge_date DATE, 
amount_pledged NUMBER(7,2), 
amount_paid NUMBER(7,2), 
payment_dt DATE; 
 
  
    CREATE TABLE donations 
(pledge_id NUMBER PRIMARY KEY, 
donor_id NUMBER CONSTRAINT donor_id_fk REFERENCES donors(donor_id), pledge_date DATE, 
amount_pledged NUMBER(7,2), 
amount_paid NUMBER(7,2), 
payment_dt DATE); 
(*)
 
  
      
      Correct  
  
      
  29.  What is an attribute of data that is entered into a primary key column?  Mark for Review 
(1) Points 
      
    Null and non-unique values cannot be entered into a primary key column. (*) 
  
    Data that is entered into a primary key column automatically increments by a value of 1 each time a new record is entered into the table. 
  
    Data that is entered into a primary key column references a column of the same datatype in another table. 
  
    Data that is entered into a primary key column is restricted to a range of numbers that is defined by the local Oracle database. 
  
      
      Correct  
  
      
  30.  Which of the following best describes the function of a CHECK constraint?  Mark for Review 
(1) Points 
      
    A CHECK constraint enforces referential data integrity. 
  
    A CHECK constraint defines restrictions on the values that can be entered in a column or combination of columns. (*) 
  
    A CHECK constraint enforces uniqueness of the values that can be entered in a column or combination of columns. 
  
    A CHECK constraint is created automatically when a PRIMARY KEY constraint is created. 
  
      
      Correct  
  
      

 Page 3 of 5   

 
  
 
 

Test: Final Exam Semester 2 - Part I 
 
 

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

 Part I of the Semester 2 Final Exam covers Sections 8-9 of Database Programming with SQL.  


 Section 9 
      
  31.  You need to enforce a relationship between the LOC_ID column in the FACILITY table and the same column in the MANUFACTURER table. Which type of constraint should you define on the LOC_ID column?  Mark for Review 
(1) Points 
      
    UNIQUE 
  
    NOT NULL 
  
    FOREIGN KEY (*) 
  
    PRIMARY KEY 
  
      
      Incorrect. Refer to Section 9  
  
      
  32.  When creating a referential constraint, which keyword(s) identifies the table and column in the parent table?  Mark for Review 
(1) Points 
      
    FOREIGN KEY 
  
    REFERENCES (*) 
  
    ON DELETE CASCADE 
  
    ON DELETE SET NULL 
  
      
      Correct  
  
      
  33.  You need to ensure that the LAST_NAME column only contains certain character values. No numbers or special characters are allowed. Which type of constraint should you define on the LAST_NAME column?  Mark for Review 
(1) Points 
      
    CHECK (*) 
  
    UNIQUE 
  
    NOT NULL 
  
    PRIMARY KEY 
  
      
      Correct  
  
      
  34.  Evaluate this CREATE TABLE statement: 
CREATE TABLE customers 
( customer_id NUMBER, customer_name VARCHAR2(25), 
address VARCHAR2(25), 
city VARCHAR2(25), 
region VARCHAR2(25), 
postal_code VARCHAR2(11), 
CONSTRAINT customer_id_un UNIQUE(customer_id), 
CONSTRAINT customer_name_nn NOT NULL(customer_name)); 

Why does this statement fail when executed? 
 Mark for Review 
(1) Points 
      
    The NUMBER data types require precision values. 
  
    UNIQUE constraints must be defined at the column level. 
  
    The CREATE TABLE statement does NOT define a PRIMARY KEY. 
  
    NOT NULL constraints CANNOT be defined at the table level. (*) 
  
      
      Correct  
  
      
  35.  Which constraint can only be created at the column level?  Mark for Review 
(1) Points 
      
    NOT NULL (*) 
  
    FOREIGN KEY 
  
    UNIQUE 
  
    CHECK 
  
      
      Correct  
  
      
  36.  Primary Key, Foreign Key, Unique Key and Check Constraints can be added at which two levels? (Choose two)  Mark for Review 
(1) Points 
      
   (Choose all correct answers)  
      
    Null Field 
  
    Table (*) 
  
    Row 
  
    Dictionary 
  
    Column (*) 
  
      
      Correct  
  
      
  37.  Which two statements about NOT NULL constraints are true? (Choose two)  Mark for Review 
(1) Points 
      
   (Choose all correct answers)  
      
    The Oracle Server creates a name for an unnamed NOT NULL constraint. (*) 
  
    A NOT NULL constraint can be defined at either the table or column level. 
  
    The NOT NULL constraint requires that every value in a column be unique. 
  
    Columns without the NOT NULL constraint can contain null values by default. 
  
    You CANNOT add a NOT NULL constraint to an existing column using the ALTER TABLE ADD CONSTRAINT statement. (*) 
  
      
      Correct  
  
      
  38.  Which statement about constraints is true?  Mark for Review 
(1) Points 
      
    A single column can have only one constraint applied. 
  
    PRIMARY KEY constraints can only be specified at the column level. 
  
    NOT NULL constraints can only be specified at the column level. (*) 
  
    UNIQUE constraints are identical to PRIMARY KEY constraints. 
  
      
      Correct  
  
      
  39.  You need to ensure that the LAST_NAME column does not contain null values. Which type of constraint should you define on the LAST_NAME column?  Mark for Review 
(1) Points 
      
    CHECK 
  
    UNIQUE 
  
    NOT NULL (*) 
  
    PRIMARY KEY 
  
      
      Correct  
  
      
  40.  What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?  Mark for Review 
(1) Points 
      
    ALTER TABLE table_name 
DROP CONSTRAINT constraint_name CASCADE; 
(*)
 
  
    ALTER TABLE table_name 
DROP CONSTRAINT FOREIGN KEY CASCADE; 
 
  
    DROP CONSTRAINT table_name (constraint_name); 
 
  
    ALTER TABLE table_name 
DROP CONSTRAINT constraint_name; 
 
  
      
      Correct  
  
      

 Page 4 of 5   

 
  
 
 

Test: Final Exam Semester 2 - Part I 
 
 

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

 Part I of the Semester 2 Final Exam covers Sections 8-9 of Database Programming with SQL.  


 Section 9 
      
  41.  This SQL command will do what? 
ALTER TABLE employees 
ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id); 

 Mark for Review 
(1) Points 
      
    Alter the table employees and disable the emp_manager_fk constraint. 
  
    Add a FOREIGN KEY constraint to the EMPLOYEES table indicating that a manager must already be an employee. (*) 
  
    Add a FOREIGN KEY constraint to the EMPLOYEES table restricting manager ID to match every employee ID. 
  
    Alter table employees and add a FOREIGN KEY constraint that indicates each employee ID must be unique. 
  
      
      Correct  
  
      
  42.  You need to remove the EMP_FK_DEPT constraint from the EMPLOYEES table in your schema. Which statement should you use?  Mark for Review 
(1) Points 
      
    DROP CONSTRAINT EMP_FK_DEPT FROM employees; 
  
    DELETE CONSTRAINT EMP_FK_DEPT FROM employees; 
  
    ALTER TABLE employees DROP CONSTRAINT EMP_FK_DEPT; (*) 
  
    ALTER TABLE employees REMOVE CONSTRAINT EMP_FK_DEPT; 
  
      
      Correct  
  
      
  43.  You need to add a PRIMARY KEY constraint on the EMP_ID column of the EMPLOYEES table. Which ALTER TABLE statement should you use?  Mark for Review 
(1) Points 
      
    ALTER TABLE employees 
ADD CONSTRAINT PRIMARY KEY (emp_id); 
(*)
 
  
    ALTER TABLE 
ADD CONSTRAINT emp_emp_id_pk PRIMARY KEY employees(emp_id); 
 
  
    ALTER TABLE employees 
MODIFY emp_id PRIMARY KEY; 
 
  
    ALTER TABLE employees 
MODIFY CONSTRAINT PRIMARY KEY (emp_id); 
 
  
      
      Incorrect. Refer to Section 9  
  
      
  44.  You successfully create a table named SALARY in your company's database. Now, you want to establish a parent/child relationship between the EMPLOYEES table and the SALARY table by adding a FOREIGN KEY constraint to the SALARY table that references its matching column in the EMPLOYEES table. You have not added any data to the SALARY table. Which of the following statements should you issue?  Mark for Review 
(1) Points 
      
    ALTER TABLE salary 
ADD CONSTRAINT fk_employee_id_01 FOREIGN KEY (employee_id) 
REFERENCES employees (employee_id); 
(*)
 
  
    ALTER TABLE salary 
ADD CONSTRAINT fk_employee_id_ FOREIGN KEY 
BETWEEN salary (employee_id) AND employees (employee_id); 
 
  
    ALTER TABLE salary 
FOREIGN KEY CONSTRAINT fk_employee_id_ REFERENCES employees (employee_id); 
 
  
    ALTER TABLE salary 
ADD CONSTRAINT fk_employee_id_ FOREIGN KEY salary (employee_id) = employees (employee_id); 
 
  
      
      Incorrect. Refer to Section 9  
  
      
  45.  Evaluate this statement:
ALTER TABLE employees 
ADD CONSTRAINT employee_id PRIMARY KEY; 

Which result will the statement provide? 
 Mark for Review 
(1) Points 
      
    A syntax error will be returned. (*) 
  
    A constraint will be added to the EMPLOYEES table. 
  
    An existing constraint on the EMPLOYEES table will be overwritten. 
  
    An existing constraint on the EMPLOYEES table will be enabled. 
  
      
      Correct  
  
      
  46.  The LINE_ITEM table contains these columns: 
LINE_ITEM_ID NUMBER PRIMARY KEY
PRODUCT_ID NUMBER(9) FOREIGN KEY references the ID column of the PRODUCT table
QUANTITY NUMBER(9)
UNIT_PRICE NUMBER(5,2)

You need to disable the FOREIGN KEY constraint. Which statement should you use? 
 Mark for Review 
(1) Points 
      
    ALTER TABLE line_item DISABLE CONSTRAINT product_id_fk; (*) 
  
    ALTER TABLE line_item DROP CONSTRAINT product_id_fk; 
  
    ALTER TABLE line_item ENABLE CONSTRAINT product_id_fk; 
  
    ALTER TABLE line_item DELETE CONSTRAINT product_id_fk; 
  
      
      Incorrect. Refer to Section 9  
  
      
  47.  The PO_DETAILS table contains these columns: 
PO_NUM NUMBER NOT NULL, Primary Key 
PO_LINE_ID NUMBER NOT NULL, Primary Key 
PRODUCT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCTS table 
QUANTITY NUMBER 
UNIT_PRICE NUMBER(5,2) 

Evaluate this statement: 

ALTER TABLE po_details 
DISABLE CONSTRAINT product_id_pk CASCADE; For which task would you issue this statement? 
 Mark for Review 
(1) Points 
      
    To create a new PRIMARY KEY constraint on the PO_NUM column 
  
    To drop and recreate the PRIMARY KEY constraint on the PO_NUM column 
  
    To disable the PRIMARY KEY and any FOREIGN KEY constraints that are dependent on the PO_NUM column (*) 
  
    To disable the constraint on the PO_NUM column while creating a PRIMARY KEY index 
  
      
      Correct  
  
      
  48.  You disabled the EMPLOYEE_ID_PK PRIMARY KEY constraint on the ID column in the EMPLOYEES table and imported 100 records. You need to enable the constraint and verify that the new and existing ID column values do not violate the PRIMARY KEY constraint. Evaluate this statement: 
ALTER TABLE employees 
ENABLE employee_id_pk; 

Which statement is true? 
 Mark for Review 
(1) Points 
      
    The statement will achieve the desired result. 
  
    The statement will execute, but will ensure that the new ID values are unique. 
  
    The statement will execute, but will not verify that the existing values are unique. 
  
    The statement will NOT execute because it contains a syntax error. (*) 
  
      
      Correct  
  
      
  49.  The DEPARTMENTS table contains these columns:
DEPARTMENT_ID NUMBER, Primary Key 
DEPARTMENT_ABBR VARCHAR2(4) 
DEPARTMENT_NAME VARCHAR2(30) 
MANAGER_ID NUMBER 
The EMPLOYEES table contains these columns: 
EMPLOYEE_ID NUMBER 
LAST_NAME VARCHAR2(25) 
FIRST_NAME VARCHAR2(25) 
DEPARTMENT_ID NUMBER 
JOB_ID NUMBER 
MANAGER_ID NUMBER 
SALARY NUMBER(9,2) 
HIRE_DATE DATE 

Evaluate this statement: 

ALTER TABLE employees 
ADD CONSTRAINT REFERENTIAL (manager_id) TO departments(manager_id); 

Which statement is true? 
 Mark for Review 
(1) Points 
      
    The ALTER TABLE statement creates a referential constraint from the EMPLOYEES table to the DEPARTMENTS table. 
  
    The ALTER TABLE statement creates a referential constraint from the DEPARTMENTS table to the EMPLOYEES table. 
  
    The ALTER TABLE statement fails because the ADD CONSTRAINT clause contains a syntax error. (*) 
  
    The ALTER TABLE statement succeeds, but does NOT recreate a referential constraint. 
  
      
      Correct  
  
      
  50.  You need to display the names and definitions of constraints only in your schema. Which data dictionary view should you query?  Mark for Review 
(1) Points 
      
    DBA_CONSTRAINTS 
  
    USER_CONSTRAINTS (*) 
  
    ALL_CONS_COLUMNS 
  
    USER_CONS_COLUMNS 
  
      
      Correct  
  
      

 Page 5 of 5