Computer Science
Show the average salary for all departments with more than 3 people for a job.
Table: Empl
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
|---|---|---|---|---|---|---|---|
| 8369 | SMITH | CLERK | 8902 | 1990-12-18 | 800 | NULL | 20 |
| 8499 | ANYA | SALESMAN | 8698 | 1991-02-20 | 1600 | 300 | 30 |
| 8521 | SETH | SALESMAN | 8698 | 1991-02-22 | 1250 | 500 | 30 |
| 8566 | MAHADEVAN | MANAGER | 8839 | 1991-04-02 | 2985 | NULL | 20 |
| 8654 | MOMIN | SALESMAN | 8698 | 1991-09-28 | 1250 | 1400 | 30 |
| 8698 | BINA | MANAGER | 8839 | 1991-05-01 | 2850 | NULL | 30 |
| 8839 | AMIR | PRESIDENT | NULL | 1991-11-18 | 5000 | NULL | 10 |
| 8844 | KULDEEP | SALESMAN | 8698 | 1991-09-08 | 1500 | 0 | 30 |
| 8882 | SHIAVNSH | MANAGER | 8839 | 1991-06-09 | 2450 | NULL | 10 |
| 8886 | ANOOP | CLERK | 8888 | 1993-01-12 | 1100 | NULL | 20 |
| 8888 | SCOTT | ANALYST | 8566 | 1992-12-09 | 3000 | NULL | 20 |
| 8900 | JATIN | CLERK | 8698 | 1991-12-03 | 950 | NULL | 30 |
| 8902 | FAKIR | ANALYST | 8566 | 1991-12-03 | 3000 | NULL | 20 |
| 8934 | MITA | CLERK | 8882 | 1992-01-23 | 1300 | NULL | 10 |
SQL Joins & Grouping
2 Likes
Answer
SELECT job, AVG(Sal) AS AvgSalary
FROM Empl
GROUP BY job HAVING COUNT(*) > 3;
Output
+----------+-----------+
| job | AvgSalary |
+----------+-----------+
| CLERK | 1037.5 |
| SALESMAN | 1400 |
+----------+-----------+
Answered By
1 Like
Related Questions
With reference to the table below, answer the questions that follow :
Table : Employees
Empid Firstname Lastname Address City 010 Ravi Kumar Raj nagar GZB 105 Harry Waltor Gandhi nagar GZB 152 Sam Tones 33 Elm St. Paris 215 Sarah Ackerman 440 U.S. 110 Upton 244 Manila Sengupta 24 Friends street New Delhi 300 Robert Samuel 9 Fifth Cross Washington 335 Ritu Tondon Shastri Nagar GZB 400 Rachel Lee 121 Harrison St. New York 441 Peter Thompson 11 Red Road Paris Table : EmpSalary
Empid Salary Benefits Designation 010 75000 15000 Manager 105 65000 15000 Manager 152 80000 25000 Director 215 75000 12500 Manager 244 50000 12000 Clerk 300 45000 10000 Clerk 335 40000 10000 Clerk 400 32000 7500 Salesman 441 28000 7500 Salesman Write the SQL commands for the following using above tables :
(i) To show firstname, lastname, address and city of all employees living in Pairs.
(ii) To display the content of Employees table in descending order of Firstname.
(iii) To display the firstname, lastname and total salary of all managers from the tables Employes and EmpSalary, where total salary is calculated as Salary + Benefits.
(iv) To display the maximum salary among managers and clerks from the table EmpSalary.
With reference to the table below, answer the questions that follow :
Table : Employees
Empid Firstname Lastname Address City 010 Ravi Kumar Raj nagar GZB 105 Harry Waltor Gandhi nagar GZB 152 Sam Tones 33 Elm St. Paris 215 Sarah Ackerman 440 U.S. 110 Upton 244 Manila Sengupta 24 Friends street New Delhi 300 Robert Samuel 9 Fifth Cross Washington 335 Ritu Tondon Shastri Nagar GZB 400 Rachel Lee 121 Harrison St. New York 441 Peter Thompson 11 Red Road Paris Table : EmpSalary
Empid Salary Benefits Designation 010 75000 15000 Manager 105 65000 15000 Manager 152 80000 25000 Director 215 75000 12500 Manager 244 50000 12000 Clerk 300 45000 10000 Clerk 335 40000 10000 Clerk 400 32000 7500 Salesman 441 28000 7500 Salesman Give the Output of following SQL commands :
(i) Select firstname, Salary from Employees, Empsalary where Designation = 'Salesman' and Employees.Empid = Empsalary.Empid ;
(ii) Select count(distinct designation) from EmpSalary ;
(iii) Select designation, sum(salary) from EmpSalary group by designation having count(*) > 2 ;
(iv) Select sum(Benefits) from EmpSalary where Designation = 'Clerk' ;
Display only the jobs with maximum salary greater than or equal to 3000.
Table: Empl
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 8369 SMITH CLERK 8902 1990-12-18 800 NULL 20 8499 ANYA SALESMAN 8698 1991-02-20 1600 300 30 8521 SETH SALESMAN 8698 1991-02-22 1250 500 30 8566 MAHADEVAN MANAGER 8839 1991-04-02 2985 NULL 20 8654 MOMIN SALESMAN 8698 1991-09-28 1250 1400 30 8698 BINA MANAGER 8839 1991-05-01 2850 NULL 30 8839 AMIR PRESIDENT NULL 1991-11-18 5000 NULL 10 8844 KULDEEP SALESMAN 8698 1991-09-08 1500 0 30 8882 SHIAVNSH MANAGER 8839 1991-06-09 2450 NULL 10 8886 ANOOP CLERK 8888 1993-01-12 1100 NULL 20 8888 SCOTT ANALYST 8566 1992-12-09 3000 NULL 20 8900 JATIN CLERK 8698 1991-12-03 950 NULL 30 8902 FAKIR ANALYST 8566 1991-12-03 3000 NULL 20 8934 MITA CLERK 8882 1992-01-23 1300 NULL 10 Find out number of employees having "Manager" as Job.