Scaler Function, 10 Ques.sql

-- Create Employees table
CREATE TABLE Employees (
    Emp_ID INT PRIMARY KEY,
    Emp_Name VARCHAR(100),
    DOB DATE,
    Salary DECIMAL(10,2),
    Department_ID INT
)

-- Insert 50 sample rows
INSERT INTO Employees (Emp_ID, Emp_Name, DOB, Salary, Department_ID)
VALUES
(1, 'Rahul',  '1995-03-12', 120000, 1),
(2, 'Priya',  '1998-07-25',  75000, 2),
(3, 'Amit',   '2000-01-10',  45000, 2),
(4, 'Sneha',  '1992-11-05',  95000, 3),
(5, 'Arjun',  '1999-04-18',  30000, 1),
(6, 'Kavita', '1994-09-30', 150000, 3),
(7, 'Rohan',  '1996-02-14', 60000, 2),
(8, 'Meena',  '1997-08-20', 85000, 1),
(9, 'Vikas',  '1993-12-01', 40000, 3),
(10,'Anjali', '1990-05-22', 110000, 2),
(11,'Suresh', '1988-07-11', 95000, 1),
(12,'Pooja',  '1991-09-15', 72000, 3),
(13,'Deepak', '1999-01-05', 35000, 2),
(14,'Neha',   '1995-04-10', 125000, 1),
(15,'Manoj',  '1992-06-18', 80000, 3),
(16,'Rita',   '1996-11-30', 47000, 2),
(17,'Sanjay', '1993-03-25', 105000, 1),
(18,'Alok',   '1997-07-07', 65000, 3),
(19,'Divya',  '1994-02-19', 90000, 2),
(20,'Nitin',  '1990-10-12', 115000, 1),
(21,'Geeta',  '1998-01-28', 55000, 3),
(22,'Varun',  '1995-09-09', 48000, 2),
(23,'Shreya', '1992-12-17', 102000, 1),
(24,'Ravi',   '1996-05-03', 70000, 3),
(25,'Komal',  '1999-08-21', 32000, 2),
(26,'Ajay',   '1994-04-14', 98000, 1),
(27,'Payal',  '1997-06-29', 60000, 3),
(28,'Harsh',  '1991-02-08', 125000, 2),
(29,'Monika', '1993-07-19', 45000, 1),
(30,'Rajesh', '1990-11-23', 135000, 3),
(31,'Tina',   '1998-03-16', 75000, 2),
(32,'Karan',  '1995-12-05', 88000, 1),
(33,'Bhavna', '1992-09-27', 56000, 3),
(34,'Ashok',  '1996-01-11', 47000, 2),
(35,'Simran', '1999-10-30', 30000, 1),
(36,'Mohit',  '1994-05-20', 150000, 3),
(37,'Rekha',  '1997-07-02', 68000, 2),
(38,'Anand',  '1993-08-15', 94000, 1),
(39,'Lata',   '1990-12-25', 120000, 3),
(40,'Dev',    '1995-02-09', 81000, 2),
(41,'Sarita', '1998-06-18', 35000, 1),
(42,'Gaurav', '1992-04-22', 108000, 3),
(43,'Isha',   '1996-09-14', 72000, 2),
(44,'Vivek',  '1994-01-07', 95000, 1),
(45,'Anu',    '1999-11-26', 40000, 3),
(46,'Lokesh', '1991-03-03', 115000, 2),
(47,'Snehal', '1997-05-12', 65000, 1),
(48,'Parul',  '1993-07-30', 87000, 3),
(49,'Dhruv',  '1995-10-17', 56000, 2),
(50,'Swati',  '1992-02-28', 98000, 1);

Select * from Employees

create function return_square(num INT)
returnS INT as $$
begin
     return num * num;
end;
$$ language plpgsql;
select return_square(6)


CREATE OR REPLACE FUNCTION fn_upper_name(emp_name VARCHAR)
RETURNS VARCHAR AS $$
BEGIN
    RETURN UPPER(emp_name);
END;
$$ LANGUAGE plpgsql;
SELECT name, fn_upper_name(name) AS upper_name
FROM employees;


CREATE OR REPLACE FUNCTION fn_name_length(emp_name VARCHAR)
RETURNS INT AS $$
BEGIN
    RETURN LENGTH(emp_name);
END;
$$ LANGUAGE plpgsql;
SELECT name, fn_name_length(name) AS name_length
FROM employees;



CREATE OR REPLACE FUNCTION fn_calculate_age(dob DATE)
RETURNS INT AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM AGE(dob));
END;
$$ LANGUAGE plpgsql;
SELECT empid, name, dob, fn_calculate_age(dob) AS age
FROM employees;


create function fx_assign_grade_on_salary(salary int)
returns text as $$
begin
return case
           when salary >= 80000 then 'A'
           when salary >= 50000 then 'B'
           else 'C'
end;
end;
$$ language plpgsql;
select fx_assign_grade_on_salary(80000)



CREATE OR REPLACE FUNCTION fn_birth_year(dob DATE)
RETURNS INT AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM dob);
END;
$$ LANGUAGE plpgsql;


-- Create a scalar function to convert annual to monthly salary
CREATE FUNCTION GetMonthlySalary(Salary int)
RETURNS int AS $$
BEGIN
    return @Salary = @Salary / 12;
END;
$$ language plpgsql;
select GetMonthlySalary(120000)
drop function  getMonthlySalary


CREATE FUNCTION cal_tax(Salary int)
RETURNS int AS $$
BEGIN
return case
when salary >= 10000 then (10/100) * salary
else (5/100) * salary
END;
$$ language plpgsql;
select cal_tax(120000)
drop function  getMonthlySalary



-- Create scalar function
CREATE FUNCTION dbo.fn_GetEmpIDName (@EmpID INT)
RETURNS VARCHAR(150)
AS
BEGIN
    DECLARE @Result VARCHAR(150);

    SELECT @Result = CAST(Emp_ID AS VARCHAR(10)) + '-' + Emp_Name
    FROM Employees
    WHERE Emp_ID = @EmpID;

    RETURN @Result;
END;


create function bonus(salary int)
returns text as $$
begin
return case
           when salary >= 50000 then 'Bonus'
           else 'NO Bonus'
end;
end;
$$ language plpgsql;
select bonus(80000)





🟢 Basic

Write a scalar function to return the square of any integer.

Write a scalar function to return employee names in uppercase.

Write a scalar function to return the length of a given employee name.

🟡 Intermediate

Write a scalar function to calculate age from DOB.

Write a scalar function to assign grade based on Salary (A/B/C).

Write a scalar function to return the birth year from DOB.

🔴 Advanced

Write a scalar function to convert annual Salary into monthly salary.

Write a scalar function to calculate tax (10% if salary ≥ 100000, else 5%).

Write a scalar function to return employee name as EmpID-Name.

Write a scalar function to check if employee is eligible for bonus (Salary ≥ 50000).