Bài thực hành Oracle (4 tuần) - Tunghuynh_bai3
-- ------------------- STUDENT: Tùng Huynh
-- ------------------- CLASS: K7A
-- ------------------- Lesson 3.
-- Cau I: (Session -> New Session: SYSTEM)
-- Tạo user và grant quyền
create user may12tunghuynh IDENTIFIED by 12345;
grant CONNECT to may12tunghuynh;
grant RESOURCE to may12tunghuynh;
grant create view to may12tunghuynh;
-- Câu II: Đăng nhập vào user hr (Session -> New Session: HR)
-- II.1.
-- Hiển thị mô tả các bảng (Tên các trường, kiểu dữ liệu,...)
-- COUNTRIES, DEPARTMENTS, EMPLOYEES, JOB_HISTORY, JOBS, LOCATIONS, REGIONS của hr.
describe hr.employees;
describe hr.countries;
describe hr.departments;
describe hr.job_history;
describe hr.jobs;
describe hr.locations;
describe hr.regions;
-- II.2.
-- Grant quyền select trên các bảng COUNTRIES, DEPARTMENTS, EMPLOYEES,
-- JOB_HISTORY, JOBS, LOCATIONS, REGIONS của hr cho user đã tạo ở câu I
grant select on countries to may12tunghuynh;
grant select on departments to may12tunghuynh;
grant select on employees to may12tunghuynh;
grant select on job_history to may12tunghuynh;
grant select on jobs to may12tunghuynh;
grant SELECT on locations to may12tunghuynh;
grant SELECT on regions to may12tunghuynh;
-- Cau III: Đăng nhập vào user đã tạo ở câu I (Session -> New Session: MAY12TUNGHUYNH)
SELECT * from hr.jobs
-- III.1.
-- Hiển thị đầy đủ các trường của các bảng COUNTRIES, DEPARTMENTS,
-- EMPLOYEES, JOB_HISTORY, JOBS, LOCATIONS, REGIONS trong user hr.
-- III.2.
-- Hiển thị cột FIRST_NAME ghép với cột LAST_NAME cách nhau
-- bằng một khoảng trống và đặt tên cột ghép này là FULL NAME.
select hr.employees.first_name ||' ' || hr.employees.last_name as "Full name"
from hr.employees
-- III.3.
-- Hiển thị tên đầy đủ của nhân viên, email, số điện thoại,
-- tên công việc, ngày vào làm. Dữ liệu lấy về phải sắp xếp theo ngày vào làm giảm dần.
-- Hiển thị ngày vào làm phải định dạng theo dd/mm/yyyy.
select e.first_name ||' ' || e.last_name as "Full name" ,
e.email, e.phone_number, j.job_title,
to_date(e.hire_date,'dd/mm/yyyy') as "Ngay vao lam"
from hr.employees e, hr.job_history jh, hr.jobs j
where jh.employee_id=e.employee_id
and e.job_id=j.job_id
and j.job_id=hr.jh.job_id
ORDER by e.hire_date desc
-- III.4.
-- Hiển thị tên các phòng ban, địa điểm đặt, thuộc nước nào, châu lục nào
select d.department_name, lo.street_address, lo.city, co.country_name, re.region_name
from hr.countries co, hr.locations lo, hr.regions re, hr.departments d
where lo.location_id=d.location_id
and co.country_id=lo.country_id
and re.region_id=co.region_id
-- III.5.
-- SELECT * from hr.employees
-- Hiển thị tên nhân viên, thuộc phòng ban, người quản lý.
-- Nếu nhân viên không thuộc phòng ban nào thì in ra thông báo No department,
-- nếu nhân viên không có người quản lý thì in ra thông báo No manager.
-- Sắp xếp dữ liệu theo tên người quản lý
select e.first_name ||' ' || e.last_name as "Full name",
nvl(d.department_name,'No Department') as Department,
nvl(trim(m.first_name ||' ' || m.last_name),'No manager') as Manager
-- nvl(string1,string2): Null VaLue: Ktra neu string1=null thi in ra string2
from hr.employees e, hr.departments d, hr.employees m -- Nguoi quan ly nam trong danh sach nhan vien cong ty
where e.department_id=d.department_id(+)
and m.employee_id(+)=e.manager_id
order by e.first_name
-- CHÚ THÍCH: 5.1.3. Mối liên kết cộng
-- Mối liên kết cộng trả về cả các giá trị NULL trong biểu thức điều kiện.
-- Dấu (+) để ở vế nào tính thêm các giá trị NULL ở vế đó.
-- Một câu lệnh select chỉ đặt được 1 mối liên kết cộng, dấu (+) đặt ở bên phải column liên kết
-- III.6.
-- SELECT * from hr.jobs
-- Hiển thị công việc có mức lương cao nhất.
select hr.jobs.job_title, hr.jobs.max_salary
from hr.jobs
where hr.jobs.max_salary= (select max(hr.jobs.max_salary)
from hr.jobs)
-- Hiển thị công việc có mức lương thấp nhất.
select hr.jobs.job_title, hr.jobs.min_salary
from hr.jobs
where hr.jobs.min_salary = (select min(hr.jobs.min_salary)
from hr.jobs)
-- Hiển thị công việc có mức lương thấp nhất nằm trong khoảng 4000 -> 6000.
select hr.jobs.job_title, hr.jobs.min_salary
from hr.jobs
where hr.jobs.min_salary = (select min(hr.jobs.min_salary)
from hr.jobs
where hr.jobs.min_salary >=4000
and hr.jobs.min_salary <= 6000)
-- III.7.
-- Hiển thị công việc có mức lương nằm trong khoảng 4000 -> 10000
select hr.jobs.job_title, hr.jobs.min_salary, hr.jobs.max_salary
from hr.jobs
where hr.jobs.min_salary >= 4000
and hr.jobs.max_salary <= 10000
-- Hiển thị công việc có biên độ trả lương rộng nhất
select hr.jobs.job_title, hr.jobs.min_salary, hr.jobs.max_salary
from hr.jobs
where hr.jobs.max_salary-hr.jobs.min_salary =
(select max(hr.jobs.max_salary-hr.jobs.min_salary)
from hr.jobs)
-- III.8.
-- Hiển thị trung bình mức lương tối thiểu, trung bình mức lương cao nhất, làm tròn đến 3 số sau dấu phẩy.
select trunc(avg(hr.jobs.min_salary),3) as "AVG min",
trunc(avg(hr.jobs.max_salary),3) as "AVG max"
from hr.jobs
-- Hiển thị những công việc có mức lương tối thiểu nhỏ hơn mức lương tối thiểu trung bình
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)
-- Hiển thị những công việc có mức lương cao nhất lớn hơn mức lương trung bình cao nhất
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)
-- III.9.
-- Hiển thị công việc và số người làm công việc đó.
select hr.employees.job_id, hr.jobs.job_title, count(hr.employees.employee_id) as Sonhanvien
from hr.jobs, hr.employees
where hr.jobs.job_id=hr.employees.job_id
group by hr.employees.job_id, hr.jobs.job_title
-- Hiển thị công việc có số người làm nhiều nhất.
select * from (
select hr.employees.job_id, hr.jobs.job_title, count(hr.employees.employee_id) as Sonhanvien
from hr.jobs, hr.employees
where hr.jobs.job_id=hr.employees.job_id
group by hr.employees.job_id, hr.jobs.job_title
order by Sonhanvien desc
) where rownum=1
-- Hiển thị tên nhân viên làm công việc có số người làm nhiều nhất đó
select hr.employees.first_name||' '||hr.employees.last_name as "Ten nhan vien"
from hr.employees
where hr.employees.job_id = (
select job_id from (
select hr.employees.job_id, hr.jobs.job_title, count(hr.employees.employee_id) as Sonhanvien
from hr.jobs, hr.employees
where hr.jobs.job_id=hr.employees.job_id
group by hr.employees.job_id, hr.jobs.job_title
order by Sonhanvien desc
) where rownum=1
-- III.10.
-- Hiển thị tên đầy đủ của nhân viên và số tiền thưởng, nếu không có tiền thưởng thì hiển thị thông báo “No Commission”.
-- Đặt tên cột tiền thưởng là COMM
select hr.employees.first_name||' '||hr.employees.last_name as "Ten nhan vien",
nvl(TO_CHAR(hr.employees.commission_pct),'No commission') as Comm
from hr.employees
order by "Ten nhan vien"
-- III.11.
-- Thực hiện tăng 20% lương cho tất cả các nhân viên.
-- Hiện thị ra màn hình tên nhân viên, cột lương cũ và cột lương mới với tên cột lương mới là New Salary
select hr.employees.first_name||' '||hr.employees.last_name as "Ten nhan vien",
hr.employees.salary,
hr.employees.salary*0.2+hr.employees.salary as "New salary"
from hr.employees
-- III.12.
-- Hiển thị tên đầy đủ của các nhân viên có ký tự thứ 3 trong first_name là A (ví dụ: BLAKE, CLARK).
-- VD: Char, PHA,...
SELECT hr.employees.first_name||' '||hr.employees.last_name as "Ten nhan vien"
from hr.employees
where UPPER(hr.employees.first_name) like UPPER('__a%')
-- III.13.
-- Hiển thị tên đầy đủ và ngày vào công ty (HIREDATE) của các nhân viên vào công ty năm 1999,
-- định dạng HIREDATE là dd/mm/yyyy
select hr.employees.first_name||' '||hr.employees.last_name as "Ten nhan vien",
to_char(hr.employees.hire_date,'dd/mm/yyyy') as "Hire date"
from hr.employees
where EXTRACT(year from hr.employees.hire_date )=1999
-- Hoac: where to_char(hr.employees.hire_date,'yyyy')='1999';
-- III.14.
-- Hiển thị tên đầy đủ và ngày vào công ty (HIREDATE) của các nhân viên vào công ty sau nhân viên Austin (last_name).
-- Dữ liệu được xắp xếp theo HIREDATE giảm dần
select hr.employees.first_name||' '||hr.employees.last_name as "Ten nhan vien",
to_date(hr.employees.hire_date,'dd/mm/yyyy') as "Hire date"
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
-- III.15.
-- Hiển thị tên đầy đủ, độ dài tên đầy đủ của các nhân viên có tên (first_name) bắt đầu là J, A hoặc M.
-- Định dạng tên sao cho ký tự đầu tiên là ký tự hoa, các ký tự còn lại là ký tự thường.
-- Đặt tên cho các cột tương ứng là Name, Length Name
select hr.employees.first_name||' '||hr.employees.last_name as "Name",
length(hr.employees.first_name)+length(hr.employees.last_name)+1 as "Length name"
from hr.employees
where hr.employees.first_name like 'J%'
or hr.employees.first_name like 'A%'
or hr.employees.first_name like 'M%'
-- III.16.
-- Hiển thị các dòng giá trị duy nhất của các chức danh (job_title) trong phòng ban (department_id) 30,
-- kèm theo địa chỉ (đường và thành phố) của phòng ban
SELECT DISTINCT hr.jobs.job_title, hr.locations.street_address, hr.locations.city
from hr.locations, hr.jobs, hr.departments, hr.employees
where hr.departments.department_id=30
and hr.departments.location_id=hr.locations.location_id
and hr.employees.department_id=hr.departments.department_id
and hr.employees.job_id=hr.jobs.job_id
-- III.17.
-- Hiển thị tên và nghề nghiệp (JOB) của tất cả các nhân viên không có quản lý (MGR).
select hr.employees.first_name||' '||hr.employees.last_name as "Name",
hr.jobs.job_title
from hr.jobs, hr.employees
where hr.jobs.job_id=hr.employees.job_id
and hr.employees.manager_id is null
-- III.18.
-- Hiển thị tên (ENAME), nghề nghiệp (JOB) và lương (SAL)
-- của các nhân viên có nghề nghiệp là President hoặc Accountant
-- và mức lương không bằng $1000, $9000.
select hr.employees.first_name||' '||hr.employees.last_name as "Name",
hr.jobs.job_title, hr.employees.salary
from hr.jobs, hr.employees
where (upper(hr.jobs.job_title) like UPPER('President')
or upper(hr.jobs.job_title) like UPPER('Accountant'))
and hr.employees.salary != 1000
and hr.employees.salary != 9000
and hr.employees.job_id=hr.jobs.job_id
-- III.19.
-- Hiển thị tên phòng ban (DNAME), địa chỉ (LOC),
-- số nhân viên thuộc phòng ban và lương (SAL) trung bình
-- của các nhân viên trong phòng ban. Đặt tên các cột tương ứng là dname,
-- loc, Number of People và Salary.
-- Làm tròn lương trung bình đến 2 chữ số thập phân
---------- Trước tiên tạo 1 view để lấy ID phòng ban và số nhân viên, lương TB trong từng phòng ban đó
create view dem
as
select hr.employees.department_id as "ID", count(hr.employees.employee_id) as "NumPp",
trunc(avg(hr.employees.salary),2) as "Salary"
from hr.employees
group by hr.employees.department_id
--select * from dem
---------- Phần chính
select hr.departments.department_name as "Dname",
hr.locations.street_address||' '||hr.locations.city as "Loc",
dem."NumPp" as "Number of People", dem."Salary"
from hr.departments, hr.locations, dem
where hr.departments.department_id=dem.ID
and hr.locations.location_id=hr.departments.location_id
--------- Cách 2 không cần dùng view:
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
-- III.20.
-- Tìm ngày thứ 6 đầu tiên cách 2 tháng so với ngày hiện tại,
-- hiển thị ngày dưới dạng 09 February 1990.
-- select sysdate from dual
select to_char(next_day(add_months(sysdate,2),'thứ sáu'),'dd month yyyy')as Day
from dual
-- next_day(d,string): Bắt đâu từ ngày d tăng dần cho đến khi gặp ngày có day=string ('thứ sáu')
-- add_month(d,int): Tăng ngày tháng d thêm int tháng (int có thể âm -> lùi tháng, dương -> tiến tháng)
-- III.21.
-- Hiển thị tên nhân viên, ngày vào công ty và ngày trong tuần (thứ)
-- của ngày vào công ty (đặt tên cột là DAY).
-- Sắp xếp kết quả trả về theo các ngày trong tuần, bắt đầu từ chủ nhật, thứ hai....
select hr.employees.first_name||' '||hr.employees.last_name as "Ten nhan vien",
hr.employees.hire_date, to_char(hr.employees.hire_date,'day') as Day
from hr.employees
order by Day
-- III.22.
-- Hiển thị lương (SAL) cao nhất, thấp nhất, tổng lương
-- và lương trung bình của tất cả các nhân viên.
-- Đặt tên các cột tương ứng là Maximum, Minimum, Sum và Average.
-- Làm tròn kết quả trả về
select max(hr.employees.salary) as Maximum,
min(hr.employees.salary) as Minimum,
Sum(hr.employees.salary) as Sum,
round(avg(hr.employees.salary),2) as Average
from hr.employees
-- III.23.
-- Hiển thị mức lương cao nhất, thấp nhất,
-- tổng lương và lương trung bình cho mỗi loại nghề nghiệp
select hr.jobs.*, round((hr.jobs.min_salary+hr.jobs.max_salary)/2,2) as AVG
from hr.jobs
-----------------------
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