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