Restriction and sorting [ WHERE clause ]

SELECT * 
FROM employees;

Number

SELECT * 
FROM   employees
WHERE  department_id = 90;

String:

SELECT * 
FROM   employees
WHERE  First_name = 'steven';

no date fount

*data is case sensitive in oracle

SELECT * 
FROM   employees
WHERE  First_name = 'Steven';

Date:

Date Format  :  DD-MON-YY     e.g. 14-JUN-12

SELECT first_name, Hire_date FROM employees
WHERE hire_date = '08-MAR-00';

Operators :

       =   ,    >  ,  >=   ,   <   ,    <=   ,   <>    (or)    !=       (or)      ^=
     
=

SELECT * FROM employees
WHERE salary = 24000;

>

SELECT * FROM employees
WHERE salary > 17000;

<

SELECT * FROM employees
WHERE salary >= 17000;


!=   [or]  < >  [or]  ^=


SELECT * FROM employees
WHERE first_name != 'Steven';

Other operators

      BETWEEN ...  AND  ....     
      NOT BETWEEN ...  AND  ....     
      IN  
      NOT IN  
      LIKE  
      NOT LIKE
      IS NULL 
      IS NOT NULL     

BETWEEN ...  AND  ....     


SELECT * FROM employees
WHERE salary BETWEEN 5000 AND 10000;


NOT  BETWEEN ...  AND  ....


SELECT * FROM employees
WHERE salary not between 5000 and 10000;


IN


SELECT * FROM employees
WHERE salary IN (24000, 10000, 17000);


NOT IN


SELECT * FROM employees
WHERE salary NOT IN (24000, 10000, 17000);


LIKE


     Pattern matching operator

     % -> something or zero  
     _  -> position


Query to fetch all the first name which starts with S


SELECT * FROM employees
WHERE first_name LIKE 'S%';


Query to fetch all the first name which starts with S and end with n


SELECT * FROM employees
WHERE first_name LIKE 'S%n';


Query to fetch all the first name which contain u as second character


SELECT * FROM employees
WHERE first_name LIKE '_u%';


Query to fetch all the first name which contain u as second character and i as fourth character


SELECT * FROM employees
WHERE first_name LIKE '_u_i%';


Query to fetch all the mail_id which contain _ at third position 


SELECT * FROM employees
WHERE mail_id LIKE '__\_%' ESCAPE '\';

                                  [ or ]

SELECT * FROM employees
WHERE mail_id LIKE '__$_%' ESCAPE '$';


NULL


SELECT * FROM employees
WHERE commission_pct IS NULL;


IS NOT NULL


SELECT * FROM employees
WHERE commission_pct IS NOT NULL;


AND and OR logical operator

AND operator

SELECT * FROM employees
WHERE  First_name = 'Steven'  AND 
       Salary = 24000;


OR operator


SELECT * FROM employees
WHERE  First_name = 'Steven'  OR
       Salary = 24000;


AND and OR operator


SELECT * FROM employees
WHERE  (First_name = 'Steven' OR First_name='Lex') AND 
       Salary > 15000;

ASC and DESC

          Ascending      ASC     [ Default ]
          Descending    DESC


SELECT * FROM employees;

Sorting based on ASC

SELECT * FROM employees
ORDER BY department_id ASC;

Sorting based on DESC

SELECT * FROM employees
ORDER BY department_id DESC;

Sorting based on ASC (default)

SELECT * FROM employees
ORDER BY by department_id;

Sorting based on alias name

SELECT first_name, hire_date, salary as a
FROM employees
ORDER BY a DESC;

Sorting based on multiple columns

SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, salary ASC;


Thanks for reading!!!!