Reference no: EM132713182
Assignment
Background: Congratulations on having just landed a new job in Company Z's Compliance Department! As your first assignment, you have been asked to verify Company Z's employees are complying with all software license agreements. This follows management concerns that employees have been inappropriately sharing licenses and using licenses beyond their expiration date.
Files relating to a sample of employees have been provided to complete your review. Your mentor has provided you guidance for the assignment, in the form of five questions. Each question requires the creation of SQL code. Once you've created the code, your Compliance Reporting Dept. will use your code to produce web-based reports.
All data required for this analysis is in F20_Optional_Extra_Credit_Data.xlsx
This file contains three sets of data (one tab for each), with the data fitting these assumptions:
1. Employees: One record per employee. File includes each employees User ID and Office Location.
2. User Licenses: One record per employee, per software license. File includes User ID, Software Name, license start and end date.
3. User Access Log: One record per user access date. File includes User ID, Access Data, and Software Name.
Assignment:
1. Load the data from Excel into MS-SQL Server or Access. You can use either database, but not both, to complete the assignment.
2. Verify the data sets provided are accurate. Identify any anomalies.
3. Answer the 5 questions below. For each question, provide both a screenshot out of either SSMS or Access and the SQL code. As with Assignment #3, the SQL code must be copy/paste-able and the column headings must be descriptive.
Questions
1. How many Employees are based at each Office Location?
2. How many Employees in Australia have a SQL Server license?
3. How many Employees have both a Tableau license and a SQL Server license? The SQL should produce one number.
4. What is the earliest and latest Access Date for each software application?
5. Analyse the data to identify all instances where software has been used inappropriately. List User ID, Software accessed, date of the violation, license start date, license end date, violation type. The violation types are "Expired License" and "No License" - Hint: Case stmt. Sort by User ID.
Attachment:- Optional Data.rar