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

  • Postgre SQL

 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 column does not contain any duplicate value at the time of insertion, and the 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. The specialty of the statement is that the ON DELETE SET NULL action will set the foreign key column values in the child table(employees) to NULL when the record in the parent table(jobs) is deleted, with a condition that the foreign key column in the child table must accept NULL values and the ON UPDATE SET NULL action resets the values in the rows in the child table(employees) to NULL values when the rows in the parent table(jobs) are updated.

CREATE TABLE IF NOT EXISTS jobs ( 
JOB_ID INTEGER NOT NULL UNIQUE PRIMARY KEY, 
JOB_TITLE varchar(35) NOT NULL DEFAULT ' ', 
MIN_SALARY decimal(6,0) DEFAULT 8000, 
MAX_SALARY decimal(6,0) DEFAULT NULL
);
   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  employees ( 
EMPLOYEE_ID decimal(6,0) NOT NULL PRIMARY KEY, 
FIRST_NAME varchar(20) DEFAULT NULL, 
LAST_NAME varchar(25) NOT NULL, 
JOB_ID INTEGER, 
SALARY decimal(8,2) DEFAULT NULL, 
FOREIGN KEY(JOB_ID) 
REFERENCES  jobs(JOB_ID)
ON DELETE SET NULL 
ON UPDATE SET NULL
);

Output:

postgres=# CREATE TABLE  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(# JOB_ID INTEGER, SALARY decimal(8,2) DEFAULT NULL,
postgres(# FOREIGN KEY(JOB_ID)
postgres(# REFERENCES  jobs(JOB_ID)
postgres(# ON DELETE SET NULL ON UPDATE SET NULL
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
 job_id      | integer               |
 salary      | numeric(8,2)          | default NULL::numeric
Indexes:
    "employees_pkey" PRIMARY KEY, btree (employee_id)
Foreign-key constraints:
    "employees_job_id_fkey" FOREIGN KEY (job_id) REFERENCES jobs(job_id) ON UPDATE SET NULL ON DELETE SET NULL
سجل الدخول لتستطيع الإجابة
هل كان المحتوى مفيد؟

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, email, phone_number hire_date, job_id, salary,
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

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

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

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

ابحث عن مدرسك
محتاج مساعدة باختيار المدرس الافضل؟ تواصل مع فريقنا الان لمساعدتك بتأمين افضل مدرس
ماهو التخصص الذي تبحث عنه؟
اكتب هنا...