--dang nhap vao user hr va grant select tren cac bang

--cau II.1

--hien thi mo ta bang
desc employees;
---...


--cau II.2
grant select on countries to may01nga;
grant select on departments to may01nga;
grant select on employees to may01nga;
grant select on job_history to may01nga;
grant select on jobs to may01nga;
grant select on locations to may01nga;
grant select on regions to may01nga;

commit;



--dang nhap lai user da tao ra

--cau III.1
select * from hr.employees


--cau III.2
select hr.employees.first_name||' '||hr.employees.last_name as "Full name"
from hr.employees


--cau III.3
select hr.employees.first_name||' '||hr.employees.last_name as "Full name",
        hr.employees.email,hr.employees.phone_number,
        hr.jobs.job_title,to_char(hr.employees.hire_date,'dd/mm/yyyy') as "Start date"
from hr.employees,hr.jobs
where hr.employees.job_id=hr.jobs.job_id
order by hr.employees.hire_date desc;

--cau III.4
select hr.departments.department_name, hr.locations.street_address,
        hr.locations.city, hr.countries.country_name,hr.regions.region_name
from hr.departments,hr.locations,hr.countries,hr.regions
where hr.departments.location_id=hr.locations.location_id
and hr.locations.country_id=hr.countries.country_id
and hr.countries.region_id=hr.regions.region_id
order by hr.regions.region_name

--cau III.5
select  em.first_name||' '||em.last_name as employees,
            nvl(de.department_name,'No department') as Department,
            nvl(trim(ma.first_name||' '||ma.last_name),'No manager') as Manager
from    hr.employees em,hr.departments de, hr.employees ma
where   em.department_id =de.department_id(+)
and     em.manager_id = ma.employee_id(+)
order by Manager


--cau III.6
--hien thi muc luong cao nhat
select * from(
    select hr.jobs.job_title, hr.jobs.max_salary
    from hr.jobs

    order by hr.jobs.max_salary desc
)
where rownum=1

--thap nhat
select * from(
    select hr.jobs.job_title, hr.jobs.min_salary
    from hr.jobs
    order by hr.jobs.min_salary asc
)
where rownum=1

--muc luong thap nhat trong 1 khoang
select  hr.jobs.job_title, hr.jobs.min_salary
from    hr.jobs
--where   hr.jobs.min_salary between 4000 and 6000
where   hr.jobs.min_salary >= 4000
and     hr.jobs.min_salary <= 6000
order by hr.jobs.min_salary asc


--cau III.7
--hien thi cong viec co muc luong nam trong khoang
select *
from    hr.jobs
where   hr.jobs.min_salary>=4000
and     hr.jobs.max_salary<=10000

--hien thi cong viec co bien do tra luong cao nhat
select * from(
    select  hr.jobs.job_title,hr.jobs.min_salary,hr.jobs.max_salary,
            hr.jobs.max_salary-hr.jobs.min_salary as "Bien do max"
    from    hr.jobs
    order by "Bien do max" desc
)where rownum=1


--cau III.8
--hien thi muc luong trung binh toi thieu
select trunc (avg(hr.jobs.min_salary),3) as "Tb toi thieu",
        trunc (avg(hr.jobs.max_salary),3) as "Tb cao nhat"
from hr.jobs;

select  hr.jobs.job_title,hr.jobs.min_salary
from    hr.jobs
where   hr.jobs.min_salary < ( select avg(hr.jobs.min_salary) from hr.jobs)
order by hr.jobs.min_salary;


select  hr.jobs.job_title,hr.jobs.max_salary
from    hr.jobs
where   hr.jobs.max_salary > ( select avg(hr.jobs.max_salary) from hr.jobs)
order by hr.jobs.max_salary;


--cau III.9
select  hr.jobs.job_title,count(hr.employees.job_id) as "Sum employees"
from    hr.jobs,hr.employees
where   hr.jobs.job_id=hr.employees.job_id
group by hr.jobs.job_title
order by "Sum employees"


--cong viec khong co nguoi lam
select  hr.jobs.job_title
from    hr.jobs
where   hr.jobs.job_id not in (select hr.employees.job_id from hr.employees)

--hien thi cong viec co so nguoi lam nhieu nhat
select * from(
    select  hr.jobs.job_title,count(hr.employees.job_id) as "Sum employees"
    from    hr.jobs,hr.employees
    where   hr.jobs.job_id=hr.employees.job_id
    group by hr.jobs.job_title
    order by "Sum employees" desc
)where rownum=1

--hien thi nhung nguoi lam cong viec nhieu nhat tren

