Skip to content

Latest commit

 

History

History
74 lines (55 loc) · 2.14 KB

pcc_020-020_problem2_sql_live_walkthrough.md

File metadata and controls

74 lines (55 loc) · 2.14 KB

Paylocity Coding Challenge: Problem 2 of 2

Eric Milgram, PhD

ScientificProgrammer/PaylocityCodingChallenge
Created: December 21, 2021
Last Updated: 2022-01-19 05:54:08

SQL Exercise - Live Walkthrough

Figure 1, which is shown below, illustrates an entity relationship diagram (ERD) for a logical data model consisting of three tables named Company, Employee, and Position. The ERD represented in Figure 1 also contains the following informational elements, which are important for constructing a physical data model.

  1. Field names for each table

  2. Data types for each field

  3. Field constraints, including primary keys and foreign keys

  4. Foreign key relationships between tables, including directionality,
    cardinality, and optionality

FIGURE 1: Sample ERD for logical data model for a company/employee relational database

For the data model illustrated in Figure 1, please complete the following actions.

  1. Write a SQL query to generate a result set for a report that contains the
    average salary for all employees.

  2. Update the previous query to generate a result set showing the average
    salary for all employees within each company.

  3. Let's assume your previous query was long running. Please describe the
    process you would use to find the root causes of the query's sluggishness?