Sunday, 10 February 2013

SQL AND , OR Operators Syntax and Examples

SQL AND & OR Operators
The AND operator displays a record if both the first condition and the second condition are true.
The OR operator displays a record if either the first condition or the second condition is true.

AND Operator Example:

Suppose we have a and Employee Table as follows :


Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1002
David
Team Lead
2
35000
1003
Ravi Kumar Rao
Analyst
1
10000
1004
Tamatam
Team Lead
3
25000
1005
Sai Kumar Teja
Analyst
2
15000
1006
William
Manager
3
50000
1007
Vikram Teja Reddy
Analyst
2
18000
1008
Vivek
Team Lead
1
33000
1006
Smith
Team Lead
3
50000

Now we want select the Job='Analyst' AND Salary>1000

SELECT * FROM EMP WHERE Job='AnalystAND Salary > 10000

Result :
Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1005
Sai Kumar Teja
Analyst
2
15000
1007
Vikram Teja Reddy
Analyst
2
18000


OR Operator Example:
Now we want to select  the Employees whose Job equal to "Analyst" OR 'Manager' , using below SELECT statement:

SELECT * FROM EMP WHERE Job='AnalystOR Job='Manager

Result :
Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1003
Ravi Kumar Rao
Analyst
1
10000
1005
Sai Kumar Teja
Analyst
2
15000
1006
William
Manager
3
50000
1007
Vikram Teja Reddy
Analyst
2
18000

Combining AND & OR:
You can also combine AND and OR (use parenthesis to form complex expressions).

Now we want to select  the Employees whose Dept_ID = 1 OR 2 AND Salary > 15000 , using below statement.

SELECT * FROM EMP WHERE Salary > 15000 AND (Dept_ID=1 OR Dept_ID=2 )

Result :
Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1002
David
Team Lead
2
35000
1007
Vikram Teja Reddy
Analyst
2
18000
1008
Vivek
Team Lead
1
33000

Thanks ,
TAMATAM





No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts