Some Useful SQLs

3 Tricky Oracle SQL Scenarios (with Practical Query Examples)

Hello, fellow data wranglers!

In this post, I’ve rounded up some handy and occasionally head-scratching Oracle SQL use cases that can trip up even seasoned developers. From sneaky NULL comparisons to finding the nth highest salary, each scenario is paired with a short explanation and the SQL statement that does the job.

Let’s dive in:

📅1. Get System Date Without Time Component

sql

To fetch just the date (no timestamp), use:

SELECT TRUNC(SYSDATE) FROM dual;

🔡 2. Convert Any Value to VARCHAR

Need to treat a value as a string?

sql

SELECT TRIM(SYSDATE) FROM dual;

For more control, consider using TO_CHAR() instead.

➕ 3. Perform Arithmetic Operations in SQL

You can use SQL like a calculator:

sql

SELECT 5 * 5 FROM dual;
-- Returns: 25

🥈 4. Find the Nth Highest Salary in a Table

sql

SELECT * FROM emp_sal e
WHERE &n = (
  SELECT COUNT(DISTINCT s.salary)
  FROM emp_sal s
  WHERE s.salary >= e.salary
);

To get the 2nd highest salary, substitute &n with 2.

🤔 5. Does NULL Equal NULL in SQL?

sql

SELECT CASE WHEN NULL = NULL THEN 'yup' ELSE 'nope' END FROM dual;
-- Result: 'nope'

This confirms that NULL is not equal to NULL in SQL logic.

📋 6. Check If Tables Exist in Your Schema

sql

SELECT * FROM tab;

This gives you a list of all user-accessible tables.

🛠 7. Check for PL/SQL Compilation Errors

sql

SELECT * FROM user_errors;

Use this after compiling packages, procedures, or triggers.

➕➖ 8. Get Separate Sums for Positive & Negative Values

This version uses conditional aggregation:

sql

SELECT 
  SUM(CASE WHEN column > 0 THEN column ELSE 0 END) AS pos_sum,
  SUM(CASE WHEN column < 0 THEN column ELSE 0 END) AS neg_sum
FROM your_table;

📌 9. Retrieve the Last Row in a Table

There’s no built-in “last row,” so use a timestamp column like created_at:

sql

SELECT * FROM your_table
WHERE created_at = (SELECT MAX(created_at) FROM your_table);

🪜 10. Designation-Wise Salary Ranking

Use RANK() or DENSE_RANK():

sql

SELECT name, designation, salary,
  RANK() OVER (PARTITION BY designation ORDER BY salary DESC) AS rank_within_role
FROM emp;

🔁 11. Cartesian Product Between Two Tables

sql

SELECT * FROM tab1, tab2;

Be cautious — this multiplies records and grows quickly!

🔮 12. Get Date of Next Occurrence Using LEAD()

sql

SELECT primarykey, date,
  LEAD(date) OVER (PARTITION BY primarykey ORDER BY date DESC) AS next_date
FROM table1;

Useful for identifying gaps or transitions between events.

✏️ 13. UPDATE Using a JOIN in Oracle

Oracle doesn’t support UPDATE FROM directly, so use this format:

sql

MERGE INTO TableA A
USING TableB B
ON (A.col1 = B.colx)
WHEN MATCHED THEN
  UPDATE SET A.foo = B.bar;

This achieves the same effect as an update with a join.

💬 Wrapping Up

These are just a few practical tips to keep in your Oracle SQL toolbox. Whether you're cleaning data, debugging PL/SQL, or optimizing reports, knowing how to handle edge cases like these can save hours.

Have a tricky use case of your own? Drop it in the comments or let’s troubleshoot it together.