Database-beginner/group_fuction.sql at master · 0732sta/Database-beginner · 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
select min(salary),max(salary),sum(salary)
from employees;

select min(hire_date),max(hire_date)
from employees;

select AVG(nvl(salary,0))
from employees;

select count(commission_pct)
from employees;

select count(distinct job_id)
from employees;

select AVG(salary)
from employees;

select AVG(salary)
from employees
where department_id=80;

select department_id,AVG(salary)
from employees
group by department_id;

select department_id,Count(job_id)
from employees
group by department_id;

select department_id,sum(salary)
from employees
where department_id in ('50','100','40')
group by department_id
Order by 2;

select department_id,job_id,sum(salary)
from employees
where department_id in ('50','100','40')
group by department_id,job_id
Order by 2;

select department_id,job_id,avg(salary)
from employees
where department_id in ('50','100','40')
having avg(salary)>7000
group by department_id,job_id
Order by 2;

select department_id,sum(salary)
from employees
having sum(salary)>30000
group by department_id
Order by 2;

select department_id,min(salary)
from employees
having max(salary)>3000
group by department_id
Order by 2;

select department_id,job_id,avg(salary)
from employees
where job_id like '%N_'
having avg(salary)>7000
group by department_id,job_id
Order by 2;

select max(sum(salary))
from employees
group by department_id;

select department_id,avg(salary)
from employees
group by department_id
having avg(salary)=
                    (select max(avg(salary))
                    from employees
                    group by department_id);