SQL Data Analysis Interview Question Answer — Practical Sql Test for Data Analysis

Check the following table for reference and answer the following question listed below .

This question is asked in Data Analyst Intermediate Level Post in Top MNC company as a test assignment.

1. How many marks did Roll number 1 score in Field A
2. How many fields did A, B, C appear for?
3. What was the total percentage that roll 1 scored in each field and in total too
   a. Subject Percentage = Marks scored in field / Maximum marks possible in that field
   b. Total Percentage = total marks scored / total maximum marks
4. Which Roll number scored the highest marks in Field B
5. What was the total marks that Sahil scored in all fields combined
6. What was the average marks scored in each field

. a. Average marks = Total marks scored by all students/number of students who appeared

create table Table1
(
 Name varchar(255) NOT NULL,
 Field varchar(255), 
 Score int

);
INSERT INTO Table1 (Name, Field, Score) VALUES ('Mit', 'A', 10);
INSERT INTO Table1 (Name, Field, Score) VALUES ('Sahil', 'A', 20);
INSERT INTO Table1 (Name, Field, Score) VALUES ('Rohit', 'A', 20);
INSERT INTO Table1 (Name, Field, Score) VALUES ('Mit', 'B', 30);
INSERT INTO Table1 (Name, Field, Score) VALUES ('Mit', 'C', 30);
INSERT INTO Table1 (Name, Field, Score) VALUES ('Sahil', 'B', 40);
INSERT INTO Table1 (Name, Field, Score) VALUES ('Rohit', 'C', 50);
INSERT INTO Table1 (Name, Field, Score) VALUES ('Rohit', 'B', 60);
--INSERT INTO Table1 (Name, Field, Score) VALUES ('', '', );

create table Table2(
 Number int Primary Key,
 Name varchar(255) 
)
INSERT INTO Table2 (Number, Name) VALUES (1,'Mit');
INSERT INTO Table2 (Number, Name) VALUES (2,'Rohit');
INSERT INTO Table2 (Number, Name) VALUES (3,'Sahil');

create table Table3(
 Field varchar(255) ,
 MaxMarks int 
 )
INSERT INTO Table3 (Field, MaxMarks) VALUES ('A',50);
INSERT INTO Table3 (Field, MaxMarks) VALUES ('B',80);
INSERT INTO Table3 (Field, MaxMarks) VALUES ('C',100);

1. How many marks did Roll number 1 score in Field A

select Table2.Name,Score
from Table1
join Table2
on Table1.Name = Table2.Name
where Number = 1 and Table1.Field = 'A'

2. How many fields did A, B, C appear for?

select Field,count(Field) 
from Table1
group by Field

3. What was the total percentage that roll 1 scored in each field and in total too

— a. Subject Percentage = Marks scored in field / Maximum marks possible in that field

select Table1.Name,Table1.Field, sum(Score) as score , sum(MaxMarks) maxmarks, (sum(Score)*100 / sum(MaxMarks) ) as tot
from Table1
join Table2
on Table1.Name = Table2.Name
join Table3
on Table1.Field = Table3.Field
where Number = 1
group by Table1.Field,Table1.Name

— b. Total Percentage = total marks scored / total maximum marks

select Table1.Name,sum(Score) as score, sum(MaxMarks) as marks, (sum(Score)*100 / sum(MaxMarks) ) as TotalPercentage
from Table1
join Table2
on Table1.Name = Table2.Name
join Table3
on Table1.Field = Table3.Field
where Number = 1
group by Table1.Name

4) Which Roll number scored the highest marks in Field B

select score = max(Table1.Score), Table2.Number
from Table1
join Table2
on Table1.Name = Table2.Name
where Table1.Field = 'B'
group by Table2.Number
order by Max(score) DESC

5) What was the total marks that Sahil scored in all fields combined

select Table1.Name,sum(Score) as score
from Table1
join Table2
on Table1.Name = Table2.Name
join Table3
on Table1.Field = Table3.Field
where Number = 3
group by Table1.Name

6) What was the average marks scored in each field

a. Average marks = Total marks scored by all students/number of students who appeared

select Table1.Field,sum(score) / count(Table2.Number) as Average
from Table1
join Table2
on Table1.Name = Table2.Name
group by Field