Practice Set-1 [ Datatypes, Type conversions, Nulls, Operators ]

By /

Below is a production-oriented Oracle SQL practice set designed to build both practical skills and theoretical clarity for a SQL Developer role.

Base Schema (Used Across All Topics)

Table 1: EMPLOYEES

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

Sample Data

INSERT INTO employees VALUES (101, 'Rajiv', 60000, 5000, DATE '2020-01-15', 10, 'A');
INSERT INTO employees VALUES (102, 'Ravi', 45000, NULL,  DATE '2021-03-20', 20, 'A');
INSERT INTO employees VALUES (103, 'Neha', NULL, 3000,  DATE '2019-07-10', 10, 'I');
INSERT INTO employees VALUES (104, 'Pooja', 75000, 7000, NULL,             30, 'A');
INSERT INTO employees VALUES (105, 'Amit', 50000, NULL, DATE '2022-05-01', NULL, 'A');

Table 2: DEPARTMENTS

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

Sample Data

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

1. Data Types

Concept Focus

  • NUMBER vs VARCHAR2 vs DATE
  • Precision & Scale
  • CHAR vs VARCHAR2
  • Real-world data design decisions

Practical Questions

  1. Identify which columns should never allow NULL in a production HR system and justify why.
  2. What happens if you try to insert salary = 60000.567 into NUMBER(10,2)?
  3. Why is status defined as CHAR(1) instead of VARCHAR2(1)?
  4. Write a query to display employee name and salary formatted as currency.
  5. Find employees whose hire_date is missing and explain why this is a data quality issue.
  6. Modify the table to ensure emp_name cannot exceed realistic limits.
  7. Compare storing dates using DATE datatype vs VARCHAR2.
  8. Identify columns that could cause precision loss in financial reports.
  9. Which datatype would you choose to store email IDs and why?
  10. Explain why NUMBER is preferred over FLOAT for salaries.

2. Data Type Comparison Rules

(Implicit & Explicit Data Conversion)**

Concept Focus

  • Oracle’s implicit conversion
  • Performance impact
  • TO_CHAR, TO_DATE, TO_NUMBER
  • NLS settings risk

Practical Questions

  1. Predict the output:
SELECT * FROM employees WHERE emp_id = '101';
  1. Rewrite the above query using explicit conversion and explain why it is better.
  2. What happens when you run:
SELECT * FROM employees WHERE hire_date = '15-JAN-2020';
  1. Fix the above query using TO_DATE.
  2. Identify performance risk:
SELECT * FROM employees 
WHERE TO_CHAR(hire_date,'YYYY') = '2020';
  1. Rewrite it in a performance-optimized way.
  2. What error occurs when:
SELECT * FROM employees WHERE salary = 'SIXTY THOUSAND';
  1. Convert salary to character format with commas and currency symbol.
  2. Explain how implicit conversion can break after NLS_DATE_FORMAT change.
  3. Write a query to safely compare employee ID coming from a web input (string).

3. NULLs in Conditions

Concept Focus

  • NULL ≠ 0
  • Three-valued logic
  • IS NULL / IS NOT NULL
  • NVL, NVL2, COALESCE

Practical Questions

  1. Why does this query return no rows?
SELECT * FROM employees WHERE bonus = NULL;
  1. Correct the query.
  2. List employees who do not have bonus.
  3. Display total compensation (salary + bonus) for all employees.
  4. Why does the above query return NULL for some employees?
  5. Fix it using NVL.
  6. Find employees whose department is not assigned.
  7. Write a query to show salary status:
  • SALARY PRESENT
  • SALARY MISSING
  1. Compare NVL vs COALESCE with examples.
  2. Identify employees whose hire_date is NULL and explain business impact.

4. Database Object Names and Qualifiers

Concept Focus

  • Schema names
  • Table aliases
  • Fully qualified names
  • Avoiding ambiguity
  • Production best practices

Practical Questions

  1. Write a query using table aliases to fetch employee name and department name.
  2. Why is this query ambiguous?
SELECT dept_id FROM employees e, departments d;
  1. Fix the ambiguity using aliases.
  2. Write a query using fully qualified table name assuming schema is HR.
  3. When is schema qualification mandatory in production?
  4. Explain difference between:
employees.emp_id

and

e.emp_id
  1. Identify best naming practice for database objects.
  2. What problems arise from using reserved keywords as column names?
  3. Write a join query without aliases and explain why it is discouraged.
  4. How do synonyms help in large Oracle environments?

**5. Operators

(Arithmetic, Concatenation, Logical)**

Concept Focus

  • +, -, *, /
  • ||
  • AND, OR, NOT
  • Operator precedence
  • NULL behavior

Practical Questions

  1. Calculate annual salary assuming salary is monthly.
  2. Calculate total earnings (salary + bonus).
  3. Why does the above query return NULL for some employees?
  4. Fix it using NVL.
  5. Display employee name with department in single column:
Rajiv - IT
  1. Find employees with salary > 50000 AND status = ‘A’.
  2. Find employees in IT OR Finance department.
  3. Write a query using NOT to exclude inactive employees.
  4. Explain operator precedence in:
salary > 50000 OR dept_id = 10 AND status = 'A'
  1. Rewrite using parentheses for clarity and correctness.

Production-Level Scenario Questions

  1. Why should implicit conversions be avoided in WHERE clause?
  2. How do NULLs impact index usage?
  3. What mistakes cause reports to show incorrect totals?
  4. How can bad datatype choice affect application performance?
  5. Explain real-time issue caused by date stored as VARCHAR2.
Scroll to Top