CODEWITHSUNDEEP
sqloracle-database
Naveen J P
Naveen J P

Reputation: 27

SQL: Why this error when trying to create a table in SQL?

I am new to SQL and was trying to create a basic emp table to learn a bit. When I write the query and try to execute it, I keep getting the message "ORA-00907: missing right parenthesis". However I feel that I have included a pair of braces for each function. Could anyone please help me to figure out the issue and if possible could correct me if I am wrong??

Below is the sample 

SQL> create table emp
  2  (emp id number(4),
  3  first name varchar2(25),
  4  last name varchar2(25),
  5  phone number number (10),
  6  department id number (4),
  7  job id number (4),
  8  salary number (6,2),
  9  commission_pct number (4,2),
 10  manager id number (4);
(emp id number (4),
        *
ERROR at line 2:
ORA-00907: missing right parenthesis

Upvotes: 1

Views: 150

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 180877

You have a start parenthesis at line 2;

(emp id number(4),
^

that is not closed on line 10;

manager id number (4);
                     ^missing

Also, you can't have spaces in your column names without quoting them. I'd recommend replacing spaces with _ not to have to quote them everywhere.

SQL> create table emp
  2  (emp_id number(4),
  3  first_name varchar2(25),
  4  last_name varchar2(25),
  5  phone_number number (10),
  6  department_id number (4),
  7  job_id number (4),
  8  salary number (6,2),
  9  commission_pct number (4,2),
 10  manager_id number (4));

SQLfiddle test.

Upvotes: 6

DevT
DevT

Reputation: 4933

I found 3 mistakes in here.

  1. in SQL you must use numeric instead of number.
  2. you can't keep spaces in field.
  3. you need to close the brace end of the query,

Code:

 create table emp
       (emp_id numeric(4),
        first_name varchar(25),
        lastname varchar(25),
        phone_number numeric (10),
        department_id numeric (4),
        job_id numeric (4),
        salary numeric (6,2),
        commission_pct numeric (4,2),
        manager_id numeric (4))

here is the SQL Fiddle Demo

Upvotes: 2

John Woo
John Woo

Reputation: 263693

your column has spaces on it, if you want to leave the spaces on your table, wrap it with double quotes "

create table emp
(
   "emp id" number(4), 
   "first name" varchar2(25),
   .....
);

but the best way is to create column names without adding space on it.

create table emp
(
   empID number(4), 
   firstName varchar2(25),
   .....
);

Upvotes: 2

Related Questions