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