As a data engineer at Pewlett Hackard I researched information of employees hired in the 1980s and 1990s. All the information analyzed was obtained from six csv files containing tables with employee information, these were:
- titles: contains the title id and title name of each position in the company.
- departments: contains the department number and department names.
- employees: contains the employee number, employee title id, birth date, first name, last name, sex and hire date of each employee.
- dept_manager: contains the department number and employee number of all department managers.
- dept_emp: contains the employee number and department number of all employees.
- salaries: contains the employee number and salary of all employees.
I sketched an Entity Relationship Diagram of the tables with QuickDBD.
I created a table schema for each of the 6 csv files, I specified the data types, primary keys, foreign keys and other constraints. Then I imported each csv file into its corresponding SQL table.
- For the titles table I set the title id and title name as variable characters and set the title id as a primary key:
- For the departments table I set the department number and department name as variable characters and set the department number as a primary key:
- For the employees table I set the employee number as an integer, the employee title id, first name, last name, and sex as variable characters, and the birth date and hire date as dates. I set the employee number as a primary key and the employee title id as a foreign key that references title id from the titles table:
- For the dept_manager table I set the department number as a variable character and the employee number as an integer. I set the department number as a foreign key that references department number from the departments table. I also set the employee number as a foreign key that references employee number from the employees table:
- For the dept_emp table I set the employee number as an integer and the department number as a variable character. I set the employee number as a foreign key that references employee number from the employees table. I also set the department number as a foreign key that references department number from the departments table:
- For the salaries table I set the employee number and salary as integers. I set the employee number as a foreign key that references employee number from the employees table:
- I listed the employee number, last name, first name, sex and salary of all employees:
- I listed the first name, last name, and hire date for all employees hired in 1986:
- I listed the managers of all departments with their department number, department name, employee number, last name, and first name:
- I listed the department number of all employees with their employee number, last name, first name, and department name:
- I listed the first name, last name, and sex of all employees with Hercules as their first name and a last name that begins with B:
- I listed all the employees in the Sales department with their employee number, last name, and first name:
- I listed all employees in the Sales and Development departments with their employee number, last name, first name, and department name:
- I listed the frequency counts of all the employee last names from the most frequent last name to the least frequent: