Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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
select bm.StuID,bm.StuName
, case when ((bmarks < 40 and smarks < 40) or (bmarks < 40 and emarks < 40) or (smarks < 40 and emarks 80 and smarks > 80) or (bmarks > 80 and emarks > 80) or (smarks > 80 and emarks > 80)) then ‘Excellent’
when (((bmarks between 40 and 80) and (smarks between 40 and 80)) or ((bmarks between 40 and 80) and (emarks between 40 and 80)) or
((smarks between 40 and 80) and (emarks between 40 and 80))) then ‘Pass’ end Performance from bm
join sm on bm.stuid = sm.stuid
join em on bm.stuid = em.stuid
LikeLike