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:

SELECT  xColumnName  //write down the columns that you want to display

FROM xTableNamex //write down the table name from which you want to fetch data

WHERE xConditionx;   // write a condition to apply filter on the table

We can use following operators with WHERE clause

1. = (EQUAL)
2. x (GREATER THAN)
3. x= (GREATER THAN OR EQUAL TO)
4. x (LESS THAN)
5. x= (LESS THAN OR EQUAL TO)
6. xx 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.

SELECT* FROM Employee;

Demo Table:

ID  |  Name    | Gender   | Branch   | Salary

----+----------+----------+----------+----------

1   | Jenny    | Female   | Pune     |    40000

2   | Anna     | Female   | Mumbai   |    70000

3   | Rosy     | Female   | Banglore |    60000

4   | Smith    | Male     | Pune     |    20000

5   | Robert   | Male     | Mumbai   |    43000

6   | Wills    | Male     | Pune     |    63000

7   | Mike     | Male     | Banglore |    77000

8   | Jack     | Male     | Pune     |    28000

9   | Merry    | Female   | Mumbai   |    18000

10  | Penny    | Female   | Banglore |    90000

x

Example 1:  Equal To (=) condition

Find records of employee whose ID is equal to 1

SELECT * FROM Employee WHERE ID=1;

Output:

ID |  Name   | Gender | Branch | Salary

---+---------+--------+--------+----------

1  | Jenny   | Female | Pune   |    40000

x

Example 2: Greater Than (x) condition

Find records of employee whose salary is greater than 70000

SELECT * FROM Employee WHERE Salaryx70000;

Output:

ID | Name    | Gender   | Branch   |  Salary

---+---------+----------+----------+----------

7  | Mike    | Male     | Banglore |  77000

10 | Penny   | Female   | Banglore |  90000

x

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

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

SELECT * FROM Employee WHERE Salaryx=70000;

Output:

ID  |  Name    | Gender   | Branch    | Salary

----+----------+----------+-----------+----------

2   | Anna     | Female   | Mumbai    |    70000

7   | Mike     | Male     | Banglore  |    77000

10  | Penny    | Female   | Banglore  |    90000

x

Example 4 : Less Than  (x) condition

Find records of employee whose salary is less than 40000

SELECT * FROM Employee WHERE Salaryx40000;

Output:

ID  |  Name   | Gender   | Branch   | Salary

----+---------+----------+----------+----------

4   | Smith   | Male     | Pune     |    20000

8   | Jack    | Male     | Pune     |    28000

9   | Merry   | Female   | Mumbai   |    18000

x

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

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

SELECT * FROM Employee WHERE Salaryx=20000;

Output:

ID  |  Name   | Gender    | Branch   | Salary

----+---------+-----------+----------+----------

4   | Smith    | Male     | Pune     |    20000

9   | Merry    | Female   | Mumbai   |    18000

x

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

Find records of employee whose Branch is not equal to xPunex

SELECT * FROM Employee WHERE Branch !='Pune' ;

Output:

ID | Name    | Gender   | Branch    | Salary

---+---------+----------+-----------+----------

2  | Anna    | Female   | Mumbai    | 70000

3  | Rosy    | Female   | Banglore  | 60000

5  | Robert  | Male     | Mumbai    | 43000

7  | Mike    | Male     | Banglore  | 77000

9  | Merry   | Female   | Mumbai    | 18000

10 | Penny   | Female   | Banglore  | 90000

x

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)

SELECT * FROM Employee WHERE Salaryx=70000 AND Gender='Female';

Output:

ID | Name  | Gender   | Branch   | Salary

---+-------+----------+----------+----------

2  | Anna  | Female   | Mumbai   |    70000

10 | Penny | Female   | Banglore |    90000

x

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)

SELECT * FROM Employee WHERE Salaryx=70000 OR Gender='Female';

Output:

ID | Name    | Gender   | Branch    | Salary

---+---------+----------+-----------+----------

1  | Jenny   | Female   | Pune      | 40000

2  | Anna    | Female   | Mumbai    | 70000

3  | Rosy    | Female   | Banglore  | 60000

7  | Mike    | Male     | Banglore  | 77000

9  | Merry   | Female   | Mumbai    | 18000

10 | Penny   | Female   | Banglore  | 90000

x

Example 9:  Now lets combine AND x OR operators.

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

SELECT * FROM Employee WHERE (Salaryx70000 AND Gender='Female') OR Branch='Pune';

Output:

ID  |  Name   | Gender   | Branch   | Salary

----+---------+----------+----------+----------

1   | Jenny   | Female   | Pune     | 40000

4   | Smith   | Male     | Pune     | 20000

6   | Wills   | Male     | Pune     | 63000

8   | Jack    | Male     | Pune     | 28000

10  | Penny   | Female   | Banglore | 90000

 

Example 10:  BETWEEN clause

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

SELECT * FROM Employee WHERE Salary BETWEEN 70000 AND 90000;

Output:

