Employee UserID OfficeLocation 2 UK 3 UK 4 UK 5 Australia 6 US 7 UK 8 US 10 Australia 12 UK 14 US 16 UK 17 US 18 US 20 Australia 22 Australia 25 UK 27 Australia 28 Australia 29 UK 30 Australia 32 US 33 US 34 US 35 Australia 36 US 38 US 41 Australia 44 UK 45 UK 46 Australia 47 US 48 UK 49 US 50 US 5...

The Answer is in the image, click to view ...

Employee UserID OfficeLocation 2 UK 3 UK 4 UK 5 Australia 6 US 7 UK 8 US

Fantastic news! We've Found the answer you've been seeking!

Question:

Employee

UserID	OfficeLocation
2	UK
3	UK
4	UK
5	Australia
6	US
7	UK
8	US
10	Australia
12	UK
14	US
16	UK
17	US
18	US
20	Australia
22	Australia
25	UK
27	Australia
28	Australia
29	UK
30	Australia
32	US
33	US
34	US
35	Australia
36	US
38	US
41	Australia
44	UK
45	UK
46	Australia
47	US
48	UK
49	US
50	US
52	US
53	US
54	UK
55	US
56	UK
57	Australia
59	UK
60	UK
63	UK
64	UK
68	UK
69	Australia
70	US
71	UK
72	US
74	US
75	UK
76	Australia
77	Australia
78	US
79	US
80	US
81	Australia
83	UK
86	US
87	UK
88	US
89	Australia
90	Australia
91	Australia
92	Australia
93	US
94	UK
95	US
96	Australia
98	UK
99	Australia

Access Log

UserID	AccessDate	Software
54	1/26/2019 0:00	Tableau
76	2/9/2019 0:00	SQL Server
48	2/21/2019 0:00	SQL Server
20	4/14/2019 0:00	Tableau
52	5/5/2019 0:00	Tableau
89	6/2/2019 0:00	Tableau
33	6/26/2019 0:00	SQL Server
38	6/30/2019 0:00	Tableau
71	7/6/2019 0:00	SQL Server
96	7/7/2019 0:00	Tableau
32	7/9/2019 0:00	Tableau
47	7/11/2019 0:00	SQL Server
86	7/11/2019 0:00	SQL Server
71	7/12/2019 0:00	SQL Server
12	8/1/2019 0:00	SQL Server
46	8/2/2019 0:00	Tableau
69	8/9/2019 0:00	Tableau
74	8/23/2019 0:00	Tableau
44	9/1/2019 0:00	SQL Server
68	9/3/2019 0:00	Tableau
45	9/9/2019 0:00	SQL Server
94	9/10/2019 0:00	Tableau
45	9/15/2019 0:00	Tableau
78	9/17/2019 0:00	SQL Server
34	9/18/2019 0:00	Tableau
44	9/21/2019 0:00	Tableau
10	9/22/2019 0:00	Tableau
5	10/3/2019 0:00	Tableau
80	10/8/2019 0:00	Tableau
96	10/23/2019 0:00	SQL Server
76	10/24/2019 0:00	Tableau
83	11/2/2019 0:00	Tableau
49	11/5/2019 0:00	SQL Server
18	11/10/2019 0:00	SQL Server
57	11/16/2019 0:00	Tableau
34	11/28/2019 0:00	SQL Server
55	12/2/2019 0:00	Tableau
81	12/4/2019 0:00	SQL Server
46	12/14/2019 0:00	SQL Server
71	12/19/2019 0:00	Tableau
71	12/23/2019 0:00	Tableau
36	1/10/2020 0:00	SQL Server
17	1/14/2020 0:00	SQL Server
22	1/18/2020 0:00	Tableau
49	2/6/2020 0:00	Tableau
7	2/8/2020 0:00	Tableau
63	2/14/2020 0:00	Tableau
53	2/19/2020 0:00	SQL Server
20	2/20/2020 0:00	SQL Server
90	2/24/2020 0:00	Tableau
70	2/25/2020 0:00	Tableau
70	2/27/2020 0:00	Tableau
8	3/17/2020 0:00	Tableau
16	3/17/2020 0:00	Tableau
54	3/17/2020 0:00	Tableau
60	3/17/2020 0:00	Tableau
63	3/23/2020 0:00	Tableau
32	3/24/2020 0:00	SQL Server
75	4/7/2020 0:00	SQL Server
63	4/9/2020 0:00	Tableau
63	4/9/2020 0:00	Tableau
56	4/18/2020 0:00	Tableau
22	4/27/2020 0:00	SQL Server
35	5/1/2020 0:00	Tableau
68	5/5/2020 0:00	SQL Server
60	5/12/2020 0:00	SQL Server
63	5/12/2020 0:00	Tableau
53	5/14/2020 0:00	Tableau
93	5/16/2020 0:00	Tableau
33	5/31/2020 0:00	Tableau
2	6/9/2020 0:00	Tableau
41	6/14/2020 0:00	SQL Server
25	6/19/2020 0:00	Tableau
2	6/20/2020 0:00	Tableau
79	6/21/2020 0:00	Tableau
3	7/1/2020 0:00	Tableau
16	7/5/2020 0:00	Tableau
69	7/9/2020 0:00	SQL Server
47	7/13/2020 0:00	Tableau
54	7/13/2020 0:00	Tableau
64	7/25/2020 0:00	SQL Server
56	7/31/2020 0:00	Tableau
86	7/31/2020 0:00	Tableau
71	8/2/2020 0:00	Tableau
38	8/3/2020 0:00	SQL Server
72	8/14/2020 0:00	Tableau
72	8/16/2020 0:00	Tableau
35	8/30/2020 0:00	SQL Server
56	9/30/2020 0:00	Tableau
71	9/30/2020 0:00	Tableau
92	10/12/2020 0:00	Tableau
2	10/15/2020 0:00	Tableau
16	10/21/2020 0:00	Tableau
98	11/24/2020 0:00	Tableau

