Bài thực hành Oracle (4 tuần) Bài 3 - Tùng Huynh
--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
Bạn đang đọc truyện trên: Truyen247.Pro