The WHERE provision is utilized in the SELECT, DELETE or, UPDATE explanations to determine the columns on which the activity should be done. Generally, this statement is trailed by a condition or articulation which restores a Boolean worth, the Select, erase or, update tasks are performed uniquely on the columns which fulfill the given condition.
ij> SELECT * from table_name WHERE condition;
or,
ij> DELETE from table_name WHERE condition;
or,
ij> UPDATE table_name SET column_name = value WHERE condition;
The WHERE statement can utilize the correlation administrators, for example, =,!=, <, >, <=, and >=, just as the BETWEEN and LIKE administrators.
Example
Allow us to expect we have a table named Employees in the data set with 7 records as demonstrated beneath −
ID |NAME |SALARY |LOCATION
-----------------------------------------------------------------------------
1 |Amit |30000 |Hyderabad
2 |Kalyan |40000 |Vishakhapatnam
3 |Renuka |50000 |Delhi
4 |Archana |15000 |Mumbai
5 |Trupthi |45000 |Kochin
6 |Suchatra |33000 |Pune
7 |Rahul |39000 |Lucknow
The accompanying SQL DELETE proclamation brings the records of the representatives whose compensation is more than 35000 −
ij> SELECT * FROM Employees WHERE Salary>35000;
This will deliver the accompanying yield −
ID |NAME |SALARY |LOCATION
---------------------------------------------------
2 |Kalyan |40000 |Vishakhapatnam
3 |Renuka |50000 |Delhi
5 |Trupthi |45000 |Kochin
7 |Rahul |39000 |Lucknow
4 rows selected
Likewise, you can likewise erase and refresh records utilizing this proviso.
Following model updates the area of those whose compensation is under 30000.
ij> UPDATE Employees SET Location = 'Vijayawada' WHERE Salary<35000;
3 rows inserted/updated/deleted
On the off chance that you check the substance of the table, you can see the refreshed table as demonstrated underneath −
ij> SELECT * FROM Employees;
ij> SELECT * FROM Employees;
ID |NAME |SALARY |LOCATION
------------------------------------------------------------------------------
1 |Amit |30000 |Vijayawada
2 |Kalyan |40000 |Vishakhapatnam
3 |Renuka |50000 |Delhi
4 |Archana |15000 |Vijayawada
5 |Trupthi |45000 |Kochin
6 |Suchatra |33000 |Vijayawada
7 |Rahul |39000 |Lucknow
7 rows selected
Where clause JDBC example
This segment shows you how to utilize WHERE provision and perform CURD procedure on a table in Apache Derby data set utilizing JDBC application.
On the off chance that you need to demand the Derby network worker utilizing network customer, ensure that the worker is fully operational. The class name for the Network customer driver is org.apache.derby.jdbc.ClientDriver and the URL is jdbc:derby://localhost:1527/DATABASE_NAME;create=true;user=USER_NAME;passw ord=PASSWORD".
Follow the means offered beneath to utilize WHERE statement and perform CURD procedure on a table in Apache Derby
Step 1: Register the driver
To speak with the information base, above all else, you need to enlist the driver. The forName() technique for the Class acknowledges a String esteem speaking to a class name loads it in to the memory, which naturally enlists it. Register the driver utilizing this technique
Step 2: Get the connection
When all is said in done, the initial step we do to convey to the data set is to interface with it. The Connection class speaks to the actual association with an information base worker. You can make an association object by summoning the getConnection() strategy for the DriverManager class. Make an association utilizing this technique.
Step 3: Create a statement object
You need to make a Statement or PreparedStatement or, CallableStatement objects to send SQL proclamations to the data set. You can make these utilizing the strategies createStatement(), prepareStatement() and, prepareCall() individually. Make both of these items utilizing the suitable technique.
Step 4: Execute the query
Subsequent to making an assertion, you need to execute it. The Statement class gives different techniques to execute a question like the execute() strategy to execute an explanation that profits more than one outcome set. The executeUpdate() technique executes questions like INSERT, UPDATE, DELETE. The executeQuery() technique results that profits information. Use both of these techniques and execute the assertion made beforehand.
Example
Following JDBC model exhibits how to utilize WHERE statement and perform CURD procedure on a table in Apache Derby utilizing JDBC program. Here, we are interfacing with an information base named sampleDB (will make in the event that it doesn't exist) utilizing the implanted driver.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
public class WhereClauseExample {
public static void main(String args[]) throws Exception {
//Registering the driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
//Getting the Connection object
String URL = "jdbc:derby:sampleDB;create=true";
Connection conn = DriverManager.getConnection(URL);
//Creating the Statement object
Statement stmt = conn.createStatement();
//Creating a table and populating it
String query = "CREATE TABLE Employees("
+ "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
+ "Name VARCHAR(255), Salary INT NOT NULL, "
+ "Location VARCHAR(255), "
+ "PRIMARY KEY (Id))";
String query = "INSERT INTO Employees("
+ "Name, Salary, Location) VALUES "
+ "('Amit', 30000, 'Hyderabad'), "
+ "('Kalyan', 40000, 'Vishakhapatnam'), "
+ "('Renuka', 50000, 'Delhi'), "
+ "('Archana', 15000, 'Mumbai'), "
+ "('Trupthi', 45000, 'Kochin'), "
+ "('Suchatra', 33000, 'Pune'), "
+ "('Rahul', 39000, 'Lucknow'), "
+ "('Trupti', 45000, 'Kochin')";
//Executing the query
String query = "SELECT * FROM Employees WHERE Salary>35000";
ResultSet rs = stmt.executeQuery(query);
while(rs.next()) {
System.out.println("Id: "+rs.getString("Id"));
System.out.println("Name: "+rs.getString("Name"));
System.out.println("Salary: "+rs.getString("Salary"));
System.out.println("Location: "+rs.getString("Location"));
System.out.println(" ");
}
}
}
Output
On executing the above program, you will get the accompanying yield −
Id: 2
Name: Kalyan
Salary: 43000
Location: Chennai
Id: 3
Name: Renuka
Salary: 50000
Location: Delhi
Id: 5
Name: Trupthi
Salary: 45000
Location: Kochin
Id: 7
Name: Rahul
Salary: 39000
Location: Lucknow