SQL Operators and SQL
Predicates
Introduction
SQL (Structured Query Language) is used to store, retrieve, update,
and delete data from a database.
Before
learning complex SQL queries, every student should understand:
- SQL Operators
- SQL Predicates
These are used in the WHERE clause to
filter records according to conditions.
Practical Example Database
Student Table
|
StudentID |
Name |
Course |
Marks |
Age |
City |
|
101 |
Rahul |
BCA |
85 |
20 |
Ahmedabad |
|
102 |
Priya |
BBA |
72 |
19 |
Mehsana |
|
103 |
Amit |
BCA |
90 |
21 |
Ahmedabad |
|
104 |
Neha |
MCA |
65 |
22 |
Surat |
|
105 |
Karan |
BCA |
78 |
20 |
Rajkot |
1. SQL Arithmetic Operators
Arithmetic operators
perform mathematical calculations.
|
Operator |
Meaning |
|
+ |
Addition |
|
- |
Subtraction |
|
* |
Multiplication |
|
/ |
Division |
|
% |
Modulus (Remainder) |
Example 1: Addition (+)
Suppose every student gets 5 bonus marks.
SELECT Name, Marks, Marks +
5 AS BonusMarks
FROM Student;
Output
|
Name |
Marks |
BonusMarks |
|
Rahul |
85 |
90 |
|
Priya |
72 |
77 |
Example 2: Subtraction (-)
SELECT Name, Marks, Marks -
10 AS ReducedMarks
FROM Student;
Example 3: Multiplication
(*)
SELECT Name, Marks, Marks *
2 AS DoubleMarks
FROM Student;
Example 4: Division (/)
SELECT Name, Marks/2 AS
HalfMarks
FROM Student;
Example 5: Modulus (%)
SELECT Name, Marks % 2 AS
Remainder
FROM Student;
-----------------------------------------------------------------------------------
2. SQL Logical Operators
Logical operators combine multiple conditions.
|
Operator |
Meaning |
|
AND |
Both conditions must be
true |
|
OR |
At least one condition
must be true |
|
NOT |
Opposite condition |
AND Operator
Find BCA students having marks above 80 marks.
SELECT *
FROM Student
WHERE Course='BCA' AND
Marks>80;
Output
|
Name |
Course |
Marks |
|
Rahul |
BCA |
85 |
|
Amit |
BCA |
90 |
OR Operator
Find students from Ahmedabad or Surat.
SELECT *
FROM Student
WHERE City='Ahmedabad'
OR City='Surat';
NOT Operator
Find students who are NOT from
Ahmedabad.
SELECT *
FROM Student
WHERE NOT City=’Ahmedabad’;
3. SQL Range Searching
Operator
BETWEEN Operator
Used to search values within a range.
Syntax
SELECT column_name
FROM table_name
WHERE column BETWEEN value1
AND value2;
Example
Find students whose marks are between 70 and 90.
SELECT *
FROM Student
WHERE Marks BETWEEN 70 AND
90;
Output
|
Name |
Marks |
|
Rahul |
85 |
|
Priya |
72 |
|
Amit |
90 |
|
Karan |
78 |
NOT BETWEEN Operator
SELECT *
FROM Student
WHERE Marks NOT BETWEEN 70
AND 90;
Output
Neha (65)
SQL Predicate
Predicates are special conditions used inside
the WHERE clause.
1. IN Predicate
Instead of writing multiple OR conditions, use IN.
Without IN
SELECT *
FROM Student
WHERE Course='BCA'
OR Course='MCA';
Using IN
SELECT *
FROM Student
WHERE Course IN
('BCA','MCA');
Output
|
Name |
Course |
|
Rahul |
BCA |
|
Amit |
BCA |
|
Neha |
MCA |
|
Karan |
BCA |
2. NOT IN Predicate
Find students who are NOT studying BCA or MCA.
SELECT *
FROM Student
WHERE Course NOT IN
('BCA','MCA');
Output
|
Name |
Course |
|
Priya |
BBA |
3. LIKE Predicate
LIKE searches for patterns.
Wildcards:
|
Wildcard |
Meaning |
|
% |
Any number of characters |
|
_ |
One character |
Example 1
Names
starting with "R"
SELECT *
FROM Student
WHERE Name LIKE 'R%';
Output
Rahul
Example 2
Names ending
with "a"
SELECT *
FROM Student
WHERE Name LIKE '%a';
Output
Priya
Neha
Example 3
Names
containing "it"
SELECT *
FROM Student
WHERE Name LIKE '%it%';
Output
Amit
Example 4
Names having
exactly 5 letters
SELECT *
FROM Student
WHERE Name LIKE '_____';
Output
Rahul
Priya
Karan
Combined Practical Example
Find BCA students whose marks are between 70 and 90.
SELECT *
FROM Student
WHERE Course='BCA'
AND Marks BETWEEN 70 AND
90;
Find students
from Ahmedabad or Rajkot.
SELECT *
FROM Student
WHERE City IN (‘Ahmedabad’,’Rajkot’);
Find names
starting with A.
SELECT *
FROM Student
WHERE Name LIKE 'A%';
Output
Amit
Find students
who are NOT from Ahmedabad.
SELECT *
FROM Student
WHERE City NOT IN
('Ahmedabad');
Real-Life Analogy
Imagine a college student database:
- Arithmetic Operators: Calculate bonus marks, percentages, or fees.
- Logical Operators: Find students who satisfy multiple conditions (e.g., BCA students
with marks > 80).
- Range Searching (BETWEEN): Find students within a marks or age range.
- IN: Find students enrolled
in selected courses (e.g., BCA or MCA).
- NOT IN: Exclude certain
courses or cities.
- LIKE: Search students by
name patterns (e.g., names starting with "R").
Summary Table
|
Topic |
Operator/ Predicate |
Purpose |
Example |
|
Arithmetic |
+ |
Addition |
Marks + 5 |
|
Arithmetic |
- |
Subtraction |
Marks - 10 |
|
Arithmetic |
* |
Multiplication |
Marks * 2 |
|
Arithmetic |
/ |
Division |
Marks / 2 |
|
Arithmetic |
% |
Modulus |
Marks % 2 |
|
Logical |
AND |
Both conditions true |
Course='BCA' AND
Marks>80 |
|
Logical |
OR |
Any one condition true |
City='Ahmedabad' OR
City='Surat' |
|
Logical |
NOT |
Opposite condition |
NOT City='Ahmedabad' |
|
Range Searching |
BETWEEN |
Search within a range |
Marks BETWEEN 70 AND 90 |
|
Range Searching |
NOT BETWEEN |
Exclude a range |
Marks NOT BETWEEN 70 AND
90 |
|
Predicate |
IN |
Match any value in a list |
Course IN ('BCA','MCA') |
|
Predicate |
NOT IN |
Exclude listed values |
Course NOT IN
('BCA','MCA') |
|
Predicate |
LIKE |
Pattern matching |
Name LIKE 'R%' |
-----------------------------------------------------------------------------------
Key Point
- Use Arithmetic Operators for
calculations.
- Use Logical Operators to combine
multiple conditions.
- Use BETWEEN for searching values within
a range.
- Use IN instead of multiple OR conditions
for cleaner queries.
- Use NOT IN to exclude specific values.
- Use LIKE with % and _ wildcards to
search text patterns efficiently.
- Always test your queries on a sample table before applying them to a real database.
Or follow my blog from the below link
){kind=link}
0 Comments