[go: up one dir, main page]
Scribd
Upload
70K views5 pages

Answers On Group by and Having Clause: Select Sum (Sal), Job From Emp Group by Job

This document contains 20 SQL queries using the GROUP BY and HAVING clauses to summarize and filter employee data from a database table. The queries group and count employees by job, hire date, department, salary and other fields to find aggregates, averages and records that meet certain conditions.

Uploaded by

Krishna Kishor
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
70K views5 pages

Answers On Group by and Having Clause: Select Sum (Sal), Job From Emp Group by Job

This document contains 20 SQL queries using the GROUP BY and HAVING clauses to summarize and filter employee data from a database table. The queries group and count employees by job, hire date, department, salary and other fields to find aggregates, averages and records that meet certain conditions.

Uploaded by

Krishna Kishor
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

ANSWERS ON GROUP BY AND HAVING CLAUSE

1. WRITE A QUERY TO DISPLAY TOTAL SALARY NEEDED TO PAY


EACH JOB IN EMPLOYEE TABLE.
SELECT SUM(SAL),JOB
FROM EMP
GROUP BY JOB;
2.WRITE A QUERY TO DISPLAY THE HIRE DATE ON WHICH AT LEAST 3
EMPLOYEES WHERE HIRED.
SELECT HIREDATE
FROM EMP
GROUP BY HIREDATE
HAVING COUNT(*)>=3;
3.WRITE A QUERY TO DISPLAY THE DEPARTMENT NUMBER WHICH
HAS MORE THAN 2 EMPLOYEES AND THE TOTAL AMOUNT
REQUIRED TO PAY THE MONTHLY SALARIES OF ALL THE
EMPLOYEES IN THAT DEPARTMENT SHOULD BE MORE THAN
9000.

SELECT DEPTNO,SUM(SAL)
FROM EMP
WHERE SAL>9000
GROUP BY DEPTNO
HAVING COUNT(*)>2;
4.WRITE A QUERY TO DISPLAY NUMBER OF EMPLOYEES WORKING IN
EACH DEPARTMENT AND ITS’ AVERAGE SALARY BY
EXCLUDING ALL THE EMPLOYEES WHOSE SALARY IS LESS
THAN THEIR COMMISSION.
SELECT COUNT(*),AVG(SAL)
FROM EMP
WHERE SAL<COMM
GROUP BY DEPTNO;
5.WRITE A QUERY TO DISPLAY THE SALARIES WHICH HAS
REPETITIONS IN THE SAL COLUMN OF EMPLOYEE TABLE.
SELECT COUNT(*),SAL
FROM EMP
GROUP BY SAL
HAVING COUNT(*)>=1;

6.WRITE A QUERY TO DISPLAY THE EMPLOYEE NAME ONLY IF MORE


THAN ONE PERSON IN THE EMPLOYEES OF THE COMPANY HAS
SAME NAME.
SELECT ENAME,COUNT(*)
FROM EMP
GROUP BY ENAME
HAVING COUNT(*)>1;
7.WRITE A QUERY TO DISPLAY THE DEPARTMENT NUMBER WHOSE
AVERAGE SALARY IS BETWEEN 2500 AND 3000.
SELECT DEPTNO,AVG(SAL)
FROM EMP
WHERE SAL BETWEEN 2500 AND 3000
GROUP BY DEPTNO;
8.WRITE A QUERY TO DISPLAY THE NUMBER OF EMPLOYEES ONLY IF
THEY ARE WORKING AS MANAGER OR ANALYST AND THEIR
ANNUAL SAL SHOULD END WITH A ZERO, IN EACH
DEPARTMENT.
SELECT COUNT(*),DEPTNO
FROM EMP
WHERE JOB IN('MANAGER','ANALYST') AND SAL*12 LIKE '%0'
GROUP BY DEPTNO;
9.WRITE A QUERY TO DISPLAY NO OF CLERKS WORKING IN EACH
DEPARTMENT.
SELECT COUNT(JOB),DEPTNO
FROM EMP
WHERE JOB='CLERK'
GROUP BY DEPTNO;
10.WRITE A QUERY TO DISPLAY HIGHEST SALARY GIVEN TO A
MANAGER IN EACH DEPARTMENT.
SELECT MAX(SAL),DEPTNO
FROM EMP
WHERE JOB='MANAGER'
GROUP BY DEPTNO;
11.WRITE A QUERY TO DISPLAY NO OF TIMES THE SALARIES HAVE
REPEATED IN THE EMP TABLE.
SELECT COUNT(SAL),SAL
FROM EMP
GROUP BY SAL
HAVING COUNT(*)>1;
12.WRITE A QUERY TO DISPLAY DEPTNO AND NUNBER OF
EMPLOYEES WORKING IN EACH DEPARTMENT EXCEPT FOR
THOSE WORKING IN DEPT 10
SELECT DEPTNO,COUNT(*)
FROM EMP
WHERE DEPTNO<>10
GROUP BY DEPTNO;
13.WAQTD NUMBER OF EMPLOYEES GETTING COMISSION IN EACH
DEPARTMENT
SELECT COUNT(*),DEPTNO
FROM EMP
WHERE COMM IS NOT NULL
GROUP BY DEPTNO;
14.WAQTD NUMBER OF EMPLOYEES GETTING SALARY MORE THAN
1600 EXCLUDING ALL THE MANAGERS IN EACH DEPARTEMNT
SELECT COUNT(*),DEPTNO
FROM EMP
WHERE SAL>1600 AND JOB<>'MANAGER'
GROUP BY DEPTNO;
15.WAQTD AVERAGE SALARY NEEDED TO PAY ALL THE EMPLOYEES
WHO ARE HAVING A REPORTING MANAGER IN EACH JOB.
SELECT AVG(SAL),JOB
FROM EMP
WHERE MGR IS NOT NULL
GROUP BY JOB;
16.WAQTD NUMBER OF EMPLOYEES HIRED INTO THE SAME
DEPARTMENT ON THE SAME DAY
SELECT COUNT(*),HIREDATE
FROM EMP
GROUP BY HIREDATE
HAVING COUNT(*)>1;
17.WAQTD NUMBER OF EMPLOYEES GETTING THE SAME SALARY,
WORKING IN THE SAME DEPARTMENT
SELECT COUNT(*),DEPTNO,SAL
FROM EMP
GROUP BY DEPTNO,SAL
HAVING COUNT(SAL)>1;
18.WAQTD MAXIMUM SALARY GIVEN IN EACH DESIGNATION
EXCLUDING THOSE WHOS NAME STARTS WITH ‘K’
SELECT MAX(SAL),JOB
FROM EMP
WHERE ENAME NOT LIKE'K%'
GROUP BY JOB;
19.WAQTD NUMBER OF EMPLOYEES REPORTING TO 7839
SELECT COUNT(*)
FROM EMP
WHERE MGR=7839;
20.WAQTD NUMBER OF EMPLOYEE NAMES STARTING WITH AN
VOWEL IN EACH DEPARTMENT
SELECT ENAME
FROM EMP
WHERE ENAME LIKE 'A%' OR ENAME LIKE 'E%' OR ENAME LIKE
'I%' OR ENAME LIKE 'O%' OR ENAME LIKE 'U%';

You might also like