CODEWITHSUNDEEP
sql-server
Hunar
Hunar

Reputation: 399

how make grading system in sql server table creation

I have a table of student averages of four college year and field for find the average of four year degrees and the final field is for grading system.

My question: I want to make grade field value based on final_avg column. How can I achieve this?

For example if final_avg < 50 then grade = failed, if final_avg >= 50 and final_avg < 60 then grade = satisfactory and so on.

This is my table:

CREATE TABLE averages_tbl(
    [avg_id] [int] IDENTITY(1,1) NOT NULL,
    [Student_ID] [int] NOT NULL,
    [f_s_avg] [float] NULL,
    [s_s_avg] [float] NULL,
    [t_s_avg] [float] NULL,
    [fu_s_avg] [float] NULL,
    [final_avg]  AS ((([f_s_avg]+[s_s_avg])+[t_s_avg])+[fu_s_avg]),
    [grade] [nvarchar](20) NULL,
)

Upvotes: 1

Views: 1678

Answers (1)

gofr1
gofr1

Reputation: 15977

Simple way:

CREATE TABLE averages_tbl(
    [avg_id] [int] IDENTITY(1,1) NOT NULL,
    [Student_ID] [int] NOT NULL,
    [f_s_avg] [float] NULL,
    [s_s_avg] [float] NULL,
    [t_s_avg] [float] NULL,
    [fu_s_avg] [float] NULL,
    [final_avg]  AS ((([f_s_avg]+[s_s_avg])+[t_s_avg])+[fu_s_avg]),
    [grade] AS (CASE WHEN ((([f_s_avg]+[s_s_avg])+[t_s_avg])+[fu_s_avg]) < 50 THEN 'Failed' 
                     WHEN ((([f_s_avg]+[s_s_avg])+[t_s_avg])+[fu_s_avg]) between 50 and 59 THEN 'Satisfied'
                     ELSE 'Whatever' END)
)

Or with ALTER TABLE:

ALTER TABLE averages_tbl DROP COLUMN [grade]

ALTER TABLE averages_tbl ADD [grade] AS (
    CASE WHEN ((([f_s_avg]+[s_s_avg])+[t_s_avg])+[fu_s_avg]) < 50 THEN 'Failed' 
         WHEN ((([f_s_avg]+[s_s_avg])+[t_s_avg])+[fu_s_avg]) between 50 and 59 THEN 'Satisfied'
         ELSE 'Passed' END)

Upvotes: 1

Related Questions