Function and Operators
<< Getting Started With CSQL - TOC - CSQL Aggregate Functions And Grouping >>
This Chapter describes about various logical and comparison operators and Aggregate functions supported by CSQL.
Logical operators
In the previous section, single condition could be added to a query using a WHERE clause. While this is useful, usually more than a single condition is required to produce the correct result. To support multiple rules the user need to make use of NOT, OR, AND logical operators.
CSQL supports the following logical operators:
- AND
- OR
- NOT
AND
The basic way of supporting multiple conditions in a single query is by making use of AND. It provides a way of connecting two rules together such that all the conditions must be true before the row is shown. For example,
SELECT * FROM stud WHERE stud. roll=2 AND stud. name=’Rakesh Chandra’ ;
OR
Either Condition can apply. For example,
SELECT * FROM stud WHERE stud.dept_no=20 OR stud.dept_no=40;
NOT
The NOT operator does the opposite of whatever comparison is being done.
SELECT * FROM stud WHERE stud.roll !=3 ; SELECT * FROM stud WHERE NOT (stud. name=’Rakesh Chandra’)
Comparison operators
Comparison operators test whether two expressions are same or not. These are used with character, numeric, or date type or can be used in the WHERE clause. Comparison operators evaluate to a boolean value and return TRUE or FALSE based on the outcome of the tested condition.
Following comparison operators are supported:
| Operator | Name | Example | Description |
| = | Equal | Gender=’Male’ | Check if selection is equal to Male |
| >= | Greater than or equal to | Age >=18 | Check if Age is greater than or equal to 18 |
| <= | Less than or equal to | Age <= 50 | Checks if Age is greater than or equal to 50 |
| > | Greater than | Age > 24 | Checks if Age is greater than 24 |
| < | Less than | Age < 45 | Checks if Age is less than 45 |
| = | Not equal | Gender!= ‘Male’ | Checks if Gender is not equal to Male |
|---|
Mathematical operators
Below table shows the basic arithmetic operators supported in CSQL.
| Operator | Description |
| + | Addition |
| - | Subtraction |
| * | Multiplication |
| / | Division |
Note: CSQL restricts these operators to use only with UPDATE statements to modify the data.
Aggregate functions
Aggregate functions perform a calculation on a set of values and return a single value. And it is frequently used with the GROUP BY clause of the SELECT statement.
CSQL supports the below aggregate functions:
- AVG( ) – Returns the average value.
- COUNT( ) – Returns the number of rows.
- MAX( ) – Returns the largest value.
- MIN( ) – Returns the smallest value
- SUM( ) – Returns the sum.
The AVG( ) Function
It returns the average value of a numeric column. And it accepts the numeric field names to perform the query. For example,
SELECT AVG (stud.course_fee) FROM stud;
The COUNT( ) Function
It returns the number of rows that matches specified criteria and returns the number of values of the specified column except NULL values, which is integer data type value. For example,
SELECT COUNT (stud. roll) FROM stud;
If the argument is the ‘*’ qualifier then it returns the number of records and includes NULL values and duplicates also. For example,
SELECT COUNT(*) FROM stud ;
The MAX( ) Function
The MAX( ) function returns the largest value of the selected column and can be used with numeric, character, date, and time column. It returns a value same as expression and ignores any null values.
SELECT MAX (stud.course_fee) FROM stud; SELECT MAX (stud. name) FROM stud;
The MIN( ) Function
To calculate the minimum and smallest value present in a particular column the MIN function should be used. It also accepts the numeric and character in its arguments and ignores NULL values. For example,
SELECT MIN (stud.couse_fee) FROM stud;
SQL Operators
CSQL supports SQL operators such as IN, BETWEEN and LIKE.
IN operator
The IN operators allows the user to specify multiple values in WHERE clause.
SELECT * FROM stud WHERE stud. roll IN (10,40) ;
BETWEEN operator
Range operators are used to create ranges and to see if a value is within the range created. The BETWEEN operator is used in a WHERE clause to select a range of data between two values.
SELECT * FROM stud WHERE stud. roll BETWEEN 10 AND 40;
LIKE operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It makes the searching task by allowing for unknown characters (using underscore,-) and partial strings (using a wildcard, %). It takes as input any string or partial string and attempts to find a match in the data being searched. Here is the example to search the students name from stud table using both underscore and wild card. As underscore character ( _ ) matches exactly one character and percentile character (%) matches zero or more characters.
SELECT * FROM stud WHERE stud, name LIKE ‘R%’; SELECT * FROM stud WHERE stud. name LIKE ‘_i%’;
IS NULL
When a NULL is compared to any value even another NULL, the result is neither true nor false but unknown. Often we will need to distinguish between false and unknown and rows containing column values and those containing NULL, for this SQL provides special operator, which is used with the keyword NULL to locate and treat NULL values.
The form for NULL predicate is:
< col_name > IS NULL < col_name > IS NOT NULL
Example: To list all employees who receive commission?
SELECT ename, sal, comm FROM emp WHERE comm IS NOT NULL;
Example: List the highest designated employee of the company
SELECT ename, job, salary FROM emp WHERE mgr IS NULL;
<< Getting Started With CSQL - TOC - CSQL Aggregate Functions And Grouping >>