SQL Operators

• SQL operators are special words or characters used to query a database in SQL expressions
• A WHERE clause queries a database using operators.
• Operators are essential for defining a condition in SQL, connecting two or more conditions.
• Operators manipulate data, providing results based on their functionality.

Unary and Binary Operators

• Unary operators act on a single operand and appear as "operator operand".
• Binary operators act on two operands and appear as "operand1 operator operand2".

Operator Types

• Arithmetic Operators perform arithmetic operations on numerical data
  • Includes: Addition (+), Subtraction (-), Multiplication (*), Division (/), and Modulus (%)
• Comparison Operators compare values and return a Boolean (True or False) result.
  • Includes: Equal to (=), Not equal to (<> or !=), Greater than (>), Greater than or equal to (>=), Less than (<), and Less than or equal to (<=)
• Logical Operators combine multiple conditions and return a Boolean result.
  • Includes: AND (true if both conditions are true), OR (true if at least one condition is true), and NOT (reverses a condition's result).
• Bitwise Operators perform bitwise operations on integer values.
  • Includes: Bitwise AND (&), Bitwise OR (|), Bitwise XOR (^), Bitwise NOT (~), Bitwise LEFT SHIFT (<<), and Bitwise RIGHT SHIFT (>>)
• Assignment Operators assign values to variables.
  • Includes: Assignment (=), Increment (+=), Decrement (-=), Multiply and assign (*=), Divide and assign (/=), Modulus and assign (%=), Bitwise AND and assign (&=), Bitwise OR and assign (=), Bitwise XOR and assign (^=), Bitwise LEFT SHIFT and assign (<<=), and Bitwise RIGHT SHIFT and assign (>>=)
• String Concatenation Operators (e.g., || in MySQL and Oracle) concatenate strings.
• Membership Operators (IN and NOT IN in Oracle) test for membership in a set of values.
• Pattern Matching Operators (LIKE and NOT LIKE in MySQL) are used for pattern matching with wildcard characters.

Logical Operators in SQL

• SQL logical operators test the truth of a condition, returning TRUE, FALSE, or UNKNOWN.
• Logical operators combine or manipulate conditions in a query.
• AND: Returns TRUE if both Boolean expressions are TRUE.
• IN: Returns TRUE if the operand equals one of a list of expressions.
• NOT: Reverses the value of another Boolean operator.
• OR: Returns TRUE if either Boolean expression is TRUE.
• LIKE: Returns TRUE if the operand matches a pattern.
• BETWEEN: Returns TRUE if the operand is within a range.
• ALL: Returns TRUE if all comparisons in a set are TRUE.
• ANY: Returns TRUE if any comparison in a set is TRUE.
• EXISTS: Returns TRUE if a subquery contains any rows.
• SOME: Returns TRUE if some comparisons in a set are TRUE.

AND Operator

• AND returns TRUE only if all component conditions are TRUE; FALSE if any are FALSE; otherwise, it returns UNKNOWN.
• Syntax: SELECT column1, column2,... FROM table_name WHERE condition1 AND condition2 AND condition3 ...;
• Selecting all employees who work in HR and whose first name is Ram

SELECT * FROM employee WHERE Emp_dept='HR' AND F_Name='Ram';

AND Operator Alternative

• && can be used instead of AND in the WHERE clause:

SELECT * FROM employee WHERE Emp_dept='HR' && F_Name='Ram';

• Multiple AND Example:

SELECT * FROM employee WHERE Emp_dept='HR' AND F_Name='Ram'AND Emp_id=1;

OR Operator

• OR returns TRUE if at least one component condition is TRUE; FALSE if all are FALSE; otherwise, it returns UNKNOWN.
• Syntax: SELECT column1, column2,... FROM table_name WHERE condition1 OR condition2 OR condition3...;
• Output example from employee table :

SELECT * FROM employee WHERE Emp_dept='HR' OR F_Name='Aman';

OR Operator Alternative

• || can be used instead of OR for combining two conditionals:

SELECT * FROM employee WHERE (Emp_dept='HR' || F_Name='Aman');

• Multiple OR example:

SELECT * FROM employee WHERE Emp_dept='HR' || F_Name='Aman'OR Emp_id=4;

NOT Operator

• NOT is used with other operators to give the opposite or negative result.
• Syntax: SELECT * FROM Customers WHERE NOT Country = 'Spain';
• This query selects all customers that are not from Spain.

IN Operator

• The IN operator specifies multiple values in a WHERE clause and is a shorthand for multiple OR conditions.
• Syntax: SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2,...);
• To see all of the employees that work in HR or Marketing :

SELECT * FROM employee WHERE Emp_dept IN('HR',' Marketing');

NOT IN Operator

• Negates the IN operator.
• Returns all records that do not match any values in the list:

SELECT * FROM employee WHERE Emp_dept NOT IN('HR','Marketing');

BETWEEN Operator

• BETWEEN defines a range limit value that can be numbers, text, or dates.
• The BETWEEN command is inclusive of the begin and end values:

SELECT Column_Name_1, Column_Name_2, Column_Name_3,......, Column_Name_N FROM Table_Name WHERE Column_Name BETWEEN Value_1 AND Value_2; 

• To select all employees where their employee number is between 1-4:

