Oracle SQL Tutorial Lesson 2

 

Oracle SQL Basic - Day 2

 Filtering Data with WHERE and Sorting with ORDER BY

In the previous lesson, we covered how to write simple SELECT queries and how to sort results using ORDER BY. Now, let’s take the next step and learn how to filter data using the WHERE clause.

This lesson covers:

• Recap of SELECT
• Filtering data with WHERE
• Sorting filtered data using ORDER BY

---

1. Recap: SELECT Statement

A quick review for past practice. The SELECT statement retrieves data from a table.

Syntax:

SELECT column1, column2

FROM table_name;

Example:

SELECT first_name, last_name FROM employees;

---

2. Filtering Data with the WHERE Clause

The WHERE clause lets you filter rows that meet certain conditions. Write the WHERE clause after SELECT statement and before ORDER BY. It will be filter based on columns of the tables or views. 

There are many conditions and operators use to filter the Data such as =, != >, >= , < and many more.

These filters can be apply with character data, Numbers and Date as well. Lets do some basic practice first and understand them.

It's like telling the database: "Only give me the rows that match this rule.". 

Syntax:

SELECT column1, column2

FROM table_name

WHERE condition;

---

Example Table: employees

employee_id	first_name	last_name	department_id	salary
101	Alice	Johnson	10	7000
102	Bob	Smith	20	6000
103	Clara	Davis	10	8000
104	David	White	30	9000

---

Examples:

➤ Get employees from department 10:

SELECT first_name, last_name

FROM employees

WHERE department_id = 10;

In above example we are getting data of all employees who's department no = 10. Try this with and without where clause and you understand what is the difference after applying the filter or WHERE CLAUSE.

➤ Get employees with salary greater than 7000:

SELECT first_name, salary

FROM employees

WHERE salary > 7000;

Here we are getting the employees data who's salary is more then 7000. Note that SALARY is a column and the data type of salary is Number. You can find out data types by using DESC EMPLOYEES command

Remember when we are using NUMBER data type as used in above we can use it without single quotation signs (') but when we filter data where the column's data type is character/varchar or Date then the Filter should be use with (') single quotation.

example: 

select * from employees where first_name = 'SMITH';

select * from employees where hire_date > '01-jan-1998';

Remember, the value input as characters e.g. 'SMITH' is case sensitive. It means Smith and SMITH are not same 

---

 

3. Using ORDER BY with WHERE

You can filter the data using WHERE and then sort it using ORDER BY.

Example:

➤ Get employees from department 10, sorted by salary:

SELECT first_name, salary

FROM employees

WHERE department_id = 10

ORDER BY salary;

➤ Get employees with salary below 8000, sorted by last name:

SELECT first_name, last_name, salary

FROM employees

WHERE salary < 8000

ORDER BY last_name;

---

 4. Using Multiple Conditions with AND / OR

You can add more than one condition using logical operators:

• AND – All conditions must be true
• OR – At least one condition must be true

AND means all condition should be true then data retrieval occur based on these conditions. 

For example get all employees data where salary is 5000 and name is Smith.

The database engine will get data if both condition are true 

OR condition will get data if any condition match from in filter like if data exist with name smith it appears if salary 5000 there for it appears.

You can use below command with AND then try same using OR

Example:

➤ Employees in department 10 with salary above 7500:

SELECT first_name, salary

FROM employees

WHERE department_id = 10 AND salary > 7500;

Select first_name, salary from employees where department_id = 10 or salary > 7500;

 

---

Next lesson 

Summary

In this lesson, you learned how to:

• Use the WHERE clause to filter rows based on conditions
• Combine WHERE with ORDER BY to sort filtered data
• Use comparison operators like =, >, <
• Add multiple conditions with AND and OR

---

 Next Lesson

What's Next?

In the next lesson, we’ll explore:

• Using aggregate functions like COUNT, AVG, SUM
• Grouping data with GROUP BY