It is assumed that you have gone through the previous tutorials and have a basic understanding of it. It is
also assumed that you have practised on the previous examples shown so that you have grasped the syntax notations
so that writing simple queries is possible by yourself.
Here the 'AND'
operator is executed on the 'Employees', 'Department' and 'Designation' tables.
The AND operator
The 'AND" operator acts as a conjunction to a multiple set of conditions. It displays a resultset only if all the conditions are
true. The AND operator can be used n number of times. It is used in the WHERE
clause.
SQL WHERE Syntax in Select, Update and Delete Statements
Select
column_name FROM table_name
where condition1 AND condition2 AND condition3....
Update table_name set (column_name1 =value1,Column_name2=value2,Column_name3=value3)
where condition1 AND Condition2 AND condition3....
Delete FROM table_name
where condition1 AND condition2 And Condition3....
The SELECT Statement Example with AND operator
| 1 |
CEO |
| 2 |
CTO |
| 3 |
Program Manager |
| 4 |
Project Lead |
| 5 |
System Analyst |
| 6 |
Senior Software Engineer |
| 7 |
Test Manager |
| 8 |
Software Engineer |
| 9 |
Trainee Engineer |
| 10 |
Test Engineer |
The above table displays all the records present in the 'Designation' table.
The below table displays all the details of the employees present in the 'EMPLOYEES' table.
| 1 |
Richard Hughes |
4/23/1945 12:00:00 AM |
1 |
1 |
New Orleans |
| 2 |
Taryn Sinclair |
3/22/1980 12:00:00 AM |
2 |
2 |
San Francisco |
| 3 |
Ted Horowitz |
3/31/1960 12:00:00 AM |
3 |
4 |
New York |
| 4 |
Jonathan Douglas |
7/7/1971 12:00:00 AM |
3 |
4 |
Salt Lake City |
| 5 |
Miranda Leigh |
7/19/1983 12:00:00 AM |
7 |
3 |
New York |
The Select statement on the 'Employees' table below displays all the employees who are in 'Development' department and is 'CTO'
as shown
below. Since there is only one record that satisfies all the given conditions,
it pulls only that up.
Select a.EmployeeName,b.Designation ,a.City
from Employees a, Designation b
where a.DesignationID=b.DesignationId
AND b.Designation='CTO'
AND a.City='San Francisco'
| Taryn Sinclair |
CTO |
San Francisco |
The SELECT statement example with OR operator
The 'OR" operator acts as a conjunction to a multiple set of conditions. It displays a resultset
when any of the conditions are satisfied. The OR operator can be used n number of times. It is used in the WHERE
clause.
Select EmployeeName,City
from Employees
where city='New Orleans'
OR City='New York'
| 1 |
Richard Hughes |
New Orleans |
| 3 |
Ted Horowitz |
New York |
| 5 |
Miranda Leigh |
New York |