Hey there 👋
Hope you are doing well 🙂
In the last blog we have discussed about SELECT command and different use cases. In this blog we are going to see SQL operators ,wildcards and aggragate functions.
SQL Operators
SQL has different types of operators such as Arithmatic, Bitwise, Comparison, Compound and Logical operators. We will see one use case of each operator.
Arithmatic Operator
These are used to perform basic arithmatics on SQL numerical data.
We have aggregator functions to find the sum of complete column.
Bitwise Operator
These are basically used to do bitwise calculations.
As bitwise representation of 3 is 011
and 5 is 101
there bitwise OR will be 111
which is 7.
Comparison Operator
These are generally used with Where keyword, they generally represents a condition in the query.
Suppose we have a table that contain Students' roll_no, name and marks.
Here is the table -:
Now in this table we need to find the record of all those students whose marks is greater than or equal to 90.
So this is how we can do this -:
So here we have applied condition on Marks column and displayed the data of all those students whose Marks>=90.
Compound Operator
These are shorthand notations used to simplify expressions that update a column's value by performing an operation and then assigning the result back to that column.
This query will update Student table and increase Marks of each student by 2. We can use this operation on applying condition too for eg marks of only those students should be increased whose roll_no is 5.
Note -: Compound operators are not supported in all SQL dialects. For example, they are widely used in T-SQL (SQL Server) but may not be supported in MySQL or Oracle.
Logical Operator
Wildcards
A wildcard character is used to substitute one or more characters in a string.
Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
Wildcard Characters
- Not supported in PostgreSQL and MySQL databases. ** Supported only in Oracle databases.
Suppose we need to display all the students whose name starts with 'J. Here's how we can do it-:
Wildcards in SQL uses Regular Expression Matching or Regex behind the scenes.
Aggregator Functions
An aggregate function is a function that performs a calculation on a set of values, and returns a single value.
The most commonly used SQL aggregate functions are:
-
MIN()
- returns the smallest value within the selected column -
MAX()
- returns the largest value within the selected column -
COUNT()
- returns the number of rows in a set -
SUM()
- returns the total sum of a numerical column -
AVG()
- returns the average value of a numerical column
All of the above functions (except Count()) ignores Null values.
Suppose we need to find average marks of all students. This is how we can do it-:
Group By
The GROUP BY
clause in SQL is used to group rows with the same values in specified columns into aggregated data. It is often used in conjunction with aggregate functions such as COUNT()
, SUM()
, AVG()
, MAX()
, and MIN()
to perform calculations on each group.
So these were SQL operators, wildcard and aggregating functions.
In the next blog we will see alter table and its use cases.
I hope you liked my blog. Please leave some ❤ and don't forget to follow me.
Also have you checked my Instagram page where I upload necessary resources for software engineers.
If not check it out here -:
👉 Instagram: https://www.instagram.com/fluxx_96/
👉 YouTube: https://www.youtube.com/@Fluxx-n4q/shorts
Thankyou 💙
Top comments (0)