Case expression for null and not null columns in a table

My table:

CREATE TABLE StudentScore (
    Student_ID INT,
    Student_Name NVARCHAR (50),
    Student_Score INT) 
GO

INSERT INTO StudentScore VALUES (1,'Ali', NULL)
INSERT INTO StudentScore VALUES (2,'Zaid', 770)
INSERT INTO StudentScore VALUES (3,'Mohd', 1140)
INSERT INTO StudentScore VALUES (4,NULL, 770)
INSERT INTO StudentScore VALUES (5,'John', 1240)
INSERT INTO StudentScore VALUES (6,'Mike', 1140)
INSERT INTO StudentScore VALUES (7,'Goerge', NULL)

Query tried

select * from StudentScore
Select TYPE1 =
CASE WHEN ANY(SELECT COLUMN IS NULL) THEN 'AT least 1 NULL'
    ELSE 'NON-NULL'
END

Basically I want that if there is any single null bvalue for any column in table StudentScore, then the type of that column should be null else it should be not null (note that this is part of interview question and I cannot use information_schema etc. I need to do this using case. Can anyone help

For e.g. here ID will be NON-NULL, rest two will be type ‘At least 1 null’

EDIT after seeing answers to clarify:

I want that my code should check all rows of columns and return ‘Non-null’ if all the rows in a column are not null. All the columns should be checked individually.

For e.g. this code gives output as below:

select case when Student_Score is null then 'non-null'
else 'non-null'
end TYPE1 from StudentScore

TYPE1
non-null
non-null
non-null
non-null
non-null
non-null
non-null

The above is not my desired output.

My desired output is

Not null columns: ID ,At least 1 null value (in all the rows corresponding to one column): Student_Score, Student_name.

So the code should return ‘at least 1 null value’ if for a particular column, there is at least one null value present in all the rows .

For e.g. it should check all 8 rows corresponding to each column and if there is no null value in all rows corresponding to one column, then only that column is going to ‘Not null’

Also I removed primary key to make question more generic.

Answer

The simplest method is to put this into separate columns:

select (case when count(*) = count(student_id) then 'No Null Values' else 'Null Values' end) as student_id,
       (case when count(*) = count(student_name) then 'No Null Values' else 'Null Values' end) as student_name,
       (case when count(*) = count(student_score) then 'No Null Values' else 'Null Values' end) as student_score
from studentscores;

This should be the simplest and most performance way to do what you want.

If you want this in separate rows, I would just unpivot these results:

select v.*
from (select (case when count(*) = count(student_id) then 'No Null Values' else 'Null Values' end) as student_id,
             (case when count(*) = count(student_name) then 'No Null Values' else 'Null Values' end) as student_name,
             (case when count(*) = count(student_score) then 'No Null Values' else 'Null Values' end) as student_score
      from studentscores
     ) ss cross apply
     (values ('student_id', student_id), ('student_name', student_name), ('student_score', student_score)
     ) v(col, str)