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

Selecting Rows (and Why SELECT * Is a Trap) ## Filtering Rows with WHERE ## Combining Conditions: AND, OR, and Confusion ## Use parentheses (). Always. ## Lists of Values with IN ## Ranges with <, >=, and BETWEEN ## Wildcards and Pattern Matching (LIKE) ## NULL Is… Weird (But Makes Sense) ## Negation: Saying “NOT This” ## Final Thoughts 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. The very first thing was learning that SELECT really has two jobs: 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. This forces you to think about what you actually need, and it also: That alone already changed how I write queries. Be specific and effective. 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” “Give me just one specific row” Simple, but this is the foundation of almost every real query. This part tripped me up more than I expected. At first glance, this feels logical: But the results weren’t what I expected. That’s when I understood that: Which means SQL doesn’t read conditions left to right the way my brain wants it to. After that, the query does exactly what it looks like it should do. This alone has saved me from future bugs. Instead of writing this: 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. Important detail I learned: It's the small details that make the big differences. Finds anything starting with b. Finds toy names containing uppercase B. 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. This line returning nothing: NULL isn’t a value — it’s unknown You can flip most conditions using NOT: But again — NULL is special. To exclude nulls, you must use: There’s no shortcut here. This lesson didn’t feel flashy but it felt important. Everything else in SQL builds on this: 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. Templates let you quickly answer FAQs or store snippets for re-use. Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink. Hide child comments as well For further actions, you may consider blocking this person and/or reporting abuse CODE_BLOCK: 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; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: 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; CODE_BLOCK: 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; CODE_BLOCK: select toy_name, price from toys; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: select toy_name, price from toys; CODE_BLOCK: select toy_name, price from toys; CODE_BLOCK: select * from toys where colour = 'red'; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: select * from toys where colour = 'red'; CODE_BLOCK: select * from toys where colour = 'red'; CODE_BLOCK: select * from toys where toy_name = 'Sir Stripypants'; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: select * from toys where toy_name = 'Sir Stripypants'; CODE_BLOCK: select * from toys where toy_name = 'Sir Stripypants'; CODE_BLOCK: where toy_name = 'Mr Bunnykins' or toy_name = 'Baby Turtle' and colour = 'green'; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: where toy_name = 'Mr Bunnykins' or toy_name = 'Baby Turtle' and colour = 'green'; CODE_BLOCK: where toy_name = 'Mr Bunnykins' or toy_name = 'Baby Turtle' and colour = 'green'; CODE_BLOCK: where ( toy_name = 'Mr Bunnykins' or toy_name = 'Baby Turtle' ) and colour = 'green'; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: where ( toy_name = 'Mr Bunnykins' or toy_name = 'Baby Turtle' ) and colour = 'green'; CODE_BLOCK: where ( toy_name = 'Mr Bunnykins' or toy_name = 'Baby Turtle' ) and colour = 'green'; CODE_BLOCK: where colour = 'red' or colour = 'green' or colour = 'blue' or colour = 'yellow' Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: where colour = 'red' or colour = 'green' or colour = 'blue' or colour = 'yellow' CODE_BLOCK: where colour = 'red' or colour = 'green' or colour = 'blue' or colour = 'yellow' CODE_BLOCK: where colour in ( 'red', 'green', 'blue', 'yellow' ); Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: where colour in ( 'red', 'green', 'blue', 'yellow' ); CODE_BLOCK: where colour in ( 'red', 'green', 'blue', 'yellow' ); CODE_BLOCK: where price between 6 and 20; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: where price between 6 and 20; CODE_BLOCK: where price between 6 and 20; COMMAND_BLOCK: where price > 6 and price <= 20; Enter fullscreen mode Exit fullscreen mode COMMAND_BLOCK: where price > 6 and price <= 20; COMMAND_BLOCK: where price > 6 and price <= 20; CODE_BLOCK: where colour like 'b%'; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: where colour like 'b%'; CODE_BLOCK: where colour like 'b%'; CODE_BLOCK: where toy_name like '%B%'; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: where toy_name like '%B%'; CODE_BLOCK: where toy_name like '%B%'; CODE_BLOCK: where price = null; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: where price = null; CODE_BLOCK: where price = null; CODE_BLOCK: where price is null; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: where price is null; CODE_BLOCK: where price is null; CODE_BLOCK: where price is not null; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: where price is not null; CODE_BLOCK: where price is not null; CODE_BLOCK: where not colour = 'green'; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: where not colour = 'green'; CODE_BLOCK: where not colour = 'green'; COMMAND_BLOCK: where colour <> 'green'; Enter fullscreen mode Exit fullscreen mode COMMAND_BLOCK: where colour <> 'green'; COMMAND_BLOCK: where colour <> 'green'; CODE_BLOCK: where colour is not null; Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: where colour is not null; CODE_BLOCK: where colour is not null; - FROM → where the data lives - SELECT → what columns I actually want back - Sends less data over the network - Breaks less when columns change - BETWEEN includes both ends - If you want strict boundaries, you must write them yourself - _ matches exactly one character - % matches zero or more characters - Aggregations - Real-world analytics