When we need specific information or data or row from the database then we can use WHERE clause to restrict or specific data. A WHERE clause directly follows the FROM clause and it contains a condition that must be met. This clause can compare values in function, literal, arithmetic expressions and columns.
Syntex of WHERE clause:
=======================
SELECT * |{[DISTINCT] column/expression [alias]…}
FROM table
[WHERE condition(s)];
I am sharing some example of WHERE clause below:
–>We need to display all information for employee number 176.
SELECT * FROM employees WHERE employee_id = 176;
Above query will make the following result:
EMPLOYEE_ID LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- ---------- ------- ------------------ --------- ------ ------ -------------- ---------- -------------
176 Taylor JTAYLOR 011.44.1644.429265 24-MAR-06 SA_REP 8600 .2 149 80
–> Retrieve first name, job id, manager id, salary whoes earn more than $1500.
SELECT first_name, job_id, manager_id, salary FROM employees WHERE salary > 15000;
Above query will make the following result:
FIRST_NAME JOB_ID MANAGER_ID SALARY -------------------- ---------- ---------- ---------- Steven AD_PRES 24000 Neena AD_VP 100 17000 Lex AD_VP 100 17000
–> Retrieve job id, last name, hire date, email whose salary between $10000 and $14000.
SELECT job_id, last_name, hire_date, email FROM employees WHERE salary BETWEEN 10000 AND 14000;
Above query will make the following result:
JOB_ID LAST_NAME HIRE_DATE EMAIL ---------- ------------- --------- --------- MK_MAN Hartstein 17-FEB-04 MHARTSTE PR_REP Baer 07-JUN-02 HBAER AC_MGR Higgins 07-JUN-02 SHIGGINS FI_MGR Greenberg 17-AUG-02 NGREENBE PU_MAN Raphaely 07-DEC-02 DRAPHEAL SA_MAN Russell 01-OCT-04 JRUSSEL SA_MAN Partners 05-JAN-05 KPARTNER SA_MAN Errazuriz 10-MAR-05 AERRAZUR SA_MAN Cambrault 15-OCT-07 GCAMBRAU SA_MAN Zlotkey 29-JAN-08 EZLOTKEY SA_REP Tucker 30-JAN-05 PTUCKER SA_REP King 30-JAN-04 JKING SA_REP Vishney 11-NOV-05 CVISHNEY SA_REP Ozer 11-MAR-05 LOZER SA_REP Bloom 23-MAR-06 HBLOOM SA_REP Abel 11-MAY-04 EABEL
–>Display the job id, first name, salary, email of Ozer and Abel.
SELECT job_id, first_name, salary, email
FROM employees
WHERE last_name IN ('Ozer', 'Abel');
Above query will make the following result:
JOB_ID FIRST_NAME SALARY EMAIL ------- ----------- ------ ----- SA_REP Lisa 11500 LOZER SA_REP Ellen 11000 EABEL
–>Retrieve last name, phone number, job id who were hired in 2005.
SELECT last_name, phone_number, job_id FROM employees WHERE hire_date LIKE '%05';
We have used hire date from 2001-2008 within our database. So above query will show only 2005 related data and make the following result:
LAST_NAME PHONE_NUMBER JOB_ID ---------- ------------------ -------- Fay 603.123.6666 MK_REP Kochhar 515.123.4568 AD_VP Austin 590.423.4569 IT_PROG Chen 515.124.4269 FI_ACCOUNT Sciarra 515.124.4369 FI_ACCOUNT Baida 515.127.4563 PU_CLERK Tobias 515.127.4564 PU_CLERK Fripp 650.123.2234 ST_MAN Vollman 650.123.4234 ST_MAN Nayer 650.124.1214 ST_CLERK Bissot 650.124.5234 ST_CLERK Atkinson 650.124.6234 ST_CLERK Marlow 650.124.7234 ST_CLERK Stiles 650.121.2034 ST_CLERK Davies 650.121.2994 ST_CLERK Partners 011.44.1344.467268 SA_MAN Errazuriz 011.44.1344.429278 SA_MAN Tucker 011.44.1344.129268 SA_REP Bernstein 011.44.1344.345268 SA_REP Hall 011.44.1344.478968 SA_REP Smith 011.44.1345.729268 SA_REP Doran 011.44.1345.629268 SA_REP Vishney 011.44.1346.129268 SA_REP Ozer 011.44.1343.929268 SA_REP Hutton 011.44.1644.429266 SA_REP Bull 650.509.2876 SH_CLERK Chung 650.505.1876 SH_CLERK Dilly 650.505.2876 SH_CLERK Everett 650.501.2876 SH_CLERK