Assignment 5 (Aggregate Functions)

divya sachan

@divyasachan

Created

Oct 31, 2025

Last Modified

4 months ago

Assignment 5

✅ ASSIGNMENT 5 — Oracle Aggregate (Group) Functions

Topic: Using aggregate functions like AVG, SUM, MAX, MIN, COUNT with GROUP BY and HAVING.

📘 NOTES – Quick Revision

Function	Description	Example
AVG(expr)	Returns average of values	`AVG(salary)`
SUM(expr)	Returns total of values	`SUM(salary)`
MIN(expr)	Returns smallest value	`MIN(salary)`
MAX(expr)	Returns largest value	`MAX(salary)`
COUNT(expr)	Counts rows (non-null values)	`COUNT(commission_pct)`
*COUNT()**	Counts all rows	`COUNT(*)`

🔹 GROUP BY – Groups rows having the same column values.
🔹 HAVING – Filters groups after aggregation (like WHERE for groups).

🧠 All Queries in ONE BLOCK (With Expected Output Format)

sql

-- 1. Minimum, Maximum, Average salary of employees
SELECT MIN(salary) AS "MIN_SALARY",
       MAX(salary) AS "MAX_SALARY",
       AVG(salary) AS "AVG_SALARY"
FROM employees;

-- Expected Output:
-- MIN_SALARY | MAX_SALARY | AVG_SALARY
-- 2000        | 24000      | 6461.83


-- 2. How many job titles available in employee table
SELECT COUNT(DISTINCT job_id) AS "JOB_TITLES"
FROM employees;

-- Expected Output:
-- JOB_TITLES
-- 19


-- 3. Difference between maximum and minimum salaries
SELECT (MAX(salary) - MIN(salary)) AS "SALARY_DIFFERENCE"
FROM employees;

-- Expected Output:
-- SALARY_DIFFERENCE
-- 22000


-- 4. Total amount company spends on salaries
SELECT SUM(salary) AS "TOTAL_SALARY_EXPENSE"
FROM employees;

-- Expected Output:
-- TOTAL_SALARY_EXPENSE
-- 691400


-- 5. Average salary of all employees
SELECT AVG(salary) AS "AVERAGE_SALARY"
FROM employees;

-- Expected Output:
-- AVERAGE_SALARY
-- 6461.83


-- 6. Count total records in employee table
SELECT COUNT(*) AS "TOTAL_RECORDS"
FROM employees;

-- Expected Output:
-- TOTAL_RECORDS
-- 107


-- 7. Number of employees in each department
SELECT department_id,
       COUNT(employee_id) AS "NO_OF_EMPLOYEES"
FROM employees
GROUP BY department_id
ORDER BY department_id;

-- Expected Output:
-- DEPARTMENT_ID | NO_OF_EMPLOYEES
-- 10            | 1
-- 20            | 2
-- 30            | 6


-- 8. Maximum and minimum salary for each department
SELECT department_id,
       MAX(salary) AS "MAX_SAL",
       MIN(salary) AS "MIN_SAL"
FROM employees
GROUP BY department_id;

-- Expected Output:
-- DEPARTMENT_ID | MAX_SAL | MIN_SAL
-- 10            | 4400    | 4400
-- 30            | 9400    | 2500


-- 9. Job where number of employees is less than 3
SELECT job_id,
       COUNT(*) AS "EMP_COUNT"
FROM employees
GROUP BY job_id
HAVING COUNT(*) < 3;

-- Expected Output:
-- JOB_ID        | EMP_COUNT
-- AD_PRES       | 1
-- SA_MAN        | 2


-- 10. Department ID & avg salary where max salary > 10000
SELECT department_id,
       AVG(salary) AS "AVG_SALARY"
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

-- Expected Output:
-- DEPARTMENT_ID | AVG_SALARY
-- 90            | 19333.33
-- 100           | 8600.00


-- 11. Job ID & total monthly salary, total > 13000 and jobid not containing MAN
SELECT job_id,
       SUM(salary) AS "TOTAL_SALARY"
FROM employees
WHERE job_id NOT LIKE '%MAN%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY TOTAL_SALARY;

-- Expected Output:
-- JOB_ID     | TOTAL_SALARY
-- SA_REP     | 20000
-- AD_VP      | 34000