License

UserID	Software	LicenseStartDate	LicenseEndDate
44	Tableau	1/20/2019 0:00	1/19/2020 0:00
76	SQL Server	1/22/2019 0:00	1/21/2020 0:00
17	Tableau	1/25/2019 0:00	1/24/2020 0:00
18	Tableau	1/25/2019 0:00	1/24/2020 0:00
10	Tableau	1/26/2019 0:00	1/25/2020 0:00
54	Tableau	1/26/2019 0:00	1/25/2020 0:00
47	SQL Server	1/27/2019 0:00	1/26/2020 0:00
34	Tableau	1/29/2019 0:00	1/28/2020 0:00
48	SQL Server	2/12/2019 0:00	2/11/2020 0:00
89	Tableau	2/14/2019 0:00	2/13/2020 0:00
93	SQL Server	2/16/2019 0:00	2/15/2020 0:00
33	SQL Server	2/20/2019 0:00	2/19/2020 0:00
55	Tableau	3/1/2019 0:00	2/28/2020 0:00
63	Tableau	3/12/2019 0:00	3/11/2020 0:00
68	Tableau	3/12/2019 0:00	3/11/2020 0:00
81	SQL Server	3/12/2019 0:00	3/11/2020 0:00
86	SQL Server	3/13/2019 0:00	3/12/2020 0:00
78	SQL Server	3/19/2019 0:00	3/18/2020 0:00
57	Tableau	3/26/2019 0:00	3/25/2020 0:00
71	SQL Server	3/31/2019 0:00	3/30/2020 0:00
94	Tableau	4/3/2019 0:00	4/2/2020 0:00
20	Tableau	4/4/2019 0:00	4/3/2020 0:00
50	Tableau	4/6/2019 0:00	4/5/2020 0:00
77	SQL Server	4/6/2019 0:00	4/5/2020 0:00
74	Tableau	4/19/2019 0:00	4/18/2020 0:00
18	SQL Server	4/21/2019 0:00	4/20/2020 0:00
83	Tableau	4/27/2019 0:00	4/26/2020 0:00
52	Tableau	4/29/2019 0:00	4/28/2020 0:00
91	Tableau	5/1/2019 0:00	4/30/2020 0:00
78	Tableau	5/5/2019 0:00	5/4/2020 0:00
12	Tableau	5/7/2019 0:00	5/6/2020 0:00
12	SQL Server	5/19/2019 0:00	5/18/2020 0:00
46	Tableau	5/22/2019 0:00	5/21/2020 0:00
68	SQL Server	5/22/2019 0:00	5/21/2020 0:00
90	Tableau	5/23/2019 0:00	5/22/2020 0:00
45	SQL Server	5/26/2019 0:00	5/25/2020 0:00
49	Tableau	5/28/2019 0:00	5/27/2020 0:00
47	Tableau	5/31/2019 0:00	5/30/2020 0:00
93	Tableau	6/1/2019 0:00	5/31/2020 0:00
96	Tableau	6/3/2019 0:00	6/2/2020 0:00
71	Tableau	6/10/2019 0:00	6/9/2020 0:00
80	Tableau	6/10/2019 0:00	6/9/2020 0:00
32	Tableau	6/12/2019 0:00	6/11/2020 0:00
38	Tableau	6/15/2019 0:00	6/14/2020 0:00
69	Tableau	6/15/2019 0:00	6/14/2020 0:00
25	Tableau	6/22/2019 0:00	6/21/2020 0:00
34	SQL Server	6/23/2019 0:00	6/22/2020 0:00
76	Tableau	6/24/2019 0:00	6/23/2020 0:00
70	Tableau	7/1/2019 0:00	6/30/2020 0:00
96	SQL Server	7/1/2019 0:00	6/30/2020 0:00
17	SQL Server	7/8/2019 0:00	7/7/2020 0:00
32	SQL Server	7/10/2019 0:00	7/9/2020 0:00
45	Tableau	7/10/2019 0:00	7/9/2020 0:00
53	Tableau	7/11/2019 0:00	7/10/2020 0:00
80	SQL Server	7/11/2019 0:00	7/10/2020 0:00
88	Tableau	7/20/2019 0:00	7/19/2020 0:00
5	Tableau	7/30/2019 0:00	7/29/2020 0:00
22	Tableau	7/30/2019 0:00	7/29/2020 0:00
87	Tableau	7/30/2019 0:00	7/29/2020 0:00
99	SQL Server	8/2/2019 0:00	8/1/2020 0:00
27	SQL Server	8/3/2019 0:00	8/2/2020 0:00
69	SQL Server	8/8/2019 0:00	8/7/2020 0:00
56	Tableau	8/10/2019 0:00	8/9/2020 0:00
29	SQL Server	8/12/2019 0:00	8/11/2020 0:00
14	SQL Server	8/15/2019 0:00	8/14/2020 0:00
44	SQL Server	8/17/2019 0:00	8/16/2020 0:00
59	SQL Server	8/20/2019 0:00	8/19/2020 0:00
72	Tableau	8/20/2019 0:00	8/19/2020 0:00
3	SQL Server	8/31/2019 0:00	8/30/2020 0:00
30	Tableau	9/4/2019 0:00	9/3/2020 0:00
33	Tableau	9/5/2019 0:00	9/4/2020 0:00
8	Tableau	9/6/2019 0:00	9/5/2020 0:00
89	SQL Server	9/7/2019 0:00	9/6/2020 0:00
28	Tableau	9/11/2019 0:00	9/10/2020 0:00
41	SQL Server	9/12/2019 0:00	9/11/2020 0:00
49	SQL Server	9/12/2019 0:00	9/11/2020 0:00
2	Tableau	9/20/2019 0:00	9/19/2020 0:00
16	Tableau	9/21/2019 0:00	9/20/2020 0:00
36	SQL Server	10/5/2019 0:00	10/4/2020 0:00
77	Tableau	10/16/2019 0:00	10/15/2020 0:00
92	Tableau	10/18/2019 0:00	10/17/2020 0:00
64	SQL Server	10/20/2019 0:00	10/19/2020 0:00
81	Tableau	10/21/2019 0:00	10/20/2020 0:00
95	Tableau	10/21/2019 0:00	10/20/2020 0:00
46	SQL Server	10/24/2019 0:00	10/23/2020 0:00
6	Tableau	10/28/2019 0:00	10/27/2020 0:00
60	SQL Server	10/29/2019 0:00	10/28/2020 0:00
86	Tableau	11/9/2019 0:00	11/8/2020 0:00
38	SQL Server	11/13/2019 0:00	11/12/2020 0:00
98	Tableau	11/16/2019 0:00	11/15/2020 0:00
54	SQL Server	11/22/2019 0:00	11/21/2020 0:00
35	Tableau	12/9/2019 0:00	12/8/2020 0:00
75	SQL Server	12/10/2019 0:00	12/9/2020 0:00
7	Tableau	12/11/2019 0:00	12/10/2020 0:00
20	SQL Server	12/15/2019 0:00	12/14/2020 0:00
79	Tableau	12/16/2019 0:00	12/15/2020 0:00
35	SQL Server	12/21/2019 0:00	12/20/2020 0:00
53	SQL Server	12/23/2019 0:00	12/22/2020 0:00
4	Tableau	12/31/2019 0:00	12/30/2020 0:00

Background: Congratulations on having just landed a new job in Company A's Compliance Department! As your first assignment, you have been asked to verify Company A'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 the 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 S22_Optional_Extra_Credit_Data.xlsx

This file contains three sets of data (one tab for each), with the data fitting these assumptions:

Employees: One record per employee. File includes each employees User ID and Office Location.
User Licenses: One record per employee, per software license. File includes User ID, Software Name, license start and end date.
User Access Log: One record per user access date. File includes User ID, Access Data, and Software Name.

Assignment:

Load the data from Excel into MS-SQL Server using the SQL Server Import and Export data wizard. Follow the video provided with the assignment if you need assistance.
Verify the data sets provided are accurate. Identify any anomalies. (1 pt)
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

How many Employees are based at each Office Location? (1 pt)

Screenshot	SQL Code
<>	<>

How many Employees in Australia have a SQL Server license? (2 pt)

Screenshot	SQL Code
<>	<>

How many Employees have both a Tableau license and a SQL Server license? The SQL should produce one number. (2 pt)

Screenshot	SQL Code
<>	<>

What is the earliest and latest Access Date for each software application? (1 pt)

Screenshot	SQL Code
<>	<>

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. (3 pt)