RDBMS SOLUTION


ASSIGNMENT 5 

SET A Q.1 SOLUTION

Write a cursor which will display list of projects started in month of “January” 2020.




Source code:

    

    /*Assignment 5 set a q 1 solution

    Here first we will create tables then enter the records and then create cursor

    Explain of the pl/sql code will be at the end*/

/*Creating tables*/

create table pproject(

    pno number not null primary key,

    pname varchar2(40),

    start_date date,

    budget number,

    status varchar2(10)

)

create table department(

    dno number not null primary key,

    dname varchar2(40),

    hod varchar2(40),

    loc varchar2(40)

)

create table p_d(

    project_number number not null references pproject(pno),

    department_number number not null references department(dno)

)

/*Inserting records*/

insert into pproject values( 1, 'website',to_date('01/02/2000','dd/mm/yyyy'),100000 ,'Active');

insert into pproject values( 2, 'database',to_date('01/01/2020','dd/mm/yyyy'),'5000000 ','Active');

insert into pproject values( 3, 'computer software',to_date('05/01/2022','dd/mm/yyyy'),'2000000 ','Active');

insert into pproject values( 4, 'accessories',to_date('10/04/2000','dd/mm/yyyy'),'300000 ','Active');

insert into department values(1 ,'computer science','h1','l1');

insert into department values( 2,'computer science','h2','l2');

insert into department values( 3,'computer science','h3','l3');

insert into department values( 4,'human resource','h4','l4');

insert into p_d values(1,2);

insert into p_d values(1,1);

insert into p_d values(2,4);

insert into p_d values(3,2);

insert into p_d values(1,2);

insert into p_d values(1,2);

insert into p_d values(1,2);

/*creating cursor*/

declare

pno pproject.pno%type;

pname pproject.pname%type;

pbudget pproject.budget%type;

cursor list_p is select pno,pname,budget from pproject where start_date=to_date('01/2020','mm/yyyy');

begin

open list_p;

loop

Exit when list_p%notfound;

fetch list_p into pno,pname,pbudget;

dbms_output.put_line(pno||' '||pname||'   '||pbudget);

end loop;

close list_p;

end;

/*

Explaination:

This code is written in PL/SQL, and it is used to retrieve project details from a database. Here is a breakdown of the code:

The code first declares three variables to hold project number, project name, and project budget. Then it defines a cursor named |list_p| to select project details from the |pproject| table where the |start_date| is January 2020. The |to_date()| function is used to convert the string '01/2020' into a date value.

Next, the code opens the cursor using the |open| statement. It then enters a loop and fetches each row of data from the cursor into the declared variables using the |fetch| statement. Inside the loop, the |dbms_output.put_line()| procedure is used to print the project details to the console.

When there is no more data available in the cursor, the loop is exited using the |Exit when| statement. Finally, the cursor is closed using the |close| statement.  */
 Download code         next