CSL 333 DATABASE MANAGEMENT SYSTEM LAB
Date: 15/11/2024 (Friday), Time: 9:30 am to 12:30 pm
SET A
1. Create the following table with the mapping given below.
emp_details (emp_no, emp_name, DOB, address, doj, mobile_no, dept_no, salary)
dept_details (dept no, dept_name, location)
Write a PL/SQL program to input department name and update the salary of
employees working in that department by 10%.
ANS:
--CREATE THE TABLE emp_details & dept_details
--INSERT VALUES TO IT
DECLARE
v_dept_name VARCHAR2(50);
v_dept_no NUMBER(3);
BEGIN
-- Get department name from user
DBMS_OUTPUT.PUT_LINE('Enter department name: ');
DBMS_INPUT.GET_LINE(v_dept_name);
-- Find the department number based on the department name
SELECT dept_no INTO v_dept_no
FROM dept_details
WHERE dept_name = v_dept_name;
-- Update the salary of employees in the specified department
UPDATE emp_details
SET salary = salary * 1.1
WHERE dept_no = v_dept_no;
DBMS_OUTPUT.PUT_LINE('Salary of employees in department updated successfully.');
END;
Downloaded from Ktunotes.in
/
2. Consider the employee database given below
emp (emp_id, emp_name, Street_No, city)
works (cmp_id, company name, salary)
company (company name, city)
a. Display the employee names group by their city.
ANS:
SELECT e.city, e.emp_name
FROM emp e
GROUP BY e.city;
b. Display the employee name who earns the maximum salary.
ANS:
SELECT e.emp_name
FROM emp e
JOIN works w ON e.emp_id = w.emp_id
WHERE w.salary = (SELECT MAX(salary) FROM works);
c. Find all employees who earn more than the average salary of all employees
of their company.
ANS:
SELECT e.emp_name
FROM emp e
JOIN works w ON e.emp_id = w.emp_id
WHERE w.salary > (SELECT AVG(salary) FROM works);
Downloaded from Ktunotes.in
