Bank SQL Interview Questions | Backend Developer Experience (Must Watch!)

This question was asked in one of the MNC Bank

Table :

CREATE TABLE GRADE (
Grades VARCHAR2(2),
Marks NUMBER
);

INSERT INTO GRADE (Grades, Marks) VALUES ('A+', 90);
INSERT INTO GRADE (Grades, Marks) VALUES ('A', 80);
INSERT INTO GRADE (Grades, Marks) VALUES ('B', 70);
INSERT INTO GRADE (Grades, Marks) VALUES ('C', 60);
CREATE TABLE STUDENTS (
Rollnum NUMBER,
Name VARCHAR2(50),
Marks NUMBER
);

INSERT INTO STUDENTS (Rollnum, Name, Marks) VALUES (1, 'Nitin', 86);
INSERT INTO STUDENTS (Rollnum, Name, Marks) VALUES (2, 'Pranshu', 78);
INSERT INTO STUDENTS (Rollnum, Name, Marks) VALUES (3, 'Rohan', 64);
INSERT INTO STUDENTS (Rollnum, Name, Marks) VALUES (4, 'Vijay', 68);
INSERT INTO STUDENTS (Rollnum, Name, Marks) VALUES (5, 'Divya', 72);
INSERT INTO STUDENTS (Rollnum, Name, Marks) VALUES (5, 'Sonam', 99);
Query :

select rl,nm,mr,gr from (SELECT
s.rollnum as rl,
s.name as nm,
s.marks as mr,
g.marks,
g.grades as gr,
abs(s.marks - g.marks) AS diff,
rank() over(partition by s.name,s.marks order by abs(s.marks - g.marks) ) as rnk
FROM
students s
CROSS JOIN grade g
ORDER BY
s.rollnum) where rnk = 1 order by rl,nm;

Видео Bank SQL Interview Questions | Backend Developer Experience (Must Watch!) канала vikas_vooradi