Do you think IIT Guwahati certified course can help you in your career?
No
Introduction
Extracting data using a simple query may not be easy when dealing with an extensive database. The solution to this problem is to find whether any SQL clauses exist that help gets data on the required conditions. One such clause that comes in handy in this kind of query is AND & OR clause.
So, let us learn how to use the AND clause & OR clauses from this article.
AND & OR clause
AND clause will help fetch data from the table where all the supplied conditions are true.
The OR clause will help get data from the table where any condition from the supplied conditions is true.
AND clause & OR clause filter out the records with SELECT, INSERT, UPDATE, and DELETE statements. While using this clause in combining the conditions, it is essential to use parentheses so the database can figure out in which order conditions should be evaluated.
Note: All the queries and questions in this blog apply to the following database Table called Table Employee.
Problem statement 1
Create the Employee’s table, which has all the employee data consisting of columns like EmployeeName, EmployeeAge, EmployeeSalary, Dept, and EmployeeID as the primary key (a set of values of the table that uniquely defines each record in this table).
The AND clause displays a record if all the conditions separated by AND are TRUE.
Truth Table
The below image describes the truth table for the AND clause:
Syntax
SELECT ColumnName[s] ,
FROM TableName
WHERE condition1 AND condition2 AND condition3..n;
Note: A comma will separate different column names.
Problem statement 4
Find all the employee names with salaries of more than 25000 and less than 60000 and print EmployeeSalary, EmployeeName.
SQL Query:
SELECT
EmployeeSalary,
EmployeeName
FROM
Employee
WHERE
EmployeeSalary > 25000
AND EmployeeSalary < 60000;
Output:
Different operators can be used with AND, which also applies to the OR clause.
Let's make use of these operators to run the queries.
Problem statement 5
Find all the employee names with ages not equal to 24 and less than equal to 30 and print EmployeeAge, EmployeeName.
SQL Query:
SELECT
EmployeeAge,
EmployeeName
FROM
Employee
WHERE
EmployeeAge != 24
AND EmployeeAge <= 30;
Output:
OR clause
It allows many conditions to appear in a SQL query's WHERE clause.
If either of the requirements separated by OR is true, the OR clause outputs a record.
Truth Table
The below image describes the truth table for the OR clause.
Syntax
SELECT ColumnName[s]
FROM TableName
WHERE condition1 OR condition2 OR condition3..n;
Note: A comma will separate different column names.
Problem statement 6
Find all the employee names belonging to either the IT or Marketing Department and print EmployeeName, EmployeeSalary, and Dept.
SQL Query:
SELECT
EmployeeName,
EmployeeSalary,
Dept
FROM
Employee
WHERE
Dept = 'Marketing'
OR Dept = 'IT';
Output:
The following query will show how to use the different operators mentioned previously.
Problem statement 7
Find all the employee names whose age is odd or whose salary is less than equal to 30000 and print EmployeeName, EmployeeAge, and EmployeeSalary.
SQL Query:
SELECT
EmployeeName,
EmployeeAge,
EmployeeSalary
FROM
Employee
WHERE
EmployeeAge % 2 != 0
OR EmployeeSalary <= 30000;
Output:
Combining AND & OR clause
AND and OR clauses can be combined to meet the query's requirements.
Easy
Problem statement 8
Find all the employee names whose age is not equal to 24 and also need to find the salaries of employees over 50000 or less than 35000 and print EmployeeName, EmployeeAge, and EmployeeSalary.
SQL Query:
SELECT
EmployeeName,
EmployeeAge,
EmployeeSalary
FROM
Employee
WHERE
EmployeeAge != 24
AND (
EmployeeSalary > 50000
OR EmployeeSalary <= 35000
);
Output:
Medium
Problem statement 9
Find all the employee names whose salary is less than 40000 or the employees whose age is less than 28 & greater than 23. We also need to print EmployeeName, EmployeeAge, and EmployeeSalary.
SQL Query:
SELECT
EmployeeName,
EmployeeAge,
EmployeeSalary
FROM
Employee
WHERE
EmployeeSalary < 40000
OR (
EmployeeAge < 28
AND EmployeeAge > 23
);
Output:
Hard
Problem statement 10
Find all employees that are in HR dept but are not 24 years old and do not have salary greater than 20000
SQL Query:
SELECT
*
FROM
Employee
WHERE
(
Dept = 'HR'
AND EmployeeAge != 24
)
OR (EmployeeSalary < 20000);
Working of AND and OR clauses with different statements
The implementation of both clauses is the same for all statements like UPDATE, INSERT, and DELETE.
Let's examine a few instances:
UPDATE statement
Problem statement 11
Update the employee salary to 75000 and change the employee's department to Hiring, whose age is equal to 23 and salary is less than 40000.
SQL Query:
UPDATE
Employee
SET
EmployeeSalary = 75000,
Dept = 'Hiring'
WHERE
EmployeeAge = 23
AND EmployeeSalary < 40000;
After the above query, no table will be printed.
Write a simple SQL query to print the entire table.
SELECT
*
FROM
Employee
Result for the above query:
DELETE Statement
Problem statement 12
Delete employee’s data who are in the HR department or have salaries of more than 50000.
SQL Query:
DELETE FROM
Employee
WHERE
Dept = 'HR'
OR EmployeeSalary > 50000;
Note: After the above query, no table will be printed.
Write a simple SQL query to print the entire table.
SELECT
*
FROM
Employee
Result for the above query:
Frequently Asked Questions
With which type of statement does the AND & OR clause work?
A SELECT, INSERT, UPDATE, or DELETE query can test for many criteria by combining the AND and OR clauses.
Is it valid to use the AND & OR clauses in different combinations?
Using both clauses in one query is possible, but using parentheses is mandatory so the SQL can determine how to evaluate the conditions.
What are some uses for SQL?
When speaking with a database, SQL is utilized. RDBMS use it as their standard language. For example, updating data in a database or retrieving data from a database are actions that are carried out using SQL statements.
What is an example of an RDBMS?
A relational database management system is referred to as an RDBMS. It's a system for managing information with a data model as its focal point. Here, all of the data is correctly recorded in tables. SQL Server, Oracle, MySQL, MariaDB, and SQLite are examples of RDBMS systems.
What do clauses in a query mean?
An SQL clause is a query component that enables you to filter or personalize how you want your data to be accessed.
Conclusion
This article discusses how to use AND & OR Clauses using various examples and write SQL queries. We also saw to use of both Clauses in combinations. Also, learn when to use these clauses based on queries asked. You can also refer to the below articles: