CREATE TABLE PERSONAL (
  name VARCHAR(255),
  rollno INT,
  address VARCHAR(255),
  score INT
);

CREATE TABLE RESULT (
  rollno INT,
  marks INT,
  sub VARCHAR(255)
);

INSERT INTO PERSONAL (name, rollno, address, score) VALUES
('Ayan', 1, 'KOL', 90),
('Ramu', 2, 'KOL', 89),
('Nikhil', 3, 'DEL', 99),
('Shyam', 4, 'DEL', 82),
('Mrinal', 5, 'KOL', 92);

INSERT INTO RESULT (rollno, marks, sub) VALUES
(1, 90, 'DBMS'),
(1, 88, 'GRAPHICS'),
(2, 89, 'DBMS'),
(2, 95, 'GRAPHICS'),
(3, 99, 'DBMS'),
(3, 98, 'GRAPHICS'),
(4, 82, 'DBMS'),
(4, 79, 'GRAPHICS'),
(5, 92, 'DBMS'),
(5, 75, 'GRAPHICS');

SELECT P.name, P.rollno, R.marks, R.sub FROM PERSONAL AS P INNER join RESULT AS R ON P.rollno = R.rollno;
SELECT P.name, SUM(R.marks) AS TOTAL_MARKS FROM PERSONAL AS P INNER join RESULT AS R ON P.rollno = R.rollno GROUP BY P.name;
SELECT P.name, SUM(R.marks) AS TOTAL_MARKS FROM PERSONAL AS P INNER join RESULT AS R ON P.rollno = R.rollno ORDER BY TOTAL_MARKS desc limit 1;
SELECT MAX(CASE WHEN R.sub = 'DBMS' THEN R.marks END) AS HIGEST_dbms_MARKS, MAX(CASE WHEN R.sub = 'GRAPHICS' THEN R.marks END) AS HIGEST_graphics_MARKS FROM PERSONAL AS P INNER join RESULT AS R ON P.rollno = R.rollno where P.address = 'KOL';
SELECT P.name, P.rollno, R.marks, R.sub FROM PERSONAL AS P INNER join RESULT AS R ON P.rollno = R.rollno where (R.marks + R.marks) between 170 AND 184;
SELECT P.name, P.rollno, R.marks, R.sub FROM PERSONAL AS P INNER JOIN RESULT AS R ON P.rollno = R.rollno where R.sub = 'DBMS';
SELECT P.* FROM PERSONAL AS P where P.name LIKE 'A%';