In my previous article I have given you multiple examples of simple SQL statement. In this article I would like to give you multiple examples of where clause in SQL and would like to answer the question about – What is where clause in SQL with real life examples? This article is really useful in preparation of OCA. The article will give you information about how to restrict the values in oracle. In real world SQL scenarios, we do not need direct SQL statements. We require to restrict the SQL data for getting correct data in real life scenarios.
What you will find in this article?
1.Syntax of Where clause
2.Multiple examples to restrict the values
Syntax of Where clause:
We need to start with restriction of values with simple where clause. We will then check the complex statements with using where clause. These examples you can directly run on livesql.oracle.com I would like to give you different scenarios of where clause in SQL with real life examples.
Syntax :
Select * ,[col1,col2…coln] from tablename where condition;
Examples of where clause in SQL:
Scenario 1: Fetching all values from Employee table.
SELECT * FROM EMPLOYEES;
Scenario 2: Example of where clause using simple condition.
We can fetch the values from employees table where manager_id is specific or salary is specific.
SELECT * FROM HR.EMPLOYEESWHERE manager_ID=100;
SELECT * FROM EMPLOYEES WHERE salary=17000;
Scenario 3: Example of where clause using date and character conditions.
We require to use the single quotation marks to give the condition. Those values are case-sensitive value and date values are format-sensitive.The default date display format is DD-MON-RR
Select * from Employees where First_Name= ‘Neena’;
SELECT * FROM employees WHERE HIRE_DATE = ’21-SEPT-05′;
Scenario 4: Some examples using comparison operator
SELECT * FROM HR.EMPLOYEES WHERE SALARY>=5000;
SELECT * FROM employees WHERE HIRE_DATE > ’21-SEPT-05′ ;
You will get more information about SQL statements :
https://docs.oracle.com/cd/B12037_01/server.101/b10759/sql_elements002.htm
Scenario 5 : Using where clause with between..and
We need to use lower limit after the between and upper limit after AND.
SELECT * FROM HR.EMPLOYEES WHERE SALARY BETWEEN 7000 AND 10000;
If you switch the upper limit and lower limit. The output will be NO data found.
SELECT * FROM EMPLOYEES WHERE SALARY BETWEEN 10000 AND 5000;
Scenario 6: Example of where condition using IN operator.
We can use the select statement using IN operator and where clause.
SELECT * FROM HR.EMPLOYEES where SALARY IN (10000,7000);
The above query will fetch the employees where salary is 10000 and 7000.
Scenario 7: The execution of select statement with where clause using % operator
Fetch all the Employee data where First_Name starts with A.
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE ‘A%’;
Fetch all Employees data where Last name ends with r.
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE ‘%r’;
Fetch all Employees data where First_name contains ‘am’.
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE ‘%am%’;
Fetch all employees where FIRST_NAME second letter is ‘m’
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE ‘_m%’;
SELECT * FROM EMPLOYEES WHERE FIRST_NAME not LIKE ‘A%’;
Scenario 8: Using Where clause and Null operator
The following statement is not correct.
SELECT * FROM EMPLOYEES where SALARY=null;
SELECT * FROM EMPLOYEES where SALARY is null;
SELECT * FROM EMPLOYEES where SALARY is not null;
Scenario 9 : Using not Operator
You can use the not operator for Department_id is not 20
select * from employees where DEPARTMENT_ID<>20;
select * from employees where DEPARTMENT_ID !=20;
Scenario 10 : The where condition using the and/or/not operators
select * from employees where DEPARTMENT_ID =20 and First_name=’Amit’;
select * from employees where DEPARTMENT_ID =20 or First_name=’Amit’;
select * from employees where DEPARTMENT_ID is not null;
SELECT last_name, job_id, salary FROM EMPLOYEES WHERE (job_id = ‘SA_REP’ OR job_id = ‘AD_PRES’) AND SALARY > 15000;
Scenario 11: Where clause using order by clause
SELECT * FROM EMPLOYEES ORDER BY HIRE_DATE;
SELECT * FROM EMPLOYEES ORDER BY HIRE_DATE desc;
SELECT FIRST_NAME first_name FROM EMPLOYEES ORDER BY first_name;
SELECT DEPARTMENT_ID,first_name,salary FROM EMPLOYEES ORDER BY DEPARTMENT_ID,FIRST_NAME;
Scenario 12 : Fetch statement
In this scenario if user wants to fetch the specific data. If you want to fetch first 5 employees.
fetch 5 rows :
SELECT FIRST_NAME first_name FROM EMPLOYEES ORDER BY first_name FETCH 5 FIRST 5 ROWS ONLY;
The above query will fetch first 5 rows.
SELECT FIRST_NAME first_name FROM EMPLOYEES ORDER BY first_name FETCH 5 FIRST 50% ROWS ONLY;
The above query will fetch 50% of rows from the Employees table.
SELECT FIRST_NAME first_name FROM EMPLOYEES ORDER BY first_name OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
With Ties statement,
If there are 2 duplicate rows and we require to come that rows in fetch statement we need to use ties statement. You can check by TIES statement.
SELECT FIRST_NAME first_name FROM EMPLOYEES ORDER BY first_name FETCH 5 FIRST 5 ROWS ONLY TIES;
These are some important examples of ‘ where clause in SQL ‘. I hope you like this article. If you like this article or if you have some issues of concerns with the same kindly comment in comments section.