A fictional company needs to do some research on employees during the 1980's to 1990's. The data is spread through six different CSV files. In order to accomplish this, we need to design tables that will hold the data from the csv files, import the csv files into the SQL database, and provide answers regarding the data. To accomplish this, we need to perfom data modeling, data engineering and data analysis.
Make sure that there is a folder called Employee_SQL. The you will need these csv files to import into pgADmin4. In that folder you will find:
1. departments.csv
2. dept_emp.csv
3. dept_manager.csv
4. employees.csv
5. salaries.csv
6. titles.csv
Data Modeling:
You can find the image in, "ERD_Pewlett_Hacckard.png" or right below:
For futher details on creating a data model, you can click on this link: https://app.quickdatabasediagrams.com/#/d/MDz0ma
1. Create a database.
- Right click on PostgrsSQL
- Click on create --> Database
- In the Database field type Pewlett_Hackard_DB
- In Owner field, keep postgres
- Leave comment field blank
- Click on save
2. Create the tables
- Right click on the database, "Pewlett_Hackard_DB"
- Click on Query Tool
- Open, "Schemata_Pewlett_Hackard.sql", to copy and paste each table creation
- After creating a table, import the appropriate csv file accordingly. After that, check the table to ensure that the data was properly imported:
* department table --> departments.csv
* titles --> titles.csv
* employees --> employees.csv
* department_manager --> dept_manager.csv
* department_employee --> dept_emp.csv
* salaries --> salaries.csv
1. Open, "Queries_Pewlett_Hackard.sql", file
2. Copy and paste each query section one at a time OR paste all of it and highlight one section at a time to run each query indvidually
Source Filter for date https://stackoverflow.com/questions/15817871/postgresql-filter-a-date-range