ข้อ 1

select count(department_id)
from hr.employees
where department_id = 50 or department_id =80 and commission_pct is null

ข้อ 2

select job_id,count(employee_id)
from hr.employees
where department_id = 100
group by job_id

ข้อ 3

select 'Average salary of ' || lower(job_id) ||q'['s]' || LPAD(round(avg(salary*10),-3),10,' ') || ' Bath' as "Average Salary of IT"
from hr.employees
group by job_id

ข้อ 4

select manager_id as Manager ,count(employee_id) as "Number of Supervisee"
from hr.employees
where employee_id > 5
group by manager_id
order by count(employee_id) desc

ข้อ 5

select department_id,job_id,sum(salary)
from hr.employees
where department_id is not null
group by department_id,job_id
order by department_id asc , sum(salary) desc

ข้อ 6

select last_name,hire_date,round((sysdate-hire_date)/365,0) as "Work(year)",salary,salary+salary*0.02,job_id
from hr.employees
where round((sysdate-hire_date)/365,0) > 5 and job_id like 'IT_PROG'

ข้อ 7

select country_id,count(city)
from hr.locations
where state_province is not null
group by country_id
having count(city) >=2