Màu nền Màu tối Mặc định Xám nhạt Xanh nhạt Xanh đậm Vàng nhạt Màu sepia Vàng đậm Vàng ố

Font chữ: Palatino Linotype Bookerly Segoe UI Patrick Hand Times New Roman Verdana Tahoma Arial

Chiều cao dòng: 100 120 140 160 180 200

Kích cỡ chữ:

--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