DATA BASE
MANAGEMENT
SYSTEM
CSE-2004
LAB ASSIGNMENT-5
SLOT: L39+L40
Submitted By: Sanidhaya Agarwal
Reg. No.: 20BCE2853
TABLE CREATION:
TABLE VALUES:
Aim: To understand the concept of PL/SQL Programming
1. Write a PL/SQL block to accept an empno and display the salary of the
person.
DECLARE
SALARY empl.Salary%type;
EMPNO empl.SSN_NUMBER%type;
BEGIN
EMPNO:=:EMPNO;
select Salary into SALARY from empl where SSN_NUMBER=EMPNO;
dbms_output.put_line('Salary of '||EMPNO||'is='||SALARY);
END;
2. Write a PL/SQL program to delete one record in employee table
BEGIN
DELETE FROM EMPL
WHERE FirstName = 'Sanidhaya';
COMMIT;
END;
/
3. Write a program to delete employee details who are having age >60.
BEGIN
DELETE FROM EMPL
WHERE age>60;
COMMIT;
END;
/
4. Write a PL/SQL block to display employees who make a minimum
salary of $1,000.
5. Write a PL/SQL to delete a records whose basic salary is <2000 from
employee table
BEGIN
DELETE FROM EMPL
WHERE salary<2000;
COMMIT;
END;
/
Aim: To know the usage of different sequential control structures in
PL/SQL effective programming
1. Write a PL/SQL block to find the greatest of three numbers.
declare
a number;
b number;
c number;
begin
a:=:a;
b:=:b;
c:=:c;
dbms_output.put_line('a='||a||' b='||b||' c='||c);
if a>b AND a>c
then
dbms_output.put_line('a is greatest');
else
if b>a AND b>c
then
dbms_output.put_line('b is greatest');
else
dbms_output.put_line('c is greatest');
end if;
end if;
end;
2. Write a PL/SQL code to print the student’s grade accepting their
marks in three subjects (hint use: case selector….)
DECLARE
grade1 CHAR(1);
grade2 CHAR(1);
grade3 CHAR(1);
BEGIN
grade1:=:grade1;
grade2:=:grade2;
grade3:=:grade3;
dbms_output.Put_line('Your Grade for 1st subject');
CASE grade1
WHEN 'A' THEN dbms_output.Put_line('Your Grade is: Outstanding');
WHEN 'B' THEN dbms_output.Put_line('Your Grade is: Very Good');
WHEN 'C' THEN dbms_output.Put_line('Your Grade is: Average');
ELSE dbms_output.Put_line('No such grade in the list.');
END CASE;
dbms_output.Put_line('Your Grade for 2nd subject');
CASE grade2
WHEN 'A' THEN dbms_output.Put_line('Your Grade is: Outstanding');
WHEN 'B' THEN dbms_output.Put_line('Your Grade is: Very Good');
WHEN 'C' THEN dbms_output.Put_line('Your Grade is: Average');
ELSE dbms_output.Put_line('No such grade in the list.');
END CASE;
dbms_output.Put_line('Your Grade for 3rd subject');
CASE grade3
WHEN 'A' THEN dbms_output.Put_line('Your Grade is: Outstanding');
WHEN 'B' THEN dbms_output.Put_line('Your Grade is: Very Good');
WHEN 'C' THEN dbms_output.Put_line('Your Grade is: Average');
ELSE dbms_output.Put_line('No such grade in the list.');
END CASE;
END;
Procedure
1. Write a procedure to accept an employee name and display his
Department names.
DECLARE
ECODE Empl.ssn_number % TYPE;
first_name Empl.firstname%TYPE;
department_name Department.department_name%TYPE;
BEGIN
ECODE := :ECODE;
select department_name into department_name from empl natural join Department
where ssn_number=ecode;
Select firstname into first_name from Empl where ssn_number=ecode;
DBMS_OUTPUT.put_line ('Employee Name: ' || first_name);
DBMS_OUTPUT.put_line ('Department: ' || department_name);
Exception
When others then
DBMS_OUTPUT.put_line ('Error: ' || sqlerrm);
END;
/
Cursor
Aim: To understand implicit and explicit cursor in PL/SQL
1. Retrieve the employee details using cursors.
DECLARE
CURSOR z_emp_info IS
SELECT SSN_Number,
firstname,
lastname,
salary
FROM empl;
r_emp_info z_emp_info%ROWTYPE;
BEGIN
OPEN z_emp_info;
LOOP
FETCH z_emp_info INTO r_emp_info;
EXIT WHEN z_emp_info%NOTFOUND;
dbms_output.Put_line('Employees Information:: '
||' ID: '
||r_emp_info.SSN_Number
||' Name: '
||r_emp_info.firstname
||' '
||r_emp_info.lastname);
END LOOP;
CLOSE z_emp_info;
END;
/
2. Write a cursor program to display all the employee and department
details
DECLARE
CURSOR z_emp_info IS
SELECT DepartmentNumber,
Department_Name,
ManagerSSN
FROM department;
r_emp_info z_emp_info%ROWTYPE;
BEGIN
OPEN z_emp_info;
LOOP
FETCH z_emp_info INTO r_emp_info;
EXIT WHEN z_emp_info%NOTFOUND;
dbms_output.Put_line('Employees Information:: '
||' ID: '
||r_emp_info.DepartmentNumber
||' Name: '
||r_emp_info.DepartmentName
||' '
||r_emp_info.ManagerSSN);
END LOOP;
CLOSE z_emp_info;
END;
/
