Querying & Filtering in Oracle Databases: What Actually Clicked for Me As a Beginner

Today felt like one of those quiet but important SQL days. No new tables. No fancy joins. Just learning how to ask better questions of the data I already have.

This lesson was all about querying and filtering rows basically learning how to tell the database exactly what I want back, and nothing more.

I learnt this through a simple toys table, which honestly helped a lot. Oracle courses teach in a weird but funny manner which allows you to learn and have fun while you do. They make very daunting topics look less intimidating by approaching them with easy to understand and relatable concepts.

create table toys (
  toy_name varchar2(100),
  colour   varchar2(10),
  price    number(10, 2)
);

insert into toys values ( 'Sir Stripypants', 'red', 0.01 );
insert into toys values ( 'Miss Smelly_bottom', 'blue', 6.00 );
insert into toys values ( 'Cuteasaurus', 'blue', 17.22 );
insert into toys values ( 'Mr Bunnykins', 'red', 14.22 );
insert into toys values ( 'Baby Turtle', 'green', null );

commit;

Selecting Rows (and Why SELECT * Is a Trap)

The very first thing was learning that SELECT really has two jobs:

• FROM → where the data lives
• SELECT → what columns I actually want back

At first, SELECT * FROM toys; is very convenient but only when your database is small. Imagine a bigger database with over 10000 rows. A select * isn’t going to help you find Mr BunnyKins in there.

select toy_name, price
from toys;

This forces you to think about what you actually need, and it also:

• Sends less data over the network
• Breaks less when columns change

That alone already changed how I write queries. Be specific and effective.

Filtering Rows with WHERE

So to start being more effective instead of getting everything in the table, you can start asking questions like:

“Only show me the red toys”

select *
from toys
where colour = 'red';

Or:

“Give me just one specific row”

select *
from toys
where toy_name = 'Sir Stripypants';

Simple, but this is the foundation of almost every real query.

Combining Conditions: AND, OR, and Confusion

This part tripped me up more than I expected.

At first glance, this feels logical:

where toy_name = 'Mr Bunnykins'
or toy_name = 'Baby Turtle'
and colour = 'green';

But the results weren’t what I expected.

That’s when I understood that:

AND runs before OR

Which means SQL doesn’t read conditions left to right the way my brain wants it to.

The fix?

Use parentheses (). Always.

where ( toy_name = 'Mr Bunnykins' or toy_name = 'Baby Turtle' )
and colour = 'green';

After that, the query does exactly what it looks like it should do. This alone has saved me from future bugs.

Lists of Values with IN

Instead of writing this:

where colour = 'red'
or colour = 'green'
or colour = 'blue'
or colour = 'yellow'

You can write this:

where colour in ( 'red', 'green', 'blue', 'yellow' );

Much cleaner. Much easier to read and very effective. This feels like one of those features you don’t appreciate until you really need it. Imagine the 100,000 rows in the table and I want just a handful that meet some conditions. it’s more effective to use the IN than to write multiple OR statements.

Ranges with <, >=, and BETWEEN

where price between 6 and 20;

Important detail I learned:

• BETWEEN includes both ends
• If you want strict boundaries, you must write them yourself

where price > 6
and price <= 20;

It’s the small details that make the big differences.

Wildcards and Pattern Matching (LIKE)

where colour like 'b%';

Finds anything starting with b.

where toy_name like '%B%';

Finds toy names containing uppercase B.

So wildcards:

• _ matches exactly one character
• % matches zero or more characters

And if you actually want to search for _ or % themselves… you need ESCAPE.

That’s one of those things you won’t know until it breaks something.

NULL Is… Weird (But Makes Sense)

This line returning nothing:

where price = null;

Turns out:

NULL isn’t a value — it’s unknown

So you must write:

where price is null;

And the opposite:

where price is not null;

Negation: Saying “NOT This”

You can flip most conditions using NOT:

where not colour = 'green';

Or by using <>:

where colour <> 'green';

But again — NULL is special.

To exclude nulls, you must use:

where colour is not null;

There’s no shortcut here.

Final Thoughts

This lesson didn’t feel flashy but it felt important.

Everything else in SQL builds on this:

• Aggregations
• Joins
• Subqueries
• Real-world analytics

If you can’t filter data confidently, everything else feels fragile.

I’m learning to slow down, be explicit, and write queries that are readable and effective.

If you’re also learning SQL and sometimes feel silly getting tripped up by WHERE clauses… you’re not alone.

I’ll keep documenting this journey, the confusion, clarity, and all.