GTU MCA RDBMS Set 6 Solution: PL/SQL Block to Display Employees Working for Last 5 Years

Official Set 6

Set 6 — EMP and DEPT: PL/SQL Block for Employees Working for the Last Five Years

• Substitution variable
• Parameterised cursor
• ADD_MONTHS
• MONTHS_BETWEEN
• NO_DATA_FOUND

Subject

MC01094031 RDBMS

Tool

Oracle 10g or above

Questions

1

Status

Question list: official GTU PDF

Source check: The task and both table structures are taken from the official GTU PDF. Sample data is illustrative. Interpretation: 'working since last five years' is taken to mean *employees whose joining date is at least five years before today* (service ≥ 5 years). If your instructor means *employees who joined within the last five years*, change <= to > in the cursor condition — shown in the explanation.

On this page

1. Introduction
2. Learning objectives
3. Original question list
4. Database schema
5. Prerequisites
6. Table creation & sample data
7. Solutions
8. Important concepts
9. Common mistakes
10. Viva questions
11. Summary
12. Download script
13. Related practicals

Introduction

This practical joins SQL*Plus input with PL/SQL processing. The block asks for a department name, looks up its number, then uses a cursor with a parameter to list the employees of that department who have completed at least five years of service.

Learning objectives

• Read user input into a PL/SQL variable with a substitution variable.
• Declare and use a cursor with a parameter.
• Calculate service length with ADD_MONTHS and MONTHS_BETWEEN.
• Handle an invalid department name with NO_DATA_FOUND.

Original question list

1. Write a PL/SQL block (table above EMP-DEPT table) which takes as input Department name and displays all the employees of this department who has been working since last five years

Database schema

EMP

Column	Oracle type	Meaning / constraint
empno	NUMBER(4)	Primary key
empnm	VARCHAR2(25)	Employee name
empadd	VARCHAR2(30)	Address
salary	NUMBER(9,2)
date_birth	DATE
joindt	DATE	Joining date
deptno	NUMBER(2)	FK → DEPT

DEPT

Column	Oracle type	Meaning / constraint
deptno	NUMBER(2)	Primary key
deptnm	VARCHAR2(20)	Department name, UNIQUE

Prerequisites

• Set 4 (PL/SQL blocks and cursors).
• Set 4 uses the same EMP structure; the clean-up script drops and recreates it with this set's data.
• Run in SQL*Plus or SQL Developer (F5) so that the &dept_name prompt works.

Optional clean-up — run this first if you have attempted the practical before. Errors such as *ORA-00942: table or view does not exist* are harmless on the first run.

Optional clean-up

DROP TABLE emp  CASCADE CONSTRAINTS;
DROP TABLE dept CASCADE CONSTRAINTS;

Table creation & sample data

Step 1 Create the DEPT and EMP tables

Oracle SQL

CREATE TABLE dept (
    deptno  NUMBER(2)    PRIMARY KEY,
    deptnm  VARCHAR2(20) NOT NULL UNIQUE
);

CREATE TABLE emp (
    empno       NUMBER(4)    PRIMARY KEY,
    empnm       VARCHAR2(25) NOT NULL,
    empadd      VARCHAR2(30),
    salary      NUMBER(9,2)  CHECK (salary > 0),
    date_birth  DATE,
    joindt      DATE         NOT NULL,
    deptno      NUMBER(2)    REFERENCES dept (deptno)
);

Expected Output

Table created.  (2 times)

Step 2 Insert sample data

Oracle SQL

INSERT INTO dept VALUES (10, 'Sales');
INSERT INTO dept VALUES (20, 'Production');
INSERT INTO dept VALUES (30, 'Finance');

INSERT INTO emp VALUES (1, 'Anil Kapoor', 'Ahmedabad', 52000, TO_DATE('14-02-1985','DD-MM-YYYY'), TO_DATE('01-06-2012','DD-MM-YYYY'), 10);
INSERT INTO emp VALUES (2, 'Bina Shah', 'Surat', 38000, TO_DATE('22-08-1992','DD-MM-YYYY'), TO_DATE('15-07-2019','DD-MM-YYYY'), 10);
INSERT INTO emp VALUES (3, 'Chetan Rao', 'Vadodara', 61000, TO_DATE('05-11-1983','DD-MM-YYYY'), TO_DATE('10-01-2015','DD-MM-YYYY'), 20);
INSERT INTO emp VALUES (4, 'Deepa Iyer', 'Rajkot', 29000, TO_DATE('17-03-1998','DD-MM-YYYY'), TO_DATE('03-02-2023','DD-MM-YYYY'), 10);
INSERT INTO emp VALUES (5, 'Esha Nair', 'Anand', 47000, TO_DATE('30-09-1990','DD-MM-YYYY'), TO_DATE('20-04-2018','DD-MM-YYYY'), 20);
INSERT INTO emp VALUES (6, 'Farhan Ali', 'Ahmedabad', 33000, TO_DATE('11-12-1996','DD-MM-YYYY'), TO_DATE('01-08-2022','DD-MM-YYYY'), 20);
INSERT INTO emp VALUES (7, 'Gita Menon', 'Surat', 58000, TO_DATE('25-06-1987','DD-MM-YYYY'), TO_DATE('12-09-2014','DD-MM-YYYY'), 30);
INSERT INTO emp VALUES (8, 'Harish Jain', 'Bharuch', 41000, TO_DATE('08-01-1994','DD-MM-YYYY'), TO_DATE('05-03-2021','DD-MM-YYYY'), 10);

