  # SQL Operators

Module - 7 Filtering in SQL
SQL Operators

Overview

SQL Operators are used to specify conditions in an SQL statement. The combination of values, operators, and SQL statements can be used to retrieve data from one or more tables in a database. Common operators include comparison operators such as Not Equal and Equal operators (e.g. =, >, <, >=, <=, <>), logical operators (e.g. AND, OR, NOT), arithmetic operators (e.g. +, -, *, /), and set operators (e.g. UNION, INTERSECT, EXCEPT). SQL also includes special operators, such as LIKE and BETWEEN, for additional functionality.

SQL Arithmetic Operators

A retail store uses SQL to track customer purchases. The store could use the WHERE clause to filter customer purchase data by product type and apply the greater than operator (>) to find the customers who have purchased more than a certain amount of a particular product type.Let's help the store employee with it.

1. +: Used to add two values
2. -: Used to subtract one value from another
3. *: Used to multiply two values
4. /: Used to divide one value by another
5. %: Used to calculate the remainder of a division

Examples

``````SELECT 5 + 5;
``````

This statement will add 5 and 5 together and return the result of 10.

``````SELECT 10 - 5;
``````

This statement will subtract 5 from 10 and return the result of 5.

``````SELECT 10 * 5;
``````

This statement will multiply 10 and 5 together and return the result of 50.

``````SELECT 10 / 5;
``````

This statement will divide 10 by 5 and return the result of 2.

``````SELECT 10 % 5;
``````

This statement will calculate the remainder of 10 divided by 5 and return the result of 0.

SQL Bitwise Operators

1. Bitwise AND ( & )
2. Bitwise OR ( | )
3. Bitwise XOR ( ^ )

For example, if we wanted to retrieve all records from a table whose ID is either 1, 4, or 5, we could use the following SQL query:

Table:

1Bob123 Main St
2Joe456 Main St
3Jane789 Main St
4Sam123 Elm St
5Tom456 Elm St
``````SELECT * FROM table WHERE ID & (1 | 4 | 5);
``````

This query uses the bitwise OR (|) operator to combine the values 1, 4, and 5 into a single expression. The bitwise AND (&) operator is then used to compare this expression with the values in the ID column. If any of the values match, that record will be returned.

SQL Comparison Operators

1. = (equal to)
2. <> (not equal to)
3. >(greater than)
4. < (less than)
5. >= (greater than or equal to)
6. <= (less than or equal to)

Examples

1. = (equal to)

Table:

idnameagenationality
1Alice35USA
2Bob36UK
``````SELECT * FROM table WHERE age = 35;
``````

This statement will select all records from the table where the age of the record is equal to 35.

1. <> (not equal to)
``````SELECT * FROM table WHERE age <> 35;
``````

This statement will select all records from the table where the age of the record is not equal to 35.

1. >(greater than)
``````SELECT * FROM table WHERE age > 35;
``````

This statement will select all records from the table where the age of the record is greater than 35.

1. < (less than)
``````SELECT * FROM table WHERE age < 35;
``````

This statement will select all records from the table where the age of the record is less than 35.

1. >= (greater than or equal to)
``````SELECT * FROM table WHERE age >= 35;
``````

This statement will select all records from the table where the age of the record is greater than or equal to 35.

1. <= (less than or equal to)
``````SELECT * FROM table WHERE age <= 35;
``````

This statement will select all records from the table where the age of the record is less than or equal to 35.

Key takeaways

1. SQL Operators are used to filtering, group, and sort data within relational databases.
2. The different types of SQL Operators include comparison, logical, arithmetic, and set operators.
3. Comparison operators allow you to compare values within a query and return specific results.
4. Logical operators are used to combining multiple conditions within a query.
5. Arithmetic operators are used to performing mathematical calculations on data from a database.
6. Set operators allow you to combine the results of multiple queries into one.
7. SQL Operators are essential for database querying and manipulation.

Conclusion

The retail store used SQL to filter and sort customer purchase data and successfully identify customers who had purchased more than a certain amount of a particular product type. This allowed them to target those customers with special offers and discounts, which resulted in increased sales.

Quiz

1. Which of the following is a SQL operator?
1. AND
2. OR
3. XOR
4. ALL

1. Which of the following is a logical operator?
1. AVG
2. OR
3. BETWEEN
4. MAX

1. Which of the following is a comparison operator?
1. LIKE
2. IN
3. MOD
4. INT

1. Which of the following is an arithmetic operator?
1. BETWEEN
2. +
3. AND
4. IS

Open Compiler
###### Recommended Courses
Masters in CS: Data Science and Artificial Intelligence  20,000 people are doing this course
Join India's only Pay after placement Master's degree in Data Science. Get an assured job of 5 LPA and above. Accredited by ECTS and globally recognised in EU, US, Canada and 60+ countries.
Certification in Full Stack Data Science and AI  20,000 people are doing this course
Become a job-ready Data Science professional in 30 weeks. Join the largest tech community in India. Pay only after you get a job above 5 LPA.

Related Tutorials   4209 Data Science Tutorial  1982   806   1143 GATE Data Science and AI 2024  1367

Related Articles How does Zomato use Machine Learning?  8 mins  4588 Here Is How Ai Is Changing the World of Sports Forever!  11 mins  2515 How Machine Learning is Revolutionizing Customer Credit Risk Management  5 mins  3271 Implementation of Credit Risk Using ML  9 mins  2168 How Netflix Uses ML & AI For Better Recommendation for Users  9 mins  3418 Why do we always take p-value as 5%?  7 mins  4757

AlmaBetter’s curriculum is the best curriculum available online. AlmaBetter’s program is engaging, comprehensive, and student-centered. If you are honestly interested in Data Science, you cannot ask for a better platform than AlmaBetter. Kamya Malhotra
Statistical Analyst
Fast forward your career in tech with AlmaBetter

Vikash SrivastavaCo-founder & CPTO AlmaBetter Related Tutorials to watch  Made with  in Bengaluru, India