Tuesday, January 15, 2013

LIKE , BETWEEN, IN, NOT


All keywords are used with WHERE.

LIKE

LIKE is used to map values with String including some characters. There are two special symbols: percent %, replaced for many characters and underscore _, replaced for only one character.
Examples Statement:
SELECT JOB_TITLE FROM JOBS
WHERE JOB_TITLE LIKE S%;


The results are job titles starting with S.
Examples Statement:
SELECT JOB_TITLE FROM JOBS
WHERE JOB_TITLE LIKE S_l%;


The results are job titles starting with S, any character at the second index, l at the third index.

BETWEEN

BETWEEN is used to check the condition when the values are in a range or not.
Examples Statement:
SELECT JOB_TITLE, MIN_SALARY FROM JOBS
WHERE MIN_SALARY >= 4000 AND MIN_SALARY <= 9000;


We could use BETWEEN … AND… for this case:
SELECT JOB_TITLE, MIN_SALARY FROM JOBS
WHERE MIN_SALARY BETWEEN 4000 AND 9000;


IN

Examples Statement:
SELECT JOB_TITLE, MIN_SALARY FROM JOBS
WHERE MIN_SALARY = 4000 OR MIN_SALARY = 9000 OR MIN_SALARY = 6000;


When we want to check the value that is on one set or not, we could use IN:
SELECT JOB_TITLE, MIN_SALARY FROM JOBS
WHERE MIN_SALARY IN (4000, 6000, 9000);


NOT

We could use NOT with BETWEEN, LIKE or IN:
SELECT JOB_TITLE, MIN_SALARY FROM JOBS
WHERE MIN_SALARY NOTE BETWEEN 4000 AND 9000;