Hour 3, "Managing Database Objects"

Quiz Answers

1: Will the following CREATE TABLE statement work? If not, what needs to be done to correct the problem(s)?

CREATE TABLE EMPLOYEE_TABLE AS:

   ( SSN             NUMBER(9)       NOT NULL,
    LAST_NAME        VARCHAR2(20)    NOT NULL,
    FIRST_NAME       VARCHAR2(20)    NOT NULL,
    MIDDLE_NAME      VARCHAR2(20)    NOT NULL,
    ST ADDRESS       VARCHAR2(30)    NOT NULL,
    CITY             CHAR(20)        NOT NULL,
    STATE            CHAR2)          NOT NULL,
    ZIP              NUMBER(4)       NOT NULL,
    DATE HIRED       DATE)
    STORAGE
        (INITIAL        3K,
         NEXT        1K);

A1: The CREATE TABLE statement will not work because there are several errors in the syntax. The corrected statement follows. A listing of what was incorrect follows a corrected statement.
CREATE TABLE EMPLOYEE_TABLE
 ( SSN        NUMBER()    NOT NULL,
LAST_NAME    VARCHAR2(20)    NOT NULL,
FIRST_NAME    VARCHAR2(20)    NOT NULL,
MIDDLE_NAME    VARCHAR2(20),
ST_ADDRESS    VARCHAR2(30)    NOT NULL,
CITY        VARCHAR2(20)    NOT NULL,
STATE        CHAR(2)        NOT NULL,
ZIP        NUMBER(5)    NOT NULL,
DATE_HIRED    DATE )
STORAGE
(INITIAL        3k
NEXT                1k);

The following needs to be done:

  1. The as: should not be in this CREATE TABLE statement.

  2. Missing a comma after the NOT NULL for the last_name column.

  3. The MIDDLE_NAME column should be NULL because not everyone has a middle name.

  4. The column ST ADDRESS should be ST_ADDRESS. Being two words, the database looked at ST as being the column name, which would make the database look for a valid data type, where it would find the word ADDRESS.

  5. The city column works, although it would be better to use the VARCHAR2 data type. If all city names were constant length, CHAR would be okay.

  6. The STATE column is missing a left parenthesis.

  7. The ZIP column length should be (5), not (4).

  8. The DATE HIRED column should be DATE_HIRED with an underscore to make the column name one continuous string.

  9. The comma after 3k in the STORAGE clause should not be there.

2: Can you drop a column from a table?
A2: Yes. However, even though it is an ANSI standard, you must check your particular implementation to see if it has been accepted.
3: What happens if you do not include the STORAGE clause in the CREATE TABLE statement?
A3: The CREATE TABLE statement should process, barring any syntax errors of course; however, most implementations have a default sizing. Check your particular implementation for the sizing.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset