Scribd
Upload
5 views2 pages

Solution Lab - N°3

The document contains SQL queries and a PL/SQL block for managing employee data in an HR database. It includes commands to list tables, display employee details, filter employees by salary, and classify an employee's salary based on their ID. The PL/SQL block also handles exceptions for missing data and errors.

Uploaded by

ellyrhita222
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views2 pages

Solution Lab - N°3

The document contains SQL queries and a PL/SQL block for managing employee data in an HR database. It includes commands to list tables, display employee details, filter employees by salary, and classify an employee's salary based on their ID. The PL/SQL block also handles exceptions for missing data and errors.

Uploaded by

ellyrhita222
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Question 1: List all the tables in the HR database

SELECT table_name
FROM user_tables ;

Question 2: Display the first name, last name, and salary of all employees
SELECT first_name, last_name, salary
FROM employees
ORDER BY employee_id;

Question 3: Show the employees who earn more than 10,000


SELECT first_name, last_name, salary
FROM employees
WHERE salary > 10000 ORDER BY salary DESC;

Question 4: List employees who work in department 90, ordered by salary (highest first)
SELECT first_name, last_name, department_id, salary
FROM employees
WHERE department_id = 90
ORDER BY salary DESC;

Question 5: PL/SQL block to classify employee salary (employee_id = 103)


DECLARE
v_employee_id employees.employee_id%TYPE := 103;
v_first_name employees.first_name%TYPE;
v_last_name employees.last_name%TYPE;
v_salary [Link]%TYPE;
v_classification VARCHAR2(20);
BEGIN
SELECT first_name, last_name, salary
INTO v_first_name, v_last_name, v_salary
FROM employees
WHERE employee_id = v_employee_id;
IF v_salary < 5000
THEN v_classification := 'Low';
ELSIF v_salary BETWEEN 5000 AND 10000
THEN v_classification := 'Medium';
ELSE v_classification := 'High'; END IF;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_first_name || ' ' || v_last_name);
DBMS_OUTPUT.PUT_LINE('Salary: $' || v_salary);
DBMS_OUTPUT.PUT_LINE('Classification: ' || v_classification);
EXCEPTION WHEN NO_DATA_FOUND
THEN DBMS_OUTPUT.PUT_LINE('Employee with ID ' || v_employee_id || ' not found.');
WHEN OTHERS
THEN DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM); END; /

You might also like