SQL-Practice-Queries/04_Aggregate_Functions.sql at main · Pranjalitech26/SQL-Practice-Queries · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
-- ===========================================
-- Aggregate Functions
-- ===========================================


-- ===========================================
-- COUNT()
-- ===========================================

-- Query 1 - Count Total Employees

SELECT COUNT(*) AS Total_Employees
FROM employees;


-- ===========================================
-- SUM()
-- ===========================================

-- Query 2 - Calculate Total Salary

SELECT SUM(salary) AS Total_Salary
FROM employees;


-- ===========================================
-- AVG()
-- ===========================================

-- Query 3 - Calculate Average Salary

SELECT AVG(salary) AS Average_Salary
FROM employees;


-- ===========================================
-- MIN()
-- ===========================================

-- Query 4 - Find Minimum Salary

SELECT MIN(salary) AS Minimum_Salary
FROM employees;


-- ===========================================
-- MAX()
-- ===========================================

-- Query 5 - Find Maximum Salary

SELECT MAX(salary) AS Maximum_Salary
FROM employees;


-- ===========================================
-- GROUP BY
-- ===========================================

-- Query 6 - Count Employees in Each Department

SELECT
    department_id,
    COUNT(*) AS Total_Employees
FROM employees
GROUP BY department_id;


-- Query 7 - Calculate Total Salary by Department

SELECT
    department_id,
    SUM(salary) AS Total_Salary
FROM employees
GROUP BY department_id;


-- Query 8 - Calculate Average Salary by Department

SELECT
    department_id,
    AVG(salary) AS Average_Salary
FROM employees
GROUP BY department_id;


-- ===========================================
-- HAVING
-- ===========================================

-- Query 9 - Departments with More Than One Employee

SELECT
    department_id,
    COUNT(*) AS Total_Employees
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 1;


-- Query 10 - Departments with Average Salary Greater Than 50000

SELECT
    department_id,
    AVG(salary) AS Average_Salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;