SQL AND OR in WHERE query Commands and statements

SQL AND OR statement can be used with WHERE clause to list a set of  records with matching combination of a table. We can use these logical operators in query with NOT  combination also to filter out some records.
This is our table ( Showing 10 records ) ,

idnameclassmarksex
1John DeoFour78male
2Max RuinThree85male
3ArnoldThree55male
4Krish StarFour60male
5John MikeFour60male
6Alex JohnFour55male
7My John RobFifth78male
8AsruidFive85male
9Tes QrySix78male
10Big JohnFour55male
 
We will apply AND  command to display records of class Four with more than 70 mark.
SELECT * FROM student WHERE class='Four' AND mark >70
idnameclassmarksex
1John DeoFour78male
15Tade RowFour88male
16GimmyFour88male
31Marry ToeeyFour88female
WE can see the record returned is class= Four and mark is more than 70.

Here both the conditions are satisfied.
SELECT * FROM student WHERE class='Five' OR mark >90
idnameclassmarksex
8AsruidFive85male
12ReckySix94male
18HonnyFive75male
33Kenn ReinSix96male
Here the records returned are either belongs to class Five OR having mark more than 90. Any one of the two condition is to be satisfied.

AND OR

condition1 OR condition2 Any one of the condition has to be satisfied for the record
condition1 AND condition2 Both conditions are to be satisfied for the record.

Using both AND OR with bracket.

Display all students of class Five and Six who got more than 80 mark.
SELECT * FROM student WHERE class='Five' OR class='Six'  and mark >80
This will include some wrong records
idnameclassmarksex
8AsruidFive85male
11RonaldSix89male
12ReckySix94male
18HonnyFive75male
33Kenn ReinSix96male
35Rows NoumpSix88female
You have one student with 75 mark .... This is because in our query considers all students of class six who has got more than 80 but does not apply the same condition of mark more than 80 to students of class Five. We used one OR condition in wrong place. Here is the correct SQL
SELECT * FROM student WHERE (class='Five' OR class='Six')  and mark >80
idnameclassmarksex
8AsruidFive85male
11RonaldSix89male
12ReckySix94male
33Kenn ReinSix96male
35Rows NoumpSix88female
We have different set of pass marks for different classes, display all who got more than pass mark.
SELECT * FROM student WHERE (class='Five' AND mark>75) OR (class='Six' AND mark>80) OR (class='Seven' AND mark>85)
idnameclassmarksex
8AsruidFive85male
11RonaldSix89male
12ReckySix94male
13KtySeven88male
14BigySeven88male
25Giff TowSeven88male
28Rojj BaseSeven86male
32Binn RottSeven90male
33Kenn ReinSix96male
35Rows NoumpSix88female


DELETE Query with OR AND

Delete all records of class three and class four
DELETE  FROM student where class='Three' OR class='Four'
Delete all records from class Six and class Four who got less than 80 mark
DELETE  FROM student where (class='Six' or class='Four') and mark<80

UPDATE Query with OR AND

Let us give extra 5 mark to class Three and class Four students who got more than 80 mark.
UPDATE `student` set mark=mark + 5  WHERE (class='Three' or class='Four')  and mark >80
Your Rating


Google+

Post Comment This is for short comments only. Use the forum for more discussions.








HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2017 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer