how can i insert this data into this table from other tables?-CodePudding

Table XX_EMPLOYEES

CREATE TABLE XX_EMPLOYEES

( EMP_ID NUMBER NOT NULL, EMP_FIRST_NAME VARCHAR2(250) NOT NULL, EMP_MIDDLE_NAME VARCHAR2(250) NOT NULL, EMP_LAST_NAME VARCHAR2(250) NOT NULL, Hired_Date DATE NOT NULL, Country VARCHAR2(250) NOT NULL, Salary NUMBER NOT NULL );

INSERT ALL

INTO XX_EMPLOYEES (EMP_ID, EMP_FIRST_NAME, EMP_MIDDLE_NAME, EMP_LAST_NAME, Hired_Date, Country, Salary) VALUES (1,'Tomm','Jef','Adam','01-Jan-2016','JORDAN',1000) INTO XX_EMPLOYEES (EMP_ID, EMP_FIRST_NAME, EMP_MIDDLE_NAME, EMP_LAST_NAME, Hired_Date, Country, Salary) VALUES (2,'Mohammed','Ahmed','Mahmoud','15-Jul-2009','UAE',900) INTO XX_EMPLOYEES (EMP_ID, EMP_FIRST_NAME, EMP_MIDDLE_NAME, EMP_LAST_NAME, Hired_Date, Country, Salary) VALUES (4,'Ali','Ahmad','Mahmoud','07-Jul-2000','UK',1200) INTO XX_EMPLOYEES (EMP_ID, EMP_FIRST_NAME, EMP_MIDDLE_NAME, EMP_LAST_NAME, Hired_Date, Country, Salary) VALUES (10,'Basel','Jamal','Saeed','10-Apr-2001','UAE',1000)

SELECT * FROM dual;

Table Jobs

CREATE TABLE Jobs
(
    JOB_ID NUMBER NOT NULL,
    JOB_Description VARCHAR2(250) NOT NULL
);

INSERT ALL

INTO Jobs (Job_ID, Job_Description) VALUES (1, 'Accountant')
INTO Jobs (Job_ID, Job_Description) VALUES (2, 'General Manager')
INTO Jobs (Job_ID, Job_Description) VALUES (3, 'Administration')
INTO Jobs (Job_ID, Job_Description) VALUES (4, 'Senior Accountant')

SELECT * FROM dual;

Table Profession

CREATE TABLE  PROFESSION
   (     EMP_ID  NUMBER NOT NULL, 
     JOB_ID  NUMBER NOT NULL, 
     MANAGER_ID NUMBER, 
     EFFECTIVE_FROM DATE NOT NULL, 
     EFFECTIVE_TO DATE NOT NULL, 
     CONSTRAINT RESTRICT CHECK (EFFECTIVE_FROM < EFFECTIVE_TO)
   )

Now how do I add this data to profession table using all 3 tables?

(Tomm, General Manager,null,01-Jan-2000,null)

(Mohammed, Senior Accountant, Tomm,01-Jan-2010, Null)

(Ali, Administration, Tomm,01-Jan-2000,Null)

(Basel, Accountant, Mohammed,01-Mar-2007,Null)

The table profession has these restrictions:-

Staff, Effective_from and Effective_to is mandatory fields, and must be entered.

Effective_from must be less than Effective_to.

For the same employee, he must not have two records conjunction in the effective days.

Tried this but still can't get it

INSERT INTO PROFESSION(EMP_ID,JOB_ID,STAFF)

VALUES ((SELECT EMP_ID FROM XX_EMPLOYEES WHERE EMP_ID=1003),(SELECT JOB_ID FROM JOBS WHERE JOB_ID=102),(SELECT EMP_FIRST_NAME FROM XX_EMPLOYEES WHERE EMP_ID=1003))

CodePudding user response：

To insert rows, you'll have to join xx_employees and jobs via case expression, literally joining people with jobs.

SQL> INSERT INTO profession (emp_id,
  2                          job_id,
  3                          manager_id,
  4                          effective_from,
  5                          effective_to)
  6     SELECT e.emp_id,
  7            j.job_id,
  8            --
  9            CASE                        -- this CASE expression sets the manager
 10               WHEN e.emp_id = 1 THEN NULL
 11               WHEN e.emp_id = 2 THEN 1
 12               WHEN e.emp_id = 4 THEN 1
 13               WHEN e.emp_id = 10 THEN 2
 14            END manager_id,
 15            --
 16            DATE '2000-01-01',
 17            DATE '3000-01-01' -- the "TO" date can't be null, so it is set to far future
 18       FROM xx_employees e
 19            JOIN jobs j
 20               ON j.job_id =              -- CASE expressions says who does what
 21                  CASE
 22                     WHEN e.emp_id = 1 THEN 2
 23                     WHEN e.emp_id = 2 THEN 4
 24                     WHEN e.emp_id = 4 THEN 1
 25                     WHEN e.emp_id = 10 THEN 1
 26                  END;

4 rows created.

The result is again join of several tables; note that you have to use xx_employees twice: the e alias is for "employees", while the m alias is for managers:

SQL>   SELECT e.emp_first_name,
  2           j.job_description,
  3           m.emp_first_name manager,
  4           p.effective_from,
  5           p.effective_to
  6      FROM profession p
  7           JOIN xx_employees e ON e.emp_id = p.emp_id
  8           LEFT JOIN xx_employees m ON m.emp_id = p.manager_id
  9           JOIN jobs j ON j.job_id = p.job_id
 10  ORDER BY p.emp_id;

EMP_FIRST_NAME  JOB_DESCRIPTION      MANAGER    EFFECTIVE_F EFFECTIVE_T
--------------- -------------------- ---------- ----------- -----------
Tomm            General Manager                 01-jan-2000 01-jan-3000
Mohammed        Senior Accountant    Tomm       01-jan-2000 01-jan-3000
Ali             Accountant           Tomm       01-jan-2000 01-jan-3000
Basel           Accountant           Mohammed   01-jan-2000 01-jan-3000

SQL>