Operators in SQL with Example
Various Types of Operators in SQL are :
Arithmetic Operators :
+ (ADDITION) - (DIFFERENCE) * (MULTIPLY) / (DIVISION)
Relational Operators :
= (EQUAL TO) < (LESS THAN) > (GREATER THAN) <> (NOT EQUAL TO) <= (LESS THAN EQUAL TO) >= (GREATER THAN EQUAL TO)
Conjunction Operators : Conjunction Operators are used to combine the conditions. Operators are :
AND Operator OR Operator
Logical Operators : Logical Operators in SQL are :
|Unique||Used to search every row of a specified table for uniqueness (no duplicate)|
|All||Used to compare a value to all values in another value set.|
|Any||Used to compare a value to any applicable value in the list according to the condition|
|Is Null||Used to compare a value with a NULL value|
|Between||Used to search for values that are within a set of value, given the minimum value and maximum value.|
|In||Used to compare a value to a list of literals values that have been specified.|
|Like||Used to compare a value to similar values using wildcard operators. There are two wildcards:
(%) – It(percent symbol) indicates sequence of 0 or more characters or numbers.
(_) – It(underscore) indicates a single number or character
|Exist||Used to search for the presence of a row in a specified table that meets certain criteria.|
Negating Conditions with the NOT operator :
The NOT operator reverses the meaning of the logical operators with which it is used. For example,
- [NOT] IN
- ]NOT] EXIST
- IS [NOT] NULL
- [NOT] BETWEEN
- [NOT] LIKE
The operator enclosed in square brackets [..] is optional.
Aggregate Functions or Summary Functions
Aggregate function is used to provide summarisation information for SQL statement, count , total and averages. Some aggregate functions are:
|Count ([DISTINCT] attribute)||Used to count rows or values of a column that do not contain null value.|
|Count (*)||Used to count all the rows of a table including duplicates.|
|Sum ([DISTINCT] attribute)||Used to return the total awesome on the values of a numeric column for a group of rows.|
|AVG ([DISTINCT] attribute)||Used to find average is for a group of rows.|
|Max (attribute)||Used to return the maximum value for the values of a column in a group of rows.|
|Min (attribute)||Used to return the minimum value for the values of a column in a group of rows.|
Null Values are always discarded from aggregation Operators.
Set Operators in SQL
- UNION ALL
- INTERSECT ALL
- MINUS ALL
Consider the two relation R(A) and S(A) such that
The CONTAIN Operator is similar to Division Operator in Relational Algebra.
NULL values :
NULL is the set of some randomly generated ASCII value, generated by DBMS engine.
- No two NULL values are equal.
- NULL value is non zero.
More About Operators :
The BETWEEN and NOT BETWEEN Operator
The BETWEEN and NOT BETWEEN operators are used in range queries.
The BETWEEN operator select a range of data between two values. The values can be numbers, text or dates.
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
The NOT BETWEEN operator select the data outside the range of data between two values. The values can be text, numbers or date.
SELECT column_name(s) FROM table_name WHERE column_name NOT BETWEEN value1 AND value2;
Combining AND & OR Operator
We can also combine and and or useful and used parenthesis to form complex expressions.
SELECT * FROM Student WHERE Age=19 AND (Name = 'Ankit' OR Marks = 458)
All and any
- If subquery is prefixed with ‘ANY’ keyword, then the condition will be true, if it is satisfied by any of the values produced by inner query.
- Is subquery is prefixed with ‘ALL’ keyword, then the condition is true, if it is satisfied by all the values produced by inner query.
Example 1 :
Consider the relation Employee(Eno,Ename,Sal,Dno)
Question : Retrieve employees whose salary is greater than salaries of any employee in department 5. Query : SELECT Eno FROM Employee WHERE sal > ANY (SELECT Sal FROM Employee WHERE Dno=5) Solution : >ANY(15,25) ⇒ E4
The EXIST and NOT EXIST Operator
- If the subquery is prefixed with ‘EXIST’ operator, then the condition is treated as true, if inner query returns non empty rows.
- If the inner query is prefixed with ‘NOT EXIST’, then the condition is treated as true, if inner query returns empty rows.
- Always EXIST and NOT EXIST should have SELECT command.
Difference Between EXIST & ALL and ANY
|EXIST||ALL and ANY|
|Any query formulated in ALL and ANY can also be formulated in EXIST.||Any query formulated in EXIST may not be formulated in ALL and ANY.|
|Exist queries always leads to correlated subquery.||They need not be correlated query|
|They are less efficient.||They are more efficient.|
|Query and Subquery in SQL||SQL LIKE Operator|
Incoming search terms:
- questions on operators in sql