Single row Multiple Row
1. It operates on a single row at a It operates on groups of rows.
time. It returns one result for a group of
2. It returns one result per row. rows.
3. It can be used in Select, Where, It can be used in the select clause
and Order by clauses. only.
4. Math, String and Date Max(),Min(), Avg(), Sum(), Count()
functions are examples of and Count(*) are examples of
single-row functions. multiple row functions.
(H) Substring pattern matching
In some situations where we do not want to query by matching exact text or value.
Rather, we are interested in finding a match of only a few characters or values in
column values. For example, to find out names starting with “T” or to find out pin
codes starting with ‘60’. This is called substring pattern matching. We cannot match
such patterns using an operator, as we are not looking for an exact match. SQL
provides a LIKE operator that can be used with the WHERE clause to search for a
specified pattern in a column.
The LIKE operator makes use of the following two wild card characters:
% (per cent)- used to represent zero, one, or multiple characters
_ (underscore)- used to represent exactly a single character
Example: The following query selects details of all those employees whose name
starts with ’ ‘.
MySQL>SELECT * FROM EMPLOYEE
-> W HERE Ename like ‘K%’;
1 row in set (0.00 sec)
Example 9.14 The following query selects details of all those employees whose name
ends with ‘a’, and gets a salary of more than 45000.
MYSQL> SELECT * FROM EMPLOYEE
-> WHERE Ename like ‘%a’
-> AND Salary > 45000;
MOHAN C, HOD,DEPT. OF COMPUTER SCIENCE 1
When we type the first letter of a contact name in our contact list on our mobile
phones, all the names containing that character are displayed. Can you relate the
SQL statement to the process? List other real-life situations where you can visualise
a SQL statement in operation.
Example: The following query selects details of all those employees whose name
consists of exactly 5 letters and starts with any letter but has ‘ANYA’ after that.
MYSQL> SELECT * FROM EMPLOYEE
-> WHERE Ename like ‘_ANYA’;
Example 9.16 The following query selects the names of all employees containing ‘se’ as
a substring in their names.
mysql> SELECT Ename FROM EMPLOYEE
-> WHERE Ename like ‘%se%’;
Example 9.17 The following query selects the names of all employees containing ‘a
as the second character.
_mysql> SELECT EName FROM EMPLOYEE
-> WHERE Ename like ‘a%’;
MOHAN C, HOD,DEPT. OF COMPUTER SCIENCE 2