Database

ALTER TABLE application
ADD CONSTRAINT pk_application PRIMARY KEY (categories,sequence)

<pre lang="sql">  ALTER TABLE D_D_ext
  ALTER COLUMN Categories NVARCHAR(1) NOT NULL ;
  ALTER TABLE D_D_ext
  ALTER COLUMN Sequence NVARCHAR(1) NOT NULL ;

ALTER TABLE D_D_ext
  ADD CONSTRAINT pk_D_D_ext_Categories_Sequence PRIMARY KEY (Categories, Sequence)

ALTER TABLE [Application] ALTER COLUMN Categories NVARCHAR(1) NOT NULL
ALTER TABLE [Application] ALTER COLUMN Sequence NVARCHAR(1) NOT NULL

ALTER TABLE [Application] ADD CONSTRAINT PK_Application PRIMARY KEY (Categories, Sequence)

JammoD87 wrote:

I was asked a question the other day

-- TODO: Change these as required:
DECLARE @DistinctionThreshold int = 90;
DECLARE @PassThreshold int = 70;

WITH cteCounts As
(
    SELECT
        ClassId,
         SUM(CASE 
            WHEN Marks < @PassThreshold THEN 1 
            ELSE 0 
        END) As Failed,
        SUM(CASE 
            WHEN Marks >= @PassThreshold And Marks < @DistinctionThreshold THEN 1 
            ELSE 0 
        END) As Passed,
        SUM(CASE 
            WHEN Marks >= @DistinctionThreshold THEN 1 
            ELSE 0 
        END) As Distinction
    FROM
        dbo.Marks
    GROUP BY
        ClassId
)
SELECT
    C.Class,
    M.Failed,
    M.Passed,
    M.Distinction
FROM
    dbo.Class As C
    INNER JOIN cteCounts As M
    ON M.ClassId = C.ClassId
;

CREATE TABLE Class
(
    Id   BIGINT PRIMARY KEY
   ,Name VARCHAR(50)
)
INSERT INTO Class (Id, Name) VALUES (1, '7th Class')
INSERT INTO Class (Id, Name) VALUES (2, '8th Class')
INSERT INTO Class (Id, Name) VALUES (4, '10th Class')
CREATE TABLE Marks
(
    StudentId BIGINT,
    ClassId BIGINT,
    Marks BIGINT
)
INSERT INTO Marks (StudentId, ClassId, Marks) VALUES (1, 1, 35)
INSERT INTO Marks (StudentId, ClassId, Marks) VALUES (2, 1, 90)
INSERT INTO Marks (StudentId, ClassId, Marks) VALUES (3, 2, 75)
INSERT INTO Marks (StudentId, ClassId, Marks) VALUES (4, 2, 30)

SELECT c.Name,
      (SELECT COUNT( * ) FROM Marks WHERE ClassId = c.Id AND Marks >= 50) AS [Passed],
      (SELECT COUNT( * ) FROM Marks WHERE ClassId = c.Id AND Marks < 50) AS [Failed]
  FROM Class c

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]