*****

PostgreSQL Create Table - Exercises, Practice, Solution

Last update on August 19 2022 21:50:56 (UTC/GMT +8 hours)

PostgreSQL Create Table [21 exercises with solution]

1. Write a sql statement to create a simple table countries including columns country_id,country_name and region_id.

Click me to see the solution of Exercise No.- 1. Click me again to collapse.

CREATE TABLE countries (
COUNTRY_ID varchar(3),
COUNTRY_NAME varchar(45) ,
REGION_ID decimal(10,0)
);

2. Write a sql statement to create a simple table countries including columns country_id,country_name and region_id which is already exists.

Click me to see the solution of Exercise No.- 2. Click me again to collapse.

CREATE TABLE countries ( 
COUNTRY_ID varchar(3),
COUNTRY_NAME varchar(45) ,
REGION_ID decimal(10,0)
);

3. Write a sql statement to create the structure of a table dup_countries similar to countries.

Click me to see the solution of Exercise No.- 3. Click me again to collapse.


CREATE TABLE dup_countries AS 
(SELECT * 
FROM countries_n)  
WITH NO DATA;

4. Write a sql statement to create a duplicate copy of countries table including structure and data by name dup_countries.

Click me to see the solution of Exercise No.- 4. Click me again to collapse.

CREATE TABLE dup_countries
AS SELECT * FROM  countries;

5. Write a sql statement to create a table countries set a constraint NULL.

Click me to see the solution of Exercise No.- 5. Click me again to collapse.

CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID varchar(2) NOT NULL,
COUNTRY_NAME varchar(40) NOT NULL,
REGION_ID decimal(10,0) NOT NULL
);

6. Write a sql statement to create a table named jobs including columns job_id, job_title, min_salary, max_salary and check whether the max_salary amount exceeding the upper limit 25000.

Click me to see the solution of Exercise No.- 6. Click me again to collapse.

CREATE TABLE IF NOT EXISTS jobs ( 
JOB_ID varchar(10) NOT NULL , 
JOB_TITLE varchar(35) NOT NULL, 
MIN_SALARY decimal(6,0), 
MAX_SALARY decimal(6,0) 
CHECK(MAX_SALARY<=25000)
);

7. Write a sql statement to create a table named countries including columns country_id, country_name and region_id and make sure that no countries except Italy, India and China will be entered in the table.

Click me to see the solution of Exercise No.- 7. Click me again to collapse.

CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID varchar(2),
COUNTRY_NAME varchar(40)
CHECK(COUNTRY_NAME IN('Italy','India','China')) ,
REGION_ID decimal(10,0)
);

8. Write a sql statement to create a table named countries including columns country_id,country_name and region_id and make sure that no duplicate data against column country_id will be allowed at the time of insertion.

Click me to see the solution of Exercise No.- 8. Click me again to collapse.

CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID varchar(2) NOT NULL,
COUNTRY_NAME varchar(40) NOT NULL,
REGION_ID decimal(10,0) NOT NULL,
UNIQUE(COUNTRY_ID)
);

9. This example can also be written like below.

Click me to see the solution of Exercise No.- 9. Click me again to collapse.

CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID varchar(2) NOT NULL UNIQUE,
COUNTRY_NAME varchar(40) NOT NULL,
REGION_ID decimal(10,0) NOT NULL
);

10. Write a sql statement to create a table named jobs including columns job_id, job_title, min_salary and max_salary, and make sure that, the default value for job_title is blank and min_salary is 8000 and max_salary is NULL will be entered automatically at the time of insertion if no value assigned for the specified columns.

Click me to see the solution of Exercise No.- 10. Click me again to collapse.

CREATE TABLE IF NOT EXISTS jobs ( 
JOB_ID varchar(10) NOT NULL UNIQUE, 
JOB_TITLE varchar(35) NOT NULL DEFAULT ' ', 
MIN_SALARY decimal(6,0) DEFAULT 8000, 
MAX_SALARY decimal(6,0) DEFAULT NULL
);

11. Write a sql statement to create a table named countries including columns country_id, country_name and region_id and make sure that the country_id column will be a key field which will not contain any duplicate data at the time of insertion.

Click me to see the solution of Exercise No.- 11. Click me again to collapse.

CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID varchar(2) NOT NULL UNIQUE PRIMARY KEY,
COUNTRY_NAME varchar(40) NOT NULL,
REGION_ID decimal(10,0) NOT NULL
);

12. The above statement can be written like below.

Click me to see the solution of Exercise No.- 12. Click me again to collapse.

CREATE TABLE countries (
COUNTRY_ID varchar(2) NOT NULL DEFAULT '',
COUNTRY_NAME varchar(40) DEFAULT NULL,
REGION_ID decimal(10,0) DEFAULT NULL,
PRIMARY KEY (COUNTRY_ID));

13. Write a sql statement to create a table countries including columns country_id, country_name and region_id and make sure that the column country_id will be unique and store an auto incremented value.

Click me to see the solution of Exercise No.- 13. Click me again to collapse.

CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID SERIAL PRIMARY KEY, 
COUNTRY_NAME varchar(40) NOT NULL, 
REGION_ID decimal(10,0) NOT NULL 
);

14. Write a sql statement to create a table countries including columns country_id, country_name and region_id and make sure that the combination of columns country_id and region_id will be unique.

Click me to see the solution of Exercise No.- 14. Click me again to collapse.

CREATE TABLE IF NOT EXISTS countries_n (
COUNTRY_ID varchar(2) NOT NULL UNIQUE DEFAULT '',
COUNTRY_NAME varchar(40) DEFAULT NULL,
REGION_ID decimal(10,0) NOT NULL,
PRIMARY KEY (COUNTRY_ID,REGION_ID));

15. Write a sql statement to create a table job_history including columns employee_id, start_date, end_date, job_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 job_id contain only those values which are exists 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
Indexes:
    "jobs_pkey" PRIMARY KEY, btree (job_id)

Click me to see the solution of Exercise No.- 15. Click me again to collapse.

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)
);

16. Write a 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 columns combined by department_id and manager_id columns contain only those unique combination values, which combinations are exists in the departments table.

Assume the structure of departments table below.

     Column      |         Type          |           Modifiers
-----------------+-----------------------+--------------------------------
 department_id   | numeric(4,0)          | not null
 department_name | character varying(30) | not null
 manager_id      | numeric(6,0)          | not null default NULL::numeric
 location_id     | numeric(4,0)          | default NULL::numeric
Indexes:
    "departments_pkey" PRIMARY KEY, btree (department_id, manager_id)

Click me to see the solution of Exercise No.- 16. Click me again to collapse.

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,MANAGER_ID) 
REFERENCES  departments(DEPARTMENT_ID,MANAGER_ID)
);

17. Write a 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 are exists 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 are exists 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)

Click me to see the solution of Exercise No.- 17. Click me again to collapse.

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)
);

18. Write a 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 are exists in the jobs table. The specialty of the statement is that, The ON UPDATE CASCADE action allows you to perform cross-table update and ON DELETE RESTRICT action reject the deletion. The default action is ON DELETE RESTRICT.

Assume that the following is the structure of the table jobs.

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     | integer               | not null
 job_title  | character varying(35) | not null default ' '::character varying
 min_salary | numeric(6,0)          | default 8000
 max_salary | numeric(6,0)          | default NULL::numeric
Indexes:
    "jobs_pkey" PRIMARY KEY, btree (job_id)

Click me to see the solution of Exercise No.- 18. Click me again to collapse.

CREATE TABLE IF NOT EXISTS employees ( 
EMPLOYEE_ID INTEGER NOT NULL PRIMARY KEY, 
FIRST_NAME varchar(20) DEFAULT NULL, 
LAST_NAME varchar(25) NOT NULL, 
JOB_ID INTEGER NOT NULL, 
SALARY decimal(8,2) DEFAULT NULL, 
FOREIGN KEY(JOB_ID) 
REFERENCES  jobs(JOB_ID) 
ON UPDATE CASCADE ON DELETE RESTRICT
);

19. Write a 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 are exists in the jobs table. The specialty of the statement is that, The ON DELETE CASCADE that lets you allow to delete records in the employees(child) table that refer to a record in the jobs(parent) table when the record in the parent table is deleted and the ON UPDATE RESTRICT actions reject any updates.

Assume that the following is the structure of the table jobs.

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     | integer               | not null
 job_title  | character varying(35) | not null default ' '::character varying
 min_salary | numeric(6,0)          | default 8000
 max_salary | numeric(6,0)          | default NULL::numeric
Indexes:
    "jobs_pkey" PRIMARY KEY, btree (job_id)

Click me to see the solution of Exercise No.- 19. Click me again to collapse.

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, 
JOB_ID INTEGER NOT NULL, 
SALARY decimal(8,2) DEFAULT NULL, 
FOREIGN KEY(JOB_ID) 
REFERENCES  jobs(JOB_ID) 
ON DELETE CASCADE ON UPDATE RESTRICT
);

20. Write a 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 are exists 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.

Assume that the following is the structure of two table jobs.

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     | integer               | not null
 job_title  | character varying(35) | not null default ' '::character varying
 min_salary | numeric(6,0)          | default 8000
 max_salary | numeric(6,0)          | default NULL::numeric
Indexes:
    "jobs_pkey" PRIMARY KEY, btree (job_id)

Click me to see the solution of Exercise No.- 20. Click me again to collapse.

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
);

21. Write a 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 are exists in the jobs table. The specialty of the statement is that, The ON DELETE NO ACTION and the ON UPDATE NO ACTION actions will reject the deletion and any updates.

Assume that the following is the structure of two table jobs.

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     | integer               | not null
 job_title  | character varying(35) | not null default ' '::character varying
 min_salary | numeric(6,0)          | default 8000
 max_salary | numeric(6,0)          | default NULL::numeric
Indexes:
    "jobs_pkey" PRIMARY KEY, btree (job_id)

Click me to see the solution of Exercise No.- 21. Click me again to collapse.

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 NOT NULL, 
SALARY decimal(8,2) DEFAULT NULL, 
FOREIGN KEY(JOB_ID) 
REFERENCES  jobs(JOB_ID)
ON DELETE NO ACTION 
ON UPDATE NO ACTION
);