Zero Setup with LiveSQL: Filter and Sort Using WHERE and ORDER BY

Struggling to pinpoint the right data or make sense of messy query results? Welcome to our beginner-friendly SQL series, where we’re mastering essential SQL skills with Oracle’s LiveSQL—a free, zero-setup browser-based platform for instant SQL practice! Today, we’re tackling filtering with WHERE and sorting with ORDER BY to make your queries laser-focused and organized. Let’s dive in! 🤩🤺🥳

---

Why WHERE and ORDER BY Are Game-Changers

As a new SQL learner, you want data that answers your questions. The WHERE clause filters rows (e.g., “Show only recent hires”). The ORDER BY clause sorts results (e.g., “List jobs by start date”). Together, they’re your tools for clean, meaningful data—perfect for any SQL lover!

In this tutorial, we’ll:

• Filter data with WHERE using conditions like equals or greater than.

• Sort results with ORDER BY, including handling nulls.

• Leverage LiveSQL’s auto-complete, error highlighting, and Library scripts to experiment!!

Step 1: Jump into LiveSQL

New to LiveSQL? Watch this:

Visit livesql.oracle.com, sign in, and you’re dropped straight into the SQL Worksheet. We’re going to be using the HR schema’s JOB_HISTORY table (columns: employee_id, start_date, end_date, job_id) for fresh examples, different from SCOTT in my video above. See setup details.

---

Step 2: Filtering with WHERE

WHERE lets you pick rows matching specific conditions. The WHERE clause is used to specify a certain condition while fetching or modifying data in a database!

Example 1: Basic Filtering

Find job changes after 2000:

SELECT employee_id, start_date, job_id
FROM job_history
WHERE start_date > TO_DATE('2000-01-01', 'YYYY-MM-DD');

Auto-complete suggests TO_DATE:

Run the query, and you’ll see LiveSQL’s error highlighting in action! The error is because LiveSQL needs the schema name (HR.) to find the table job_history—it should be HR.job_history. Error highlighting flags this mistake, helping you learn fast!

Let’s fix it:

Example 2: Multiple Conditions

Find job changes for employee 101 with a specific job ID:

SELECT start_date, job_id
FROM HR.job_history
WHERE employee_id = 101 AND job_id = 'AC_ACCOUNT';

Query results:

Tips for WHERE

• Use =, >, <, !=, LIKE (e.g., job_id LIKE 'SA%').

• Text needs single quotes (e.g., 'AC_ACCOUNT').

Example 3: Using IN for Multiple Values

Need to filter for several employees? Use IN to list their IDs:

SELECT employee_id, start_date, job_id
FROM HR.job_history
WHERE employee_id IN (100, 101, 102, 103, 104, 105);

This shows job history for employees with IDs 100 to 105—super handy for filtering multiple values at once!

[Placeholder: Screenshot of query results]

---

Step 3: Sorting with ORDER BY

ORDER BY organizes results with ASC or DESC. ASC and DESC determines that the values in the specified column should be sorted in ascending or descending order. ASC sorts from the lowest value to highest value. DESC sorts from highest value to lowest value. ASC is the default sort order.

Tips for ORDER BY

• ASC is default.

• Use NULLS FIRST/LAST for nulls.

Example 1: Simple Sorting

Sort job history by start_date (newest first):

SELECT employee_id, start_date
FROM HR.job_history
ORDER BY start_date DESC;

The query results show us an employee’s start date in descending (DESC) order, from most recent start date to oldest:

Example 2: Handling Nulls

Sort by end_date, nulls last:

SELECT employee_id, end_date
FROM HR.job_history
ORDER BY end_date DESC NULLS LAST;

Try NULLS FIRST to compare!

Example 3: Stretch Goal—Custom Sorting

Let’s say you’re making a report and want to spotlight all 'IT_PROG' jobs first, then list other jobs, all sorted by start date. This query does just that:

SELECT job_id, start_date
FROM HR.job_history
ORDER BY CASE WHEN job_id = 'IT_PROG' THEN 1 ELSE 2 END, start_date;

Query results prioritize the ‘IT_PROG’ jobs first:

Here’s the query breakdown: The CASE trick checks if job_id is 'IT_PROG'. If it is, it gives those rows a 1 (top priority); all other jobs get a 2. So, IT_PROG jobs come first! Then, within each group, start_date sorts them from earliest to latest. It’s like saying, “Show me IT programmers first, then everyone else, all in date order.” Pretty cool, right? 🤠

Try tweaking the query for fun—maybe prioritize 'SA_REP' instead!

---

Step 4: Combine WHERE and ORDER BY

Find job changes after 1998 for job ID ‘SA_REP’, sorted by the job ID’s start date:

SELECT start_date, job_id
FROM HR.job_history
WHERE start_date > TO_DATE('1998-01-01', 'YYYY-MM-DD')
  AND job_id = 'SA_REP'
ORDER BY start_date DESC;

Query results:

---

Step 5: Save and Explore More

Save your work:

1. Click “Save” in LiveSQL.

2. Name it (e.g., “filter_sort.sql”) and download.

3. Try Run Script (F5) to execute multiple queries from your .sql file!

Explore Library Sample Scripts—search “WHERE” to load pre-built queries and tweak them. Check session history to revisit experiments or Explain Plan for performance insights.

---

Practice Makes Perfect

Try these challenges:

1. Filter job_id starting with ‘AD’ and sort by end_date (nulls first).

2. Find job changes before 1999, sorted by employee_id.

---

What’s Next?

In the next blog post, I’ll show you how to group data using GROUP BY in LiveSQL!

Try LiveSQL if you haven’t already :-)
Check out my blog post on Mastering SQL Joins ~

Happy querying!