SOLOSOLOKU: Section 13 Quiz

Section 13 Quiz
(Answer all questions in this section)

1. Which SQL statement below will correctly create the EMP table based on the structure of the EMPLOYEES table? Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and DEPARTMENT_ID

CREATE TABLE emp
AS SELECT employee_id, first_name, last_name, salary, department_id
FROM employees;(*)

CREATE TABLE emp (employee_id, first_name, last_name, salary, department_id);

CREATE TABLE employee
AS SELECT employee_id, first_name, last_name, salary, department_id
FROM employees;

CREATE TABLE emp
SELECT (employee_id, first_name, last_name, salary, department_id FROM employees);

2. Evaluate this CREATE TABLE statement:
CREATE TABLE line_item ( line_item_id NUMBER(9), order_id NUMBER(9), product_id NUMBER(9));
You are a member of the SYSDBA role, but are logged in under your own schema. You issue this CREATE TABLE statement. Which statement is true?

You created the table in the SYSDBA schema.
You created the LINE_ITEM table in the SYS schema.
You created the LINE_ITEM table in the public schema.
You created the table in your schema. (*)

3. You want to create a table named TRAVEL that is a child of the EMPLOYEES table. Which of the following statements should you issue?

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, emp_id number(10) REFERENCES
employees (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));

4. Which statement about creating a table is true?  

If a schema is explicitly included in a CREATE TABLE statement and the schema does not exist, it will be created.
If no schema is explicitly included in a CREATE TABLE statement, the CREATE TABLE statement will fail.
If no schema is explicitly included in a CREATE TABLE statement, the table is created in the current user's schema. (*)
With a CREATE TABLE statement, a table will always be created in the current user's schema.

5. CREATE TABLE student_table
    (id NUMBER(6),
     lname VARCHAR(20),
     fname VARCHAR(20),
     lunch_num NUMBER(4));
Which of the following statements best describes the above SQL statement:

Creates a table named student_table with four columns: lname, fname, lunch, num
Creates a table named student_table with four columns: lname, fname, lunch, num
Creates a table named student with four columns: id, lname, fname, lunch_num
Creates a table named student_table with four columns: id, lname, fname, lunch_num (*)

Section 13 Quiz
(Answer all questions in this section)

6. Comments on tables and columns can be stored for documentation by:  

Using the COMMENT ON TABLE or COMMENT on COLUMN (*)
Embedding /* comment */ within the definition of the table.
Using an UPDATE statement on the USER_COMMENTS table
Using the ALTER TABLE CREATE COMMENT syntax

7. You need to change the name of the EMPLOYEES table to the EMP table. Which statement should you use?

ALTER TABLE employees TO emp;
RENAME employees emp;
RENAME employees TO emp; (*)
ALTER TABLE employees RENAME TO emp;

8. The TEAMS table contains these columns:
TEAM_ID NUMBER(4) Primary Key
TEAM_NAME VARCHAR2(20)
MGR_ID NUMBER(9)

The TEAMS table is currently empty. You need to allow users to include text characters in the manager identification values. Which statement should you use to implement this?

ALTER TABLE teams
MODIFY (mgr_id VARCHAR2(15)); (*)
ALTER teams TABLE
MODIFY COLUMN (mgr_id VARCHAR2(15));
You CANNOT modify the data type of the MGR_ID column.
ALTER TABLE teams
REPLACE (mgr_id VARCHAR2(15));
ALTER teams
MODIFY (mgr_id VARCHAR2(15));

9. Which of the following will correctly change the name of the LOCATIONS table to NEW_LOCATIONS?  

ALTER TABLE LOCATIONS RENAME NEW_LOCATIONS
MODIFY TABLE LOCATIONS RENAME NEW_LOCATIONS
RENAME LOCATIONS TO NEW_LOCATIONS (*)
 None of the above; you cannot rename a table, you can only CREATE, ALTER and DROP a table.

10. To do a logical delete of a column without the performance penalty of rewriting all the table datablocks, you can issue the following command:

Alter table modify column
Alter table drop column
Drop column "columname"
Alter table set unused (*)

Section 13 Quiz
(Answer all questions in this section)

11. 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?  

INTERVAL YEAR TO MONTH
DATE (*)
TIMESTAMP
CHAR

12. The BLOB datatype can max hold 128 Terabytes of data. True or False?
True (*)
False

13. 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?

TIMESTAMP WITH LOCAL TIME ZONE (*)
TIMESTAMP
TIMESTAMP WITH TIME ZONE
DATETIME

14. To store large amounts of text you should simply create a series of VARCHAR2 columns in a table. True or False?
True
False (*)

15. The TIMESTAMP data type allows what?
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.