section 8

-----------------------------------------------------------------------------------------
Creating Tables
1.Once they are created, external tables are accessed with normal SQL statements? (True or False)
True (*)
False

2.Given this employee table: (employee_id NUMBER(10) NOT NULL,
first_name VARCHAR2(25) NOT NULL,
last_name VARCHAR2(30) NOT NULL,
hire_date DATE DEFAULT sysdate)
What will be the result in the hire_date column following this insert statement:
INSERT INTO employees VALUES (10, 'Natacha', 'Hansen', DEFAULT);


Statement will fail, as you must list the columns into which you are inserting.
Statement will work and the hire_date column will have the value of the date when the statement was run. (*)
The character string SYSDATE.
The column for hire_date will be null.

3.CREATE TABLE bioclass
(hire_date DATE DEFAULT SYSDATE,
first_name varchar2(15),
last_name varchar2(15)); The above CREATE TABLE statement is acceptable, and will create a Table named bioclass that contains a hire_date,
first_name and last_name column. True or False?

True (*)
False

4.When creating a new table, which of the following naming rules apply: (Choose three)
Must begin with a letter (*)
Can have the same name as another object owned by the same user
Must contain ONLY A - Z, a - z, 0 - 9, _ (underscore), $, and # (*)
Must be an Oracle reserved word
Must be between 1 and 30 characters long (*)

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 with four columns: id, lname, fname, lunch_num
creates a table named student_table with four columns: id, lname, fname, lunch_num (*)
creates a table named student_table with four columns: lname, fname, lunch, num

6.  Examine this CREATE TABLE statement: CREATE TABLE emp_load
(employee_number CHAR(5),
employee_dob CHAR(20),
employee_last_name CHAR(20),
employee_first_name CHAR(15),
employee_middle_name CHAR(15),
employee_hire_date DATE)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY def_dir1
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS (employee_number CHAR(2),
employee_dob CHAR(20),
employee_last_name CHAR(18),
employee_first_name CHAR(11),
employee_middle_name CHAR(11),
employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy“))
LOCATION ('info.dat'));
What kind of table is created here?

An external table with the data stored in a file outside the database. (*)
A View.
An external table with the data stored in a file inside the database.
None. This is in invalid statement.

7.  I have a table named School_Friends in my schema. You want to build a table in your schema named School_Friends.
This is ______________, because ____________________________________.

possible; my schema is separate from yours, and it is okay for us to have like-named tables in our separate schemas. (*)
possible; our data will merge into one table, and we can more easily access our mutual friends information.
impossible; no matter what, there can never be two tables with the same name, even if they are in separate schemas.
impossible; School_Friends is a reserved term in SQL.

8.  DCL, which is the acronym for Data Control Language, allows:
the ALTER command to be used.
a Database Administrator the ability to grant privileges to users. (*)
the TRUNCATE command to be used.
the CONTROL TRANSACTION statement can be used.

9.  It is possible to create a table by using the CREATE TABLE command in conjunction with a subquery. True or False?
True (*)
False

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

2. Which of the following are valid Oracle datatypes?

DATE, BLOB, LOB, VARCHAR2
DATE, TIMESTAMP WITH LOCAL TIMEZONE, BLOB (*)
TIMESTAMP, LOB, VARCHAR2, NUMBER
SYSDATE, TIMESTAMP, DATE, LOCAL TIMEZONE

3.The BLOB datatype can hold a maximum of 128 Terabytes of data. True or False?
True (*)

4.INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. True or False?
True (*)
False

-----------------------------------------------------------------------------------------
Modify a table

1.To completely get rid of a table, its contents, its structure, AND release the storage space the keyword is:
DROP (*)
DELETE
TRUNCATE
KILL

2.  The following code creates a table named student_table with four columns: id, lname, fname, lunch_num CREATE TABLE student_table
(id NUMBER(6),
lname VARCHAR(20),
fname VARCHAR(20),
lunch_num NUMBER(4));
The lunch_num column in the above table has been marked as UNUSED. Which of the following is the best statement you can use if you wish to remove the UNUSED column from the student_table?

DROP column
ALTER TABLE DELETE UNUSED COLUMNS
ALTER TABLE DROP UNUSED COLUMNS (*)
ALTER TABLE DELETE ALL COLUMNS

3.  You can use DROP COLUMN to drop all columns in a table, leaving a table structure with no columns. True or False?
True
False (*)

4.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.

5.When should you use the SET UNUSED command?
Never, there is no SET UNUSED command
You should use it if you think the column may be needed again later
You should use it when the system is being heavily used (*)
You should only use this command if you want the column to still be visible when you DESCRIBE the table

6.  Comments can be added to a table by using the COMMENT ON TABLE statement. The comments being added are enclosed in:
Double quotes " "
Single quotes ' ' (*)
Parentheses ( )
Brackets { }

7.  You can use the ALTER TABLE statement to:
Add a new column
Modify an existing column
Drop a column
All of the above (*)

8.  When you use ALTER TABLE to add a column, the new column:
Becomes the first column in the table
Becomes the last column in the table (*)
Can be placed by adding a GROUP BY clause
Will not be created because you cannot add a column after the table is created

9. ALTER TABLE table_name RENAME can be used to:
Rename a row.
Rename a column.
Rename a table. (*)
All of the above.

10. The FLASHBACK TABLE to BEFORE DROP can restore only the table structure, but not its data back to before the table was dropped.  True or False?
 True
 False (*)

11.  After issuing a SET UNUSED command on a column, another column with the same name can be added using an ALTER TABLE statement. True or False?
True (*)
False

12. The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT. True or False?
True
False (*)

13.A column?s data type can always be changed from NUMBER to VARCHAR2 but not from VARCHAR2 to NUMBER, provided the table is empty. True or False?
True
False (*)

14.  The data type of a column can never be changed once it has been created. True or False?
True
False (*)