Test: Final Exam Semester 2 - Part I (31-40)

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.	Which of the following types of constraints enforces uniqueness?		Mark for Review (1) Points
			CHECK
			FOREIGN KEY
			PRIMARY KEY (*)
			NOT NULL
				Correct
		32.	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
		33.	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
		34.	You need to add a NOT NULL constraint to the EMAIL column in the EMPLOYEES table. Which clause should you use?		Mark for Review (1) Points
			ADD
			CHANGE
			MODIFY (*)
			DISABLE
				Correct
		35.	You can view the columns used in a constraint defined for a specific table by looking at which data dictionary table?		Mark for Review (1) Points
			USER_CONS_COLUMNS (*)
			CONSTRAINTS_ALL_COLUMNS
			SYS_DATA_DICT_COLUMNS
			US_CON_SYS
				Correct
		36.	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);
				Correct
		37.	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
		38.	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
		39.	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
		40.	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;
				Correct