Q:

Write a postgre SQL statement to create a table job_history, including employee_id, start_date, end_date, job_id and department_id and make sure that, the employee_id column does not contain

hamza
Save to bookmark
|
2022-05-18
|
  177 
0

. Write a  postgre SQL statement to create a table job_history, including employee_id, start_date, end_date, job_id and department_id and make sure that, the employee_id column does not contain any duplicate values at the time of insertion and the foreign key column job_id contain only those values which exist in the jobs table.

Here is the structure of the table jobs;

   Column   |         Type          |               Modifiers
------------+-----------------------+----------------------------------------
 job_id     | character varying(10) | not null default ''::character varying
 job_title  | character varying(35) | not null
 min_salary | numeric(6,0)          | default NULL::numeric
 max_salary | numeric(6,0)          | default NULL::numeric
Postgre SQL,
login and add your own answer to this question

All Answers

hamza
2022-05-18
need an explanation for this answer? contact us directly to get an explanation for this answer 

CREATE TABLE job_history ( 
EMPLOYEE_ID decimal(6,0) NOT NULL PRIMARY KEY, 
START_DATE date NOT NULL, 
END_DATE date NOT NULL, 
JOB_ID varchar(10) NOT NULL, 
DEPARTMENT_ID decimal(4,0) DEFAULT NULL, 
FOREIGN KEY (job_id) REFERENCES jobs(job_id)
);

Output:

postgres=# CREATE TABLE job_history (
postgres(# EMPLOYEE_ID decimal(6,0) NOT NULL PRIMARY KEY,
postgres(# START_DATE date NOT NULL,
postgres(# END_DATE date NOT NULL,
postgres(# JOB_ID varchar(10) NOT NULL,
postgres(# DEPARTMENT_ID decimal(4,0) DEFAULT NULL,
postgres(# FOREIGN KEY (job_id) REFERENCES jobs(job_id)
postgres(# );
CREATE TABLE

Here is the command to see the structure of the created table :

postgres=# \d job_history;
                 Table "public.job_history"
    Column     |         Type          |       Modifiers
---------------+-----------------------+-----------------------
 employee_id   | numeric(6,0)          | not null
 start_date    | date                  | not null
 end_date      | date                  | not null
 job_id        | character varying(10) | not null
 department_id | numeric(4,0)          | default NULL::numeric
Indexes:
    "job_history_pkey" PRIMARY KEY, btree (employee_id)
Foreign-key constraints:
    "job_history_job_id_fkey" FOREIGN KEY (job_id) REFERENCES jobs(job_id)

need an explanation for this answer? contact us directly to get an explanation for this answer

total answers (1)

Similar questions

need a help?

find thousands of online teachers now