Computer
Lab - Practical Question Bank
FACULTY
OF COMMERCE,
OSMANIA
UNIVERSITY
------------------------------------------------------------------------------------------
B.Com
(Business Analytics) III Semester
Data
Analytics Modeling
Time:
60 Minutes
Record : 10
Skill
Test : 15
Viva - Voce : 10
Total
Marks : 35
MS
Excel or Power query
Table
for 1-10 Questions
|
Deptname – EmpID
|
Employee
Name
|
Gender
|
Date of
Birth
|
Date of
Hiring
|
Salary
|
|
D –
45001
|
anne hardy
|
F
|
03-01-1977
|
01-06-2006
|
45,000.00
|
|
HR –
45001
|
RAM Sastry
|
m
|
23-04-1999
|
01-02-2007
|
25,000.00
|
|
S –
45203
|
syam mohan
|
m
|
30-11-1987
|
06-08-2005
|
24,000.00
|
|
O –
45301
|
riya SHAarma
|
f
|
29-05-1993
|
13-01-2010
|
22,000.00
|
|
HR –
45005
|
|
M
|
25-09-1997
|
28-10-2014
|
20,001.00
|
|
HR –
45010
|
|
M
|
04-06-1988
|
21-09-2012
|
15,800.00
|
|
S –
45210
|
soe green
|
m
|
20-08-1982
|
15-11-2013
|
20,345.00
|
|
S –
45220
|
mc george
|
F
|
26-12-1996
|
12-02-2008
|
35,789.00
|
|
S –
45230
|
sam peter
|
M
|
22-11-1994
|
15-05-2016
|
28,349.00
|
|
D –
45004
|
madhuri Dev
|
F
|
13-02-1984
|
01-09-2008
|
30,000.00
|
I.
Format
the following from Sheet I of DAM Exam Sheet.
1. Split the column Deptname-EmpID
into two columns.
2. Format the Employee Name column.
3. Format the Gender column.
4. Format the date to YYYY/MM/DD”
5. Find the Number of years of
service of service for an employee in the organisation as on
date.
6. Insert the currency “$” symbol in
the salary column.
7. Remove the decimal place in the
salary column.
8. If an employee has to retire at
the age of 60, when will they retire.
9. Employees whose experience is
greater than 10 years will get a bonus
of 10%. Calculate the bonus paid.
10. How many employees are there in
each department and what is their total salary department wise.
II.
Table for 11-15 Questions
|
Employee ID
|
First Name
|
Last Name
|
Gender (M/F)
|
Age(Years)
|
Date of Hiring
(DD/MM/YYYY)
|
Salary in $
|
|
45001
|
anne
|
hardy
|
F
|
45
|
06-01-2010
|
4,50,000
|
|
45001
|
Ram
|
sastry
|
M
|
23
|
01-05-2000
|
1,25,000
|
|
45203
|
SHYAM
|
max
|
M
|
35
|
01-08-2005
|
2,15,000
|
|
45932
|
Riya
|
sharma
|
F
|
29
|
01-03-2010
|
2,12,000
|
|
45020
|
Jai
|
dixit
|
M
|
48
|
08-10-2000
|
4,65,000
|
|
|
|
|
|
|
|
|
|
45006
|
Madhuri
|
dEv
|
F
|
38
|
|
3,10,000
|
|
45078
|
Somya Varma
|
varma
|
F
|
56
|
01-10-1990
|
6,50,000
|
|
45039
|
Raju
|
beri
|
M
|
43
|
02-08-1999
|
4,23,000
|
|
|
|
|
|
|
|
|
|
45020
|
Jai
|
dixit
|
M
|
48
|
08-10-2000
|
4,65,000
|
|
45038
|
Kalyani
|
rao
|
F
|
37
|
03-10-2010
|
3,12,000
|
|
45037
|
Sam
|
joseph
|
M
|
|
04-02-2006
|
3,20,000
|
|
45059
|
Poter
|
Parker
|
M
|
39
|
03-10-2008
|
2,89,000
|
|
45012
|
Siddu
|
rao
|
M
|
42
|
15-10-2007
|
4,12,000
|
11. Remove blank rows
12. Highlight blank cells and fill
with “No Data”
13. Concatenate First Name and Last
Name columns
14. Format the names of the
concatenated column.
15. List out all the employees whose
salary is greater than $3,00,000 and less than $4,00,000 give allowances of 5%
and above $4,00,000 give allowances of 10%.
Find the total allowances paid by the company.
III.
Table for Question No. 16-20
|
Customer ID
|
Customer Name
|
Contact Name
|
Street Name
|
City
|
Postal Code
|
Country
|
|
3124
|
Alfreds Futterkiste
|
Maria Anders
|
Obere Str. 57,
|
Berlin
|
12209
|
Germany
|
|
1238
|
Ana Trujillo helados
|
Ana Trujillo
|
avda. de la Constituci¢n 2222
|
Mexico city
|
5021
|
Mexico
|
|
4562
|
Antonio MorenoTaquer¡a
|
antonio moreno
|
mataderos 2312
|
Mexico city
|
5023
|
Mexico
|
|
678
|
Around the Horn
|
thomas hardy
|
120 Hanover Sq.
|
new yoork
|
WA1 1DP
|
USA
|
|
2314
|
Berglunds snabola
|
Christina Berglund
|
Berguvsv„gen 8
|
Sydney
|
33-22
|
Australia
|
|
3657
|
Amit Mishra
|
Maria
Anders
|
Obere Str. 57
|
Sydney
|
65-332-3
|
Australia
|
|
7890
|
williami sanuo
|
Ana Trujillo
|
Avda. de la Constitucion 2222
|
Sydney
|
43433
|
Australia
|
|
56789
|
Sonio Moreno
|
Antonio Moreno
|
120 Jefferson St.,Riverside
|
new jersy
|
8075
|
US
|
|
3657
|
Amit Mishra
|
Maria Anders
|
Obere Str. 57
|
Sydney
|
65-332-3
|
Australia
|
|
2314
|
Berglunds snabbkp
|
Christina Berglund
|
Berguvsvgen 8
|
Sydney
|
33-22
|
Australia
|
16. Using Excel, concatenate the columns Street name and city.
17. Insert "C-" in the left side of the customer Id in customer name column of the
above data.
18. Using Excel Remove Extra spaces
and format the names of customer name column.
19. From the above table
remove the duplicate rows.
20. From the above table remove
the customer whose
customer ID is not a 4 digit number
IV.
Table for Question No. 20-25
|
Employee
|
Employee
|
Gender
|
Age
|
Year of
|
Date of
|
Salary
|
|
ID
|
Name
|
M/F
|
As on
date
|
Graduation
|
Hiring
|
In
Rupees
|
|
D45078
|
Somya Rao
|
F
|
50
|
1975
|
01-10-1990
|
6,50,000
|
|
S45039
|
Raju Budda
|
M
|
43
|
2000
|
02-08-1999
|
4,23,000
|
|
M45020
|
Jai
Raj
|
M
|
48
|
1993
|
08-10-2000
|
4,65,000
|
|
M45038
|
Kalyani
Kumari
|
F
|
37
|
2004
|
|
3,12,000
|
|
M45037
|
Sam
Joes
|
M
|
36
|
2005
|
04-02-2006
|
3,20,000
|
|
S45059
|
Poter Michel
|
M
|
39
|
2004
|
03-10-2008
|
2,89,000
|
|
F45012
|
Siddu kommnani
|
M
|
42
|
2000
|
15-10-2007
|
4,12,000
|
|
F45023
|
Geeta Chowdari
|
F
|
46
|
1996
|
12-10-2005
|
4,34,000
|
|
D45078
|
Lavanya Koppula
|
F
|
49
|
1994
|
31-10-2004
|
|
|
|
Hema Sarvani
|
F
|
50
|
1992
|
25-10-2000
|
5,69,000
|
|
S45060
|
Gopal Das
|
M
|
54
|
1998
|
12-09-2001
|
6,32,000
|
|
F45062
|
Siya
Paul
|
F
|
51
|
|
19-06-2003
|
5,90,000
|
|
M45026
|
Hari Krishna
|
M
|
38
|
2003
|
25-04-2003
|
|
|
O45029
|
Priya Dixit
|
F
|
|
2005
|
12-07-2006
|
3,45,000
|
|
A45037
|
Anu Devara
|
F
|
58
|
1985
|
12-09-2008
|
6,95,000
|
21.
From the above data in Excel, Concatenate the Headers.
22. From the above data in Excel, convert
the numerals in Year column to text format.
23. From the above table in Excel,
highlight the blank spaces and fill it with "Not
Available".
24. From the above table
in Excel, select
only the first name
from the Employee name and paste in another
column.
25. Create Email Addresses using lower
case and underscore in between the names and end with “@gmail.com”. (For example priya_dixit@gmail.com)
V.
Table
for 26 to 30 Questions:
|
Customer Name
|
Company name
|
Phone number
|
Address, City, State, ZIP Code
|
Amount
|
Shipment Date
|
Order Date
|
|
Jim van
der Mheen
|
Stokes,
Rutherford and Bauch
|
7282727491
|
939
clear edge, mesita, wv, 14424
|
$
1,00,003
|
25-05-2016
|
2016-04-19
|
|
Shenita
Daven port
|
Waelchi
Inc
|
2676259210
|
873 iron
third harbor, whelen springs, nm, 37783
|
1,83,593
|
02-08-2016
|
2016-07-03
|
|
Jimmie
Mc Clure
|
Braun,
Crooks and Ortiz
|
8373183928
|
856
forge overpass, annandale, nm, 08463
|
1,59,194
|
30-10-2016
|
2016-10-21
|
|
Mary
Belle Serrano
|
Schamberger,
Yost and Dach
|
8853887212
|
515
silver highlands, natalbany, wy, 00462
|
1,80,394
|
20-04-2016
|
2016-04-01
|
|
Vito van
Helpen
|
Mann, Sauer and Sauer
|
3507306802
|
221
jagged harbor, franklin borough, ny, 80069
|
1,25,979
|
10-08-2016
|
2016-07-18
|
|
Salvatore
Van Egmond
|
Schmidt-Marks
|
5308189072
|
p.o. box
51573, berthold, sd, 16229
|
1,11,804
|
30-08-2016
|
2016-07-29
|
|
Hay Wood
Meza
|
Huels,
Schuster and Daugherty
|
8102106924
|
p.o. box
42524, matteson village, mn, 55503
|
1,08,063
|
25-02-2016
|
2016-01-15
|
|
Margrett
Cowan
|
Armstrong
Group
|
8661697766
|
249 old
passage, james village, id, 64602
|
1,74,882
|
05-08-2017
|
2017-07-15
|
|
Jerold
MC Hooper
|
Muller,
Lakin and Bogan
|
9291511240
|
843 old
camp, new holland village, wy, 32125
|
1,30,140
|
05-04-2017
|
2017-03-26
|
|
Leandro
van der Woerd
|
Mueller
and Sons
|
67145298l3
|
956 lazy
grove, ouray, mi, 38713
|
2,12,181
|
18-03-2017
|
2017-01-18
|
|
Haywood
Huber
|
Braun,
Crooks and Ortiz
|
7702510897
|
p.o. box
30184, wolfe, ok, 08774
|
1,52,568
|
20-06-2017
|
2017-05-31
|
|
Particia
Veen Hof
|
Schmidt-Marks
|
8041074292
|
897
amber bluff, huntley, in, 29911
|
1,52,088
|
08-08-2017
|
2017-08-02
|
|
Yvone
Esparza Brook
|
Weber,
Kuhlman and Hirthe
|
2099450674
|
937
indian kennedy lawn, south milwaukee, me, 85635
|
1,85,383
|
13-12-2017
|
2017-12-05
|
|
Yvone
Esparza
|
Weber,
Kuhlman and Hirthe
|
61616985o2
|
670 lazy
cider, glen raven, nc, 19967
|
1,39,493
|
2017-05-30
|
2017-04-30
|
|
Jame
Oosthuijzen
|
Graham,
Towne and Monahan
|
4729853826
|
370
eighth mews, villano beach, nv, 52255
|
1,83,377
|
2017-09-20
|
2017-09-07
|
|
Filiberto
Kues
|
Schmidt-Marks
|
8756599072
|
325 iron
bluff heights, walford, vt, 11877
|
1,98,796
|
2017-05-20
|
2017-05-16
|
26.From the above table, remove the invalid phone
number which are not in the proper format of
having 10 digits,
no characters and the
phone numbers which are starting with 9/8/7/6
27. From the above table, put the phone
numbers in US format.
28.
From the above table, split
the Address column into Address, City, State and ZIP code.
29. From the above
table, select only the middle
name from customer name.
30.
From the above table, calculate the number of days required for shipment.
Power Pivot and Power Query Questions:
VI. Power
Query link(Order data set 1)
Power
Query link ( Order data set 2)
Use Power Query Editor to answer the
following questions:
31. Remove the
rows which has improper data.
32.Split the
column Customer ID into Customer ID and Customer Name.
33.Remove the
currency Symbol and the decimals (Give reasons for removing them) from the
column Cookies shipped.
34.Calculate the
profits for each of the customer.
35.How many days
does it take for the delivery of orders.
36.Load the
second data set to the existing data set1.
37.Create Pivot
table for the cookies shipped year wise and quarter wise.
38.Create pivot
table and graph for the cookies shipped customer wise.
39.Create pivot
table to know the top 5 customers.
40.Crate pivot
table to know the relationship between the sales and the delivery of the
orders.
VI.
Power
Query and Power Pivot Link (Sales_2018)
Power
Query and Power Pivot Link (Sales_ 2019)
41.In Power Query
Editor concatenate the first two rows.
42.In Power Query
Editor Split the column ship mode and container.
43.What is percentage
of total shipping amount for each order priority.
44.Find the
bottom 3 Shipment Mode and Container by total Unit Sell Price.
45.Sort Customer
ID by Unit Sell Price in ascending order.
46.Calculate the
number days for shipment and establish a relationship between the number days
for shipment and shipment amount.
47.Find the total
selling price customer wise?
48.Has Order
priority has any influence on the shipping amount.
49.Which shipment
mode has more customers?
50.Is order Id determined
by discount percent?
https://www.dynamicwebtraining.com.au/blog/quick-ways-to-clean-excel-data
Comments
Post a Comment