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 and SQL:

Questions from 1 to 25 :: Ms Excel/Power Query

Table for Question No. 1-5

Employee
Employee Name Gender Age Date of Hiring Salary
ID
D-45001 Anne hardy f 45 6th jan 2006 4,50,000

D-45001 Ram sastry m 23 1st feb2007 1,25,000

SHYAM
D-45203 Mohan m 35 01-08-2005 2,15,000
D-45932 Riya SHARmA f 29 01-03-2010 2,12,000
D-45006 Madhuri dEv f 38 01-09-2005 3,10,000

1. Remove blank rows from the above table.

2. Format date from the above table.
3. Using Excel, insert currency in salary column.
4. Using Excel, format employee name to proper case letters.
5. Using Excel, change the letters to Upper case in gender column.
 Table for Question No. 6-10
Customer
Customer Name Contact Name Street Name City Postal Code Country
ID
Alfreds
3124 Futterkiste Maria Anders Obere Str. 57, Berlin 12209 Germany
Ana Trujillo avda. de la
1238 helados Ana Trujillo Constituci¢n 2222 Mexico city 5021 Mexico
Antonio Moreno antonio
4562 Taquer¡a moreno mataderos 2312 Mexico city 5023 Mexico
5678 Around the Horn thomas hardy 120 Hanover Sq. NY WA1 1DP USA
Berglunds Christina
2314 snabola Berglund Berguvsv„gen 8 Sydney 33-22 Australia
Maria
365 Amit Mishra Anders Obere Str. 57 Sydney 65-332-3 Australia
Ana Avda. de la
7890 williami sanuo Trujillo Constituci¢n 2222 Sydney 43433 Australia
Antonio 120 Jefferson
56789 Sonio Moreno Moreno St.,Riverside NJ 8075 US
Maria
3657 Amit Mishra Anders Obere Str. 57 Sydney 65-332-3 Australia
Berglunds Christina
2314 snabbkp Berglund Berguvsv„gen 8 Sydney 33-22 Australia

6. Using Excel, concatenate the columns Street name and city.

7. Insert "C-" in the left side of the customer Id in customer name column of the above data.
8. Using Excel, remove the leading spaces and extra spaces in Customer name and contact name columns.
9. From the above table remove the duplicate rows.
10. From the above table remove the customer whose customer ID is not a 4 digit number
 Table for Question No. 11-15

Employee
Employee Gender Age Year of Date of Salary
ID
As on In
ID Name M/F Graduation Hiring
date 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
11. From the above data in Excel, Concatenate the Headers.
12. From the above data in Excel, convert the numerals in Year column to text format.
13. From the above table in Excel, highlight the blank spaces and fill it with "Not Available".
14. From the above table in Excel, select only the first name from the Employee name and paste in another
column.
15. From the above table, select the Date of Hiring column and change the format to YY-MM-DD
Customer Company PHONE Address, City, Shipment Sales
Amount Order date Commissions
Name Name NUMBER State, and ZIP Date Rep ID
Fausto Hansen- p.o. box 52367,
Wells Block 3574999014 wamac, ga, 38940 5,31,950 2020-01-18 2019-08-18 8
Lon van Stracke- p.o. box 55222,
Batenburg Kirlin 7684940151 texas, vt, 21681 6,76,359 2020-08-03 2020-02-03 3
985 quiet second
motorway,
Cordie Rohan christiana borough,
Henckes PLC 6569415846 nv, 38006 5,34,680 2019-09-21 2019-10-21 1
476 rustic
Geraldo Smith underpass, algood,
Roeder PLC 6159714872 nc, 97098 5,83,679 2020-04-22 2020-04-22 9
389 broad oval,
Debora Hyatt- south palm beach,
Dekker Durgan 9759057307 az, 58103 6,45,572 2021-02-11 2021-03-11 6
Dionne Hansen- p.o. box 33125, elm
Kalt Block 4803299394 springs, ny, 21979 6,05,863 2020-05-06 2020-04-06 7

Above Table for Question No. 16-20

16. Format the above table in proper format.
17. From the above table format the phone number in US form Ex: +1(123)345-6758
18. From the above table separate the address, city, state and zip into different columns
19. Check the inconsistencies in the table and highlight them.
20. Insert "$" in for the amount in the Amount column of the above table.
 Table for Question No. 21-25

Address, Sales
Customer Company PHONE City, State, Shipment Order Rep
Name Name NUMBER and ZIP Amount Date date Mail Id ID Commissions
p.o. box
faUsto 52367,
wElls Hansen- wamac, 2020- 2019-
wORrgo Block 3574999014 ga, 38940 5,31,950 01-18 08-18 fausto&worng@gmail.com 8
p.o. box
55222,
Lon vAn Stracke- texas, vt, 2020- 2020-
bateVburg Kirlin 7684940151 21681 6,76,359 08-03 02-03 Lonvan.gmail.com 3
985 quiet
second
motorway,
Cordie christiana
henckes Rohan borough, 2019- 2019-
dickens PLC 656941#846 nv, 38006 5,34,680 09-21 10-21 CORDIE-@gmail.com 1
476 rustic
geraldo underpass,
roERDder Smith algood, nc, 2020- 2020-
dugoe PLC 615971487 97098 5,83,679 04-22 04-22 geraldo@gmail.com 9
389 broad
oval, south
Debora palm
Dekker Hyatt- beach, az, 2021- 2021-
Kilin Durgan 9759057307 58103 6,45,572 02-11 03-11 deboradekker@yahoo.in 6
p.o. box
33125,
DioNNE elm
Kalt Hansen- springs, 2020- 2020-
HEnse Block 4803299394 ny, 21979 6,05,863 05-06 04-06 Kalthense@c.in 7

21. 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
22. From the above table, put the phone numbers in US format.
23. From the above table, format the customer name.
24. From the above table, select only the middle name from customer name
25. From the above table, format/ validate the mail IDs
 Questions from 26 to 50 :: SQL
A. Create a Supplier table as shown below : (for questions from 26 to 35)

Sup_No Sup_Name Item_Supplied Item_Price City

(Primary Key)
S1 Suresh Keyboard 400 Hyderabad
S2 Kiran Processor 8000 Delhi
S3 Mohan Mouse 350 Delhi
S4 Ramesh Processor 9000 Bangalore
S5 Manish Printer 6000 Mumbai
S6 Srikanth Processor 8500 Chennai

26. Write sql query to display Suplier numbers and Supplier names whose name starts with ‘R’
27. Write sql query to display the name of suppliers who supply Processors and whose city is Delhi.
28. Write sql query to display the names of suppliers who supply the same items as supplied by Ramesh.
29. Write sql query to increase the price of Keyboard by 200.
30. Write sql query to display supplier numbers, Suplier names and itemprice for suppliers in delhi in the
ascending order of itemprice.
31. Write sql query to add a new column called CONTACTNO.
32. Write sql query to delete the record whose itemprice is the lowest of all the items supplied .
33. Create a view on the table which displays only supplier numbers and supplier names.
34. Write sql query to display the records in the descending order of itemprice for each itemsupplied.
35. Write sql query to display the records of suppliers who supply items other than Processor or
Keyboard.
Below are the details of Employees working for a software Company. (For questions from 36 to 45)

Create the table called EmpDetails with the below mentioned details.

Eid Ename DOB Designation Salary DOJ

(Primary Key)
E101 Suma 29-Dec-89 Designer 20000 01-Apr-10
E102 Amit 10-Jan-95 Programmer 25000 18-Feb-18
E103 Payal 15-Aug-85 Tester 35000 13-Jun-11
E104 Kiran 20-Apr-90 Programmer 40000 7-Mar-14
E105 Meenal 29-May-83 DBA 50000 9-Dec-11
E106 Sheila 1-May-70 Analyst 60000 25-Sep-18
E107 Swamy 13-Jan-85 Programmer 45000 14-Feb-16
E108 Sushma 22-Dec-76 DBA 45000 31-Jan-12

36. Write sql query to display all the employees whose designation is Programmer.
37. Write sql query to display employees who have joined after 2014.
38. Write sql query to display all the employees whose name ends with ‘a’.
39. Write sql query to display the total salary of all the employees whose designation is
programmer.
40. Write sql query to display all the employee names in upper case.
41. Write sql query to display the details of the employee with highest experience.
42. Write sql query to display the details of the employees whose name contains ‘ee’.
43. Write sql query to increase the salaries of employees by 5000 whose designation is DBA.
44. Write sql query to display the employees whose salary is more than the average salary of all
the employees.
45. Write sql query to display the record in the following format: xxxxxxxxx is working as
xxxxxxxxxxxxxx with a Salary ofRs.xxxxxxxx
eg: Suma is working as Designer with a Salary of Rs. 20000.
 Create the two tables as shown below with the given constraints: (for questions 46 to 50)

Table name: Employee Tablename: Department

Constraints: Eid is Primary key and DeptId is foreign key Constraints:DeptId Primary key
Salary should not be less than 10000 and Dname is NOT NULL

Eid Ename DeptId Designation Salary DOJ DeptId Dname

(Primary Key) (Foreign ( > 10000) (Primary
Key) Key)
101 Sudha D2 Clerk 20000 01-Apr-10 D1 Sales
102 David D1 Manager 50000 18-Feb-18 D2 Marketing
103 Preethi D3 Clerk 35000 13-Jun-11 D3 Finance
104 Kiran D1 Salesman 20000 7-Mar-14
105 Meenal D2 Clerk 50000 9-Dec-11
106 Sunitha D3 Manager 60000 25-Sep-18
107 Akhil D3 Clerk 25000 14-Feb-16
108 Sushma D2 Manager 45000 31-Jan-12

46. Write sql query to display all the employees who earn more than average salary of all the
employees in the company.
47. Write sql query to display the fields Eid, Ename and Dname.
48. Write sql query to sort the employee table in the descending order of salaries.
49. Write sql query to list all the job designations in the employee table without repetitions.
50. Write sql query to display all the employee details Department wise and in the ascending
order of their salaries.