SQL5.txt

SQL5 

1.WAQTD NUMBER OF EMPLOYEES GETTING SALARY LESS THAN 2000 IN DEPTΝΟ 10

SELECT EMPNO
FROM EMP
WHERE SAL<2000 AND DEPTNO=10;

2.WAQTD TOTAL SALARY NEEDED TO PAY EMPLOYEES WORKING AS CLERK

SELECT SUM(SAL)
FROM EMP
WHERE JOB='CLERK';

3.WAQTD AVERAGE SALARY NEEDED TO PAY ALL EMPLOYEES

SELECT AVG(SAL)
FROM EMP;

4.WAQTD NUMBER OF EMPLOYEES HAVING 'A' AS THEIR FIRST CHARACTER

SELECT ENAME
FROM EMP
WHERE ENAME=LIKE'A%';

5.WAQTD NUMBER OF EMPLOYEES WORKING AS CLERK OR MANAGER

SELECT ENAME
FROM EMP
WHERE JOB='CLERK' OR JOB='MANAGER';

6.WAQTD TOTAL SALARY NEEDED TO PAY EMPLOYEES HIRED IN FEB

SELECT SUM(SAL)
FROM EMP
WHERE TO_CHAR(HIREDATE,'MON')='FEB';

7.WAQTD NUMBER OF EMPLOYEES REPORTING TO 7839 (MGR)

SELECT EMPNO
FROM EMP
WHERE MGR=7839;

8.WAQTD NUMBER OF EMPLOYEES GETTING COMISSION IN DEPTNO 30

SELECT EMPNO
FROM EMP
WHERE DEPTNO=30 AND COMM IS NOT NULL;

9.WAQTD AVG SAL, TOTAL SAL, NUMBER OF EMPS AND MAXIMUM SALARY GIVEN TO EMPLOYEES WORKING AS PERSIDENT

SELECT AVG(SAL),SUM(SAL),MAX(SAL),COUNT(*)
FROM EMP
WHERE JOB='PRESIDENT';

10. WAQTD NUMBER OF EMPLOYEES HAVING 'A' IN THEIR NAMES

SELECT EMPNO,ENAME
FROM EMP
WHERE ENAME LIKE'%A%';

11.WAQTD NUMBER OF EMPS AND TOTAL SALARY NEEDED TO PAY THE EMPLOYEES WHO HAVE 2 CONSICUTIVE L's IN THEIR NAMES

SELECT COUNT(*),SUM(SAL)
FROM EMP
WHERE ENAME LIKE'%LL%';

12.WAQTD NUMBER OF DEPARTMENTS PRESENT IN EMPLOYEE TABLE

SELECT COUNT(DEPTNO)
FROM EMP;

13.WAQTD NUMBER OF EMPLOYEES HAVING CHARACTER 'Z' IN THEIR NAMES

SELECT EMPNO
FROM EMP
WHERE ENAME LIKE'%Z%';

14. WAQTD NUMBER OF EMPLOYEES HAVING '$' IN THEIR NAMES.

SELECT EMPNO
FROM EMP
WHERE ENAME LIKE'%$%';

15.WAQTD TOTAL SALARY GIVEN TO EMPLOYEES WORKING AS CLERK IN DEPT 30

SELECT SUM(SAL)
FROM EMP
WHERE JOB='CLERK' AND DEPTNO=30;

16.WAQTD MAXIMUM SALARY GIVEN TO THE EMPLOYEES WORKING AS ANALYST

SELECT MAX(SAL)
FROM EMP
WHERE JOB='ANALYST';

17.WAQTD NUMBER OF DISTINCT SALARIES PRESENT IN EMPLOYEE TABLE

SELECT COUNT(DISTINCT SAL)
FROM EMP;

18. WAQTD NUMBER OF JOBS PRESENT IN EMPLOYEE TABLE

SELECT COUNT(DISTINCT JOB)
FROM EMP;

19.WATQD AVG SALARY GIVEN TO THE CLERK

SELECT AVG(SAL)
FROM EMP
WHERE JOB='CLERK';

20.WAQTD MINIMUM SALARY GIVEN TO THE EMPLOYEES WHO WORK IN DEPT 10 AS MANAGER OR A CLERK

SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO=10 AND (JOB='MANAGER' OR JOB='CLERK');

			OR

SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO=10 AND JOB IN('MANAGER','CLERK');