SQL WHERE clause
Where clause
The basic form of the SELECT statement is SELECT-FROM-WHERE block. In a SELECT statement, WHERE clause is optional. Using SELECT without a WHERE clause is useful for browsing data from tables.
In a WHERE clause, you can specify a search condition (logical expression) that has one or more conditions. When the condition (logical expression) evaluates to true the WHERE clause filter unwanted rows from the result.
Here is the syntax:
Syntax:
SELECT <column_list> FROM < table name > WHERE <condition>;
Parameters:
| Name | Description |
|---|---|
| table_name | Name of the table. |
| column_list | Name of the columns of the table. |
| condition | Search condition. |
SQL: WHERE clause - Syntax diagram
Contents:
- Types of conditions
- Example: Using the WHERE clause
- Example : WHERE clause using comparison conditions
- Example: WHERE clause using expression
- Example: WHERE clause using BETWEEN condition
- Example: WHERE clause using IN condition
- Example: WHERE clause using LIKE condition
- Example: WHERE clause using NULL condition
- Example: WHERE clause using Logical Conditions
Types of conditions
| Condition | SQL Operators |
|---|---|
| Comparison | =, >, >=, <, <=, <> |
| Range filtering | BETWEEN |
| Match a character pattern | LIKE |
| List filtering [Match any of a list of values] | IN |
| Null testing | IS NULL |
A list of SQL Operators and Descriptions can be used in WHERE clause:
| Operator | Description |
|---|---|
| = | Tests for equality between two values. |
| <> | Tests for inequality between two values. |
| > | Tests if the left operand is greater than the right operand. |
| < | Tests if the left operand is less than the right operand. |
| >= | Tests if the left operand is greater than or equal to the right operand. |
| <= | Tests if the left operand is less than or equal to the right operand. |
| BETWEEN | Tests if a value lies within a specified range (inclusive). |
| IN | Tests if a value matches any value in a list of specified values. |
| LIKE | Tests if a value matches a specified pattern, using wildcards (%) to represent zero or more characters |
| and (_) to represent a single character. | |
| IS NULL | Tests if a value is NULL (has no value). |
| IS NOT NULL | Tests if a value is not NULL (has a value). |
SQL: Comparison condition - Syntax diagram
Example: Using the WHERE clause in SQL
The query selects the employee_id, first_name, last_name, and department_id columns from the employees table, filtering the results to include only rows where the department_id is equal to 100.
Sample table : employees
SQL Code:
-- Selecting specific columns: employee_id, first_name, last_name, department_id
-- From the 'employees' table
SELECT employee_id, first_name, last_name, department_id
FROM employees
-- Filtering the results to include only rows where the 'department_id' is equal to 100
-- From the 'employees' table
WHERE department_id = 100;
Explanation:
- SELECT employee_id, first_name, last_name, department_id: This line specifies the columns that you want to retrieve data from. It selects the columns 'employee_id', 'first_name', 'last_name', and 'department_id' from the 'employees' table.
- FROM employees: This line specifies the table from which you want to retrieve data. In this case, it's the 'employees' table.
- WHERE department_id = 100: This line specifies a condition for filtering the results. It filters the results to only include rows where the value in the 'department_id' column is equal to 100. This condition acts as a filter, allowing only rows with a 'department_id' of 100 to be included in the result set.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT_ID
----------- -------------------- ------------------------- -------------
108 Nancy Greenberg 100
109 Daniel Faviet 100
110 John Chen 100
111 Ismael Sciarra 100
112 Jose Manuel Urman 100
113 Luis Popp 100
Visual presentation :
The provided SQL code retrieves the employee ID, job ID, and salary of employees with the last name 'Lorentz' from the employees table.
Note : Character strings are enclosed in quotation marks. Character values are case-sensitive for some database.
SQL Code:
-- Selecting specific columns: employee_id, job_id, salary
SELECT employee_id, job_id, salary
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the 'last_name' is equal to 'Lorentz'
WHERE last_name = 'Lorentz';
Explanation:
- SELECT employee_id, job_id, salary: This line specifies the columns that you want to retrieve data from. It selects the columns 'employee_id', 'job_id', and 'salary' from the 'employees' table.
- FROM employees: This line specifies the table from which you want to retrieve data. In this case, it's the 'employees' table.
- WHERE last_name = 'Lorentz': This line specifies a condition for filtering the results. It filters the results to only include rows where the value in the 'last_name' column is equal to 'Lorentz'. This condition acts as a filter, allowing only rows with the last name 'Lorentz' to be included in the result set.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
EMPLOYEE_ID JOB_ID SALARY
----------- ---------- ----------
107 IT_PROG 4200
Visual presentation :
Example: WHERE clause using comparison conditions in SQL
The provided SQL query retrieves the employee ID, first name, last name, and salary of employees with a salary greater than or equal to 4000.
Sample table : employees
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
SQL Code:
-- Selecting specific columns from the 'employees' table
SELECT employee_id, first_name, last_name, salary
-- Filtering the results to include only rows where the 'salary' is greater than or equal to 4000
FROM employees
WHERE salary >= 4000;
Explanation:
- SELECT employee_id, first_name, last_name, salary: This line specifies the columns that you want to retrieve data from. It selects the columns 'employee_id', 'first_name', 'last_name', and 'salary' from the 'employees' table.
- FROM employees: This line specifies the table from which you want to retrieve data. In this case, it's the 'employees' table.
- WHERE salary >= 4000: This line specifies a condition for filtering the results. It filters the results to only include rows where the value in the 'salary' column is greater than or equal to 4000. This condition acts as a filter, allowing only rows with a salary of 4000 or higher to be included in the result set.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
100 Steven King 24000
101 Neena Kochhar 17000
102 Lex De Haan 17000
103 Alexander Hunold 9000
104 Bruce Ernst 6000
105 David Austin 4800
106 Valli Pataballa 4800
107 Diana Lorentz 4200
108 Nancy Greenberg 12008
109 Daniel Faviet 9000
110 John Chen 8200
111 Ismael Sciarra 7700
112 Jose Manuel Urman 7800
113 Luis Popp 6900
114 Den Raphaely 11000
.........................
.........................
Example: WHERE clause using expression in SQL
The given SQL query retrieves the first name, last name, salary, and net salary (calculated as salary plus the product of salary and commission percentage) of employees whose net salary falls within the range of 10000 to 15000 and who receive a commission percentage greater than zero.
Sample table: employees
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
SQL Code:
-- Selecting specific columns from the 'employees' table: first_name, last_name, salary
-- Calculating the "Net Salary" by adding the commission to the salary
SELECT first_name, last_name, salary, (salary + (salary * commission_pct)) "Net Salary"
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the "Net Salary" is between 10000 and 15000
-- and the commission_pct is greater than 0
WHERE (salary + (salary * commission_pct)) BETWEEN 10000 AND 15000 AND commission_pct > 0;
Explanation:
- SELECT first_name, last_name, salary, (salary + (salary * commission_pct)) "Net Salary": This line specifies the columns that you want to retrieve data from. It selects the columns 'first_name', 'last_name', and 'salary' from the 'employees' table. Additionally, it calculates the "Net Salary" by adding the commission percentage (commission_pct) to the salary. The result is aliased as "Net Salary".
- FROM employees: This line specifies the table from which you want to retrieve data. In this case, it's the 'employees' table.
- WHERE (salary + (salary * commission_pct)) BETWEEN 10000 AND 15000 AND commission_pct > 0: This line specifies conditions for filtering the results. It filters the results to only include rows where the calculated "Net Salary" falls between 10000 and 15000, and where the commission percentage (commission_pct) is greater than 0. These conditions act as filters, allowing only rows that meet both criteria to be included in the result set.
Output:
FIRST_NAME LAST_NAME SALARY Net Salary -------------------- ------------------------- ---------- ---------- Gerald Cambrault 11000 14300 Eleni Zlotkey 10500 12600 Peter Tucker 10000 13000 David Bernstein 9500 11875 Peter Hall 9000 11250 Janette King 10000 13500 Patrick Sully 9500 12825 Allan McEwen 9000 12150 Lindsey Smith 8000 10400 Clara Vishney 10500 13125 Danielle Greene 9500 10925 Lisa Ozer 11500 14375 Harrison Bloom 10000 12000 Tayler Fox 9600 11520 Ellen Abel 11000 14300 Alyssa Hutton 8800 11000 Jonathon Taylor 8600 10320 Jack Livingston 8400 10080
Visual presentation :
Example: WHERE clause using BETWEEN condition in SQL
The BETWEEN condition is used to test for values in a list.
SQL: BETWEEN condition - Syntax diagram
The given SQL query retrieves the employee ID, first name, last name, and salary of employees whose salary falls within the range of 4000 to 6000, inclusive.
Sample table : employees
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
SQL Code:
-- Selecting specific columns from the 'employees' table: employee_id, first_name, last_name, salary
SELECT employee_id, first_name, last_name, salary
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the 'salary' is between 4000 and 6000
WHERE salary BETWEEN 4000 AND 6000;
Explanation:
- SELECT employee_id, first_name, last_name, salary: This line specifies the columns that you want to retrieve data from. It selects the columns 'employee_id', 'first_name', 'last_name', and 'salary' from the 'employees' table.
- FROM employees: This line specifies the table from which you want to retrieve data. In this case, it's the 'employees' table.
- WHERE salary BETWEEN 4000 AND 6000: This line specifies a condition for filtering the results. It filters the results to only include rows where the value in the 'salary' column falls within the range of 4000 to 6000 (inclusive). The BETWEEN keyword is used to specify a range condition.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
104 Bruce Ernst 6000
105 David Austin 4800
106 Valli Pataballa 4800
107 Diana Lorentz 4200
124 Kevin Mourgos 5800
184 Nandita Sarchand 4200
185 Alexis Bull 4100
192 Sarah Bell 4000
200 Jennifer Whalen 4400
202 Pat Fay 6000
Visual presentation :
Example: WHERE clause using IN condition in SQL
The IN condition is used to test for values in a list.
SQL: IN condition - Syntax diagram
The following query displays the employee_id, first_name, last_name, department_id and salary of employees whose department_id 60, 90 or 100.
Sample table : employees
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
SQL Code:
-- Selecting specific columns from the 'employees' table: employee_id, first_name, last_name, department_id, salary
SELECT employee_id, first_name, last_name, department_id, salary
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the 'department_id' is in the list (60, 90, 100)
WHERE department_id IN (60, 90, 100);
Explanation:
- SELECT employee_id, first_name, last_name, department_id, salary: This line specifies the columns that you want to retrieve data from. It selects the columns 'employee_id', 'first_name', 'last_name', 'department_id', and 'salary' from the 'employees' table.
- FROM employees: This line specifies the table from which you want to retrieve data. In this case, it's the 'employees' table.
- WHERE department_id IN (60, 90, 100): This line specifies a condition for filtering the results. It filters the results to only include rows where the value in the 'department_id' column is contained within the list (60, 90, 100). The IN keyword is used to check if the value of 'department_id' matches any value in the provided list.
Relational Algebra Expression:
Relational Algebra Tree:
Output :
EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT_ID SALARY
----------- -------------------- ------------------------- ------------- ----------
103 Alexander Hunold 60 9000
104 Bruce Ernst 60 6000
105 David Austin 60 4800
106 Valli Pataballa 60 4800
107 Diana Lorentz 60 4200
100 Steven King 90 24000
101 Neena Kochhar 90 17000
102 Lex De Haan 90 17000
108 Nancy Greenberg 100 12008
109 Daniel Faviet 100 9000
110 John Chen 100 8200
111 Ismael Sciarra 100 7700
112 Jose Manuel Urman 100 7800
113 Luis Popp 100 6900
Visual presentation :
Example: WHERE clause using LIKE condition in SQL
The LIKE condition facilitates wildcard searches within string values. This condition allows for the inclusion of numbers or literal characters, where '_' represents a single character and '%' represents zero or multiple characters.
SQL: LIKE condition - Syntax diagram
The following query displays the employee_id, first_name, last_name and salary of employees whose first_name starting with 'S'.
Sample table : employees
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
SQL Code:
-- Selecting specific columns from the 'employees' table: employee_id, first_name, last_name, department_id, salary
SELECT employee_id, first_name, last_name, department_id, salary
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the 'first_name' starts with the letter 'S'
WHERE first_name LIKE ('S%');
Explanation:
- SELECT employee_id, first_name, last_name, department_id, salary: This line specifies the columns that you want to retrieve data from. It selects the columns 'employee_id', 'first_name', 'last_name', 'department_id', and 'salary' from the 'employees' table.
- FROM employees: This line specifies the table from which you want to retrieve data. In this case, it's the 'employees' table.
- WHERE first_name LIKE ('S%'): This line specifies a condition for filtering the results. It filters the results to only include rows where the value in the 'first_name' column starts with the letter 'S'. The LIKE keyword is used for pattern matching, where 'S%' represents any string that starts with 'S' followed by zero or more characters.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT_ID SALARY
----------- -------------------- ------------------------- ------------- ----------
166 Sundar Ande 80 6400
116 Shelli Baida 30 2900
192 Sarah Bell 50 4000
205 Shelley Higgins 110 12008
100 Steven King 90 24000
173 Sundita Kumar 80 6100
128 Steven Markle 50 2200
203 Susan Mavris 40 6500
194 Samuel McCain 50 3200
161 Sarath Sewall 80 7000
138 Stephen Stiles 50 3200
117 Sigal Tobias 30 2800
123 Shanta Vollman 50 6500
Visual presentation :
Example : WHERE clause using NULL condition in SQL
IS NULL operator is used to test for nulls.
SQL: NULL condition - Syntax diagram
The following query displays the employee_id, first_name, last_name and salary of employees whose department_id is null.
Sample table : employees
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
SQL Code:
-- Selecting specific columns from the 'employees' table: employee_id, first_name, last_name, department_id, salary
SELECT employee_id, first_name, last_name, department_id, salary
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the 'department_id' is NULL
WHERE department_id IS NULL;
Explanation:
- SELECT employee_id, first_name, last_name, department_id, salary: This line specifies the columns that you want to retrieve data from. It selects the columns 'employee_id', 'first_name', 'last_name', 'department_id', and 'salary' from the 'employees' table.
- FROM employees: This line specifies the table from which you want to retrieve data. In this case, it's the 'employees' table.
- WHERE department_id IS NULL: This line specifies a condition for filtering the results. It filters the results to only include rows where the value in the 'department_id' column is NULL. The IS NULL condition is used to check for NULL values in the 'department_id' column.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT_ID SALARY
----------- -------------------- ------------------------- ------------- ----------
178 Kimberely Grant 7000
Visual presentation :
Example : WHERE clause using Logical Conditions in SQL
Logical Conditions
| Operators | Description |
|---|---|
| AND | Returns TRUE if both conditions are true. |
| OR | Returns TRUE if either condition is true. |
| NOT | Returns TRUE if the following condition is false. |
SQL: Logical condition - Syntax diagram
Example : WHERE clause using the AND operator in SQL
The following query displays the employee_id, first_name, last_name and salary of employees whose first_name starting with 'S' and salary greater than or equal to 4000.
Sample table : employees
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
SQL Code:
-- Selecting specific columns from the 'employees' table: employee_id, first_name, last_name, department_id, salary
SELECT employee_id, first_name, last_name, department_id, salary
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the 'first_name' starts with the letter 'S'
-- and the 'salary' is greater than or equal to 4000
WHERE first_name LIKE ('S%') AND salary >= 4000;
Explanation:
- SELECT employee_id, first_name, last_name, department_id, salary: This line specifies the columns that you want to retrieve data from. It selects the columns 'employee_id', 'first_name', 'last_name', 'department_id', and 'salary' from the 'employees' table.
- FROM employees: This line specifies the table from which you want to retrieve data. In this case, it's the 'employees' table.
- WHERE first_name LIKE ('S%') AND salary >= 4000: This line specifies conditions for filtering the results. It filters the results to only include rows where:
- The value in the 'first_name' column starts with the letter 'S' (using the LIKE keyword for pattern matching).
- The value in the 'salary' column is greater than or equal to 4000.
- The AND keyword is used to combine these conditions, ensuring that both conditions must be true for a row to be included in the result set.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT_ID SALARY
----------- -------------------- ------------------------- ------------- ----------
166 Sundar Ande 80 6400
192 Sarah Bell 50 4000
205 Shelley Higgins 110 12008
100 Steven King 90 24000
173 Sundita Kumar 80 6100
203 Susan Mavris 40 6500
161 Sarath Sewall 80 7000
123 Shanta Vollman 50 6500
Example: WHERE clause using the OR operator in SQL
The following query displays the employee_id, first_name, last_name and salary of employees whose first_name starting with 'S' or 'A'.
Sample table: employees
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
SQL Code:
-- Selecting specific columns from the 'employees' table: employee_id, first_name, last_name, department_id, salary
SELECT employee_id, first_name, last_name, department_id, salary
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the 'first_name' starts with the letter 'S'
-- OR the 'first_name' starts with the letter 'A'
WHERE first_name LIKE ('S%') OR first_name LIKE ('A%');
Explanation:
- SELECT employee_id, first_name, last_name, department_id, salary: This line specifies the columns that you want to retrieve data from. It selects the columns 'employee_id', 'first_name', 'last_name', 'department_id', and 'salary' from the 'employees' table.
- FROM employees: This line specifies the table from which you want to retrieve data. In this case, it's the 'employees' table.
- WHERE first_name LIKE ('S%') OR first_name LIKE ('A%'): This line specifies conditions for filtering the results. It filters the results to only include rows where:
- The value in the 'first_name' column starts with the letter 'S' (using the LIKE keyword for pattern matching), OR
- The value in the 'first_name' column starts with the letter 'A'.
- The OR keyword is used to combine these conditions, ensuring that a row will be included in the result set if either condition is true.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT_ID SALARY
----------- -------------------- ------------------------- ------------- ----------
100 Steven King 90 24000
103 Alexander Hunold 60 9000
115 Alexander Khoo 30 3100
116 Shelli Baida 30 2900
117 Sigal Tobias 30 2800
121 Adam Fripp 50 8200
123 Shanta Vollman 50 6500
128 Steven Markle 50 2200
138 Stephen Stiles 50 3200
147 Alberto Errazuriz 80 12000
158 Allan McEwen 80 9000
161 Sarath Sewall 80 7000
166 Sundar Ande 80 6400
167 Amit Banda 80 6200
173 Sundita Kumar 80 6100
175 Alyssa Hutton 80 8800
185 Alexis Bull 50 4100
187 Anthony Cabrio 50 3000
192 Sarah Bell 50 4000
194 Samuel McCain 50 3200
196 Alana Walsh 50 3100
203 Susan Mavris 40 6500
205 Shelley Higgins 110 12008
Example: WHERE clause using the NOT operator in SQL
The following query displays the employee_id, first_name, last_name and salary of employees except the department_id 90, 60 or 100 :
Sample table: employees
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
SQL Code:
-- Selecting specific columns from the 'employees' table: employee_id, first_name, last_name, department_id, salary
SELECT employee_id, first_name, last_name, department_id, salary
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the 'department_id' is NOT IN (90, 60, 100)
WHERE department_id NOT IN (90, 60, 100);
Explanation:
- SELECT employee_id, first_name, last_name, department_id, salary: This line selects specific columns from the 'employees' table, namely 'employee_id', 'first_name', 'last_name', 'department_id', and 'salary'.
- FROM employees: This specifies that the data is being retrieved from the 'employees' table.
- WHERE department_id NOT IN (90, 60, 100): This condition filters the rows to only include those where the 'department_id' is not equal to 90, 60, or 100. The NOT IN operator negates the list of values provided within the parentheses.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT_ID SALARY
----------- -------------------- ------------------------- ------------- ----------
114 Den Raphaely 30 11000
115 Alexander Khoo 30 3100
116 Shelli Baida 30 2900
117 Sigal Tobias 30 2800
118 Guy Himuro 30 2600
119 Karen Colmenares 30 2500
120 Matthew Weiss 50 8000
121 Adam Fripp 50 8200
122 Payam Kaufling 50 7900
123 Shanta Vollman 50 6500
124 Kevin Mourgos 50 5800
125 Julia Nayer 50 3200
126 Irene Mikkilineni 50 2700
127 James Landry 50 2400
128 Steven Markle 50 2200
129 Laura Bissot 50 3300
130 Mozhe Atkinson 50 2800
131 James Marlow 50 2500
132 TJ Olson 50 2100
133 Jason Mallin 50 3300
134 Michael Rogers 50 2900
135 Ki Gee 50 2400
136 Hazel Philtanker 50 2200
137 Renske Ladwig 50 3600
................
................
Visual presentation :
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: SELECT with DISTINCT Multiple Columns
Next: SQL Operators
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql/where-clause.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics