Assignment No.5: Cursors

Assignments: 

SET A: 

 Consider the following entities and their relationships. 

 Project (pno, pname, start_date, budget, status) 

Department (dno, dname, HOD, loc) 

The relationship between Project and Department is Many to One. 

Constraint: Primary key. Project Status Constraints: C – Completed, -Progressive, I –Incomplete 

 Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

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

ANS

--Table Create

CREATE TABLE Department (

  dno INT PRIMARY KEY,

  dname VARCHAR(255),

  HOD VARCHAR(255),

  loc VARCHAR(255)

);

INSERT INTO Department VALUES (1, 'Marketing', 'Rajesh Patel', 'Mumbai');

INSERT INTO Department VALUES (2, 'Sales', 'Priya Sharma', 'Delhi');

INSERT INTO Department VALUES (3, 'Finance', 'Suresh Verma', 'Bangalore');

INSERT INTO Department VALUES  (4, 'Computer Science', 'Dr.K.K.Patil', 'Shrirampur');

select * from Department;

CREATE TABLE Project (

  pno INT PRIMARY KEY,

  pname VARCHAR(255),

  start_date DATE,

  budget DECIMAL(10, 2),

  status CHAR(1) check(status in('C', 'P', 'I')),

  dno INT,

  FOREIGN KEY (dno) REFERENCES Department(dno)

);

drop table project;

INSERT INTO Project VALUES (101, 'Product Launch', '01/jan/2020', 100000, 'C', 1);

INSERT INTO Project VALUES (102, 'Product Increase', '01/jan/2020', 100000, 'C', 1);

INSERT INTO Project VALUES (103, 'Sales Campaign', '01/feb/2020', 50000, 'P', 2);

INSERT INTO Project VALUES (104, 'Budget Analysis', '01/mar/2020', 550000, 'P', 3);

INSERT INTO Project VALUES (105, 'Website Redesign', '01/mar/2020', 550000, 'I', 3);

INSERT INTO Project VALUES  (106, 'App', '01/jan/2019', 350000, 'I', 4);

INSERT INTO Project VALUES  (107, 'Web', '01/jan/2019', 150000, 'P', 4);

select * from Project;

--Create Cursor

Declare

 cursor Project_cursor is 

 select pname, start_date from project 

 where start_date >= '01/jan/2020' and start_date <= '30/jan/2020';

Begin

dbms_output.put_line('Project Name | Start Date');

dbms_output.put_line('------------------------------------------');

 for rec IN Project_cursor loop

  dbms_output.put_line(rec.pname || ' | ' || rec.start_date );

 end loop;

End;

/

Output:

-------------------------------------------------------

2) Write a cursor which will display status wise project details of each department.

ANS

Declare

cursor c1_details IS 

select pname, start_date, budget, status, dname from department, project 

where project.dno = department.dno order by status;

Begin

dbms_output.put_line('Project Name | S_Date | Budget | Status | Department');

dbms_output.put_line('----------------------------------------------');

for rec IN c1_details loop

dbms_output.put_line(rec.pname || ' | ' || rec.start_date||' | '||rec.budget||' | '||rec.status||' | '||rec.dname);

End loop;

End;

/

Output:

--------------------------------------------------------------------------------

3) Write a cursor which displays the details of project having budget more than 100000 from ‘Computer Science Department’. 

ANS

Declare

cursor c_budget IS select pname, start_date, budget, status 

from department, project

where project.dno = department.dno and

budget > 100000 and dname = 'Computer Science';

Begin

dbms_output.put_line('Project | start date | Budget | Status ');

dbms_output.put_line('-------------------------------------------');

 for rec IN c_budget loop

  dbms_output.put_line(rec.pname || ' | ' || rec.start_date || ' | ' || rec.budget || ' | ' || rec.status);

 end loop;

End;

/

Output:

-------------------------------------------------------------

SET B: 

Consider the following entities and their relationships. 

 Gym (Name, city, charges, scheme) 

Member (ID, Name, PhoneNo, address) 

Relation between Gym and member is one to many. 

Constraint: Primary Key, charges must be greater than 0. 

 Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

1) Write a cursor which will display Gym details having charges more than 5000 from ‘Pune’ city.

ANS

Declare

Cursor c_charges IS 

select gym.G_Name, city, charges, scheme 

from Gym 

where charges > 5000 and city = 'Pune'; 

Begin

 dbms_output.put_line('Gym Name | City | Charges | Scheme ');

 dbms_output.put_line('-----------------------------------');

 for rec IN c_charges loop

  dbms_output.put_line(rec.G_Name || ' | '|| rec.city || ' | ' || rec.charges || ' | '|| rec.scheme);

 end loop;

End;

Output

------------------------------------------------------------------

2) Write a cursor which will display city wise Gym details.

ANS

Declare

cursor c_gym_details IS select G_Name, city, charges, scheme 

from Gym order by city;

Begin

 dbms_output.put_line('Gym Name | City | Charges | Scheme');

 dbms_output.put_line('-----------------------------------------');

 for rec IN c_gym_details loop

  dbms_output.put_line(rec.G_Name|| ' | ' || rec.city || ' | ' || 

  rec.charges || ' | ' || rec.scheme);

 end loop;

End;

/

Output

---------------------------------------------------------------------

3) Write a cursor which will display gym wise member details.(Use Parametrized Cursor)

ANS