It’s been two weeks since you were hired as a new data engineer at Pewlett Hackard (a fictional company). Your first major task is to do a research project about people whom the company employed during the 1980s and 1990s. All that remains of the employee database from that period are six CSV files. For this project, you’ll design the tables to hold the data from the CSV files, import the CSV files into a SQL database, and then answer questions about the data. That is, you’ll perform data modeling, data engineering, and data analysis, respectively.
A fundamental data modeling approach known as Entity-Relationship Diagrams (ERD) was employed to structure the employee data. This technique facilitated the identification of six distinct entities or tables within the employee database: namely, employees, departments, salaries, titles, department managers, and department employees. Comprehensive details about the attributes and data types associated with each entity were also provided. The final step involved creating an ER diagram that visually represents the relationships between these entities/objects, including primary and foreign keys within the database. For a comprehensive understanding of the employee database, you can access the detailed description by following this link and downloading the corresponding txt file: Employees_ERD.txt.
The visual representation of the ER diagram is as follows:
| app.quickdatabasediagrams.com | pgAdmin 4 |
|---|---|
 |
 |
Utilizing the provided information, a table schema was meticulously crafted for each of the six CSV files. This process encompassed defining data types, establishing primary keys, configuring foreign keys, and implementing necessary constraints. The arrangement of the tables was meticulously organized to align with the hierarchy of primary and foreign key relationships.
For a direct view of the detailed schema file, you can access it via the following link: employees_schema.sql.
After completing the importing process a Postgresql analysiss was perfomed and you can find the full query in this file employees_query.sql
The analysis query was executed and subsequently presented in the following formats:
- The query to list the following details of each employee: employee number, last name, first name, sex, and salary
SELECT employees.emp_no, employees.last_name, employees.first_name, employees.sex, salaries.salary
FROM employees
JOIN salaries
ON employees.emp_no = salaries.emp_no;
- The query to list first name, last name, and hire date for employees who were hired in 1986.
SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date BETWEEN '1986-01-01' AND '1987-01-01';
- The query list the manager of each department with the following information: department number, department name, the manager's employee number, last name, first name.
SELECT departments.dept_no, departments.dept_name, dept_manager.emp_no, employees.last_name, employees.first_name
FROM departments
JOIN dept_manager
ON departments.dept_no = dept_manager.dept_no
JOIN employees
ON dept_manager.emp_no = employees.emp_no;
- The query to list the department of each employee with the following information: employee number, last name, first name, and department name.
SELECT dept_emp.emp_no, employees.last_name, employees.first_name, departments.dept_name
FROM dept_emp
JOIN employees
ON dept_emp.emp_no = employees.emp_no
JOIN departments
ON dept_emp.dept_no = departments.dept_no;
- The query to list first name, last name, and sex for employees whose first name is "Hercules" and last names begin with "B."
SELECT first_name, last_name,sex
FROM employees
WHERE first_name = 'Hercules'
AND last_name LIKE 'B%';
- The query to list all employees in the Sales department, including their employee number, last name, first name, and department name.
SELECT dept_emp.emp_no, employees.last_name, employees.first_name, departments.dept_name
FROM dept_emp
JOIN employees
ON dept_emp.emp_no = employees.emp_no
JOIN departments
ON dept_emp.dept_no = departments.dept_no
WHERE departments.dept_name = 'Sales';
- The query to list all employees in the Sales and Development departments, including their employee number, last name, first name, and department name.
SELECT dept_emp.emp_no, employees.last_name,employees.first_name, departments.dept_name
FROM dept_emp
JOIN employees
ON dept_emp.emp_no = employees.emp_no
JOIN departments
ON dept_emp.dept_no = departments.dept_no
WHERE departments.dept_name = 'Sales'
OR departments.dept_name = 'Development';
- In descending order, list the frequency count of employee last names, i.e., how many employees share each last name.
SELECT last_name,
COUNT(last_name) AS "shared_last_name"
FROM employees
GROUP BY last_name
ORDER BY
COUNT(last_name) DESC;