ID  | Name   | Gender   | Branch   | Salary

----+--------+----------+----------+----------

2   | Anna   | Female   | Mumbai   | 70000

7   | Mike   | Male     | Banglore | 77000

10  | Penny  | Female   | Banglore | 90000

x

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

SELECT * FROM Employee WHERE Branch IN ('Pune', 'Mumbai');

Output:

ID |  Name   | Gender   | Branch   | Salary

------+-----------+----------+----------+----------

1  | Jenny   | Female   | Pune     | 40000

2  | Anna    | Female   | Mumbai   | 70000

4  | Smith   | Male     | Pune     | 20000

5  | Robert  | Male     | Mumbai   | 43000

6  | Wills   | Male     | Pune     | 63000

8  | Jack    | Male     | Pune     | 28000

9  | Merry   | Female   | Mumbai   | 18000

x

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:

• %   x The percent sign represents zero, one, or multiple characters
• _   x 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 xyx

SELECT * FROM Employee WHERE Name LIKE '%y';

Output:

ID  | Name   | Gender   | Branch   | Salary

----+--------+----------+----------+----------

1   | Jenny  | Female   | Pune     | 40000

3   | Rosy   | Female   | Banglore | 60000

9   | Merry  | Female   | Mumbai   | 18000

10  | Penny  | Female   | Banglore | 90000

x

Example b:  Find records of employees whose Names starts with character xJx

SELECT * FROM Employee WHERE Name LIKE 'J%';

Output:

ID |  Name   | Gender   | Branch   | Salary

---+---------+----------+----------+----------

1  | Jenny   | Female   | Pune     |    40000

8  | Jack    | Male     | Pune     |    28000

x

Example c:  Find records of employees whose Names start with character xRx and end with character xtx.  In between any characters are allowed

SELECT * FROM Employee WHERE Name LIKE 'R%t';

Output:

ID | Name    | Gender  | Branch   | Salary

---+---------+---------+----------+----------

5  | Robert   | Male    | Mumbai   | 43000

 

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

SELECT * FROM Employee WHERE Name LIKE '%nn%';

Output:

ID  |  Name   | Gender   | Branch   | Salary

----+---------+----------+----------+----------

1   | Jenny   | Female   | Pune     | 40000

2   | Anna    | Female   | Mumbai   | 70000

10  | Penny   | Female   | Banglore | 90000

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

SELECT * FROM Employee WHERE Name LIKE 'M - - - - ';

Output:

ID | Name   | Gender   | Branch   | Salary

---+--------+----------+----------+----------

9  | Merry  | Female   | Mumbai   | 18000

x

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

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

SELECT * FROM Employee WHERE Name LIKE '_o%';

OUTPUT:

ID |  Name   | Gender   | Branch    | Salary

---+---------+----------+-----------+----------

3 | Rosy     | Female   | Banglore  | 60000

5 | Robert   | Male     | Mumbai    | 43000

x

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.

SELECT * FROM Employee WHERE Branch NOT IN ('Pune', 'Mumbai');

Output:

ID  |  Name   | Gender   | Branch     | Salary

------+-----------+----------+----------+----------

3   | Rosy    | Female   | Banglore   | 60000

7   | Mike    | Male     | Banglore   | 77000

10  | Penny   | Female   | Banglore   | 90000

x

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

SELECT * FROM Employee WHERE ID NOT BETWEEN 5 AND 10;

Output:

ID |  Name   | Gender   | Branch   | Salary

---+---------+----------+----------+----------

1  | Jenny   | Female   | Pune     | 40000

2  | Anna    | Female   | Mumbai   | 70000

3  | Rosy    | Female   | Banglore | 60000

4  | Smith   | Male     | Pune     | 20000

x

Example c: Find records of employees whose name does NOT start with xMx:

SELECT * FROM Employee WHERE Name NOT LIKE 'M%';

Output:

ID  | Name   | Gender   | Branch   | Salary

----+--------+----------+----------+----------

1   | Jenny  | Female   | Pune     | 40000

2   | Anna   | Female   | Mumbai   | 70000

3   | Rosy   | Female   | Banglore | 60000

4   | Smith  | Male     | Pune     | 20000

5   | Robert | Male     | Mumbai   | 43000

6   | Wills  | Male     | Pune     | 63000

8   | Jack   | Male     | Pune     | 28000

10  | Penny  | Female   | Banglore | 90000

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

Tutorial Index:

1. SQL For QA : Tutorial#1 – Select Query
2. SQL For QA : Tutorial#2 – Where Clause
3. SQL for QA: Tutorial#3 – INSERT INTO Statement
4. SQL for QA: Tutorial#4 – UPDATE Statement
5. SQL for QA: Tutorial#5 – DELETE Statement
6. SQL for QA: Tutorial#6 – ORDER BY Clause
7. SQL for QA: Tutorial#7 – GROUP BY Clause
8. SQL for QA: Tutorial#8 – UNION Clause
9. SQL for QA: Tutorial#9 – JOIN Clause

x