Data analytics

Part 1: SQL
Using staff, sections, locations tables from lectures:
1. Provide a SQL query that outputs the following information for all staff members who were
hired in 2003 and 2004, and who work in sections belonging to the ‘Hardware’ category –
a. First_name
b.

Last_name
c. Section
d. Hire_date
e.

Salary

f. Maximum Salary within his/her section. Name this column as max_sal
g.

Difference between max_sal and salary. Name this column as salary_diff
h. Label staff members working in region ‘United States’ as American, those working in
region ‘Asia’ as Asian, and those working in region ‘Canada’ as Canadian.

Put these
labels in a column named as nationality

2. Using the table created in previous question as data source, provide a SQL query that
displays the following output containing count of Americans, Asians and Canadians:
Part 2: Tableau
1. Using AmazingMart dataset from lectures, create the following four worksheets for orders
belonging to the ‘Technology’ category.

Each worksheetshould be filterable by year, and none
should contain more than one chart.
Worksheet 1: Total sales and profit margin on map of Europe (at state level of granularity)
Worksheet 2: Total profit per customer in each sub-category (at state level of granularity)
Worksheet 3: Sales (expressed as percentage of total sales) in each sub-category
Worksheet 4: Monthly sales and monthly targets on a dual axis chart
2. Combine the four worksheets into a dashboard and implement action filter between
Worksheet 1 and Worksheet 2.

Again, all visualizations in your dashboard should be filterable
by year.
3. Provide a Data Analysis Report (max 1000 words), containing four meaningful insights
extracted from visualizations. Include at least one visualization snapshot (either worksheet or
dashboard) with each insight.
Using your judgement, present only those insights that you believe will be most valuable to
the management of Amazing Mart for future decision-making.

Report will be graded on the
quality of insights, and not the quantity of words.
This task is similar to creating ‘Story’ within Tableau, however, a report document gives more
space to elaborate on insights.