MySQL

SQL Assignment:

As part of this SQL assignment, you are required to design SQL queries that will extract the necessary data from a relational database to generate insights for a Company Management System Dashboard. The dashboard will focus on key areas such as employees, departments, projects, contracts, and client management.

1. Department Overview

• Task: Write SQL queries to generate the following metrics:
  1. Number of Departments: Write a query to count the total number of departments in the company.
  2. Department Names and Locations: Write a query to list all departments along with their respective locations.
  3. Employees per Department: Write a query to retrieve the number of employees in each department.

2. Employee Overview

• Task: Write SQL queries to pull employee-related insights:
  1. Number of Employees: Write a query to count the total number of employees in the system.
  2. Employee Salaries: Write a query to retrieve the average, minimum, and maximum salary for employees, grouped by department (for a stacked bar chart).
  3. Employee Hire Dates: Write a query to retrieve the number of employees hired over time, grouped by month.

3. Project Overview

• Task: Use SQL to pull project-related insights:
  1. Total Number of Projects: Write a query to count how many projects are currently active in the system.
  2. Projects by Employee: Write a query that retrieves the list of employees and the number of projects they are assigned to.
  3. Projects with End Date Violations: Write a query to identify projects where the end_date is before the start_date (CHECK constraint violation).

4. Client Overview

• Task: Write SQL queries to manage client-related data:
  1. List of Clients: Write a query to list all clients along with their emails and ensure no duplicate clients exist (use the unique constraint on client_name).
  2. Client Contracts: Write a query to retrieve the total amount of contracts for each client.
  3. Recently Registered Clients: Write a query that lists all clients who have registered in the last 6 months.

5. Contract and Invoice Overview

• Task: Use SQL queries to manage contract and invoice data:
  1. Total Contract Value: Write a query that retrieves the total value of all contracts.
  2. Outstanding Invoices: Write a query that lists all invoices where the amount_due is greater than 0.
  3. Invoices by Project: Write a query that retrieves the total value of invoices for each project.

Key Visuals and Corresponding SQL Queries:

Note: All dashboard queries must work against current month or current year filters

1. Stacked Bar Chart - Employee Salaries by Department (Max, Min, Avg)

   • Query: Write an SQL query to retrieve the maximum, minimum, and average salaries for employees, grouped by department, from the `EMPLOYEE` and `DEPARTMENT` tables.
   • X-Axis: Department names.
   • Y-Axis: Salary values.
   • Bars: Three stacked bars for each department representing maximum, minimum, and average salary values.

2. Pie Chart - Employee Birthdates by Month

   • Query: Write a query to count the number of employees born in each month (using the birthdate field in the `EMPLOYEE` table), grouped by month.
   • Sections: Months with the count of employees born in each month as values.

3. Line Graph - Projects Count by Month

   • Query: Write a query to retrieve the number of projects started per month from the `PROJECTS` table.
   • X-Axis: Project Start date (grouped by month).
   • Y-Axis: Number of projects.

4. Table - Client Contract Summary

   • Query: Write an SQL query to retrieve contract details from the `CONTRACT` and `CLIENT` tables, showing client names, contract dates, and total contract amounts.
   • Columns: Client Name, Contract Date, Total Amount.

GOOD LUCK WITH YOUR ASSIGNMENT!!!

Don't forget to contact us if you need any further assistance with your assignments, and most importantly, for a manual review and approval of your work.

UX Screens for Company Management System Dashboard Mockup