#Allstate
Given the homework results of a group of students, calculate the average grade and the completion rate of each student. A homework is considered not completed if no grade has been assigned.
Output first name of a student, their average grade, and completion rate in percentages. Note that it's possible for several students to have the same first name but their results should still be shown separately.
table name:allstate_students
table name:allstate_homework
Solution:
select s.student_firstname
, convert(decimal(7,2),avg(w.grade)) as avgScore,
convert(decimal(7,2),100 * count(w.grade)*1.0/count(*)) as comp
from allstate_students as s
join allstate_homework as w on
s.student_id = w.student_id
group by s.student_firstname,s.student_lastname
Output:
SQL Script:
USE [StrataScratch]
GO
CREATE TABLE [dbo].[allstate_homework](
[student_id] [int] NULL,
[homework_id] [int] NULL,
[grade] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[allstate_students](
[student_id] [int] NULL,
[student_firstname] [varchar](50) NULL,
[student_lastname] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[allstate_homework] ([student_id], [homework_id], [grade]) VALUES (1, 1, 7)
GO
INSERT [dbo].[allstate_homework] ([student_id], [homework_id], [grade]) VALUES (1, 2, 6)
GO
INSERT [dbo].[allstate_homework] ([student_id], [homework_id], [grade]) VALUES (1, 3, 6)
GO
INSERT [dbo].[allstate_homework] ([student_id], [homework_id], [grade]) VALUES (2, 1, 8)
GO
INSERT [dbo].[allstate_homework] ([student_id], [homework_id], [grade]) VALUES (2, 2, NULL)
GO
INSERT [dbo].[allstate_homework] ([student_id], [homework_id], [grade]) VALUES (2, 3, NULL)
GO
INSERT [dbo].[allstate_homework] ([student_id], [homework_id], [grade]) VALUES (3, 1, 4)
GO
INSERT [dbo].[allstate_homework] ([student_id], [homework_id], [grade]) VALUES (3, 2, NULL)
GO
INSERT [dbo].[allstate_homework] ([student_id], [homework_id], [grade]) VALUES (3, 3, 6)
GO
INSERT [dbo].[allstate_homework] ([student_id], [homework_id], [grade]) VALUES (4, 1, NULL)
GO
INSERT [dbo].[allstate_homework] ([student_id], [homework_id], [grade]) VALUES (4, 2, NULL)
GO
INSERT [dbo].[allstate_homework] ([student_id], [homework_id], [grade]) VALUES (4, 3, NULL)
GO
INSERT [dbo].[allstate_students] ([student_id], [student_firstname], [student_lastname]) VALUES (1, N'Marie ', N'Butters')
GO
INSERT [dbo].[allstate_students] ([student_id], [student_firstname], [student_lastname]) VALUES (2, N'Joan ', N'Reed')
GO
INSERT [dbo].[allstate_students] ([student_id], [student_firstname], [student_lastname]) VALUES (3, N'Marie ', N'Maynard')
GO
INSERT [dbo].[allstate_students] ([student_id], [student_firstname], [student_lastname]) VALUES (4, N'Hugo ', N'Wright')
GO