sql-challenge

This respository examines information about employees hired at Pewlett Hackard during the 1980s and 1990s. Code to assess specific questions about employees, their jobs, their salaries, their name relationships, their hiring dates, etc. is coded in .sql.

Data Modeling

Table relationships for the database are mapped into a 3NF entity relationship diagram (ERD) in the .png file labelled ERD_employeedata.png. The data in these 6 tables is found in the data folder. Data is in .csv file format.

Data Engineering

The .sql code to import the CSV files into a SQL database is stored in the creating_tables.sql file. This code establishes the tables in the order they should be created. It codes primary and secondary keys that correspond to information in each of the 6 .csv files in the data folder. The tables are named according to the corresponding .csv files, for ease of importing the correct data into the correct file.

The following tables hold the following information.

departments

• dept_no - Department ID number (5 characters long), primary key
• dept_name - Name of the department (varchar/string)

titles

• title_id - Employee job ID number (5 characters long), primary key
• title - Employee job title (varchar/string)

employees

• emp_no - Employee ID number (5 characters long), primary key
• emp_title - Employee job ID number (5 characters long), foreign key
• birth_date - Date of employee's birth (date)
• first_name - Employee first name (varchar/string)
• last_name - Employee last name (varchar/string)
• sex - Employee sex (5 characters long)
• hire_date - Date of employee's hiring at Pewlett Hackard (date)

dept_employees/Department Employees

• emp_no - Employee ID number (5 characters long), foreign key
• dept_no - Department ID number (5 characters long), foreign key

dept_manager/Department Managers

• dept_no - Department ID number (5 characters long), foreign key
• emp_no - Employee ID number (5 characters long), foreign key

salaries

• emp_no - Employee ID number (5 characters long), foreign key
• salary - Employee salary (integer)

Please review the ERD .png file in the repository for more detail about the table relationships.

Data Analysis

The .sql code to analyze the established employee database is stored in the emp_queries.sql file. This code runs queries to answer the following questions:

1. List the employee number, last name, first name, sex, and salary of each employee hired at Pewlett Hackard during the 1980s and 1990s.
2. List the first name, last name, and hire date for Pewlett Hackard employees who were hired in 1986.
3. List the manager of each Pewlett Hackard department along with their department number, department name, employee number, last name, and first name.
4. List the department number for each Pewlett Hackard employee along with that employee’s employee number, last name, first name, and department name.
5. List first name, last name, and sex of each Pewlett Hackard employee whose first name is Hercules and whose last name begins with the letter B.
6. List each employee in the Pewlett Hackard Sales department, including their employee number, last name, and first name.
7. List each employee in the Pewlett Hackard Sales and Development departments, including their employee number, last name, first name, and department name.
8. List the frequency counts, in descending order, of all the Pewlett Hackard employee last names.