SQL Training: Exercises on Functions

Exercises on Functions:
1. Write a Function which returns Ename from EMP table by taking empno as argument.

2. Write a scalar value return function which returns sum of two given numbers.

3. Write a Function to display biggest of 3 given numbers(need to take 3 argument for 3 values)

4. Write a Function to display maximum salary of a given deptno from EMP table.

5. Write a Function to display the SUM, AVG sal of employees according to their deptno’s.

6. Write a Function to display details of departments and their corresponding Employee details from DEPT and EMP tables according to deptno.

7. Write a Function to display all the employee details whose salary between first argument value and second argument value.

8. Write a function to display the Names of Employees whose name contain particular character (character we need to take from argument).

9.Write a function to display specific number of senior most employees in arganization.

10.write a table valued return function to display second heighest salary for each department.

MY SOLUTIONS:

---------------------------------------------------
---------------------------------------------------
--EXERCISES ON FUNCTIONS

--Q1
CREATE FUNCTION dbo.udf_EName(@empno AS NUMERIC(4,0))
     RETURNS VARCHAR(10)
AS
BEGIN
   DECLARE @empname VARCHAR(10)
   SELECT @empname =  ENAME FROM dbo.emp WHERE EMPNO = @empno
   RETURN @empname
END

--SELECT dbo.udf_EName(7369)

--Q2
CREATE FUNCTION dbo.udf_SumOfTwoNo(@num1 AS INT,@num2 AS INT)
     RETURNS INT
AS
BEGIN
   DECLARE @sum INT
   SELECT @sum = @num1 + @num2
   RETURN @sum
END
--SELECT dbo.udf_SumOfTwoNo(2,3)

--Q3
CREATE FUNCTION dbo.udf_MaxOfThreeNo(@num1 AS INT,@num2 AS INT,@num3 AS INT)
     RETURNS INT
AS
BEGIN
   DECLARE @max INT
   SELECT @max =  MAX(v) FROM (VALUES (@num1), (@num2), (@num3)) AS value(v);
   RETURN @max
END
--SELECT dbo.udf_MaxOfThreeNo(2,3,4)

--Q4
CREATE FUNCTION dbo.udf_MaxSal(@deptno AS NUMERIC(2,0))
     RETURNS NUMERIC(7,0)
AS
BEGIN
   DECLARE @max NUMERIC(7,0)
   SELECT @max =  MAX(SAL) FROM dbo.emp WHERE DEPTNO= @deptno
   RETURN @max
END

--SELECT dbo.udf_MaxSal(20)

--Q5
CREATE FUNCTION dbo.udf_MaxandAvgSal(@deptno AS NUMERIC(2,0))
  RETURNS TABLE
  RETURN SELECT MAX(SAL) AS MAXSAL,AVG(SAL) AS AVGSAL
         FROM dbo.emp
WHERE DEPTNO= @deptno

--SELECT * FROM dbo.udf_MaxandAvgSal(20)

--Q6
CREATE FUNCTION dbo.udf_DeptAndEmpDetails(@deptno AS NUMERIC(2,0))
  RETURNS TABLE
  RETURN SELECT d.DEPTNO
                   ,d.DNAME
                   ,d.LOC
   ,e.EMPNO
   ,e.ENAME
   ,e.JOB
   ,e.MGR
   ,e.HIREDATE
   ,e.SAL
   ,e.COMM
           FROM dbo.dept d
   INNER JOIN dbo.emp e
          ON d.DEPTNO = e.DEPTNO
  WHERE d.DEPTNO= @deptno

--SELECT * FROM dbo.udf_DeptAndEmpDetails(20)

--Q7

CREATE FUNCTION dbo.udf_SalInRange(@lowerlim AS NUMERIC(7,0),@upperlim AS NUMERIC(7,0))
  RETURNS TABLE
  RETURN SELECT e.EMPNO
   ,e.ENAME
   ,e.JOB
   ,e.MGR
   ,e.HIREDATE
   ,e.SAL
   ,e.COMM
   ,e.DEPTNO
           FROM dbo.emp e
  WHERE e.SAL BETWEEN  @lowerlim AND @upperlim

--SELECT * FROM dbo.udf_SalInRange(1100,3000)

--Q8

CREATE FUNCTION dbo.udf_Enamewithspecificchar(@specificchar AS CHAR(1))
  RETURNS TABLE
  RETURN SELECT e.ENAME
           FROM dbo.emp e
  WHERE e.ENAME LIKE '%' + @specificchar +'%'

--SELECT * FROM dbo.udf_Enamewithspecificchar('K')

--Q9

CREATE FUNCTION dbo.udf_GivenNoOfSeniorEmp(@num INT)
  RETURNS TABLE
  RETURN SELECT TOP (@num) e.EMPNO
   ,e.ENAME
   ,e.JOB
   ,e.MGR
   ,e.HIREDATE
   ,e.SAL
   ,e.COMM
   ,e.DEPTNO
           FROM dbo.emp e
  ORDER BY e.HIREDATE

--SELECT * FROM dbo.udf_GivenNoOfSeniorEmp(2)

--Q10

CREATE FUNCTION dbo.udf_SecondMaxSalPerDept(@deptno AS NUMERIC(2,0))
  RETURNS TABLE
  RETURN  SELECT TOP 1 X.EMPNO
,X.ENAME
,X.JOB
,X.MGR
,X.HIREDATE
,X.SAL
,X.COMM
,X.DEPTNO
   FROM
  (SELECT TOP 2 *
FROM dbo.emp e
  WHERE  e.DEPTNO = @deptno
  ORDER BY e.SAL DESC)X
  ORDER BY X.SAL

--SELECT * FROM dbo.udf_SecondMaxSalPerDept(30)