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?

Answer 1

Try This

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

Answer 2

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.

Answer 3

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.