Mini-Project for CLP_PLSQL_E2 Hands-on | Fresco Play Hands on Solution #tcs #hackerrank

Search Engine Optimization
Jul
31

Mini-Project for CLP_PLSQL_E2 Hands-on | Fresco Play Hands on Solution #tcs #hackerrank

07/31/2024 12:00 AM by Admin in Technology


Mini-Project for CLP_PLSQL_E2 Hands-on | Fresco Play Hands on Solution #tcs #hackerrank

Introduction

The "Mini-Project for CLP_PLSQL_E2 Hands-on" is a part of the Fresco Play learning platform by TCS, hosted on Hackerrank. This mini-project presents a set of four distinct problems designed to test and enhance your PLSQL skills. Whether you're a beginner or looking to sharpen your PLSQL abilities, this hands-on project provides practical challenges that mimic real-world scenarios.

In this blog post, we'll delve into each of these problems, providing detailed solutions and explanations. Our goal is to guide you through each task, ensuring you not only understand the solution but also the underlying principles of PLSQL. By the end, you'll have a solid grasp of how to approach similar problems, making this an invaluable resource for anyone tackling PLSQL challenges on platforms like Fresco Play and Hackerrank.

 

Understanding the Mini-Project for CLP_PLSQL_E2 Hands-on

The mini-project is structured around four distinct problems, each requiring a unique approach and understanding of PLSQL. Let's explore each problem and its solution in detail.

1. Problem 1: Employees under terminated Manager | Hackerrank Solution

In the first problem, you're tasked with writing a PLSQL block to fetch data using a cursor. The cursor must iterate over a specific set of data and display the results in a formatted manner. This problem tests your ability to handle cursors and manipulate data fetched from a database.

Solution:

To solve this, we first declare a cursor to select the desired data from the table. Then, we use a loop to fetch each row of data and display it. Here's a basic outline of the solution:

SET NULL "NULL";

SET FEEDBACK OFF;

SET ECHO OFF;

SET HEADING OFF;

SET WRAP OFF;

SET LINESIZE 10000;

SET TAB OFF;

SET PAGES 0;

SET DEFINE OFF;

 

/*

Enter your query below.

Please append a semicolon ";" at the end of the query

*/

SET SERVEROUTPUT ON;

 

DECLARE

v_active_count NUMBER;

v_terminated_count NUMBER;

BEGIN

-- Counting Active and Terminated Employees

SELECT COUNT(*) INTO v_active_count

FROM EMP

WHERE EMP_STATUS = 'Active';

 

SELECT COUNT(*) INTO v_terminated_count

FROM EMP

WHERE EMP_STATUS = 'Terminated';

 

DBMS_OUTPUT.PUT_LINE('**Status Count**');

DBMS_OUTPUT.PUT_LINE('Active' ||' ' || v_active_count);

DBMS_OUTPUT.PUT_LINE('Terminated' ||' ' || v_terminated_count);

 

 

DBMS_OUTPUT.PUT_LINE('**Employees under Terminated Manager**');

DBMS_OUTPUT.PUT_LINE('emp_id emp_name Mgr_name Mgr_Status Mgr_Supervisor');

FOR r IN (

SELECT E.EMP_ID, E.EMP_FNAME || ' ' || E.EMP_LNAME AS EMP_NAME,

M.MGR_FNAME || ' ' || M.MGR_LNAME AS MGR_NAME,

M.MGR_STATUS, M.MGR_SUPERVISOR

FROM EMP E

JOIN MGR M ON E.MGR_ID = M.MGR_ID

WHERE E.EMP_STATUS = 'Active' AND M.MGR_STATUS = 'Terminated'

) LOOP

DBMS_OUTPUT.PUT_LINE(r.EMP_ID || ' ' || r.EMP_NAME || ' ' || r.MGR_NAME || ' ' || r.MGR_STATUS || ' ' || r.MGR_SUPERVISOR);

END LOOP;

END;

/

 

2. Problem 2: Employees with yearly incentive Amounts | Hackerrank Solution

The second problem focuses on creating a PLSQL function that calculates a specific value based on input parameters. This function must be called within a PLSQL block to demonstrate its use.

Solution:

SET NULL "NULL";

SET FEEDBACK OFF;

SET ECHO OFF;

SET HEADING OFF;

SET WRAP OFF;

SET LINESIZE 10000;

SET TAB OFF;

SET PAGES 0;

SET DEFINE OFF;

 

/*

Enter your query below.

Please append a semicolon ";" at the end of the query

*/

set serveroutput on;

declare

incentive number;

work_exp number;

cursor curr is

select emp_id,

concat(emp_fname,concat(' ',emp_lname)) as empname,

emp_hiredate

from emp

where emp_status='Active'

and extract(month from to_date(emp_hiredate, 'yyyy-mm-dd'))=12;

c curr%rowtype;

begin

open curr;

