Resumen Negocios

-- Assignment Day 3 Exercise on Cursors

use assignmentDay1

--1. Create a Cursor to select all records from employee and display number of records retrieved.

DECLARE curr1 CURSOR STATIC

FOR SELECT * FROM Employee

OPEN curr1

SELECT @@CURSOR_ROWS

FETCH FROM Curr1

WHILE (@@FETCH_status = 0)

BEGIN

FETCH Next FROM curr1

END

CLOSE curr1

DEALLOCATE curr1

--2. Extend the Solution of Question 1 for displaying all records of the Employee table. ****

DECLARE curr2 CURSOR STATIC

FOR SELECT * FROM Employee

OPEN curr2

SELECT @@CURSOR_ROWS

FETCH FROM Curr2

WHILE (@@FETCH_status = 0)

BEGIN

FETCH Next FROM curr2

END

CLOSE curr2

DEALLOCATE curr2

--3. Create a Cursor to hold Candidate Code, Candidate First Name and Test Score from the

-- Candidate table. Fetch each row to check for the test score of the candidate. If the test

-- score of the candidate is greater than 60 then “Interview call letter” along with the Code,

-- First Name and Test Score has to display else “Rejection letter” message has to be

-- displayed along with the Code, First Name and Test Score.

DECLARE Curr3 CURSOR STATIC

FOR SELECT CandCode, CandFName, TestScore, ' ' = CASE

WHEN (TestScore > 60)

THEN 'Interview Call'

ELSE 'Rejection'

END

FROM Candidate

OPEN Curr3

FETCH FROM Curr3

WHILE (@@FETCH_status = 0)

BEGIN

FETCH Next FROM Curr3

END

CLOSE Curr3

DEALLOCATE Curr3

--4. Create a Cursor to hold Consultant Code and Consultant Fee. Fetch each row to check for the consultant fee,

-- If Consultant fee is less than 15 increments by one.

--SELECT ConsultantCode,ConsultantFirmName, ConsultantFee FROM Consultants

DECLARE Curr4 CURSOR STATIC

FOR SELECT ConsultantCode,ConsultantFirmName, ConsultantFee = CASE

WHEN (ConsultantFee <= 15)

THEN (ConsultantFee + 1)

ELSE ConsultantFee

END

FROM Consultants

OPEN Curr4

FETCH FROM Curr4

WHILE (@@FETCH_status = 0)

BEGIN

FETCH NEXT FROM Curr4

END

CLOSE Curr4

DEALLOCATE Curr4

--5. Create a Cursor to hold Candidate Code and Date of Application from the Candidate table.

-- If the date of application is greater than 5 years then delete those records.

DECLARE Curr5 CURSOR

FOR SELECT * FROM Candidate

OPEN Curr5

FETCH FROM Curr5

DELETE Candidate

WHERE DATEDIFF (MM, DateOfApplication, GETDATE ())/12 > 5

WHILE (@@FETCH_status = 0)

BEGIN

FETCH NEXT FROM Curr5

DELETE Candidate

WHERE DATEDIFF (MM, DateOfApplication, GETDATE ())/12 > 5

END

...