Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | The Student and the Marks Puzzle

The Puzzle is simple. There are 3 tables , lets say BM – book marks , SM – Sports marks , EM – Education marks. All these tables contains stud ID, Name and their respective marks. Write a query which will satisfy below conditions

/*
if any of the two marks are below 40 then the student is fail
if any of the two marks are between 40 and 80 then the student is Pass
if any of the two marks are greater than 80 then the student is Excellent
*/

Sample Input

BM

StuID StuName BMarks
1 A 90
2 B 60
3 C 50
4 D 20

EM

StuID StuName EMarks
1 A 90
2 B 60
3 C 10
4 D 40

SM

StuID StuName SMarks
1 A 90
2 B 60
3 C 40
4 D 20

Expected Output

StuID StuName Performance
1 A Excellent
2 B Pass
3 C Pass
4 D Fail

Rules/Restrictions
◾The solution should be should use “SELECT” statement or “CTE”.
◾Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


--

CREATE TABLE BM
(
StuID INT
,StuName VARCHAR(1)
,BMarks INT
)
GO

INSERT INTO BM(StuID, StuName, BMarks) VALUES
(1,'A',90),
(2,'B',60),
(3,'C',50),
(4,'D',20)

GO

CREATE TABLE SM
(
StuID INT
,StuName VARCHAR(1)
,SMarks INT
)
GO

INSERT INTO SM(StuID, StuName, SMarks) VALUES
(1,'A',90),
(2,'B',60),
(3,'C',40),
(4,'D',20)

GO

CREATE TABLE EM
(
StuID INT
,StuName VARCHAR(1)
,EMarks INT
)
GO

INSERT INTO EM(StuID, StuName, EMarks) VALUES
(1,'A',90),
(2,'B',60),
(3,'C',10),
(4,'D',40)

GO
--

UPDATE – 10-Apr-2015 – Solution 1 


-- Solution 1 --

SELECT EM.StuID , EM.StuName ,
CASE  WHEN  (BMarks > 40 AND EMarks <= 60 ) 
           OR (EMarks > 40 AND SMarks <= 60 ) 
           OR (SMarks > 40 AND BMarks <= 60 ) THEN 'Pass' 
       WHEN  (BMarks < 40 ) 
           OR (EMarks < 40 ) 
           OR (SMarks < 40 ) THEN 'Fail'        
       WHEN  (BMarks > 80 ) 
           OR (EMarks > 80 ) 
           OR (SMarks > 80 ) THEN 'Excellent' END 'Performance'
FROM BM INNER JOIN EM on EM.StuId = BM.StuID INNER JOIN SM on SM.StuID = BM.StuID

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com