sql-challenge

Module 9 Assignment

Files in the repository and their functions

• In the data folder, there are CSV files that are the only remaining records of the employees, departments, and managers of a company called Pewlett Hackard.

  • departments.csv - contains department numbers and department names
  • dept_emp.ssv - cross references employee numbers and department numbers
  • dept_manager.csv - contains department numbers and department manager IDs
  • employees.csv - contains employee numbers, employee title ID, birth date, first name, last name, sex, and hire date
  • salaries.csv - contains employee ID number and the salary they get
  • titles.csv - contains the ID number associated with each position title name

• In the main folder, there is a TableSchema.sql0 which creates and initiates table schema with the proper columns, primary keys, composite keys, and foreign keys to establish proper relationships

• Also in the main folder, an image file displaying the above information in a graphical / diagram format exists as well. This is the ERD (entity relationship diagram).

• The main code file PewlettHackwardEmployeeQuery.sql performs the below operations

  • Deletes the 6 tables if they exist to make sure we are starting with fresh data
  • Creates tables named dept_manager, employees, departments, salaries, titles, and dept_emp with columns matching the CSV file columns with appropriate data types
  • Using pgAdmin, I used the Import function to insert the data from CSV files in the data folder into the tables created above
  • Use the select function and proper arguments to list the employee number, last name, first name, sex, and salary of each employee

  

  • Use the select function and proper arguments to list the first name, last name, and hire date for the employees who were hired in 1986

  

  • Use the select function and proper arguments to list the manager of each department along with their department number, department name, employee number, last name, and first name

  

  • Use the select function and proper arguments to list the department number for each employee along with that employee’s employee number, last name, first name, and department name

  

  • Use the select function and proper arguments to list first name, last name, and sex of each employee whose first name is Hercules and whose last name begins with the letter B.

  

  • Use nested select functions and proper arguments to list each employee in the Sales department, including their employee number, last name, and first name.

  

  • Use inner joins to combine data from multiple tables in order to extract information to list each employee in the Sales and Development departments, including their employee number, last name, first name, and department name.

  

  • Use the select function, group by function, and order by function to properly capture, order, and display a list of the frequency counts, in descending order, of all the employee last names (that is, how many employees share each last name).