[go: up one dir, main page]
Scribd
Upload
4K views3 pages

Group by and Having Clause Assignment

This document provides 20 SQL queries using the GROUP BY and HAVING clauses to analyze and summarize data from an EMPLOYEE table. The queries return aggregated results like total salary by job, hire dates with more than 3 employees, departments with over 2 employees and total salary over 9000, counts and averages by department excluding employees where salary is less than commission, repeated salaries, employees with the same name, averages between 2500-3000 by department, managers and analysts with annual salary ending in zero by department, clerks by department, highest manager salary by department, repeated salaries, employees by department excluding department 10, employees getting commission by department, employees making over 1600 excluding managers by department, average salary by job for employees with a manager,

Uploaded by

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

Group by and Having Clause Assignment

This document provides 20 SQL queries using the GROUP BY and HAVING clauses to analyze and summarize data from an EMPLOYEE table. The queries return aggregated results like total salary by job, hire dates with more than 3 employees, departments with over 2 employees and total salary over 9000, counts and averages by department excluding employees where salary is less than commission, repeated salaries, employees with the same name, averages between 2500-3000 by department, managers and analysts with annual salary ending in zero by department, clerks by department, highest manager salary by department, repeated salaries, employees by department excluding department 10, employees getting commission by department, employees making over 1600 excluding managers by department, average salary by job for employees with a manager,

Uploaded by

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

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 NOT IN(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 NOT IN('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