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:
|
1 2 3 4 5 |
SELECT <ColumnName //<strong>write down the columns that you want to display</strong> FROM <TableName> //<strong>write down the table name from which you want to fetch data </strong> WHERE <Condition>; // <strong>write a condition to apply filter on the table</strong> |
We can use following operators with WHERE clause
- = (EQUAL)
- > (GREATER THAN)
- >= (GREATER THAN OR EQUAL TO)
- < (LESS THAN)
- <= (LESS THAN OR EQUAL TO)
- <> or != (NOT EQUAL TO)
- AND
- OR
- BETWEEN
- IN
- LIKE
- NOT
We will take a demo table Employee to demonstrate all type of queries with WHERE clause.
|
1 |
SELECT* FROM Employee; |
Demo Table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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 |
Example 1: Equal To (=) condition
Find records of employee whose ID is equal to 1
|
1 |
SELECT * FROM Employee WHERE ID=1; |
Output:
|
1 2 3 4 5 |
ID | Name | Gender | Branch | Salary ---+---------+--------+--------+---------- 1 | Jenny | Female | Pune | 40000 |
Example 2: Greater Than (>) condition
Find records of employee whose salary is greater than 70000
|
1 |
SELECT * FROM Employee WHERE Salary>70000; |
Output:
|
1 2 3 4 5 6 7 |
ID | Name | Gender | Branch | Salary ---+---------+----------+----------+---------- 7 | Mike | Male | Banglore | 77000 10 | Penny | Female | Banglore | 90000 |
Example 3: Greater Than or Equal To (>=) condition
Find records of employee whose salary is greater than or equal to 70000
|
1 |
SELECT * FROM Employee WHERE Salary>=70000; |
Output:
|
1 2 3 4 5 6 7 8 9 |
ID | Name | Gender | Branch | Salary ----+----------+----------+-----------+---------- 2 | Anna | Female | Mumbai | 70000 7 | Mike | Male | Banglore | 77000 10 | Penny | Female | Banglore | 90000 |
Example 4 : Less Than (<) condition
Find records of employee whose salary is less than 40000
|
1 |
SELECT * FROM Employee WHERE Salary<40000; |
Output:
|
1 2 3 4 5 6 7 8 9 |
ID | Name | Gender | Branch | Salary ----+---------+----------+----------+---------- 4 | Smith | Male | Pune | 20000 8 | Jack | Male | Pune | 28000 9 | Merry | Female | Mumbai | 18000 |
Example 5 : Less Than or Equal To (<=) condition
Find records of employee whose salary is less than or equal to 20000
|
1 |
SELECT * FROM Employee WHERE Salary<=20000; |
Output:
|
1 2 3 4 5 6 7 |
ID | Name | Gender | Branch | Salary ----+---------+-----------+----------+---------- 4 | Smith | Male | Pune | 20000 9 | Merry | Female | Mumbai | 18000 |
Example 6 : Not Equal To (!=) / (<>) condition
Find records of employee whose Branch is not equal to ‘Pune’
|
1 |
SELECT * FROM Employee WHERE Branch !='Pune' ; |
Output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 |
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)
|
1 |
SELECT * FROM Employee WHERE Salary>=70000 AND Gender='Female'; |
Output:
|
1 2 3 4 5 6 7 |
ID | Name | Gender | Branch | Salary ---+-------+----------+----------+---------- 2 | Anna | Female | Mumbai | 70000 10 | Penny | Female | Banglore | 90000 |
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)
|
1 |
SELECT * FROM Employee WHERE Salary>=70000 OR Gender='Female'; |
Output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 |
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.
|
1 |
SELECT * FROM Employee WHERE (Salary>70000 AND Gender='Female') OR Branch='Pune'; |
Output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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
|
1 |
SELECT * FROM Employee WHERE Salary BETWEEN 70000 AND 90000; |
Output:
|
1 2 3 4 5 6 7 8 9 |
ID | Name | Gender | Branch | Salary ----+--------+----------+----------+---------- 2 | Anna | Female | Mumbai | 70000 7 | Mike | Male | Banglore | 77000 10 | Penny | Female | Banglore | 90000 |
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
|
1 |
SELECT * FROM Employee WHERE Branch IN ('Pune', 'Mumbai'); |
Output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 |
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’
|
1 |
SELECT * FROM Employee WHERE Name LIKE '%y'; |
Output:
|
1 2 3 4 5 6 7 8 9 10 11 |
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 |
Example b: Find records of employees whose Names starts with character ‘J’
|
1 |
SELECT * FROM Employee WHERE Name LIKE 'J%'; |
Output:
|
1 2 3 4 5 6 7 |
ID | Name | Gender | Branch | Salary ---+---------+----------+----------+---------- 1 | Jenny | Female | Pune | 40000 8 | Jack | Male | Pune | 28000 |
Example c: Find records of employees whose Names start with character ‘R’ and end with character ‘t’. In between any characters are allowed
|
1 |
SELECT * FROM Employee WHERE Name LIKE 'R%t'; |
Output:
|
1 2 3 4 5 |
ID | Name | Gender | Branch | Salary ---+---------+---------+----------+---------- 5 | Robert | Male | Mumbai | 43000 |
Example d: This query finds records with employee names of pattern ‘nn’ in between a employee name.
|
1 |
SELECT * FROM Employee WHERE Name LIKE '%nn%'; |
Output:
|
1 2 3 4 5 6 7 8 9 |
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 “M”
|
1 |
SELECT * FROM Employee WHERE Name LIKE 'M - - - - '; |
Output:
|
1 2 3 4 5 |
ID | Name | Gender | Branch | Salary ---+--------+----------+----------+---------- 9 | Merry | Female | Mumbai | 18000 |
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.
|
1 |
SELECT * FROM Employee WHERE Name LIKE '_o%'; |
OUTPUT:
|
1 2 3 4 5 6 7 |
ID | Name | Gender | Branch | Salary ---+---------+----------+-----------+---------- 3 | Rosy | Female | Banglore | 60000 5 | Robert | Male | Mumbai | 43000 |
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.
|
1 |
SELECT * FROM Employee WHERE Branch NOT IN ('Pune', 'Mumbai'); |
Output:
|
1 2 3 4 5 6 7 8 9 |
ID | Name | Gender | Branch | Salary ------+-----------+----------+----------+---------- 3 | Rosy | Female | Banglore | 60000 7 | Mike | Male | Banglore | 77000 10 | Penny | Female | Banglore | 90000 |
Example b: Find records of employees whose ID is Not between 5 and 10
|
1 |
SELECT * FROM Employee WHERE ID NOT BETWEEN 5 AND 10; |
Output:
|
1 2 3 4 5 6 7 8 9 10 11 |
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 |
Example c: Find records of employees whose name does NOT start with “M”:
|
1 |
SELECT * FROM Employee WHERE Name NOT LIKE 'M%'; |
Output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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:
- SQL For QA : Tutorial#1 – Select Query
- SQL For QA : Tutorial#2 – Where Clause
- SQL for QA: Tutorial#3 – INSERT INTO Statement
- SQL for QA: Tutorial#4 – UPDATE Statement
- SQL for QA: Tutorial#5 – DELETE Statement
- SQL for QA: Tutorial#6 – ORDER BY Clause
- SQL for QA: Tutorial#7 – GROUP BY Clause
- SQL for QA: Tutorial#8 – UNION Clause
- SQL for QA: Tutorial#9 – JOIN Clause
