Scribd
Upload

SQL Assignment by U. Diwakar

255 views3 pages
This document contains an SQL assignment submitted by U. Diwakar for an online SQL batch. It includes 5 queries with results showing employee data like name counts by job, department statist…

Uploaded by

DIWAKAR U
  • SQL Assignment Introduction
  • Query 5: Count Employees with 'A'
  • Query 6: Count and Average Salary
  • Query 7: Salesman Count and Salary Total
  • Query 8: Maximum Salaries by Job

SQL ASSIGNMENT

NAME : [Link] BCA


BATCH : ONLINE (3PM)
SUBMITTED TO : MOHAMMED MOYN RAZVI SIR
DATE : 03 – 08 – 2021

[Link] NUMBER OF EMPLOYEES HAVING CHARACTER 'A'


IN THEIR NAMES IN EACH JOB

SELECT JOB,COUNT(*)
FROM EMP
WHERE ENAME LIKE '%A%'
GROUP BY JOB

JOB COUNT(*)
------------- -------------
SALESMAN 3
CLERK 2
MANAGER 2

1
[Link] NUMBER OF EMPLOYEES AND AVG SALARY
NEEDED TO PAY THE EMPLOYEES WHO'S SALARY IS
GREATER THAN 2000 IN EACH DEPARTMENT

SELECT DEPTNO,COUNT(*),AVG(SAL)
FROM EMP
WHERE SAL > 2000
GROUP BY DEPTNO;

DEPTNO COUNT(*) AVG(SAL)


---------- ---------- ----------
30 1 2850
20 3 2991.66667
10 2 3725

[Link] NUMBER OF EMPLOYEES AND TOTAL SALARY


GIVEN TO ALL THE SALESMAN IN EACH DEPT

SELECT DEPTNO,COUNT(*),SUM(SAL)
FROM EMP
WHERE JOB = 'SALESMAN'
GROUP BY DEPTNO;

2
DEPTNO COUNT(*) SUM(SAL)
----------- ------------ -------------
30 4 5600

[Link] NUMBER OF EMPLOYEES WITH THEIR MAXIMUM


SALARIES IN EACH JOB

SELECT JOB,COUNT(*),MAX(SAL)
FROM EMP
GROUP BY JOB;

JOB COUNT(*) MAX(SAL)


--------- ---------- -----------
CLERK 4 1300
SALESMAN 4 1600
PRESIDENT 1 5000
MANAGER 3 2975
ANALYST 2 3000

1 SQL ASSIGNMENT NAME : U.DIWAKAR BCA BATCH : ONLINE (3PM) SUBMITTED TO : MOHAMMED MOYN RAZVI SIR DATE : 03 – 08 – 202
2 6.WAQTD NUMBER OF EMPLOYEES AND AVG SALARY NEEDED TO PAY THE EMPLOYEES WHO'S SALARY IS GREATER THAN 2000 IN EACH DEPA
3 DEPTNO COUNT(*) SUM(SAL) ----------- ------------ ------------- 30 4

You might also like