COMMIT;

Explanation

Joining dates range from 2012 to 2023 so that some Sales employees qualify and some do not.

Expected Output

1 row created.  (11 times)

Commit complete.

Solutions

Q1 Write a PL/SQL block (table above EMP-DEPT table) which takes as input Department name and displays all the employees of this department who has been working since last five years

Oracle PL/SQL

SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
    v_deptnm  dept.deptnm%TYPE := '&dept_name';
    v_deptno  dept.deptno%TYPE;
    v_count   NUMBER := 0;

    CURSOR c_emp (p_deptno dept.deptno%TYPE) IS
        SELECT empno, empnm, joindt
        FROM   emp
        WHERE  deptno = p_deptno
        AND    joindt <= ADD_MONTHS(SYSDATE, -60)      -- 60 months = 5 years
        ORDER  BY joindt;
BEGIN
    SELECT deptno
    INTO   v_deptno
    FROM   dept
    WHERE  UPPER(deptnm) = UPPER(TRIM(v_deptnm));

    DBMS_OUTPUT.PUT_LINE('Employees of department ' || v_deptnm ||
                         ' working for 5 years or more');
    DBMS_OUTPUT.PUT_LINE('EMPNO  NAME            JOINED       YEARS');
    DBMS_OUTPUT.PUT_LINE('-----  --------------  -----------  -----');

    FOR r IN c_emp(v_deptno) LOOP
        v_count := v_count + 1;
        DBMS_OUTPUT.PUT_LINE(RPAD(r.empno, 7) || RPAD(r.empnm, 16) ||
                             RPAD(TO_CHAR(r.joindt, 'DD-MON-YYYY'), 13) ||
                             TRUNC(MONTHS_BETWEEN(SYSDATE, r.joindt) / 12));
    END LOOP;

    IF v_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No employee has completed 5 years in this department.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Total: ' || v_count || ' employee(s)');
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Department "' || v_deptnm || '" does not exist.');
END;
/

Explanation

Input. SQL*Plus replaces '&dept_name' with the text you type before the block is compiled. SET VERIFY OFF hides the *old/new* substitution lines.

Finding the department. SELECT ... INTO fetches the department number. UPPER(...) and TRIM make the search case- and space-insensitive (typing *sales* works). If no department matches, Oracle raises NO_DATA_FOUND, handled in the EXCEPTION section.

Parameterised cursor. c_emp (p_deptno ...) is opened with FOR r IN c_emp(v_deptno), so the same cursor can be reused for any department.

Five-year rule. ADD_MONTHS(SYSDATE, -60) is the date exactly five years ago; employees who joined on or before that date have at least five years of service. TRUNC(MONTHS_BETWEEN(SYSDATE, joindt) / 12) shows completed years. For the opposite interpretation (joined within the last five years) use joindt > ADD_MONTHS(SYSDATE, -60).

Oracle note: The output depends on today's date. The expected output below was computed on the day this article was generated, using the sample data above.

Expected Output (computed from this article's sample data)

Enter value for dept_name: Sales

Employees of department Sales working for 5 years or more
EMPNO  NAME            JOINED       YEARS
-----  --------------  -----------  -----
1      Anil Kapoor     01-JUN-2012  14
2      Bina Shah       15-JUL-2019  7
8      Harish Jain     05-MAR-2021  5
Total: 3 employee(s)

PL/SQL procedure successfully completed.

Important concepts used

Substitution in PL/SQL

&var is replaced by SQL*Plus before compilation, so it can initialise a PL/SQL variable.

Parameterised cursor

A cursor that accepts arguments when it is opened, like a function.

ADD_MONTHS / MONTHS_BETWEEN

Date arithmetic in months; handles month ends correctly, unlike adding 365×5 days.

Exception handling

NO_DATA_FOUND is raised by SELECT INTO when no row matches.

Common mistakes

• Writing &dept_name without quotes for a text value — the block fails to compile.
• Using SYSDATE - 5*365, which ignores leap years.
• Forgetting that the department lookup is case-sensitive without UPPER.
• Not handling NO_DATA_FOUND and getting *ORA-01403: no data found* for a wrong name.

Viva questions

What is a parameterised cursor?

A cursor declared with parameters, e.g. CURSOR c(p NUMBER) IS ..., whose query uses the parameter; values are supplied when the cursor is opened.

How is user input taken in a PL/SQL block?

Through SQL*Plus substitution variables (&name); the value is substituted into the source text before the block is compiled. PL/SQL itself has no input statement.

What does ADD_MONTHS do?

Adds (or with a negative number subtracts) a number of months to a date, adjusting for month ends.

What is NO_DATA_FOUND?

A predefined exception raised when SELECT INTO returns no row.

What is %TYPE used for here?

To declare v_deptnm and v_deptno with the same data types as the DEPT columns.

Summary

The block combines input, a lookup with error handling and a parameterised cursor. Date arithmetic with ADD_MONTHS keeps the five-year rule correct across leap years and month ends.

Download script

set-06-employee-and-department-pl-sql.sql

All CREATE, INSERT and solution statements of Set 6 in one file — run it in SQL*Plus with @set-06-employee-and-department-pl-sql.sql.

Related practicals

• Set 5 — Entrance Examination Database
• Set 7 — Customer and Order
• All 10 practical sets
• Viva questions

← Previous practicalSet 5: Entrance Examination DatabaseNext practical →Set 7: Customer and Order

Independent educational resource — not an official GTU publication. Verify questions and solutions with your current syllabus and lab instructor. Disclaimer