Teradata SQL Test – 2

We have an EmployeeSalary Table which holds information about Employee and the Salary which the Employee is getting in respective months. The table is holding the data for past six months. Below is the snapshot of the EMP_SAL table:

SNAPSHOT of EMP_SAL Table
SNAPSHOT of EMP_SAL Table

Now we need to write an SQL Query which can retrieve the name of EMPLOYEES who have received more than INR 120,000 in total as their SAL in last six months. The answer as per the snapshot of the table is RAJ

Can you write the correct query? Leave your SQL Query as comment below. There may be several ways of getting the desired output. You can check the answer on ANSWERS PAGE too. However for better understanding of Teradata SQL , it is suggested that you leave a comment below with your SQL Query and then check our answer. We may get better and more ways of getting the same result.

Hint: Try it via GROUP BY HAVING clause

You may also like...

16 Responses

  1. S Joseph says:

    SELECT emp_name, SUM(salary) FROM employee WHERE SAL_MONTH>MONTH(CURRENT_DATE)-6 GROUP BY emp_name HAVING SUM(SALARY) > 120000;

  2. vikas says:

    select t1.name ,sum(sal) from employee T1
    group by t1.name having sum(sal)>12000

  3. Singh says:

    sel emp_name from EMP_Sal QUALIFY sum(sal) over( partition by Emp_id order by sal desc)=1

  4. Surya says:

    SEL EMP_NAME
    FROM EMP_SAL
    GROUP BY EMP_NAME
    HAVING SUM(SAL) > 120000
    ;

  5. Niraj says:

    SEL EMP_NAME , SUM(SALARY) OVER (PARTITION BY EMP_NAME) AS EMP_SALARY
    FROM EMP_SAL
    QUALIFY EMP_SALARY>12000

  6. NANDHINI says:

    SEL EMPNO,ENAME,SAL FROM EMP_TABLE A WHERE (SEL SUM(SAL) FROM EMP_TABLE B WHERE A.EMPNO=B.EMPNO) > 12000

  7. Vivek.M says:

    sel emp_name from emp_sal
    having sum(sal)>120000
    group by 1

  8. Pooja says:

    select empname from (
    select empname,sum(salary) as sal from sand.emptest group by 1) a
    where sal >120000

  9. savy4dwh says:

    select
    emp_name
    ,sum(sal) as sal_till_date
    from
    employee
    group by emp_name
    having sal_till_date > 120000;

  10. santhosh says:

    SELECT DISTINCT EMP_NAME FROM EMP_SAL_GUJJA

    QUALIFY SUM(SAL) OVER(PARTITION BY EMP_ID)>’120000′

  11. kapil tiwari says:

    SEL DISTINCT EMP_NAME FROM EMP_SAL QUALIFY SUM(SAL) OVER (PARTITION BY EMP_ID ORDER BY SAL ASC ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)>120000;

  12. nanda says:

    SEL DISTINCT(EMP_NAME)
    FROM EMP_SAL
    WHERE EMP_ID IN ( SEL EMP_ID FROM EMP_SAL GROUP BY EMP_ID HAVING SUM(SAL) > 120000)

  13. Renu Singh says:

    This also can be done this way

    select sum(sal),Emp_Name
    from emp
    group by Emp_NAme
    having sum(sal) > 120000

  14. Raj says:

    Hi Himanshu

    Just a small correction in your query, use ‘IN’ instead of ‘=’:
    SEL DISTINCT(EMP_NAME) FROM EMP_SAL WHERE EMP_ID IN ( SEL EMP_ID FROM EMP_SAL GROUP BY EMP_ID HAVING SUM(SAL) > 120000)

  15. Himanshu Kumar says:

    This can also be done in this way :

    SEL DISTINCT(EMP_NAME) FROM EMP_SAL WHERE EMP_ID = ( SEL EMP_ID FROM EMP_SAL GROUP BY EMP_ID HAVING SUM(SAL) > 120000)

Leave a Reply

Your email address will not be published. Required fields are marked *