Operators in RDBMS with SQL Queries

Learn operators in RDBMS with SQL queries and examples. Understand arithmetic, comparison, logical, special, and set operators with practical MySQL examples for students and beginners.

Operators in RDBMS with SQL Queries

Introduction

Operators in RDBMS are special symbols or keywords used in SQL queries to perform operations on data.
They are used in SELECT, WHERE, HAVING, and JOIN clauses to filter, compare, and manipulate data.

Types of Operators in SQL

1. Arithmetic Operators

Arithmetic operators are used to perform mathematical calculations on numeric data.

Example

SELECT price, price * 2 AS double_price
FROM part;

2. Comparison (Relational) Operators

Used to compare values in SQL queries.

Example

SELECT *
FROM part
WHERE price > 50;

3. Logical Operators

Logical operators are used to combine multiple conditions.

Example

SELECT *
FROM part
WHERE color = ‘Red’ AND price > 10;

4. Special Operators

a) IN Operator

Checks whether a value exists in a given list.

SELECT *
FROM supplier
WHERE City IN (‘Delhi’, ‘Mumbai’);

b) BETWEEN Operator

Checks whether a value lies within a range.

SELECT *
FROM part
WHERE price BETWEEN 10 AND 50;

c) LIKE Operator

Used for pattern matching.

SELECT *
FROM part
WHERE pname LIKE ‘B%’;

d) IS NULL Operator

Checks for NULL values.

SELECT *
FROM supplier
WHERE Phone IS NULL;

5. Set Operators

Used to combine results of two queries.

Example

SELECT City FROM supplier1
UNION
SELECT City FROM supplier2;

Advantages of Operators

• Helps in data filtering and comparison
• Simplifies complex conditions
• Improves query flexibility
• Essential for decision-making queries

Conclusion

Operators are a core part of SQL queries.
They allow arithmetic calculations, comparisons, logical decisions, pattern matching, and result set operations.
Proper use of operators makes SQL queries efficient, accurate, and powerful.

Queries on Operators

1. Find the pname, phoneno and cost of parts which have cost ≥ 200 and ≤ 600

Operator used: BETWEEN

SELECT p.pname,
p.price AS cost,
s.Phone AS phoneno
FROM part p
JOIN supplies sp ON p.PID = sp.PID
JOIN supplier s ON s.SID = sp.SID
WHERE p.price BETWEEN 200 AND 600;

2. Find the sname, SID and branch of suppliers who are in ‘local’ or ‘global’ branch

Operator used: OR / IN

SELECT SID, Sname, Branch
FROM supplier
WHERE Branch = ‘local’ OR Branch = ‘global’;

3. Find the pname and color of parts which has the word ‘NET’ anywhere in pname

Operator used: LIKE

SELECT pname, color
FROM part
WHERE pname LIKE ‘%NET%’;

4. Find the PID and pname of parts with pname either ‘NUT’ or ‘BOLT’

Operator used: OR / IN

SELECT PID, pname
FROM part
WHERE pname IN (‘Nut’, ‘Bolt’);

5. List the suppliers who supplied parts on

‘1st May 2000’, ‘12 Jan 2021’, ‘17 Dec 2000’, ‘10 Jan 2021’

Operator used: IN

SELECT DISTINCT s.SID, s.Sname
FROM supplier s
JOIN supplies sp ON s.SID = sp.SID
WHERE sp.date_supplied IN
(‘2000-05-01’, ‘2021-01-12’, ‘2000-12-17’, ‘2021-01-10’);

6. Find all the distinct costs of parts

Operator used: DISTINCT

SELECT DISTINCT price AS cost
FROM part;

Queries on Operators

1. Find the pname, phoneno and cost of parts which have cost equal to or greater than 200 and less than or equal to 600.

Ans. : – SELECT p.pname,

->        p.price AS cost,

->        s.Phone AS phoneno

-> FROM part p

-> JOIN supplies sp ON p.PID = sp.PID

-> JOIN supplier s ON s.SID = sp.SID

-> WHERE p.price BETWEEN 50 AND 150;

2. Find the sname , SID and branch of suppliers who are in ‘local’ branch or ‘global’ branch

select * from supplier where Branch=’Electronics’ or Branch=’Computer’;

3. Find the pname and color of parts, which has the word ‘NET’ anywhere in its pname.

select * from part where pname like ‘%ear%’;

4. Find the PID and pname of parts with pname either ‘NUT’ or ‘BOLT’

mysql> select * from part where pname=’Bolt’ or pname=’Nut’;

5. List the suppliers who supplied parts on ‘1st may2000’, ‘12 JAN 2021’ ,’17 dec 2000’,’10 Jan 2021’

mysql> SELECT DISTINCT s.SID, s.Sname

-> FROM supplier s

-> JOIN supplies sp ON s.SID = sp.SID

-> WHERE sp.date_supplied IN

-> (‘2025-05-01’, ‘2025-01-12’, ‘2025-12-17’, ‘2025-01-10’);

Some More: 

• POP- Introduction to Programming Using ‘C’
• DS – Data structure Using C
• OOP – Object Oriented Programming 
• Java Programming
• DBMS – Database Management System
• RDBMS – Relational Database Management System
• https://defineinfoloop.blogspot.com/?m=1