SELECT * FROM employee WHERE Emp_id between 1 AND 4;

NOT BETWEEN Operator

• Returns the values that are not in the range specified.
• For example finding all numbers that are NOT between employee number 1-4

SELECT * FROM employee WHERE Emp_id Not between 1 AND 4;

LIKE Operator

• LIKE is used for pattern matching in character string comparisons
• Returns rows where the operand matches a specified pattern and is used in a WHERE clause to search for patterns in a column.
• Wildcards used:
  • %: represents zero, one, or multiple characters.
  • _: represents one, single character.
• Syntax: SELECT column1, column2,... FROM table_name WHERE column N LIKE pattern;
  • The underscore represents a single character

SELECT column1, column2,... FROM table_name WHERE column N LIKE pattern;

• The % wildcard represents any number of characters including zero

SELECT * FROM Customers WHERE city LIKE '%L%';

• Given an employee table

+--------+-----------+---------+
| Emp_id | F_Name | L_Name |
+--------+-----------+---------+
|   1 | Ram       | Sharma |
|   2 | John      | joseph |
|   3 | Aman       | Agrawal |
|   4 | Bob      | Perish |
|   5 | Catherine | Hanks |
|   6 | Arun      |P      |
+--------+-----------+---------+

• The following can be used to retrive the employee(s) whose name starts with the letter “A”.

SELECT * from Emp_info where F_name Like'A%';

• The following can be used to retrive the employee(s) whose name ends with the letter “M”.

SELECT * from Emp_info where F_name Like'%m';

• The following can be used to retrive the employee(s) whose name contains the characters “AT”.

SELECT * from Emp_info where F_name Like'%at%';

• The following can be used to retrive the employee(s) whose name have the letter “O” at the second index.

SELECT * from Emp_info where F_name Like'_O%';

• The following can be used to retrive the employee(s) whose name have the letter “O” at the second index and have 4 characters.

SELECT * from Emp_info where F_name Like'_O__';

SET Operators

• SET operators combine the results of two queries.
• Operators:
  • UNION
  • UNION ALL
  • INTERSECT
  • MINUS

Union Operator

• Combines the result of two select statements and eliminates duplicate rows.
• The UNION statement must have the same number of columns and the same domain of every column:

mysql> select * from Emp_info Union select * from Emp_inf02;

UNION ALL

• Combines all records from both queries, including duplicates:

mysql> select * from Emp_info Union All select * from Emp_info2;

INTERSECT

• Combines two SELECT statements and returns only records common to both.

select * from Emp_info intersect select * from Emp_info2;

EXCEPT

• Displays rows present in the first query but absent in the second query, with no duplicates:

mysql> select * from Emp_info Except select * from Emp_info2;

Concatenation Operator

• Concatenation operators link columns or character strings.

• A sample query using a concat of first and last name from sql db:

select F_Name,L_name, CONCAT(F_name,’ ‘,L_name )from employee;

Aliases

• SQL aliases provide temporary names to tables or columns which increases readability during a query.
• Aliases created with the AS keyword only exist for the duration of the query.
• Syntax: SELECT column_name AS alias_name FROM table_name;
• Example:

mysql> select emp_id AS id from employee;

Aggregate Operators

• Aggregate functions perform a calculation on a set of values, returning a single value.
• Often used with the GROUP BY clause to split the result-set and return a single value for each group.

SQL MIN() and MAX() Functions

• MIN() returns the smallest value in a selected column.
• MAX() returns the largest value in a selected column:

mysql> select max(salary) from employee;
mysql> select min(salary) from employee;

COUNT() Function

• COUNT() returns the number of rows matching a specified criterion.
• Total number of rows:

mysql> select COUNT(*) from employee;

• Total entires in a column called "city":

mysql> select COUNT(city) from employee;

Count Distinct

• Using the DISTINCT keyword in COUNT() returns the number of different entries:

mysql> select COUNT(distinct emp_id) from employee;

Distinct

• SELECT DISTINCT statement returns only distinct (different) values:

mysql> select Distinct(Emp_id) from employee;

SUM() Function

• SUM() returns the total sum of a numeric column:

SELECT SUM(column_name) FROM table_name WHERE condition;
mysql> select Sum(salary) from employee;

AVG() Function

• AVG() returns the average value of a numeric column:

SELECT AVG(column_name) FROM table_name WHERE condition;
mysql> select Avg(salary) from employee;

GROUP BY

• GROUP BY groups rows with the same values into summary rows (e.g., "find the number of customers in each country").
• Used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.
• Syntax:

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s);
mysql> select emp_dept from employee group by emp_dept;

ORDER BY

• ORDER BY sorts the result-set in ascending or descending order:

SELECT column1, column2,...FROM table_name ORDER BY column1, column2,... ASC|DESC;

• Examples:

mysql> select * from employee ORDER BY salary ASC;
mysql> select * from employee ORDER BY salary DESC;

SQL HAVING Clause

• HAVING clause cannot be used with aggregate functions and the WHERE keyword.
• Syntax:

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);

• List the number of employees in each department, only for departments with more than 2 employees:

mysql>select count(emp_id),emp_dept from employee group by emp_dept HAVING Count(Emp_id)>2;