Practice Set-2 [ Conditions ]

By /

Common Data Model (Used for All Topics)

1. EMPLOYEES

CREATE TABLE employees (
    emp_id        NUMBER(5),
    emp_name      VARCHAR2(50),
    salary        NUMBER(10,2),
    commission    NUMBER(6,2),
    hire_date     DATE,
    dept_id       NUMBER(3),
    status        CHAR(1)      -- A = Active, I = Inactive
);

Sample Records

INSERT INTO employees VALUES (101, 'Rajiv Kumar', 60000, 5000, DATE '2020-01-15', 10, 'A');
INSERT INTO employees VALUES (102, 'Ravi Sharma', 45000, NULL,  DATE '2021-03-20', 20, 'A');
INSERT INTO employees VALUES (103, 'Neha Verma', 75000, 3500,  DATE '2019-07-10', 10, 'I');
INSERT INTO employees VALUES (104, 'Pooja Mehta', 50000, NULL,  DATE '2022-02-01', 30, 'A');
INSERT INTO employees VALUES (105, 'Amit Singh', 90000, 8000,  DATE '2018-11-25', NULL, 'A');

2. DEPARTMENTS

CREATE TABLE departments (
    dept_id     NUMBER(3),
    dept_name   VARCHAR2(30),
    location    VARCHAR2(20)
);

Sample Records

INSERT INTO departments VALUES (10, 'IT', 'Bangalore');
INSERT INTO departments VALUES (20, 'HR', 'Delhi');
INSERT INTO departments VALUES (30, 'Finance', 'Mumbai');

3. PROJECTS

CREATE TABLE projects (
    project_id   NUMBER(5),
    project_name VARCHAR2(50),
    dept_id      NUMBER(3),
    budget       NUMBER(12,2),
    start_date   DATE,
    end_date     DATE
);

Sample Records

INSERT INTO projects VALUES (1001, 'Cloud Migration', 10, 500000.50, DATE '2022-01-01', DATE '2022-12-31');
INSERT INTO projects VALUES (1002, 'HR Automation', 20, 150000.75, DATE '2021-06-01', DATE '2021-12-31');
INSERT INTO projects VALUES (1003, 'Financial Audit', 30, 300000.00, DATE '2022-03-01', NULL);

1. Comparison Conditions (=, !=, >, <, >=, <=)

Use Case

HR wants to identify employees eligible for salary revision.

Practice Scenarios

  • Employees earning more than ₹60,000
  • Employees hired before 2020
  • Employees not in IT department
  • Projects with budget exceeding ₹3,00,000

Practice Questions

  1. Find employees whose salary is greater than 60,000.
  2. List employees hired before 01-JAN-2020.
  3. Display employees who are not active.
  4. Show projects with budget less than 2,00,000.

2. Floating-Point Conditions

Use Case

Finance team needs accurate comparisons of decimal values.

Real-Time Scenario

Commission calculations often result in decimal precision issues.

Practice Scenarios

  • Identify employees with commission greater than 3500.50
  • Detect projects with fractional budgets

Practice Questions

  1. Find employees whose commission is greater than 5000.75.
  2. List projects where budget has decimal values.
  3. Compare two floating-point values and observe rounding behavior.
  4. Why should equality comparison be avoided with floating-point numbers?

3. Logical Conditions (AND, OR, NOT)

Use Case

Filtering employee data for HR dashboards.

Practice Scenarios

  • Active employees in IT department
  • Employees with high salary OR high commission
  • Excluding inactive employees

Practice Questions

  1. Find active employees in department 10.
  2. List employees with salary > 70,000 OR commission > 6,000.
  3. Display employees who are NOT inactive.
  4. Explain operator precedence using AND / OR conditions.

4. Pattern-Matching Conditions (LIKE)

Use Case

Search functionality in enterprise applications.

Practice Scenarios

  • Search employees by partial name
  • Identify projects containing keyword “Cloud”

Practice Questions

  1. Find employees whose name starts with ‘A’.
  2. List employees whose name contains ‘ma’.
  3. Display projects whose name ends with ‘Automation’.
  4. Find employees whose name has exactly 5 characters.

5. NULL Conditions (IS NULL, IS NOT NULL)

Use Case

Detect missing data affecting reports.

Practice Scenarios

  • Employees without commission
  • Projects still in progress

Practice Questions

  1. Find employees who do not have commission.
  2. List employees whose department is not assigned.
  3. Display projects that have not ended yet.
  4. Explain why = NULL does not work.

6. Compound Conditions

Use Case

Complex business rules combining multiple filters.

Practice Scenarios

  • High-value employees in active projects
  • Employees with salary and commission conditions

Practice Questions

  1. Find active employees in IT with salary > 50,000.
  2. Display employees who earn > 60,000 AND are hired before 2021.
  3. Identify employees not in HR or Finance.
  4. Rewrite a compound condition using parentheses for clarity.

7. BETWEEN Condition

Use Case

Range-based filtering for reports.

Practice Scenarios

  • Salary range analysis
  • Project duration analysis

Practice Questions

  1. Find employees whose salary is between 50,000 and 80,000.
  2. List employees hired between 2019 and 2021.
  3. Display projects with budget between 2,00,000 and 6,00,000.
  4. Explain inclusive behavior of BETWEEN.

8. EXISTS Condition

Use Case

Checking existence of related records (performance optimization).

Practice Scenarios

  • Departments having employees
  • Projects assigned to departments

Practice Questions

  1. Find departments that have at least one employee.
  2. List departments that have active employees.
  3. Display projects whose departments exist in employees table.
  4. Explain difference between EXISTS and JOIN.

9. IN Condition

Use Case

Filtering against predefined lists or subqueries.

Practice Scenarios

  • Employees in selected departments
  • Projects handled by key departments

Practice Questions

  1. Find employees working in departments 10, 20.
  2. List employees whose salary is in (45000, 50000, 60000).
  3. Display projects belonging to departments having employees.
  4. Compare IN vs EXISTS performance.

Production-Level Interview Scenarios

  1. When should EXISTS be preferred over IN?
  2. How do NULL values impact IN conditions?
  3. Why is BETWEEN risky for DATE columns?
  4. How does operator precedence affect business logic?
Scroll to Top