SQL WHERE Command

SELECT * FROM table_name WHERE condition
SQL WHERE condition WHERE clause is used to collect or update or manage the records from the table based on some specified condition .


Here is the table with few rows.
idnameclassmarksex
1John DeoFour75female
2Max RuinThree90male
3ArnoldThree55male
4Krish StarFour60female
5John MikeFour60female
6Alex JohnFour55male


SELECT query with LIMIT and order by with WHERE condition checking using BETWEEN range


To get only the records of Class Four students.
SELECT * FROM student WHERE  class='Four'
Here is the result
idnameclassmarksex
1John DeoFour75female
4Krish StarFour60female
5John MikeFour60female
6Alex JohnFour55male
10Big JohnFour55female
15Tade RowFour93male
16GimmyFour93male
21Babby JohnFour69female
31Marry ToeeyFour93male
This will return all the records from the table name=student of class=Four. This is what we require to get all the records of fourth standard students.

WHERE with AND

More than one condition can be added to the where clause by using various logical expressions like AND, OR, < ( less than ), greater than (> ) etc.
Collect all the records of students of fourth standard who have scored mark more than 70.
SELECT * FROM student WHERE  class='Four' AND  mark >70
idnameclassmarksex
1John DeoFour75female
15Tade RowFour93male
16GimmyFour93male
31Marry ToeeyFour93male
We used quotes for all Non Numeric columns and quotes are not used for numeric fields.
Read more on SQL Logical Operator OR , AND

WHERE with BETWEEN

Return all the records of the student table that have marks within a range.
SELECT * FROM `student` WHERE mark BETWEEN 60 and 70
idnameclassmarksex
4Krish StarFour60female
5John MikeFour60female
20JacklyNine65female
21Babby JohnFour69female
34Gain ToeSeven69male

Read more on SQL BETWEEN to get range of records

Using LIKE with WHERE

This search will return us all the records for which inside the name field 'John' is available. John name can be any where within the name column value.
SELECT * FROM `student` WHERE name LIKE '%John%'
idnameclassmarksex
1John DeoFour75female
5John MikeFour60female
6Alex JohnFour55male
7My John RobFifth78male
10Big JohnFour55female
21Babby JohnFour69female
Read more on SQL LIKE tutorial

Aggregate functions

We can use MIN(), SUM(), AVG(),COUNT() and other aggregate functions along with WHERE clause.
SELECT MAX(mark) as max_mark FROM `student` WHERE class='Three'
Number of students who got more or equal to 50 marks in class Four.
SELECT COUNT(*) FROM student WHERE class='Four' AND mark >= 50
Show Number of students, average mark, highest mark, lowest mark of class Three.
SELECT COUNT(*),AVG(mark),MAX(mark),MIN(mark) FROM student WHERE class='Three'

USING IN with WHERE

All records of student of class Fourth and Fifth
SELECT * FROM `student` WHERE class IN ('Four','fifth')
We can exclude a class by using NOT IN.
SELECT * FROM `student` WHERE class NOT IN ('Four','fifth')

Subqueries using WHERE

All details of student who got highest mark by using Subqueries.
SELECT * FROM student WHERE 
 mark =  (SELECT MAX(mark) FROM  student)
More on Subqueries

Using JOIN

SELECT t1.id, name1 FROM t1 LEFT JOIN t2 on t1.id = t2.id
 WHERE  ISNULL(t2.id)
More on Join Query

Updating records by using WHERE condition

Give additional 5 marks to all the students of class Five.
UPDATE student SET mark=mark+5 WHERE class='Five'
More on UPDATE Query

PHP code using PDO

PHP PDO connection and collecting records
require "config.php";// database connection

$sql="SELECT * FROM student WHERE class='Four' ";
echo "<table><tr><th>ID</th><th>Name</th>
	<th>Class</th><th>Mark</th></tr>";
foreach($dbo->query($sql) as $row){
echo "<tr><td>$row[id]</td><td>$row[name]</td>
	<td>$row[class]</td><td>$row[mark]</td></tr>";
}
echo "</table>";

Selecting records Case Condition Checking
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    Raju

    11-04-2013

    Can we use Where condition linking more that one table?
    wale

    23-02-2014

    I have 12 tables with the same number of fields and field names. I want to sum one of those fields that have numerical values in all tables. How can I accomplish the task.
    Ivan

    13-09-2014

    Anyone can help me how to resolve this issue?? I am getting a type mismatch with the below syntax... I dont know how to resolve it..

    adoCompName.RecordSource = "SELECT * FROM Tbl_Comp_Dtl WHERE CompName = ' * " & Text1.Text & " '"
    adoCompName.Refresh

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer