As part of this exercise, you need to take care of loading data using
You can connect to the database using the following details in the environment provided by us.
Database Name: YOUR_OS_USER_hr_db
User Name: YOUR_OS_USER_hr_user
Password: YOUR_OS_USER_PASSWORD (provided by us).
If you are using your own environment, make sure to create database for storing HR Data.
CREATE DATABASE hr_db;
CREATE USER hr_user WITH PASSWORD 'hr_password';
GRANT ALL ON DATABASE hr_db TO hr_user;
GRANT pg_read_server_files TO hr_user;
CREATE TABLE employees
( employee_id INTEGER
, first_name VARCHAR(20)
, last_name VARCHAR(25)
, email VARCHAR(25)
, phone_number VARCHAR(20)
, hire_date DATE
, job_id VARCHAR(10)
, salary NUMERIC(8,2)
, commission_pct NUMERIC(2,2)
, manager_id INTEGER
, department_id INTEGER
CREATE UNIQUE INDEX emp_emp_id_pk
ON employees (employee_id) ;
ALTER TABLE employees ADD
PRIMARY KEY (employee_id);
Check for delimiters (record as well as field).
Check whether header exists or not.
Ensure number of fields for the table and data being loaded are same or not.
Load data into the table using
COPY Command. The file is under
Validate by running these queries. You can also use SQL Workbench to run the queries to validate whether data is loaded successfully or not.
SELECT * FROM employees LIMIT 10;
SELECT count(1) FROM employees;