SQL-Quiz-8

SQL Quiz 8

1. In EMP table, print number of employee and first hire date each job as example in the below.

SELECT job, COUNT(job), MIN(hiredate)
  FROM emp
 GROUP BY job;

2. In EMP table, print number of employee each hire date as example in the below.

SELECT TO_CHAR(hiredate, 'YYYY-MM') AS hiredate,
       COUNT(TO_CHAR(hiredate, 'YYYY-MM')) AS CNT
  FROM emp
 GROUP BY TO_CHAR(hiredate, 'YYYY-MM')

3. In EMP table, print number of more than 2 employee by date of employee as example in the below.

SELECT TO_CHAR(hiredate, 'YYYY-MM') AS HIREDATE,
       COUNT(TO_CHAR(hiredate, 'YYYY-MM')) AS CNT
  FROM emp
 GROUP BY TO_CHAR(hiredate, 'YYYY-MM')
HAVING COUNT(TO_CHAR(hiredate, 'YYYY-MM')) >= 2

4. Print number of employee and sum of salary as example in the below. (Print ‘NO DEPT’ if employee who has not department)

SELECT CASE
        WHEN d.dname = '' THEN 'NO DEPT'
        ELSE d.dname
       END AS DANME,
       COUNT(e.deptno) AS CNT,
       SUM(e.sal) AS SAL
  FROM emp e
 INNER JOIN dept d
    ON e.deptno = d. deptno
 GROUP BY d.dname