dbms_output.put_line('Employees with yearly incentive amounts:');

dbms_output.put_line('**********');

dbms_output.put_line('Employee ID Name of the Employee Hire Date Incentive Amount');

dbms_output.put_line('**********');

loop

fetch curr into c;

exit when curr%notfound;

work_exp:=MONTHS_BETWEEN(to_date('31/12/2020', 'dd/mm/yyyy'),c.emp_hiredate)/12;

case

when work_exp>13 then incentive:=8000;

when work_exp>11 then incentive:=5000;

when work_exp>9 then incentive:=3000;

when work_exp>7 then incentive:=2000;

when work_exp>4 then incentive:=1000;

when work_exp>0 then incentive:=400;

else incentive:='null';

end case;

dbms_output.put_line(c.emp_id||' '||c.empname||' '||c.emp_hiredate||' '|| incentive);

end loop;

dbms_output.put_line('**********');

dbms_output.put_line('The number of rows fetched is '||curr%rowcount);

dbms_output.put_line('**********');

end;

/

 

 

 

 

 

 

 

 

3. Problem 3: Employees not assigned to any department | Hackerrank Solution

 

Solution:

SET NULL "NULL";

SET FEEDBACK OFF;

SET ECHO OFF;

SET HEADING OFF;

SET WRAP OFF;

SET LINESIZE 10000;

SET TAB OFF;

SET PAGES 0;

SET DEFINE OFF;

 

/*

Enter your query below.

Please append a semicolon ";" at the end of the query

*/

SET SERVEROUTPUT ON;

 

DECLARE

v_dept_id dept.dept_id%TYPE;

v_dept_name dept.dept_name%TYPE;

v_dept_head dept.dept_head%TYPE;

v_mgr_name VARCHAR2(100);

 

CURSOR emp_cursor IS

SELECT emp_id, emp_fname || ' ' || emp_lname AS emp_name, emp_status

FROM emp

WHERE emp_status = 'Active' AND dept_id IS NULL;

 

CURSOR dept_cursor IS

SELECT dept_id, dept_name, dept_head

FROM dept

WHERE dept_id NOT IN (SELECT dept_id FROM emp WHERE dept_id IS NOT NULL);

 

BEGIN

OPEN dept_cursor;

FETCH dept_cursor INTO v_dept_id, v_dept_name, v_dept_head;

CLOSE dept_cursor;

 

IF v_dept_id IS NULL THEN

DBMS_OUTPUT.PUT_LINE('No available department without employees.');

RETURN;

END IF;

 

SELECT mgr_fname || ' ' || mgr_lname INTO v_mgr_name FROM mgr WHERE dept_head = v_dept_head;

 

DBMS_OUTPUT.PUT_LINE('Emp_id Emp_name Emp_status Dept_id Dept_name Mgr_name');

 

FOR emp_rec IN emp_cursor LOOP

UPDATE emp SET dept_id = v_dept_id, mgr_id = v_dept_head WHERE emp_id = emp_rec.emp_id;

DBMS_OUTPUT.PUT_LINE(emp_rec.emp_id || ' ' || emp_rec.emp_name || ' ' || emp_rec.emp_status || ' ' || v_dept_id || ' ' || v_dept_name || ' ' || v_mgr_name);

END LOOP;

 

COMMIT;

 

/*DBMS_OUTPUT.PUT_LINE('Employees have been assigned to the department.');*/

 

END;

/

4. Problem 4: Total Amount of Salary of each department | Hackerrank Solution

Solution:

SET NULL "NULL";

SET SERVEROUTPUT ON;

SET FEEDBACK OFF;

SET ECHO OFF;

SET HEADING OFF;

SET WRAP OFF;

SET LINESIZE 10000;

SET TAB OFF;

SET PAGES 0;

SET DEFINE OFF;

SET SERVEROUTPUT ON;

 

DECLARE

-- Variables to hold total salaries and employee counts

v_total_salary_active NUMBER := 0;

v_total_employees_active NUMBER := 0;

v_total_salary_terminated NUMBER := 0;

v_total_employees_terminated NUMBER := 0;

 

-- Cursor for active employees

CURSOR active_emp_cursor IS

SELECT NVL(dept_id, 0) AS dept_id, SUM(emp_sal) AS total_salary, COUNT(*) AS total_employees

FROM emp

WHERE emp_status = 'Active'

GROUP BY NVL(dept_id, 0)

ORDER BY NVL(dept_id, 0);

 

-- Cursor for terminated employees

CURSOR terminated_emp_cursor IS

SELECT NVL(dept_id, 0) AS dept_id, SUM(emp_sal) AS total_salary, COUNT(*) AS total_employees

FROM emp

WHERE emp_status = 'Terminated'

GROUP BY NVL(dept_id, 0)

ORDER BY NVL(dept_id, 0);

 

-- Variables to hold cursor data

v_dept_id NUMBER;

