How would I write these queries in an Oracle SQL. Table Structures Find the lowe
ID: 3908638 • Letter: H
Question
How would I write these queries in an Oracle SQL.
Table Structures
Find the lowest salary earned by any employee in each department. Display only those departments where the minimum basic salary is less than $8,000. Display both the department ID and highest salary. Find the total number of employees working in each department. Display only those departments that have a count greater than 3. 7. 8. 9. Display the HOD and number of employees working for each HOD. 10. Display the department name and average salary of all the employees in each department. Sort the output by average salary.Explanation / Answer
7.
SELECT DEPARTMENT_ID, min(SALARY)
FROM EMPLOYEE
WHERE min(SALARY)>8000
GROUP BY DEPARTMENT_ID ;
group by is used to display result department wise i.e. display lowest salary of each department
8.
SELECT DEPARTMENT_NAME, COUNT(*)
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID
GROUP BY DEPARTMENT_NAME
HAVING COUNT(*) >3 ;
Above query display number of employees working in each department.
Having clause is used to display department names whose count is greater than 3
9.
SELECT HOD , COUNT(*)
FROM EMPLOYEE
GROUP BY HOD ;
Above query is used to select HOD and number of employees working for it
10.
SELECT DEPARTMENT_NAME, AVG(SALARY)
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID
GROUP BY DEPARTMENT_NAME
ORDER BY AVG(SALARY) ASC ;
Above query returns average salary department wise.
order by is used to sort the result by average salary.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.