.jpg)
Sometimes a simple table teaches bigger lessons. I still remember working with a small employee dataset where nothing looked unusual on the surface. Yet as the saying goes, “daal mein kuchh kala tha”—something subtle was hiding. A few employees were linked to departments that didn’t actually exist. That tiny mismatch created confusion in reporting, payroll validation, and downstream analytics.
This post walks through that exact scenario using Oracle SQL, the kind of practical example that quietly builds strong problem-solving instincts. It starts with a basic table structure and leads into a real-world query that identifies employees whose department IDs do not match any entry in the department master table.
When data pipelines get messy or dashboards don’t reconcile, queries like this save the day. They are simple but powerful, like a well-aimed torch in a dark room.
Setting Up the EMPLOYEE Table
To recreate the scenario, here is the sample Oracle SQL setup:
SET DEFINE OFF
CREATE TABLE EMPLOYEE (
EMP_ID INTEGER,
EMP_NAME VARCHAR2(20),
SALARY NUMBER(8, 2),
DEPT_ID INTEGER,
MANAGER_ID INTEGER
);
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, SALARY, DEPT_ID, MANAGER_ID) VALUES (1, 'Emma Thompson', 12071.0, 1, 6);
INSERT INTO EMPLOYEE VALUES (2, 'Daniel Rodriguez', 14488.0, 1, 6);
INSERT INTO EMPLOYEE VALUES (3, 'Olivia Smith', 23799.0, 1, 6);
INSERT INTO EMPLOYEE VALUES (4, 'Noah Johnson', 10288.0, 2, 8);
INSERT INTO EMPLOYEE VALUES (5, 'Sophia Martinez', 21972.0, 1, 2);
INSERT INTO EMPLOYEE VALUES (6, 'Liam Brown', 20701.0, 3, 2);
INSERT INTO EMPLOYEE VALUES (7, 'Ava Garcia', 19173.0, 3, 2);
INSERT INTO EMPLOYEE VALUES (8, 'William Davis', 15892.0, 2, NULL);
INSERT INTO EMPLOYEE VALUES (9, 'Robin Kulkarni', 17000.0, 4, 1);
INSERT INTO EMPLOYEE VALUES (10, 'Rahul Bose', 19222.0, NULL, NULL);
INSERT INTO EMPLOYEE VALUES (11, 'Harish Bose', 23002.0, 5, NULL);
The Real-World Problem
In most organisations, the DEPT table acts as the source of truth for all department codes. The EMPLOYEE table simply references it. But what if some department IDs appear in the EMPLOYEE table but not in the DEPT table?
It creates what data engineers lovingly call “orphan records”. These are records that have a foreign key reference to something that does not exist.
Identifying them early prevents reporting mismatches and helps maintain clean data structures.
Finding Employees with Invalid Department IDs
Here’s the Oracle query that reveals any employee whose department does not exist in the department master.
select
e.emp_id,
e.emp_name,
e.dept_id
from employee e
left join dept d
on e.dept_id = d.dept_id
where d.dept_id is null
and e.dept_id is not null;
Expected Output
11 Harish Bose 5
This shows that department ID 5 exists in the employee table but not in the dept table. In a live environment, this tiny detail can affect payroll groups, headcount dashboards, cost-center allocations, and downstream Power BI or Tableau visuals.
Small query, big impact—“boond boond se sagar banta hai”.
Why Queries Like This Matter
Working with SQL is not only about writing code. It is about understanding the story behind the data. Every missing department, every null manager, every mismatched join hints at a bigger organisational pattern. These patterns shape the accuracy of everything built on top of the database—your analytics, your reporting, your insights.
People who build this habit of checking data integrity end up becoming the go-to problem solvers on teams. It naturally reflects in their work quality and the trust others place in them. Clean queries lead to clean decisions.
Conclusion
This example goes beyond syntax. It sharpens your thinking. It shows how a small SQL test uncovers structural gaps in the data model. When you handle these details well, your entire analytics stack becomes more dependable.
Future posts will explore other practical SQL patterns that help in real business scenarios, where accuracy is worth its weight in gold and the ability to debug quietly becomes a superpower.
Comments
Post a Comment