LAB10(DBII)

----------------------------LAB10-----------------------------------
------------------------------1-----------------------------
CREATE TABLE JOB_DIM (
 JOB_ID      VARCHAR2(10) PRIMARY KEY,
 JOB_TITLE   VARCHAR2(35),
 TOTAL       VARCHAR2(10)) ;

------------------------------2------------------------
INSERT INTO JOB_DIM
 SELECT JOB_ID, JOB_TITLE, 'TOTAL' FROM HR.jobs;

select * from job_dim;

----------------------------------3---------------------
DROP TABLE EMP_FACT ;


CREATE TABLE DEPT_DIM (
 DEPARTMENT_ID NUMBER(4) PRIMARY KEY,
 TOTAL VARCHAR2(25),
REGION VARCHAR2(25),
 COUNTRY VARCHAR2(25),
         CITY VARCHAR2(25),
         DEPARTMENT VARCHAR2(30));

INSERT INTO DEPT_DIM
 SELECT D.DEPARTMENT_ID, 'TOTAL', R.REGION_NAME ,
 C.COUNTRY_NAME, L.CITY, D.DEPARTMENT_NAME
 FROM HR.DEPARTMENTS D
 JOIN HR.LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
 JOIN HR.COUNTRIES C ON C.COUNTRY_ID = L.COUNTRY_ID
 JOIN HR.REGIONS R ON R.REGION_ID = C.REGION_ID
 GROUP BY D.DEPARTMENT_ID,'TOTAL', R.REGION_NAME,
             C.COUNTRY_NAME, L.CITY, D.DEPARTMENT_NAME
 ORDER BY 2,3,4,5,6;

 --------------------------------4-----------------------------
 CREATE TABLE EMP_FACT(
  DEPARTMENT_ID NUMBER(4),
  JOB_ID  VARCHAR2(10),
  NUM_EMP NUMBER(6) ,
  TOTAL_SALARY  NUMBER(10,2),
  CONSTRAINT EMP_FACT_PK PRIMARY KEY (DEPARTMENT_ID, JOB_ID),
  CONSTRAINT EMP_FACT_DEPT_ID_FK FOREIGN KEY (DEPARTMENT_ID)
         REFERENCES DEPT_DIM  ,
  CONSTRAINT EMP_FACT_JOB_ID_FK FOREIGN KEY (JOB_ID)
         REFERENCES JOB_DIM  ) ;

-------------------------------5----------------------------
insert into emp_fact
SELECT DEPARTMENT_ID, JOB_ID,
       COUNT(*),
       SUM(SALARY)
FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID ;

select * from emp_fact;
select 'TOTAL',sum(num_emp),sum(total_salary) from emp_fact e
    join dept_dim d on e.department_id = d.department_id
    group by 'TOTAL';

------------------------------------6-----------------------------
select * from jobs;

select j.job_title,sum(num_emp),sum(total_salary) from emp_fact e
    join job_dim j on j.job_id = e.job_id
    GROUP BY j.job_title;

------------------------------------7-----------------------------------------
select * from dept_dim;
select j.job_title,d.region,sum(num_emp),sum(total_salary) from emp_fact e
    join dept_dim d on e.department_id = d.department_id
    join job_dim j on j.job_id = e.job_id
    group by j.job_title,d.region;