v_total_salary NUMBER;

v_total_employees NUMBER;

 

BEGIN

-- Display details for active employees

DBMS_OUTPUT.PUT_LINE('Details of Active employees');

DBMS_OUTPUT.PUT_LINE('-----------------------------------');

DBMS_OUTPUT.PUT_LINE('Department_id Total_employees Total_salary');

OPEN active_emp_cursor;

LOOP

FETCH active_emp_cursor INTO v_dept_id, v_total_salary, v_total_employees;

EXIT WHEN active_emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_dept_id || ' ' || v_total_employees || ' ' || v_total_salary);

-- Accumulate totals

v_total_salary_active := v_total_salary_active + v_total_salary;

v_total_employees_active := v_total_employees_active + v_total_employees;

END LOOP;

CLOSE active_emp_cursor;

DBMS_OUTPUT.PUT_LINE('-----------------------------------');

 

-- Display accumulated totals for active employees

DBMS_OUTPUT.PUT_LINE('Total_employees ' || v_total_employees_active);

DBMS_OUTPUT.PUT_LINE('Total_salary ' || v_total_salary_active);

DBMS_OUTPUT.PUT_LINE('-----------------------------------');

 

-- Display details for terminated employees

DBMS_OUTPUT.PUT_LINE('Details of Terminated employees');

DBMS_OUTPUT.PUT_LINE('-----------------------------------');

 

DBMS_OUTPUT.PUT_LINE('Department_id Total_employees Total_salary');

OPEN terminated_emp_cursor;

LOOP

FETCH terminated_emp_cursor INTO v_dept_id, v_total_salary, v_total_employees;

EXIT WHEN terminated_emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_dept_id || ' ' || v_total_employees || ' ' || v_total_salary);

-- Accumulate totals

v_total_salary_terminated := v_total_salary_terminated + v_total_salary;

v_total_employees_terminated := v_total_employees_terminated + v_total_employees;

END LOOP;

CLOSE terminated_emp_cursor;

DBMS_OUTPUT.PUT_LINE('-----------------------------------');

 

-- Display accumulated totals for terminated employees

DBMS_OUTPUT.PUT_LINE('Total_employees ' || v_total_employees_terminated);

DBMS_OUTPUT.PUT_LINE('Total_salary ' || v_total_salary_terminated);

DBMS_OUTPUT.PUT_LINE('-----------------------------------');

 

END;

/

 

Mini-Project for CLP_PLSQL_E2 Hands-on

The "Mini-Project for CLP_PLSQL_E2 Hands-on" on Fresco Play by TCS is a comprehensive challenge that tests various aspects of PLSQL. Each problem requires a different approach, from using cursors and functions to handling exceptions and writing procedures. This diversity helps you develop a well-rounded understanding of PLSQL, crucial for real-world database management tasks.

By working through these problems, you'll not only enhance your technical skills but also gain confidence in your ability to solve complex database challenges. The solutions provided here are just one way to approach these problems. Feel free to experiment and find more efficient or elegant solutions.

FAQs

What is PLSQL and why is it important?
\r\n\\r\\n\\\\r\\\\nPLSQL is a procedural language extension for SQL used in Oracle databases. It is important because it allows for complex operations, such as conditional logic, loops, and error handling, making it powerful for managing database operations.

How do cursors work in PLSQL?
Cursors in PLSQL are used to retrieve multiple rows from a database query. They allow you to iterate over each row and perform operations on the data.

 

What is the difference between a procedure and a function in PLSQL?
The main difference is that a function returns a value, while a procedure does not. Procedures are generally used to perform an action, whereas functions are used to calculate and return values.

 

How can I handle errors in PLSQL?
PLSQL provides exception handling through the EXCEPTION block. You can handle specific exceptions like NO_DATA_FOUND or DUP_VAL_ON_INDEX or use OTHERS to catch all other exceptions.

 

What are some best practices for writing PLSQL code?
Some best practices include using meaningful variable names, handling exceptions appropriately, optimizing SQL queries, and modularizing code into procedures and functions for reusability.

Where can I practice PLSQL problems?
You can practice PLSQL on platforms like Hackerrank, LeetCode, and Fresco Play. These platforms offer various problems and challenges that can help you improve your skills.

Conclusion

The "Mini-Project for CLP_PLSQL_E2 Hands-on" is an excellent opportunity to apply and enhance your PLSQL skills. By tackling these problems, you'll gain practical experience in using cursors, functions, procedures, and exception handling. This blog post has provided detailed solutions to each problem, offering a comprehensive guide to mastering PLSQL for real-world applications.

For further learning, consider exploring additional resources on PLSQL and related database technologies. Continuous practice and exploration of more complex scenarios will help solidify your understanding and proficiency in PLSQL.


Suggestions for Inbound and Outbound Links

Inbound Links:

Outbound Links:

 


leave a comment
Please post your comments here.