Section 2

Section 2: SQL Operators

Learning Objective: By the end of this lesson, learners will be able to:

• Describe SQL operators, including Logical Operators, Arithmetic Operators, Comparison Operators.

• Explain common SQL operators.

Table of Contents

• Overview of SQL Operators:

  • Logical Operators.

  • Arithmetic Operators.

  • Comparison Operators.

• IS NULL and IS NOT NULL Operators.

• IN Operator.

• Overview of CASE Statement.

Overview of SQL Operators

SQL comes with special characters or words to perform certain operations. MySQL Operators are applied to the operands to carry out specific operations.

SQL operators common categories are:

• Logical Operators.

• Arithmetic Operators.

• Comparison Operators.

Logical Operators

Some Logical Operators are:

Operator	Description
BETWEEN	It is used to search within a set of values, by the minimum value and maximum value provided.
EXISTS	It is used to search for the presence of a row in a table which satisfies a certain condition specified in the query.
OR	It is used to combine multiple conditions in a statement by using the WHERE clause.
AND	It allows the existence of multiple conditions in an SQL statement by using the WHERE clause.
NOT	It reverses the meaning of the logical operator with which it is used. (Examples: NOT EXISTS, NOT BETWEEN, NOT IN, etc.)
IN	It is used to compare a value in a list of literal values.
ALL	It compares a value to all values in another set of values.
ANY	It compares a value to any value in the list according to the condition specified.
IS NULL	It compares a value with a NULL value.
UNIQUE	It searches for every row of a specified table for uniqueness (no duplicates).

Arithmetic Operators

In SQL, arithmetic operators are used to perform the arithmetic operations as described below:

Operator	Description	Example
+	Addition of two operands	a+b
-	Subtraction of right operand from the left operand	a-b
*	Multiplication of two operands	a*b
/	Division of left operand by the right operand	a/b
%	Modulus – the remainder of the division of left operand by the right	a%b

Comparison Operators

The comparison operators in SQL are used to compare values between operands and return true or false according to the condition specified in the statement.

IS NULL and IS NOT NULL Operators

IS Null and IS NOT NULL both are logical operators. To test whether a value is NULL or not, we can use the IS NULL operator.

IS NULL Syntax:

SELECT column_names FROM table_name WHERE column_name IS NULL;

IS NOT NULL Syntax:

SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

Example: IS NULL Operators

We will use the customers table in the classicmodels database for the demonstration. The following query uses the IS NULL operator to find customers who do not have a sales representative:

SELECT customerName, country, salesrepemployeenumber FROM classicmodels.customers WHERE salesrepemployeenumber IS NULL ORDER BY customerName;

Example: IS NOT NULL Operators

We will use the customers table in the classicmodels database for the demonstration. The following query uses the IS NOT NULL operator to find customers who do not have a sales representative:

SELECT customerName, country, salesrepemployeenumber FROM classicmodels.customers WHERE salesrepemployeenumber IS NOT NULL ORDER BY customerName;

IN Operator

• The IN operator allows you to specify multiple values in a WHERE clause.

• The IN operator allows you to determine if a specified value matches any value in a set of values or is returned by a subquery.

Syntax:

• Basic:

SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);

• Advance:

SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT STATEMENT);

IN Operator Example

SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK');

Overview of CASE Statement

• The CASE statement is a Control Flow statement.

• The CASE statement chooses from a sequence of conditions and runs the corresponding statement.

• The CASE statement has two forms:

  • Simple - a single expression is evaluated and compared to potential matches.

  • Searched - multiple conditions are evaluated and the first true condition is selected.

Syntax: Simple CASE Statement

CASE given_value
WHEN condition_value_1 THEN statements_1
WHEN condition_value_2 THEN statements_2
...
WHEN condition_value_n THEN statements_n
[ ELSE else_statements ]
END CASE;

The given_value is an expression – usually a scalar variable. Each condition_value can be either literal or an expression, and all must be of the same data type as the given_value.

Syntax: Searched CASE Statement

CASE
WHEN condition_1 THEN statements_1
WHEN condition_2 THEN statements_2
...
WHEN condition_n THEN statements_n
[ ELSE else_statements ]
END CASE;

Example 1: Simple CASE statement

In this example, we will use a simple Case statement to determine the warehouse based on the orderLinenumber column from orderdetails table.

SELECT orderLineNumber,
    CASE od.orderLineNumber
        WHEN 1 THEN 'NYC Warehouse'
        WHEN 2 THEN 'NJ Warehouse'
        WHEN 3 THEN 'CA Warehouse'
        WHEN 4 THEN 'PA Warehouse'
        ELSE 'ML warehouse'
    END AS Product_status
FROM classicmodels.orderdetails od;

Example 1 - Searched Case Statements

In this example, we will use a search Case statement to determine the status of the buying price on the buyPrice column from products table.

SELECT productName, buyPrice,
CASE
    WHEN buyPrice > 9 AND buyPrice <=  49 THEN "LOW PRICE"
    WHEN buyPrice >= 50 AND buyPrice <= 100 THEN "Medium Price"
    WHEN buyPrice > 100 AND buyPrice <= 200 THEN "high Price"
    ELSE "Out of our range"
END AS priceStatus
FROM products ORDER BY buyPrice DESC;

Example 2 - Searched Case statements

The following example uses the CASE statement to sort customers by states if the state is not NULL, or sort the country in case the state is NULL:

SELECT customerName, state, country
FROM customers
ORDER BY (
    CASE
        WHEN state IS NULL THEN country
        ELSE state
    END
);

Knowledge Check

• What is an aggregate function?

• Does COUNT() function return the number of columns in a table?

• Which keyword is used to join multiple strings into a single string?

Summary

SQL offers aggregate functions that can help with the Data computation and manipulation, as seen in the list below. In order to create more sophisticated data manipulation with aggregate functions, you have to use the GROUP BY clause. This groups together all rows that have the same values. You can use the HAVING clause if you need to filter the result of an aggregate function.

SQL Functions:

• AVG() - Returns the average value.

• COUNT() - Returns the number of rows.

• FIRST() - Returns the first value.

• LAST() - Returns the last value.

• MAX() - Returns the largest value.

• MIN() - Returns the smallest value.

• SUM() - Returns the sum.

• UCASE() - Converts a field to uppercase.

• ROUND() - Rounds a numeric field to the number of decimals specified.

• NOW() - Returns the current system date and time.

The case statement in SQL returns a value on a specified condition. We can use a case statement in select queries along with the Where, Order By, and Group By clauses.

References

• https://dev.mysql.com/doc/refman/5.7/en/case.html

• https://dev.mysql.com/doc/refman/5.7/en/functions.html

• https://dev.mysql.com/doc/refman/5.7/en/numeric-functions.html

• https://popsql.com/learn-sql/mysql/how-to-write-a-case-statement-in-mysql

Questions?

If you have any questions, please ask your instructors.