Write a postgre sql statement to create a table employees, including columns employee_id, first_name, last_name, email, phone_number hire_date, job_id, salary,

Postgre SQL

2021-09-29
mhanasmh00489829403

Write a postgre sql statement to create a table employees, including columns employee_id, first_name, last_name, email, phone_number hire_date, job_id, salary, commission, manager_id and department_id and make sure that, the employee_id column does not contain any duplicate value at the time of insertion, and the foreign key column department_id, reference by the column department_id of departments table, can contain only those values which exist in the departments table and another foreign key column job_id, referenced by the column job_id of jobs table, can contain only those values which exist in the jobs table.

Assume that the structure of two tables departments and jobs.

Column      |         Type          |       Modifiers
-----------------+-----------------------+-----------------------
 department_id   | numeric(4,0)          | not null
 department_name | character varying(30) | not null
 manager_id      | numeric(6,0)          | default NULL::numeric
 location_id     | numeric(4,0)          | default NULL::numeric\

Indexes:
    "departments_pkey" PRIMARY KEY, btree (department_id)

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

Indexes:
    "jobs_pkey" PRIMARY KEY, btree (job_id)

الأجوبة

CREATE TABLE IF NOT EXISTS employees ( 
EMPLOYEE_ID decimal(6,0) NOT NULL PRIMARY KEY, 
FIRST_NAME varchar(20) DEFAULT NULL, 
LAST_NAME varchar(25) NOT NULL, 
EMAIL varchar(25) NOT NULL, 
PHONE_NUMBER varchar(20) DEFAULT NULL, 
HIRE_DATE date NOT NULL, 
JOB_ID varchar(10) NOT NULL, 
SALARY decimal(8,2) DEFAULT NULL, 
COMMISSION_PCT decimal(2,2) DEFAULT NULL, 
MANAGER_ID decimal(6,0) DEFAULT NULL, 
DEPARTMENT_ID decimal(4,0) DEFAULT NULL, 
FOREIGN KEY(DEPARTMENT_ID) 
REFERENCES  departments(DEPARTMENT_ID),
FOREIGN KEY(JOB_ID) 
REFERENCES  jobs(JOB_ID)
);

Output:

postgres=# CREATE TABLE IF NOT EXISTS employees (
postgres(# EMPLOYEE_ID decimal(6,0) NOT NULL PRIMARY KEY,
postgres(# FIRST_NAME varchar(20) DEFAULT NULL,
postgres(# LAST_NAME varchar(25) NOT NULL,
postgres(# EMAIL varchar(25) NOT NULL,
postgres(# PHONE_NUMBER varchar(20) DEFAULT NULL,
postgres(# HIRE_DATE date NOT NULL,
postgres(# JOB_ID varchar(10) NOT NULL,
postgres(# SALARY decimal(8,2) DEFAULT NULL,
postgres(# COMMISSION_PCT decimal(2,2) DEFAULT NULL,
postgres(# MANAGER_ID decimal(6,0) DEFAULT NULL,
postgres(# DEPARTMENT_ID decimal(4,0) DEFAULT NULL,
postgres(# FOREIGN KEY(DEPARTMENT_ID)
postgres(# REFERENCES  departments(DEPARTMENT_ID),
postgres(# FOREIGN KEY(JOB_ID)
postgres(# REFERENCES  jobs(JOB_ID)
postgres(# );
CREATE TABLE

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

postgres=# \d employees
                       Table "public.employees"
     Column     |         Type          |            Modifiers
----------------+-----------------------+---------------------------------
 employee_id    | numeric(6,0)          | not null
 first_name     | character varying(20) | default NULL::character varying
 last_name      | character varying(25) | not null
 email          | character varying(25) | not null
 phone_number   | character varying(20) | default NULL::character varying
 hire_date      | date                  | not null
 job_id         | character varying(10) | not null
 salary         | numeric(8,2)          | default NULL::numeric
 commission_pct | numeric(2,2)          | default NULL::numeric
 manager_id     | numeric(6,0)          | default NULL::numeric
 department_id  | numeric(4,0)          | default NULL::numeric
Indexes:
    "employees_pkey" PRIMARY KEY, btree (employee_id)
Foreign-key constraints:
    "employees_department_id_fkey" FOREIGN KEY (department_id) REFERENCES departments(department_id)
    "employees_job_id_fkey" FOREIGN KEY (job_id) REFERENCES jobs(job_id)

سجل الدخول لتستطيع الإجابة

هل كان المحتوى مفيد؟

0 نعم

0 لا

أسئلة مشابهة

Write a postgre SQL statement to create a table employees including columns employee_id, first_name, last_name, job_id, salary and make sure that, the employee_id

Write a postgre SQL statement to create a table employees including columns employee_id, first_name, last_name, job_id, salary and make sure that

Write a postgre SQL statement to create a table employees including columns employee_id, first_name, last_name, job_id, salary and make sure that,

Write a postgre SQL statement to create a table employees, including employee_id, first_name, last_name, email, phone_number hire_date, job_id, salary,

القوائم الدراسية التي ينتمي لها السؤال

لايوجد قوائم حاليا

معلومات ذات صلة

Postgre SQL | Postgre SQL

تبحث عن مدرس اونلاين؟

ابحث عن مدرسك