SQL For QA : Tutorial#2 – Where Clause

I believe WHERE clause is the most frequently used clause with SELECT statement by QAs.

WHERE clause is not a compulsory clause in a SQL query, but a optional one to apply filter on the given table.

In WHERE clause we need to provide some condition, so the query will extract only those records from table which will fulfill the specified condition. Need to note here that if the condition given in WHERE clause will be true then only SELECT query will display the records.

WHERE clause can be used with SELECT, INSERT, UPDATE, and DELETE statements.

We can write more than one condition in WHERE clause by using AND, OR operator.

Syntax:

We can use following operators with WHERE clause

  1. = (EQUAL)
  2. > (GREATER THAN)
  3. >= (GREATER THAN OR EQUAL TO)
  4. < (LESS THAN)
  5. <= (LESS THAN OR EQUAL TO)
  6. <> or !=   (NOT EQUAL TO)
  7. AND
  8. OR
  9. BETWEEN
  10. IN
  11. LIKE
  12. NOT

We will take a demo table Employee to demonstrate all type of queries with WHERE clause.

Demo Table:

 

Example 1:  Equal To (=) condition

Find records of employee whose ID is equal to 1

Output:

 

Example 2: Greater Than (>) condition

Find records of employee whose salary is greater than 70000

Output:

 

Example 3: Greater Than or Equal To (>=) condition

Find records of employee whose salary is greater than or equal to 70000

Output:

 

Example 4 : Less Than  (<) condition

Find records of employee whose salary is less than 40000

Output:

 

Example 5 : Less Than  or Equal To (<=) condition

Find records of employee whose salary is less than or equal to 20000

Output:

 

Example 6 : Not Equal To (!=) / (<>) condition

Find records of employee whose Branch is not equal to ‘Pune’

Output:

 

Example 7: AND Operator

AND combines 2 conditions together. SELECT query will display the output only if both the conditions in WHERE clause will be True.

Find records of employee whose Salary is Greater than 70k AND she is Female (Gender)

Output:

 

Example 8: OR Operator

OR operator again can be use to combine 2 conditions together. SELECT query will display the output if any one of the condition from WHERE clause is true.

Find records of employee whose Salary is Greater than 70k AND she is Female (Gender)

Output:

 

Example 9:  Now lets combine AND & OR operators.

Find records of employee whose Salary is Greater than 70k AND she is Female (Gender) OR his/her Branch is Pune.

Output:

 

Example 10:  BETWEEN clause

Find records of employees whose salary is Between 70k and 90k. Output will Include 70k and 90k

Output:

 

Example 11: IN Keyword

Find records of employees whose Branch is Pune or Mumbai. IN is used when we need to give multiple conditions for Equal to clause

Output:

 

Example 12:  LIKE keyword.

it is used in a WHERE clause to search for a particular pattern in a column.

Two wildcards are used in conjunction with the LIKE operator:

  • %   – The percent sign represents zero, one, or multiple characters
  • _   – The underscore represents a single character

Lets see an examples of Like operator with leading wildcard

Example a:  Find records of employees whose Names ends with character ‘y’

Output:

 

Example b:  Find records of employees whose Names starts with character ‘J’

Output:

 

Example c:  Find records of employees whose Names start with character ‘R’ and end with character ‘t’.  In between any characters are allowed

Output:

 

Example d:  This query finds records with employee names of pattern ‘nn’ in between a employee name.

Output:

Example e : Find records of employees with a Name having exact 5 characters and that starts with “M”

Output:

 

Example f : Find records of employees with a Name that have “o” in the second position.

This way you can write a query to find name that have any alphabet at any position using wildcards.

OUTPUT:

 

Example 13:  NOT Condition

NOT condition can be used with IN, BETWEEN or LIKE conditions.

Example a: Find records of employees whose Branch is Not Pune and Mumbai.

Output:

 

Example b: Find records of employees whose ID is Not between 5 and 10

Output:

 

Example c: Find records of employees whose name does NOT start with “M”:

Output:

We have covered pretty much that is required for QA. In next tutorial we will see Update , Delete and Truncate queries.

 

 

Add a Comment

Your email address will not be published. Required fields are marked *