The reset function affects all variables, including system variables. For this reason, you should be careful when you use reset
CREATE TABLE EMPLOYEE_TABLE AS: ( SSN NUMBER(9) NOT NULL, LAST_NAME VARCHAR2(20) NOT NULL, FIRST_NAME VARCHAR(20) NOT NULL, MIDDLE_NAME VARCHAR2(20) NOT NULL, ST ADDRESS VARCHAR2(20) NOT NULL, CITY CHAR(20) NOT NULL, STATE CHAR(2) NOT NULL, ZIP NUMBER(4) NOT NULL, DATE HIRED DATE);
The CREATE TABLE statement does not work because there are several errors in the syntax. The corrected statement follows and is given as an Oracle-specific version. 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 );
The following needs to be done:
1. The AS: should not be in this CREATE TABLE statement.
2. A comma is missing 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. With 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 a 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.
Yes. However, even though it is an ANSI standard, you must check your particular implementation to see if it has been accepted.
ADD CONSTRAINT EMPLOYEE_PK PRIMARY KEY(SSN);
ALTER TABLE EMPOYEE_TBL
MODIFY MIDDLE_NAME VARCHAR(20), NOT NULL;
ALTER TABLE EMPLOYEE_TBL
ADD CONSTRAINT CHK_STATE CHECK(STATE=‘NY’);
ALTER TABLE EMPLOYEE_TBL
ADD COLUMN EMPID INT AUTO_INCREMENT;
True.
False. Not always; normalization can and does slow performance because more tables must be joined, which results in more I/O and CPU time.
True.
|
|
||||||||||||||||||||||||||||
