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.