Scribd
Upload
9 views2 pages

Program 9

The document outlines a SQL assignment that involves creating an 'employees' table with fields for id, name, age, address, and salary. It includes multiple insert statements to populate the table with employee data and a PL/SQL block that categorizes employees as 'Young' or 'Old' based on their age, while also counting the number of employees in each category. Finally, it outputs the details of each employee along with the counts of young and old employees.

Uploaded by

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

Program 9

The document outlines a SQL assignment that involves creating an 'employees' table with fields for id, name, age, address, and salary. It includes multiple insert statements to populate the table with employee data and a PL/SQL block that categorizes employees as 'Young' or 'Old' based on their age, while also counting the number of employees in each category. Finally, it outputs the details of each employee along with the counts of young and old employees.

Uploaded by

Rohit Lande
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

ASSIGNMENT NO : 9

CREATE TABLE employees (


id INT PRIMARY KEY,
name VARCHAR(100),
age INT,
address VARCHAR(255),
salary DECIMAL(10,2)
);

INSERT INTO employees (id, name, age, address, salary) VALUES(1, 'John Doe', 30, '123 Main St, City
A', 55000.00);
INSERT INTO employees (id, name, age, address, salary) VALUES(2, 'Jane Smith', 28, '456 Oak St, City
B', 60000.00);
INSERT INTO employees (id, name, age, address, salary) VALUES(3, 'Mike Johnson', 35, '789 Pine St,
City C', 75000.00);
INSERT INTO employees (id, name, age, address, salary) VALUES(4, 'Emily Davis', 40, '321 Elm St, City
D', 80000.00);
INSERT INTO employees (id, name, age, address, salary) VALUES(5, 'Robert Brown', 25, '654 Maple St,
City E', 50000.00);
INSERT INTO employees (id, name, age, address, salary) VALUES(6, 'Linda White', 38, '987 Birch St,
City F', 72000.00);
INSERT INTO employees (id, name, age, address, salary) VALUES(7, 'James Wilson', 45, '741 Cedar St,
City G', 85000.00);
INSERT INTO employees (id, name, age, address, salary) VALUES(8, 'Patricia Martinez', 29, '852
Willow St, City H', 58000.00);
INSERT INTO employees (id, name, age, address, salary) VALUES(9, 'David Garcia', 33, '963 Aspen St,
City I', 69000.00);
INSERT INTO employees (id, name, age, address, salary) VALUES(10, 'Sarah Lee', 27, '159 Cherry St,
City J', 62000.00);

DECLARE
CURSOR emp_cursor IS
SELECT name, address, salary, age,
CASE
WHEN age < 40 THEN 'Young'
ELSE 'Old'
END AS age_category
FROM employees;

v_name [Link]%TYPE;
v_address [Link]%TYPE;
v_salary [Link]%TYPE;
v_age [Link]%TYPE;
v_age_category VARCHAR2(10);
v_young_count NUMBER := 0;
v_old_count NUMBER := 0;

BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_name, v_address, v_salary, v_age, v_age_category;
EXIT WHEN emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ', Address: ' || v_address ||


', Salary: ' || v_salary || ', Age: ' || v_age ||
', Age Category: ' || v_age_category);

IF v_age_category = 'Young' THEN


v_young_count := v_young_count + 1;
ELSE
v_old_count := v_old_count + 1;
END IF;
END LOOP;

CLOSE emp_cursor;

DBMS_OUTPUT.PUT_LINE('Young Employees Count: ' || v_young_count);


DBMS_OUTPUT.PUT_LINE('Old Employees Count: ' || v_old_count);
END;

You might also like