select hr.employees.first_name||' '||hr.employees.last_name as "FULL NAME"
from hr.employees
where hr.employees.job_id = (
    select job_id from(
            select  hr.jobs.job_id as job_id,count(hr.employees.job_id) as "Sum employees"
            from    hr.jobs,hr.employees
            where   hr.jobs.job_id=hr.employees.job_id
            group by hr.jobs.job_id
            order by "Sum employees" desc
    )where rownum=1
)

--cau III.10
select hr.employees.first_name||' '||hr.employees.last_name as "FULL NAME",
        nvl(to_char(hr.employees.commission_pct),'No commission') as comme
from hr.employees

--cau III.11
select hr.employees.first_name||' '||hr.employees.last_name as "FULL NAME",hr.employees.salary,hr.employees.salary+hr.employees.salary*0.2 as "New Salary"
from hr.employees;

--cau III.12
select hr.employees.first_name||' '||hr.employees.last_name as "FULL NAME"
from hr.employees
where upper(hr.employees.first_name) like upper('__A%');

--cau III.13
select hr.employees.first_name||' '||hr.employees.last_name as "FULL NAME",
       to_char(hr.employees.hire_date,'dd/mm/yyyy')
from hr.employees
where to_char(hr.employees.hire_date,'yyyy')='1999';

--cau III.14
select hr.employees.first_name||' '||hr.employees.last_name as "FULL NAME",
       to_char(hr.employees.hire_date,'dd/mm/yyyy')
from hr.employees
where hr.employees.hire_date > (
            select hr.employees.hire_date
            from hr.employees
            where upper(hr.employees.last_name) = upper('austin')
)
order by hr.employees.hire_date;

--cau III.15
select initcap(hr.employees.first_name||' '||hr.employees.last_name) as "FULL NAME",
        length(hr.employees.last_name||' '||hr.employees.first_name) as "Length Name"
from hr.employees
where upper(hr.employees.first_name) like upper('J%')
or upper(hr.employees.first_name) like upper('A%')
or upper(hr.employees.first_name) like upper('M%')
order by "FULL NAME";

--cau III.16
select distinct hr.jobs.job_title,hr.locations.street_address,hr.locations.city
from hr.employees,hr.jobs,hr.departments,hr.locations
where hr.employees.job_id=hr.jobs.job_id
and  hr.employees.department_id=hr.departments.department_id
and   hr.employees.department_id=30
and hr.departments.location_id=hr.locations.location_id
order by hr.jobs.job_title;

--cau III.17
select hr.employees.first_name||' '||hr.employees.last_name as "FULL NAME",hr.jobs.job_title
from hr.employees,hr.jobs
where hr.employees.job_id=hr.jobs.job_id
and hr.employees.manager_id is null

--cau III.18
select hr.employees.first_name||' '||hr.employees.last_name as "FULL NAME",hr.jobs.job_title,
        hr.employees.salary
from hr.employees,hr.jobs
where hr.employees.job_id=hr.jobs.job_id
and (upper(hr.jobs.job_title) = upper('President') or upper(hr.jobs.job_title) = upper('Accountant'))
and hr.employees.salary != 1000
and hr.employees.salary != 9000

--cau III.19
select hr.departments.department_name as Dname,
            hr.locations.street_address||' - '||hr.locations.city as "Loc",
            A."Sum Em" as "Number of People",trunc(A."AVG SAL",2) as "Salary"
from hr.departments,hr.locations,(
                select count(hr.employees.department_id) as "Sum Em",
                trunc(avg(hr.employees.salary),3) "AVG SAL",
                hr.employees.department_id as ID
                from hr.employees
                group by hr.employees.department_id) A
where hr.departments.location_id=hr.locations.location_id
and hr.departments.department_id=A.ID

--cau III.20
select to_char(next_day(add_months(sysdate,2),'fri'),'dd MONTH yyyy')
        as "Friday after 2 moths"
from dual;


--cau III.21
select hr.employees.first_name||' '||hr.employees.last_name as "FULL NAME",
       to_char(hr.employees.hire_date,'dd/mm/yyyy') as "Hire Date",
       to_char(hr.employees.hire_date,'DAY') as "Day"
from hr.employees
order by to_number(to_char(hr.employees.hire_date,'D'))

--cau III.22
select max(hr.employees.salary) as "Maximum",
       min(hr.employees.salary) as "Minimum",  
       sum(hr.employees.salary) as "Sum",  
       trunc(avg(hr.employees.salary),3) as "Average"
from hr.employees  

--cau III.23
select max(hr.employees.salary) as "Maximum",
       min(hr.employees.salary) as "Minimum",  
       sum(hr.employees.salary) as "Sum",  
       trunc(avg(hr.employees.salary),3) as "Average"
from hr.employees
group by hr.employees.job_id