Tagicon

SQL SELECT statement using WHERE clause

Posted by tagicon_admin | in : SQL

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

 

Leave a Reply

Follow by Email
YouTube
Pinterest
LinkedIn
Share
Instagram