Test: Final Exam Semester 2 - Part II (21-30)

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

Part II of the Semester 2 Final Exam covers Sections 10-14 of Database Programming with SQL.

	Section 11
		21.	As user Julie, you issue this statement: CREATE SYNONYM emp FOR sam.employees; Which task was accomplished by this statement?		Mark for Review (1) Points
			You created a public synonym on the EMP table owned by user Sam.
			You created a private synonym on the EMPLOYEES table that you own.
			You created a public synonym on the EMPLOYEES table owned by user Sam.
			You created a private synonym on the EMPLOYEES table owned by user Sam. (*)
				Correct
		22.	Which statement would you use to remove the LAST_NAME_IDX index on the LAST_NAME column of the EMPLOYEES table?		Mark for Review (1) Points
			DROP INDEX last_name_idx; (*)
			DROP INDEX last_name_idx(last_name);
			DROP INDEX last_name_idx(employees.last_name);
			ALTER TABLE employees DROP INDEX last_name_idx;
				Correct
		23.	What would you create to make the following statement execute faster? SELECT * FROM employees WHERE LOWER(last_name) = 'chang';		Mark for Review (1) Points
			A synonym.
			An index, either a normal or a function_based index. (*)
			A composite index.
			Nothing; the performance of this statement cannot be improved.
				Correct
		24.	You need to determine the table name and column name(s) on which the SALES_IDX index is defined. Which data dictionary view would you query?		Mark for Review (1) Points
			USER_INDEXES
			USER_TABLES
			USER_OBJECTS
			USER_IND_COLUMNS (*)
				Correct
		25.	Which statement about an index is true?		Mark for Review (1) Points
			An index can only be created on a single table column.
			Creating an index will always improve query performance.
			Creating an index reorders the data in the underlying table.
			An index created on multiple columns is called a composite or concatenated index. (*)
				Correct
		26.	Barry creates a table named INVENTORY. Pam must be able to query the table. Barry wants to enable Pam to query the table without being required to specify the table's schema. Which of the following should Barry create?		Mark for Review (1) Points
			A schema
			An index
			A view
			A synonym (*)
				Correct
		27.	Unique indexes are automatically created on columns that have which two types of constraints?		Mark for Review (1) Points
			NOT NULL and UNIQUE
			UNIQUE and PRIMARY KEY (*)
			UNIQUE and FOREIGN KEY
			PRIMARY KEY and FOREIGN KEY
				Correct
		28.	The CUSTOMERS table exists in user Mary's schema. Which statement should you use to create a synonym for all database users on the CUSTOMERS table?		Mark for Review (1) Points
			CREATE PUBLIC SYNONYM cust ON mary.customers;
			CREATE PUBLIC SYNONYM cust FOR mary.customers; (*)
			CREATE SYNONYM cust ON mary.customers FOR PUBLIC;
			CREATE SYNONYM cust ON mary.customers; GRANT SELECT ON cust TO PUBLIC;
				Correct
		29.	The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER NOT NULL, Primary Key LAST_NAME VARCHAR2 (20) FIRST_NAME VARCHAR2 (20) DEPARTMENT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCT table HIRE_DATE DATE DEFAULT SYSDATE SALARY NUMBER (8,2) NOT NULL On which column is an index automatically created for the EMPLOYEES table?		Mark for Review (1) Points
			SALARY
			LAST_NAME
			HIRE_DATE
			EMPLOYEE_ID (*)
			DEPARTMENT_ID
				Correct
		30.	Evaluate this statement: CREATE INDEX sales_idx ON oe.sales (status); Which statement is true?		Mark for Review (1) Points
			The CREATE INDEX creates a function-based index.
			The CREATE INDEX statement creates a nonunique index. (*)
			The CREATE INDEX statement creates a unique index.
			The CREATE INDEX statement fails because of a syntax error.
				Correct