| Creating Table/Sequence in Oracle [message #8202] |
Sun, 03 August 2003 12:15  |
Nancy
Messages: 19
Registered: March 2002
|
Junior Member |
|
|
Hello Gurus, I need some help….
I am really new to Oracle database, Using the Oracle database, I am looking to Create and populate a table that will name EMPLOYEE_(adding my lastname, Bepat here), which has the following structure and data values:
E_id NUMBER(2) primary key (use sequence to input value, starting at 200)
E_lastname VARCHAR2(30) Murray Jones
E_firstname VARCHAR2(20) Mary John
E_address VARCHAR2(30) 144 Fisherman's Way 18 Chapel Road
E_city VARCHAR2(25) Haymarket West Mifflin
E_state CHAR(2) VA PA
E_hire_date DATE 01/02/03 01/03/03
I would like to;
Show the code used to create the sequence with sql*plus.
Show the code used to create the table with sql*plus.
Show the code used to populate the table, using the data values in bold, with sql*plus.
Show any outputs from sql*plus.
Thanks
Nancy
|
|
|
|
| Re: Creating Table/Sequence in Oracle [message #8205 is a reply to message #8202] |
Sun, 03 August 2003 20:20  |
Nancy
Messages: 19
Registered: March 2002
|
Junior Member |
|
|
This is what I came up with so far:
SQL> CREATE TABLE employees (
E_id_no NUMBER(2) NOT NULL,
E_lastname VARCHAR2(30) Murray Jones
E_firstname VARCHAR2(20) Mary John
E_address VARCHAR2(30) 144 Fisherman’s Way 18 Chapel Road
E_city VARCHAR2(25) Haymarket West Mifflin
E_state CHAR(2) VA PA
E_hire_date DATE 01/02/03 01/03/03
PRIMARY KEY(E_id_no)
);
sequence:
SQL> CREATE SEQUENCE E_id_no START WITH 1
INCREMENT BY 1 MAXVALUE 9999;
Nancy
|
|
|
|
| Re: Creating Table/Sequence in Oracle [message #8210 is a reply to message #8205] |
Mon, 04 August 2003 05:05 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
> CREATE TABLE employees (
> E_id_no NUMBER(2) NOT NULL,
> E_lastname VARCHAR2(30) Murray Jones
> ...
What's with the "Murray Jones"? Just use
[[name]] [[datatype]] [[null | not null]]
e.g:
, e_lastname VARCHAR2(30) NOT NULL
Case is ignored in database object names so there is no point mixing case. A useful convention is to use uppercase for keywords like CREATE, BEGIN, LOWER etc and lowercase for everything else.
Another useful habit to get into is putting commas at the start of the line in comma-separated lists, which IMHO makes them esier to edit and read, e.g:
INSERT INTO table
( col1
, col2
, col3 )
VALUES
( val1
, val2
, val3 );
No two developers will ever agree on this kind of thing though...
The primary key definition looks OK, although it's worth remembering that you can simplify single-column constraints to e.g:
CREATE TABLE employees
( e_id_no NUMBER(2) CONSTRAINT emp_pk PRIMARY KEY USING INDEX TABLESPACE userindex
, ...
You can skip "CONSTRAINT [[name]]" and it will use a system-generated name like "SYS_C006426". You can also skip the "USING INDEX [[specification]]" part and it will use your default tablespace. For example:
CREATE TABLE testit
( id NUMBER(2) PRIMARY KEY );
Similarly for CREATE SEQUENCE you can go with the defaults (if you're happy with them) and just use:
CREATE SEQUENCE e_id_seq;
The default is to start at 1, max out at 999999999999999999999999999 and stop.
|
|
|
|
| Re: Creating Table/Sequence in Oracle [message #8212 is a reply to message #8202] |
Mon, 04 August 2003 06:04 |
Shesh
Messages: 16
Registered: July 2003
|
Junior Member |
|
|
Hi Nancy,
First of all you can't have sequence starting with 200 because you have given datatype for E_id as Number(2).
I have modified it to Number(4) and here are the scripts.
For creating table.
Create table employee
(
E_id number(4) primary key,
E_lastname varchar2(30),
E_firstname varchar2(20),
E_address varchar2(30),
E_city varchar2(25),
E_state char(2),
E_hire_date date
);
For Creating Sequence.
Create sequence E_id_Seq start with 200 increment by 1;
For Inserting Rows.
Insert into Employee values(E_id_Seq.nextval,'&E_Lastname','&E_firstname','&E_address','&E_city','&E_state','&E_hire_date')
For selecting..
Select * from Employee;
Hope this has helped you
Thanks
Shesha
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
