PROGRAMMING: Section 8

CREATING TABLES
Once they are created, external tables are accessed with normal SQL statements?
True
          
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.
          
When creating a new table, which of the following naming rules apply.
Must begin with a letter
Must contain ONLY A - Z, a - z, 0 - 9, _ (underscore), $, and #
Must be between 1 to 30 characters long
         
It is possible to create a table by using the CREATE TABLE command in conjunction with a subquery. True or False?
True
          
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: id, lname, fname, lunch_num
          
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
         
DCL, which is the acronym for Data Control Language, allows:
A Database Administrator the ability to grant privileges to users.
          
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 work and the hire_date column will have the value of the date when the statement was run.
          
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.
            

USING DATA TYPES
INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. True or False?
True
          
Which of the following are valid Oracle datatypes?
DATE, TIMESTAMP WITH LOCAL TIMEZONE, BLOB
          
To store large amounts of text you should simply create a series of VARCHAR2 columns in a table. True or False?
False
          
The BLOB datatype can max hold 128 Terabytes of data. True or False?
True
          

 MODIFYING A TABLE

ALTER TABLE table_name RENAME can be used to:
Rename a table.
          
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?
False
          
Comments can be added to a table by using the COMMENT ON TABLE statement. The comments being added are enclosed in:
Single quotes ' '
          
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?
False
          
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
          
Which of the following will correctly change the name of the LOCATIONS table to NEW_LOCATIONS?  
RENAME LOCATIONS TO NEW_LOCATIONS
          
You can use the ALTER TABLE statement to:
All of the above
            
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?
ALTER TABLE DROP UNUSED COLUMNS
          
The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT? True or False?
False
          
To completely get rid of a table, its contents, its structure, AND release the storage space the keyword is:
DROP
          
When should you use the SET UNUSED command?
You should use it when the system is being heavily used
          
You can use DROP COLUMN to drop all columns in a table, leaving a table structure with no columns. True or False?
False
          
The data type of a column can never be changed once it has been created. True or False?
False
          
When you use ALTER TABLE to add a column, the new column:
Becomes the last column in the table