Perform queries involving predicates LIKE, BETWEEN, IN etc
LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.There are two wildcards often used in conjunction with the LIKE operator:
-
" % " - The percent sign represents zero, one, or multiple characters
-
" _ " - The underscore represents a single character
|
Syntax : SELECT column1, column2, ...FROM table_name
WHERE columnN LIKE pattern;
|
| LIKE Operator |
Description |
| WHERE CustomerName LIKE 'a%' |
Finds any values that start with "a" |
| WHERE CustomerName LIKE '%a' |
Finds any values that end with "a" |
| WHERE CustomerName LIKE '%or%' |
Finds any values that have "or" in any position |
| WHERE CustomerName LIKE '_r%' |
Finds any values that have "r" in the second position |
| WHERE CustomerName LIKE 'a_%' |
Finds any values that start with "a" and are at least 2 characters in length |
| WHERE CustomerName LIKE 'a__%' |
Finds any values that start with "a" and are at least 3 characters in length |
| WHERE ContactName LIKE 'a%o' |
Finds any values that start with "a" and ends with "o" |
Example
Consider the employee table
|
select* from employee where address like 'B_%' ;
select* from employee where address like '_A%' ;
select* from employee where address like '_U%' ;
select* from employee where address like '%A%' ;
select* from employee where address like 'B%' ;
|
Output
Between Operator
The BETWEEN operator is a logical operator that allows you to specify whether a value in a range or not. The BETWEEN operator is often used in the WHERE clause of the SELECT, UPDATE, and DELETE statements.The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.The BETWEEN operator is inclusive: begin and end values are included.
Syntax :SELECT column_name(s)FROM table_name
WHERE column_name BETWEEN value1 AND value2; |
Example
Considering the same employee table-
|
select *from employee where salary Between 10000 and 15000 ;
select *from employee where salary>15000 and salary<30000;
select *from employee where salary not Between 10000 and 15000;
|
Output
IN Operator
The IN operator allows you to determine if a specified value matches any value in a set of values or returned by a subquery. ... Use a column or an expression ( expr ) with the IN operator in the WHERE clause. Separate the values in the list by commas (,).The IN operator allows you to specify multiple values in a WHERE clause.The IN operator is a shorthand for multiple OR conditions.
syntax : SELECT column_name(s)FROM table_name
WHERE column_name IN (value1, value2, ...); |
Example
|
select *from employee where salary in(10000,20000,30000);
select *from employee where salary not in(10000,20000,30000);
|
OUTPUT-
For more Gujarat Technological University(GTU) CS-III-Sem Database Management Systems Lab Experiments Click Here
| 
| 
