SQL Logical and Like Operators

  • By
  • April 3, 2023
  • SQL
SQL Logical and Like Operators

SQL Logical and Like Operators

The SQL Logical and Like Operators are used between multiple conditions to check whether they are true or false. These two operators are called the conjunctive operators in SQL. Both these operators return the boolean type of values i.e. true or false.

AND returns true when both or all the conditions are true. If any one of the conditions is false then returns a false result.

Syntax:

SELECT column1, column2, columnN 

FROM table_name

WHERE [condition1] AND [condition2]…AND [conditionN];

OR returns true when either of all conditions is true. If all condition is false only then OR returns a result as false. OR operator does not need to be all conditions as true.

firstly, Attention to all aspiring data professionals in Pune! Are you looking to master the art of managing databases and manipulating data? Then look no further than our SQL classes in Pune!

Syntax:

SELECT column1, column2, columnN 

FROM table_name

WHERE [condition1] OR [condition2]…AND [conditionN];

 

For Free, Demo classes Call: 020-71179559
Registration Link: Click Here!

Example For SQL Logical and Like Operators

Example

#   true &&  true => true 1

select (5<6) && (4=4); # 16*4*5*0

  

  #     false &&  true  => false 0

select (15<6) && (4=4); # 16*4*5*0

      #   false   && false=> false 0

select (67 <= 4) && (78 >=100);

    #  false || true  => true

select (15<6) ||(4=4); # 16*4*5*0

  # true || true => true

select (5<6) || (4=4);

 # false  || false => false 0

select (5=6) || (4 < 4);

Consider a table Employee

id   | name   | sal | age     

+——+——–+———

|  101 | ram    | 89999.7 |  34

|  102 | raj    | 76999.7  | 25

|  103 | priya  | 85999.7 | 28

|  104 | rakesh | 65999.7 | 29

|  105 | rakesh | 40999.7 |

The below query returns the employees from the employee table whose salary is greater than 70000 AND age is less than 30000

Select * from emp where sal > 70000 and age < 30;

Select * from emp where sal > 70000 && age < 30

 

For Free, Demo classes Call: 020-71179559
Registration Link: Click Here!

 

The result of the above query is shown below:

+——+——-+———+——+

| id   | name  | sal     | age  |

+——+——-+———+——+

|  102 | raj   | 76999.7 |   25 |

|  103 | priya | 85999.7 |   28

#whenever we are using AND operator in the query then make sure that both conditions must be performed on different columns of the table

select * from student1 where  name=”raj” and sal=79999.7; 

 

IF we write above same query using OR operator then the query will be :

Select * from emp where sal > 70000 OR age < 30;

Select * from emp where sal > 70000 || age < 30

The result of the above query is shown below:

 id   | name   | sal     | age  |

+——+——–+———+——+

|  101 | ram    | 89999.7 |   34 |

|  102 | raj    | 76999.7 |   25 |

|  103 | priya  | 85999.7 |   28 |

|  104 | rakesh | 40999.7 |   29 |

+——+——–+———+——+

#we can use OR operator with multiple conditions which are performed on the SAME column name

select * from student1 where  sal=65999.7 OR sal=89999.7;

#using AND operator with More than one condition

Consider below student1 table

—–+———–+———+——+——-+

| id   | stud_name | course  | age  | marks |

+——+———–+———+——+——-+

|   24 | ram       | java    | 13   |  78.9 |

|   25 | gita      | python  | 25   |    88 |

|   26 | harsh     | java    | 18   |  89.2 |

|   90 | harry     | testing1  | 21   |  78.9 |

|   34 | era       | hadoop  | NULL |  50.8 |

|   11 | jay       | java    | 14   |  99.4 |

1.select id,stud_name,age,marks from student1 where age=18 AND marks=89.2 AND course=”java”;

2.select *from student1 where age=18 OR marks=89.2 OR course=”java”;

3.select *from student1 where (age=18 AND marks=88) OR course=”testing”;

#OR

select *from student1 where age=18 AND marks=88 OR course=”testing”;

LIKE OPERATOR IN SQL

Like operator is used to retrieve data from the table by applying filtration ru; es and logical conditions on the table. Basically, MySQL like operator is used with wildcard characters

The LIKE operator is used in a WHERE clause to search for a given pattern in a column. We can create a pattern by using wildcard characters like %(percentage) and _(underscore). 

Here, % represents zero or more characters

E.g bo% finds a bottle,botton, botany

And _ represents b_t finds but,bat

The wildcards can also be used in with each other

Examples….

Consider the below student1 table:

| id   | stud_name | course  | age  | marks |

+——+———–+———+——+——-+

|   24 | ram       | java    | 13   |  78.9 |

|   25 | gita      | python  | 25   |    88 |

|   26 | harsh     | java    | 18   |  89.2 |

|   90 | harry     | testing1 | 21   |  78.9 |

|   34 | era       | hadoop  | NULL |  50.8 |

|   11 | jay       | java    | 14   |  99.4 |

+——+———–+———+——+——-+

#query that retrieves/access only those students whose name starts with ‘h’…

select * from student1 where stud_name like “h%”;  #hitesh, hrishikesh,ham

#query that retrieves/accesses only those students whose marks with 8

select * from student1 where marks like “8%”;  #88,89.2

#query that retrieves/access only those students whose name starts with ‘j’…

select * from student1 where stud_name like “j%”;  #hitesh, hrishikesh,ham

#query that retrieves/access only those students who has exactly 3 characters in their name

select * from student1 where stud_name like “___”;  #hitesh, hrishikesh,ham

#query that retrieves/access only those students whose names start with ‘g’ and having 4 characters in the name including g 

select * from student1 where stud_name like “g___”; 

#query that retrieves/access only those students who has  character ‘r’ on any position in their name

select * from student1 where stud_name like “%r%”; 

#query that retrieves/access only those students who has  character ‘r’ on second last position

select * from student1 where stud_name like “%r_”;

#query that retrieves/access only those students who has exactly 3 characters in their name and it end with m

select * from student1 where stud_name like “__m”;

#query that retrieves/access only those students who has exactly 3 characters in their name and having 4 characters in the course selected by them

select * from student1 where stud_name like “___” and course like “____”;

#query that retrieves/access only those students whose name end with “a” or having “a” in the second last position

select * from student1 where stud_name like “%a” OR stud_name like “%a_”;

#having an in any middle position and in the last position

select * from student1 where stud_name like “%a%a”;

#Finds any values that have “a” in the second position

select * from student1 where stud_name like “_a%”;

#exactly 3 characters

select * from student1 where stud_name like “___”;

 

For Free, Demo classes Call: 020-71179559
Registration Link: Click Here!

 

#at least(min) 3  characters

select * from student1 where stud_name like “_%_%_%”;

#starts with ‘h’ and ends with ‘y’

select * from student1 where stud_name like “h%y”;

Join our SQL Training in Pune today and take your first step towards becoming a skilled SQL professional!

Want to Explore more about SQL: Click Here

 Author:-

Pooja Nandode-Bhavsar

Call the Trainer and Book your free demo class for SQL for now!!!

© Copyright 2020 | SevenMentor Pvt Ltd.

Submit Comment

Your email address will not be published. Required fields are marked *

*
*