A) Write PL/pgSQL function to increment the employee’s salary by 10% if his/her department is ‘HR’ for given(inputted)employee_id.Schema: Employee(employee_id, first_name, last_name, department, salary)B) Write PL/pgSQL function to find the number of Sundays between given dates
Question
A) Write PL/pgSQL function to increment the employee’s salary by 10% if his/her department is ‘HR’ for given(inputted)employee_id.Schema: Employee(employee_id, first_name, last_name, department, salary)B) Write PL/pgSQL function to find the number of Sundays between given dates
Solution
A) Here is a PL/pgSQL function to increment the employee’s salary by 10% if his/her department is ‘HR’ for a given employee_id.
CREATE OR REPLACE FUNCTION increment_salary(emp_id INT)
RETURNS VOID AS
$$
BEGIN
UPDATE Employee
SET salary = salary * 1.1
WHERE employee_id = emp_id AND department = 'HR';
END;
$$
LANGUAGE plpgsql;
You can call this function with an employee_id as follows:
SELECT increment_salary(1); -- replace 1 with the employee_id
B) Here is a PL/pgSQL function to find the number of Sundays between given dates.
CREATE OR REPLACE FUNCTION count_sundays(date_start DATE, date_end DATE)
RETURNS INT AS
$$
DECLARE
day_count INT;
BEGIN
SELECT COUNT(*) INTO day_count
FROM generate_series(date_start, date_end, '1 day'::interval) day
WHERE EXTRACT(DOW FROM day) = 0;
RETURN day_count;
END;
$$
LANGUAGE plpgsql;
You can call this function with two dates as follows:
SELECT count_sundays('2022-01-01', '2022-12-31'); -- replace with your dates
Similar Questions
Create PL/pgSQL procedure for the increment of employees where in salary less than 35000 will get hike of 15% in their previoussalary and other will get 10% hike in their previous salary. Using following schema, Employees (id, name, department, salary)call the procedure by id and print employee’s name with their updated salary
In PostgreSQL, create a PL/pgSQL block that defines a procedure namedupdate_employee_salary which takes two parameters: employee_id and new_salary. This procedure should update the salary ofthe employee with the given employee_id to the new salary value. Assume “employee” table with fields – eid, ename and salary
In PostgreSQL, create a PL/pgSQL block that defines a function named calculate_salary_bonus which takes two parameters:employee_id and bonus_percentage. This function should calculate the bonus amount for the employee with the givenemployee_id based on their current salary and the provided bonus percentage. Assume “employee” table with fields – eid, enameand salary
Ask user for employee name and pay rate.Create the employee object.Ask user for the number of days the employee worked.For each day it will ask the user for number of hours that employee worked and use the add_hours function to add this to the list of hours_worked in employee class.It will display the employee data as follows (use f string to format the output):Employee Name: Joe DoePay Rate: $ 25.75 per hourTotal number of hours worked: 39.45Total Salary: $ 1015.84
An employee will have a name, pay_rate, and list of hours_worked.Employee class must have following methods:Constructor – takes name and pay_rate. It will also creat an empty list of hours_worked.add_hours – takes hours as a parameter and add it to the hours_worked list.salary – calculates the salary for the employe (pay_rate x total hours worked).Write a main function that will:Ask user for employee name and pay rate.Create the employee object.Ask user for the number of days the employee worked.For each day it will ask the user for number of hours that employee worked and use the add_hours function to add this to the list of hours_worked in employee class.It will display the employee data as follows (use f string to format the output):Employee Name: Joe DoePay Rate: $ 25.75 per hourTotal number of hours worked: 39.45Total Salary: $ 1015.84
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.