Scribd Logo
Upload

Welcome to Scribd!

  • 18 views

    Ankit Vashisth

    Uploaded by

    Ankit Vashisth
    The document describes a student worksheet for a database management systems course. It includes creating an employee table, writing a PL/SQL code to retrieve reports from the employee table using cursors, and writing a program to fetch a single record and column from a table.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Ankit Vashisth

    Uploaded by

    Ankit Vashisth
    18 views5 pages
    The document describes a student worksheet for a database management systems course. It includes creating an employee table, writing a PL/SQL code to retrieve reports from the employee table using cursors, and writing a program to fetch a single record and column from a table.

    Original Title

    ankit_vashisth

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes a student worksheet for a database management systems course. It includes creating an employee table, writing a PL/SQL code to retrieve reports from the employee table using cursors, and writing a program to fetch a single record and column from a table.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    18 views5 pages

    Ankit Vashisth

    Uploaded by

    Ankit Vashisth
    The document describes a student worksheet for a database management systems course. It includes creating an employee table, writing a PL/SQL code to retrieve reports from the employee table using cursors, and writing a program to fetch a single record and column from a table.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 5

    DEPARTMENT OF

    COMPUTER SCIENCE & ENGINEERING

    WORKSHEET 3

    Student Name: Ankit Vashisth UID: 22BCS13378


    Branch: BE-CSE Section/Group: 707-A
    Semester: 4th Date of Performance:27/03/24
    Subject Name: DataBase Management System Subject Code: 22CSH-254

    1. Aim:

    I. Create table Employee with the following

    Column Name Datatype


    Emp_Id Integer
    First_Name Varchar
    Last_Name Varchar
    Email Varchar
    Phone Integer
    Hire_Date Date
    Salary Integer
    Manager_Id Integer
    Dept_Id Integer
    Job_Id Varchar

    i. Write a Pl Sql code to retrieve the reports from the employee table and
    display them using cursors.

    ii. Write a Program in Pl Sql to fetch single record and single column from a
    table.
    DEPARTMENT OF
    COMPUTER SCIENCE & ENGINEERING

    2. Source Code with screenshot of outputs:

    (i) Write a Pl Sql code to retrieve the reports from the employee table and
    display them using cursors.
    DECLARE
    Emp_id INTEGER;
    first_name VARCHAR(50);
    last_name VARCHAR(50);
    email VARCHAR(100);
    phone INTEGER;
    hire_date DATE; job_id
    VARCHAR(50); salary
    INTEGER; manager_id
    INTEGER; dept_id
    INTEGER; CURSOR
    emp_cursor IS
    SELECT empid, first_name, last_name, email, phone, hire_date, job_id, salary,
    manager_id, dept_id from employee;
    BEGIN
    OPEN emp_cursor;
    LOOP
    DEPARTMENT OF
    COMPUTER SCIENCE & ENGINEERING

    FETCH emp_cursor into empid, first_name, last_name, email, phone, hire_date,


    job_id, salary, manager_id, dept_id;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id);
    DBMS_OUTPUT.PUT_LINE('First Name: ' || first_name);
    DBMS_OUTPUT.PUT_LINE('Last Name: ' || last_name);
    DBMS_OUTPUT.PUT_LINE('Email: ' || email);
    DBMS_OUTPUT.PUT_LINE('Phone Number: ' || phone);
    DBMS_OUTPUT.PUT_LINE('Hire Date: ' || hire_date);
    DBMS_OUTPUT.PUT_LINE('Job ID: ' || job_id);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || salary);
    DBMS_OUTPUT.PUT_LINE('Manager ID: ' || manager_id);
    DBMS_OUTPUT.PUT_LINE('Department ID: ' || dept_id);
    DBMS_OUTPUT.PUT_LINE('------------------------------');
    END LOOP;
    CLOSE emp_cursor;
    END;
    (OUTPUT)
    DEPARTMENT OF
    COMPUTER SCIENCE & ENGINEERING

    (ii) Write a Program in Pl Sql to fetch single record and single column
    from a table.
    DECLARE
    CURSOR employee_cursor IS
    SELECT * FROM employee;
    employee_record employee_cursor%ROWTYPE;
    BEGIN
    OPEN employee_cursor;
    LOOP
    FETCH employee_cursor INTO employee_record;
    EXIT WHEN employee_cursor%NOTFOUND;
    DEPARTMENT OF
    COMPUTER SCIENCE & ENGINEERING

    -- Display the record


    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || employee_record.emp_id || ',
    Name: ' || employee_record.first_name || ' ' || employee_record.last_name);
    END LOOP;
    CLOSE employee_cursor;
    END;

    3. Learning Outcomes
    (i) We learnt the basics of DBMS(DataBases).
    (ii) We learnt to use PL-SQl(procedural language) code.
    (iii) We learnt the use of cursors and views.

    You might also like