SQL Average Funcion

select avg(salary) as average_salary
from simpledb.employee;

select avg(salary) avg_manager_salary
from simpledb.employee
where job='Manager';

select avg(commission)
from simpledb.employee;

https://www.youtube.com/watch?v=9DLG0GBo0WE&list=PLD20298E653A970F8&index=13

SQL Count Function

select count(*)
from simpledb.employee;

select count(*)
from simpledb.employee
where job='Clerk';

select count(distinct job)
from simpledb.employee;

select count(commission)
from simpledb.employee;

https://www.youtube.com/watch?v=yY0Go4a4IZU&list=PLD20298E653A970F8&index=14

SQL Max Function

select max(salary)
from simpledb.employee;

select max(salary)
from simpledb.employee
where job='Clerk';

select max(salary)
from simpledb.employee
where job='Salesman' or job='Manager';

https://www.youtube.com/watch?v=TC4h5XN20-o&list=PLD20298E653A970F8&index=15

SQL Min Function

select min(salary)
from simpledb.employee;

select min(salary)
from simpledb.employee
where job='manager';

select min(salary)
from simpledb.employee
where job='manager' or job='Clerk';

select min(commission)
from simpledb.employee
where commission <> 0;

https://www.youtube.com/watch?v=VKJvhwrjU9U&list=PLD20298E653A970F8&index=16

SQL Sum Function

select sum(salary)
from simpledb.employee;

select sum(salary)
from simpledb.employee
where job='Manager';

select sum(salary) sum_salary, count(salary) count_salary, sum(salary)/count(salary), avg(salary)
from simpledb.employee;

https://www.youtube.com/watch?v=pI_n_RPcsnM&list=PLD20298E653A970F8&index=17

SQL Group By

select sum(salary)
from simpledb.employee;

select job, sum(salary)
from simpledb.employee
group by job;

select job, sum(salary), min(salary), max(salary), count(salary)
from simpledb.employee
group by job;

select salary, count(employee_name)
from simpledb.employee

https://www.youtube.com/watch?v=qpF7Y2fGTrE&list=PLD20298E653A970F8&index=18

SQL Having

Where 와 Having의 차이점

select job, sum(salary)
from simpledb.employee
group by job
having sum(salary) > 5000;

https://www.youtube.com/watch?v=9VnaNTBkdXQ&list=PLD20298E653A970F8&index=19

SQL Alias

select employee_name, job, salary+commission
from simpledb.employee;

select employee_name, job, salary+commission as total_salary
from simpledb.employee;

 

isnull이 아닌 ifnull이네요

select employee_name, job, salary+ifnull(commission, 0) as total_salary
from simpledb.employee;

select job, sum(salary) as salary_sum
from simpledb.employee
group by job;

https://www.youtube.com/watch?v=pk3EzzXpb9Q&list=PLD20298E653A970F8&index=20

SQL As Statement

select e1.*, sum(e1.salary)
from simpledb.employee as e1
group by e1.job;

https://www.youtube.com/watch?v=SJaZ5tcmfPk&list=PLD20298E653A970F8&index=21