lEARNING: SQL | WHERE Clause

SQL | WHERE Clause

WHERE keyword is used for fetching filtered data in a result set.

• It is used to fetch data according to a particular criteria.
• WHERE keyword can also be used to filter data by matching patterns.

Basic Syntax:
SELECT column1,column2 FROM table_name WHERE column_name operator value;

column1 , column2: fields int the table
table_name: name of table
column_name: name of field used for filtering the data
operator: operation to be considered for filtering
value: exact value or pattern to get related data in result 

List of operators that can be used with where clause:

operator	description
>	Greater Than
>=	Greater than or Equal to
<	Less Than
<=	Less than or Equal to
=	Equal to
<>	Not Equal to
BETWEEN	In an inclusive Range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

Queries

• To fetch record of students with age equal to 20

  SELECT * FROM Student WHERE Age=20;
  

  Output:
  

  ROLL_NO	NAME	ADDRESS	PHONE	Age
  3	SUJIT	ROHTAK	XXXXXXXXXX	20
  3	SUJIT	ROHTAK	XXXXXXXXXX	20

• To fetch Name and Address of students with ROLL_NO greater than 3

  SELECT ROLL_NO,NAME,ADDRESS FROM Student WHERE ROLL_NO > 3;
  

  Output:
  

  ROLL_NO	NAME	ADDRESS
  4	SURESH	Delhi

BETWEEN operator

It is used to fetch filtered data in a given range inclusive of two values.
Basic Syntax:
SELECT column1,column2 FROM table_name WHERE column_name BETWEEN value1 AND value2;

BETWEEN: operator name 

value1 AND value2: exact value from value1 to value2 to get related data in
result set.

Queries

• To fetch records of students where ROLL_NO is between 1 and 3 (inclusive)

  SELECT * FROM Student WHERE ROLL_NO BETWEEN 1 AND 3;
  

  Output:
  

  ROLL_NO	NAME	ADDRESS	PHONE	Age
  1	Ram	Delhi	XXXXXXXXXX	18
  2	RAMESH	GURGAON	XXXXXXXXXX	18
  3	SUJIT	ROHTAK	XXXXXXXXXX	20
  3	SUJIT	ROHTAK	XXXXXXXXXX	20
  2	RAMESH	GURGAON	XXXXXXXXXX	18

• To fetch NAME,ADDRESS of students where Age is between 20 and 30 (inclusive)

  SELECT NAME,ADDRESS FROM Student WHERE Age BETWEEN 20 AND 30;
  

  Output:
  
  
  

  NAME	ADDRESS
  SUJIT	Rohtak
  SUJIT	Rohtak

LIKE operator

It is used to fetch filtered data by searching for a particular pattern in where clause.
Basic Syntax:
SELECT column1,column2 FROM table_name WHERE column_name LIKE pattern;

LIKE: operator name 

pattern: exact value extracted from the pattern to get related data in
result set.
Note: The character(s) in pattern are case sensitive.

Queries

• To fetch records of students where NAME starts with letter S.

  SELECT * FROM Student WHERE NAME LIKE 'S%'; 

  The ‘%'(wildcard) signifies the later characters here which can be of any length and
  value.More about wildcards will be discussed in the later set.
  Output:
  

  ROLL_NO	NAME	ADDRESS	PHONE	Age
  3	SUJIT	ROHTAK	XXXXXXXXXX	20
  4	SURESH	Delhi	XXXXXXXXXX	18
  3	SUJIT	ROHTAK	XXXXXXXXXX	20

• To fetch records of students where NAME contains the patter ‘AM’.

  SELECT * FROM Student WHERE NAME LIKE '%AM%';
  

  Output:
  
  
  

  ROLL_NO	NAME	ADDRESS	PHONE	Age
  1	Ram	Delhi	XXXXXXXXXX	18
  2	RAMESH	GURGAON	XXXXXXXXXX	18
  2	RAMESH	GURGAON	XXXXXXXXXX	18

IN operator

It is used to fetch filtered data same as fetched by ‘=’ operator just the difference is that here we can specify multiple values for which we can get the result set.
Basic Syntax:
SELECT column1,column2 FROM table_name WHERE column_name IN (value1,value2,..);

IN: operator name 

value1,value2,..: exact value matching the values given and get related data in result set.

Queries

• To fetch NAME and ADDRESS of students where Age is 18 or 20.

  SELECT NAME,ADDRESS FROM Student WHERE Age IN (18,20);
  

  Output:
  

  NAME	ADDRESS
  Ram	Delhi
  RAMESH	GURGAON
  SUJIT	ROHTAK
  SURESH	Delhi
  SUJIT	ROHTAK
  RAMESH	GURGAON

• To fetch records of students where ROLL_NO is 1 or 4.

  SELECT * FROM Student WHERE ROLL_NO IN (1,4);
  

  Output:
  

  ROLL_NO	NAME	ADDRESS	PHONE	Age
  1	Ram	Delhi	XXXXXXXXXX	18
  4	SURESH	Delhi	XXXXXXXXXX	18

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

Declaring Variables

Declaring Variables

int myAge;
String myName;
boolean isTired;
Variable definitions can go anywhere in a method definition (that is, anywhere a regular Java
statement can go), although they are most commonly declared at the beginning of the definition
before they are used:
public static void main (String args¸]) {
int count;
String title;
boolean isAsleep;
...
}
You can string together variable names with the same type:
int x, y, z;
String firstName, LastName;
You can also give each variable an initial value when you declare it:
int myAge, mySize, numShoes = 28;
String myName = “Laura”;
boolean isTired = true;
int a = 4, b = 5, c = 6;
If there are multiple variables on the same line with only one initializer (as in the first of the
previous examples), the initial value applies to only the last variable in a declaration. You can also
group individual variables and initializers on the same line using commas, as with the last
example, above.
Local variables must be given values before they can be used (your Java program will not compile
if you try to use an unassigned local variable). For this reason, it’s a good idea always to give local
variables initial values. Instance and class variable definitions do not have this restriction (their
initial value depends on the type of the variable: null for instances of classes, 0 for numeric
variables, ‘\0’ for characters, and false for booleans).


Notes on Variable Names
 
Variable names in Java can start with a letter, an underscore (_), or a dollar sign ($). They cannot
start with a number. After the first character, your variable names can include any letter or
number. Symbols, such as %, *, @, and so on, are often reserved for operators in Java, so be careful
when using symbols in variable names.