Conduct a research project on employees of the corporation from the 1980s and 1990s.
Six csv files provided (departments, dept_emp, dept_manager, employees, salaries, titles)
Inspect the CSVs and sketchout and ERD of the tables.
- Use the information you have to create a table schema for each of the six CSV files.
- Specify data types, primary keys, foreign keys, and other constraints.
- Import each CSV file into the corresponding SQL table.
- Once you have a complete database, the following needs to be completed:
-
List the following details of each employee: employee number, last name, first name, gender, and salary.
-
List employees who were hired in 1986.
-
List the manager of each department with the following information: department number, department name, the manager's employee number, last name, first name, and start and end employment dates.
-
List the department of each employee with the following information: employee number, last name, first name, and department name.
-
List all employees whose first name is "Hercules" and last names begin with "B."
-
List all employees in the Sales department, including their employee number, last name, first name, and department name.
-
List all employees in the Sales and Development departments, including their employee number, last name, first name, and department name.
-
In descending order, list the frequency count of employee last names, i.e., how many employees share each last name.
-
Import the SQL database into Pandas.
-
Create a bar chart of average salary by title.
-
You may also include a technical report in markdown format, in which you outline the data engineering steps taken in the homework assignment.
-
Create an image file of your ERD.
-
Create a
.sqlfile of your table schemata. -
Create a
.sqlfile of your queries. -
(Optional) Create a Jupyter Notebook of the bonus analysis.
-
Create and upload a repository with the above files to GitHub and post a link on BootCamp Spot.