1-20: Basic Aggregates
-- 1. Count all rows in a table
SELECT COUNT(*) FROM employees;
-- 2. Count distinct department IDs
SELECT COUNT(DISTINCT department_id) FROM employees;
-- 3. Sum salaries in employees table
SELECT SUM(salary) FROM employees;
-- 4. Average salary of employees
SELECT AVG(salary) FROM employees;
-- 5. Minimum salary in employees
SELECT MIN(salary) FROM employees;
-- 6. Maximum salary in employees
SELECT MAX(salary) FROM employees;
-- 7. Count employees per department (GROUP BY)
SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id;
-- 8. Sum salary by department
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;
-- 9. Average salary by department
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
-- 10. Find max salary by job_id
SELECT job_id, MAX(salary) AS max_salary
FROM employees
GROUP BY job_id;
-- 11. Count employees having salary > 5000
SELECT COUNT(*) FROM employees WHERE salary > 5000;
-- 12. Count distinct jobs in the company
SELECT COUNT(DISTINCT job_id) FROM employees;
-- 13. Sum of salaries for employees hired after 2020-01-01
SELECT SUM(salary)
FROM employees
WHERE hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD');
-- 14. Average salary excluding NULLs (default behavior)
SELECT AVG(commission_pct) FROM employees;
-- 15. Min and Max salary in one query
SELECT MIN(salary) AS min_sal, MAX(salary) AS max_sal FROM employees;
-- 16. Count employees per department with alias
SELECT department_id AS dept_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id;
-- 17. Sum of salary and count in one query grouped by department
SELECT department_id, COUNT(*) AS emp_count, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;
-- 18. Count employees grouped by department and job
SELECT department_id, job_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id, job_id;
-- 19. Average salary rounded to 2 decimals by department
SELECT department_id, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department_id;
-- 20. Using HAVING to filter groups with more than 5 employees
SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;
21-40: Filtering & Conditional Aggregates
-- 21. Count employees with salary > 5000 grouped by department
SELECT department_id, COUNT(*) AS emp_count
FROM employees
WHERE salary > 5000
GROUP BY department_id;
-- 22. Sum salaries for employees with commission_pct IS NOT NULL
SELECT SUM(salary)
FROM employees
WHERE commission_pct IS NOT NULL;
-- 23. Count distinct job_id per department
SELECT department_id, COUNT(DISTINCT job_id) AS unique_jobs
FROM employees
GROUP BY department_id;
-- 24. Count employees hired each year
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year, COUNT(*)
FROM employees
GROUP BY EXTRACT(YEAR FROM hire_date);
-- 25. Average salary of employees hired after 2015, grouped by job_id
SELECT job_id, AVG(salary)
FROM employees
WHERE hire_date > TO_DATE('2015-01-01', 'YYYY-MM-DD')
GROUP BY job_id;
-- 26. Sum salaries for each department with salary > 3000
SELECT department_id, SUM(salary)
FROM employees
WHERE salary > 3000
GROUP BY department_id;
-- 27. Employees count per job_id having avg salary > 6000
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id
HAVING AVG(salary) > 6000;
-- 28. Min salary per department excluding NULL salaries
SELECT department_id, MIN(salary)
FROM employees
WHERE salary IS NOT NULL
GROUP BY department_id;
-- 29. Count employees whose commission_pct is NULL grouped by department
SELECT department_id, COUNT(*)
FROM employees
WHERE commission_pct IS NULL
GROUP BY department_id;
-- 30. Sum salaries for employees with salary between 4000 and 8000
SELECT SUM(salary) FROM employees WHERE salary BETWEEN 4000 AND 8000;
-- 31. Count employees per manager_id
SELECT manager_id, COUNT(*)
FROM employees
GROUP BY manager_id;
-- 32. Average commission_pct grouped by department
SELECT department_id, AVG(commission_pct)
FROM employees
GROUP BY department_id;
-- 33. Employees count for departments having more than 2 jobs
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(DISTINCT job_id) > 2;
-- 34. Sum salaries excluding those with NULL salary
SELECT SUM(salary) FROM employees WHERE salary IS NOT NULL;
-- 35. Count employees with salary >= average salary of entire company
SELECT COUNT(*) FROM employees WHERE salary >= (SELECT AVG(salary) FROM employees);
-- 36. Average salary of employees grouped by department and job
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id;
-- 37. Max salary per department and job
SELECT department_id, job_id, MAX(salary)
FROM employees
GROUP BY department_id, job_id;
-- 38. Min salary per department having more than 3 employees
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 3;
-- 39. Count employees with commission_pct > 0.2 grouped by department
SELECT department_id, COUNT(*)
FROM employees
WHERE commission_pct > 0.2
GROUP BY department_id;
-- 40. Average salary of employees hired in each month
SELECT TO_CHAR(hire_date, 'MM') AS hire_month, AVG(salary)
FROM employees
GROUP BY TO_CHAR(hire_date, 'MM');
41-60: Aggregate Functions with Window Functions (OVER)
-- 41. Count employees in the entire table using window function
SELECT employee_id, COUNT(*) OVER () AS total_employees FROM employees;
-- 42. Running total of salaries ordered by hire_date
SELECT employee_id, hire_date, salary,
SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employees;
-- 43. Average salary per department repeated on each row
SELECT employee_id, department_id, salary,
AVG(salary) OVER (PARTITION BY department_id) AS avg_dept_salary
FROM employees;
-- 44. Max salary over entire table repeated on each row
SELECT employee_id, salary,
MAX(salary) OVER () AS max_salary_overall
FROM employees;
-- 45. Rank employees by salary within each department
SELECT employee_id, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;
-- 46. Count employees hired each year with window function
SELECT employee_id, hire_date,
COUNT(*) OVER (PARTITION BY EXTRACT(YEAR FROM hire_date)) AS hired_in_year
FROM employees;
-- 47. Sum salary per department with window function
SELECT employee_id, department_id, salary,
SUM(salary) OVER (PARTITION BY department_id) AS sum_dept_salary
FROM employees;
-- 48. Difference between salary and average salary in department
SELECT employee_id, department_id, salary,
salary - AVG(salary) OVER (PARTITION BY department_id) AS diff_from_avg
FROM employees;
-- 49. Moving average salary for last 3 hires ordered by hire_date
SELECT employee_id, hire_date, salary,
AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;
-- 50. First and last salary in each department using window function
SELECT employee_id, department_id, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS first_sal,
LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sal
FROM employees;
-- 51. Percent rank of employees by salary
SELECT employee_id, salary,
PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank
FROM employees;
-- 52. NTILE to distribute employees into 4 salary quartiles
SELECT employee_id, salary,
NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM employees;
-- 53. Count employees per manager with window function
SELECT employee_id, manager_id,
COUNT(*) OVER (PARTITION BY manager_id) AS team_size
FROM employees;
-- 54. Cumulative count of employees by hire_date
SELECT employee_id, hire_date,
COUNT(*) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_hires
FROM employees;
-- 55. Sum salary with sliding window of 5 rows ordered by hire_date
SELECT employee_id, hire_date, salary,
SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS sliding_sum
FROM employees;
-- 56. Average salary by job, partitioned and ordered by hire_date
SELECT employee_id, job_id, salary, hire_date,
AVG(salary) OVER (PARTITION BY job_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS avg_by_job
FROM employees;
-- 57. Row number per department ordered by salary descending
SELECT employee_id, department_id, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees;
-- 58. Use window function to find difference between current and previous salary
SELECT employee_id, salary, hire_date,
salary - LAG(salary) OVER (ORDER BY hire_date) AS diff_prev_salary
FROM employees;
-- 59. Use LEAD to find next employee's salary by hire_date
SELECT employee_id, salary, hire_date,
LEAD(salary) OVER (ORDER BY hire_date) AS next_salary
FROM employees;
-- 60. Use window function with RANGE instead of ROWS
SELECT employee_id, salary, hire_date,
SUM(salary) OVER (ORDER BY hire_date RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW) AS sum_last_30_days
FROM employees;
61-80: Aggregates with GROUPING SETS, ROLLUP, CUBE
-- 61. Grouping sets example: group by department and job, department only, job only, and total
SELECT department_id, job_id, COUNT(*) AS emp_count, SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS ((department_id, job_id), (department_id), (job_id), ());
-- 62. Using ROLLUP for hierarchical totals (department, job)
SELECT department_id, job_id, COUNT(*) AS emp_count, SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department_id, job_id);
-- 63. Using CUBE for all combinations of grouping columns
SELECT department_id, job_id, COUNT(*) AS emp_count, SUM(salary) AS total_salary
FROM employees
GROUP BY CUBE(department_id, job_id);
-- 64. Grouping function to distinguish subtotal and grand total rows
SELECT department_id, job_id,
GROUPING(department_id) AS dept_grp,
GROUPING(job_id) AS job_grp,
COUNT(*) AS emp_count
FROM employees
GROUP BY ROLLUP(department_id, job_id);
-- 65. Count employees by department, including NULL departments as separate group
SELECT NVL(TO_CHAR(department_id), 'No Dept') AS department, COUNT(*)
FROM employees
GROUP BY ROLLUP(department_id);
-- 66. Sum salary with ROLLUP and filter subtotals using HAVING
SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department_id, job_id)
HAVING GROUPING(department_id) = 0;
-- 67. Grouping sets with multiple columns and filtering
SELECT department_id, job_id, manager_id, COUNT(*)
FROM employees
GROUP BY GROUPING SETS ((department_id, job_id), (manager_id));
-- 68. Rollup on multiple columns with ordering
SELECT department_id, job_id, manager_id, COUNT(*)
FROM employees
GROUP BY ROLLUP(department_id, job_id, manager_id)
ORDER BY department_id NULLS LAST, job_id NULLS LAST;
-- 69. Cube with 3 columns for all combinations
SELECT department_id, job_id, manager_id, COUNT(*)
FROM employees
GROUP BY CUBE(department_id, job_id, manager_id);
-- 70. Using GROUPING_ID to identify group level
SELECT department_id, job_id, GROUPING_ID(department_id, job_id) AS grp_id, COUNT(*)
FROM employees
GROUP BY ROLLUP(department_id, job_id);
81-100: Advanced & Miscellaneous Aggregate Queries
-- 81. Median salary per department using MEDIAN()
SELECT department_id, MEDIAN(salary) AS median_salary
FROM employees
GROUP BY department_id;
-- 82. Variance and standard deviation of salary per department
SELECT department_id, VARIANCE(salary) AS var_salary, STDDEV(salary) AS stddev_salary
FROM employees
GROUP BY department_id;
-- 83. Correlation between salary and commission_pct
SELECT CORR(salary, commission_pct) AS corr_salary_commission FROM employees;
-- 84. Covariance between salary and commission_pct
SELECT COVAR_POP(salary, commission_pct) AS covar_pop, COVAR_SAMP(salary, commission_pct) AS covar_samp FROM employees;
-- 85. Group_concat equivalent using LISTAGG for employee names per department
SELECT department_id, LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) AS emp_names
FROM employees
GROUP BY department_id;
-- 86. Count employees and total salary with FILTER clause (Oracle 19c+)
SELECT COUNT(*) FILTER (WHERE salary > 5000) AS count_high_sal,
SUM(salary) FILTER (WHERE salary > 5000) AS sum_high_sal
FROM employees;
-- 87. Use aggregate inside CASE expression
SELECT department_id,
SUM(CASE WHEN salary > 5000 THEN 1 ELSE 0 END) AS high_salary_count
FROM employees
GROUP BY department_id;
-- 88. Approximate count distinct using APPROX_COUNT_DISTINCT (Oracle 12c+)
SELECT APPROX_COUNT_DISTINCT(job_id) FROM employees;
-- 89. Average salary excluding outliers using PERCENTILE_CONT
SELECT department_id,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees
GROUP BY department_id;
-- 90. Use XMLAGG to aggregate text data
SELECT department_id,
RTRIM(XMLAGG(XMLELEMENT(e, employee_name || ', ') ORDER BY employee_name).EXTRACT('//text()'), ', ') AS emp_list
FROM employees
GROUP BY department_id;
-- 91. Use COLLECT to aggregate objects
SELECT department_id, CAST(COLLECT(employee_name) AS SYS.ODCIVARCHAR2LIST) AS emp_names
FROM employees
GROUP BY department_id;
-- 92. Calculate weighted average salary (weight by commission_pct)
SELECT department_id,
SUM(salary * NVL(commission_pct, 0)) / SUM(NVL(commission_pct, 1)) AS weighted_avg_salary
FROM employees
GROUP BY department_id;
-- 93. Calculate cumulative distribution of salary
SELECT employee_id, salary,
CUME_DIST() OVER (ORDER BY salary) AS cum_dist
FROM employees;
-- 94. Use RATIO_TO_REPORT to get percentage of total salary
SELECT employee_id, salary,
RATIO_TO_REPORT(salary) OVER () AS salary_pct
FROM employees;
-- 95. Use aggregate with GROUPING sets for hierarchical data rollup
SELECT department_id, job_id, COUNT(*)
FROM employees
GROUP BY GROUPING SETS ((department_id, job_id), (department_id), ());
-- 96. Aggregate with FILTER for conditional sums (Oracle 19c+)
SELECT department_id,
SUM(salary) FILTER (WHERE job_id = 'SA_REP') AS sum_sales_reps,
SUM(salary) FILTER (WHERE job_id != 'SA_REP') AS sum_others
FROM employees
GROUP BY department_id;
-- 97. Use aggregate in recursive queries
WITH emp_hierarchy AS (
SELECT employee_id, manager_id, 1 AS lvl FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, eh.lvl + 1
FROM employees e JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT lvl, COUNT(*) FROM emp_hierarchy GROUP BY lvl ORDER BY lvl;
-- 98. Aggregate distinct count of salary brackets per department
SELECT department_id,
COUNT(DISTINCT CASE
WHEN salary < 3000 THEN 'Low'
WHEN salary BETWEEN 3000 AND 7000 THEN 'Medium'
ELSE 'High'
END) AS distinct_brackets
FROM employees
GROUP BY department_id;
-- 99. Use LISTAGG with ON OVERFLOW TRUNCATE
SELECT department_id,
LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) ON OVERFLOW TRUNCATE
FROM employees
GROUP BY department_id;
-- 100. Use approximate top-N aggregate with TOP-N function (Oracle 19c+)
SELECT department_id,
TOP_N(3, salary) WITHIN GROUP (ORDER BY salary DESC) AS top_3_salaries
FROM employees
GROUP BY department_id;
No comments:
Post a Comment