sql-challenge

Scenario

You're a data engineer at a company. You're tasked with modeling, engineering and analysis of employee's who worked at the company during the 1980's and 1990's.

You will have to design the tables, import the CSV files into a SQL Database and then perform analysis within SQL.

---

Data Modeling

• Looking into the CSV files provided, I gained an understanding of the columns and data types required for each table within the Database.
• I could understand where Primary Keys would be needed, and then how these would then connect to the other table columns as Foreign Keys.
• Lastly I drew up an ERD (Entity Relationship Diagram) using the QuickDataBaseDiagram application. (Model can be seen below)

---

Data Engineering

• Using my ERD, I then manually created the SQL Query to create each of the tables within the Database. I was aware to the export tool available with the QuickDBD application to create the PostgreSQL query directly from my ERD, however I was keen to practice and gain some additional experience of writing SQL queries.
• I created each of the six tables, providing all column names, Data types, Primary Keys and referencing any required Foreign Keys within the tables.
• With the tables created, I used the built-in pgAdmin 4 "Import/Export data.." tool to import each of the CSV files into the created tables.
• Once I confirmed all had imported correctly, I could then set about creating my analysis query.

---

Data Analysis

• I was provided 8 requirements to create analysis using SQL queries and the previously imported data.

• There 8 requirements were:

  1. List the employee number, last name, first name, sex, and salary of each employee.
  2. List the first name, last name, and hire date for the employees who were hired in 1986.
  3. List the manager of each department along with their department number, department name, employee number, last name, and first name.
  4. List the department number for each 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 employee whose first name is Hercules and whose last name begins with the letter B.
  6. List each employee in the Sales department, including their employee number, last name, and first name.
  7. List each employee in the 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 employee last names (that is, how many employees share each last name).

• The 8 SQL queries can be found within the "analysis_query.sql" file here

---

References

• Data Generated by Mockaroo LLC (2022) - Realistic Data Generator
• Scenario provided by EdX Boot Camps LLC
• ERD created on the QuickDatabaseDiagrams website