Test: Final Exam Semester 2 - Part I (11-20)

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.	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
		12.	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
		13.	Evaluate this CREATE TABLE statement: CREATE TABLE sales (sales_id NUMBER, customer_id NUMBER, employee_id NUMBER, sale_date TIMESTAMP WITH LOCAL TIME ZONE, sale_amount NUMBER(7,2)); Which statement about the SALE_DATE column is true?		Mark for Review (1) Points
			Data will be normalized to the client time zone.
			Data stored will not include seconds.
			Data will be stored using a fractional seconds precision of 5.
			Data stored in the column will be returned in the database's local time zone. (*)
				Correct
		14.	You need to truncate the EMPLOYEES table. The EMPLOYEES table is not in your schema. Which privilege must you have to truncate the table?		Mark for Review (1) Points
			The DROP ANY TABLE system privilege (*)
			The TRUNCATE ANY TABLE system privilege
			The CREATE ANY TABLE system privilege
			The ALTER ANY TABLE system privilege
				Correct
		15.	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
		16.	Your supervisor has asked you to modify the AMOUNT column in the ORDERS table. He wants the column to be configured to accept a default value of 250. The table constains data that you need to keep. Which statement should you issue to accomplish this task?		Mark for Review (1) Points
			ALTER TABLE orders CHANGE DATATYPE amount TO DEFAULT 250;
			ALTER TABLE orders MODIFY (amount DEFAULT 250); (*)
			DROP TABLE orders; CREATE TABLE orders (orderno varchar2(5) CONSTRAINT pk_orders_01 PRIMARY KEY, customerid varchar2(5) REFERENCES customers (customerid), orderdate date, amount DEFAULT 250);
			DELETE TABLE orders; CREATE TABLE orders (orderno varchar2(5) CONSTRAINT pk_orders_01 PRIMARY KEY, customerid varchar2(5) REFERENCES customers (customerid), orderdate date, amount DEFAULT 250)
				Correct
		17.	Comments on tables and columns can be stored for documentation by:		Mark for Review (1) Points
			Embedding /* comment */ within the definition of the table.
			Using the ALTER TABLE CREATE COMMENT syntax
			Using the COMMENT ON TABLE or COMMENT on COLUMN (*)
			Using an UPDATE statement on the USER_COMMENTS table
				Correct
		18.	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
				Correct
		19.	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
		20.	The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(9) Primary Key LAST_NAME VARCHAR2 (20) FIRST_NAME VARCHAR2 (20) DEPARTMENT_ID NUMBER(9) SALARY NUMBER(8,2) Which statement will permanently remove all the data in the EMPLOYEES table, but will retain the table's structure and storage space?		Mark for Review (1) Points
			DROP TABLE employees;
			DELETE employees; COMMIT; (*)
			TRUNCATE TABLE employees;
			ALTER TABLE employees SET UNUSED (employee_id, last_name, first_name, department_id, salary);
				Correct