Create table - SQL Oracle

Question

I need to create table, which should I call Others. I want only employeers who names which having names start with any other letter, but not K

I wrote sthg like this:

CREATE TABLE others AS select * from employees WHERE last_name no like 'K%';

I found sthg like this idea but it doesn't work

I'm receiving errror about syntax. Can you help me? The second question: there is any other way to write it?

Create a view instead. No need to store same data in several tables. — jarlh, Nov 30 '17 at 11:40

score 2 · Answer 1 · edited Nov 30 '17 at 12:40

2

Try This

CREATE TABLE others  AS (SELECT *
      FROM employees 
      WHERE last_name  NOT LIKE 'K%');

edited Nov 30 '17 at 12:40

Yogesh Sharma

49,870
5
26
52

answered Nov 30 '17 at 11:50

Chanakya

21
2

score 1 · Answer 2 · answered Nov 30 '17 at 11:58

As @jarlh said in his comment, a view would serve the same purpose, but the data would only be stored once instead of twice, thus saving disk space. You could define the view as

CREATE OR REPLACE VIEW OTHERS AS
  SELECT *
    FROM EMPLOYEES
    WHERE LAST_NAME NOT LIKE 'K%';

Best of luck.

score 0 · Answer 3 · answered Nov 30 '17 at 11:50

0

I would recommend using just string functions. Here are two ways:

WHERE SUBSTR(last_name, 1, 1) <> 'K'

or:

WHERE last_name < 'K' or last_name >= 'L'

Although you can use LIKE or REGEXP_LIKE() for this, I like this simpler approaches.

answered Nov 30 '17 at 11:50

Gordon Linoff

1,242,037
58
646
786

Create table - SQL Oracle

3 Answers3