Sorry Guys,
I was actually frustrated have searched on almost all DB forums in regards to this question and the queries that can be used to solve this issue and thought the query would suffice to get an answer.
CREATE TABLE TUTORIAL_GRP(
GROUP_ID NUMBER(1),
SESSION_DAY VARCHAR2(10),
VENUE VARCHAR2(10),
CONSTRAINT PK_TUTORIALGRP_GROUPID PRIMARY KEY (GROUP_ID)
);
CREATE TABLE STUDENT(
STUDENT_ID NUMBER(4),
STUDENT_NAME VARCHAR2(50),
GENDER VARCHAR2(10),
TUTE_GRP NUMBER(1),
COURSE VARCHAR2(10),
NATIONALITY_STATUS VARCHAR(20),
CONSTRAINT PK_STUDENT_STUDENTID PRIMARY KEY(STUDENT_ID),
CONSTRAINT FK_STUDENT_TUTEGRP FOREIGN KEY(TUTE_GRP) REFERENCES TUTORIAL_GRP
);
CREATE TABLE TASK(
TASK_CODE VARCHAR2(5),
REQUIREMENT VARCHAR2(30),
LAB NUMBER(2),
CONSTRAINT PK_TASK_TASKCODE PRIMARY KEY(TASK_CODE)
);
CREATE TABLE ATTEMPT(
STUDENT_ID NUMBER(4),
TASK_CODE VARCHAR2(5),
SCORE NUMBER(2),
CONSTRAINT PK_ATTEMPT_STUDENTID_TASKCODE PRIMARY KEY(STUDENT_ID, TASK_CODE),
CONSTRAINT FK_ATTEMPT_STUDENTID FOREIGN KEY(STUDENT_ID) REFERENCES STUDENT,
CONSTRAINT FK_ATTEMPT_TASKCODE FOREIGN KEY(TASK_CODE) REFERENCES TASK,
CONSTRAINT CK_ATTEMPT_SCORE CHECK (SCORE BETWEEN 1 AND 3)
);
I won't be able to add insert data as its too much.
So above are 4 tables that are related to each other.
I am a student and have studied till AVG, SUM, ROUND, MAX etc and have just finished adding Subqueries in queries as well and the question is based on these.
The question says : Write a single SQL statement that shows student with HIGHEST TOTAL SCORE. The result must show STUDENT_ID, STUDENT_NAME and TOTAL_SCORE
So I run the below query to find the total score for each student and sort to find the highest score and the result is below:
Select S.Student_ID, S.Student_Name, SUM(A.Score) as "TOTAL_SCORE" from Student S inner join Attempt A on S.Student_ID = A.Student_ID group by S.Student_ID, S.Student_Name order by 3 desc;
and get this result:
1030 CAMERON BECK 92
1019 OLIVER SNYDER 80
1068 MARION JAMES 73
1014 KRISTIN PITTMAN 71
1021 RANDALL PEARSON 70
Now as you can see the student with the highest score is Cameron with 92.
Now I need to show the student with the highest score
Select S.Student_ID, S.Student_Name, MAX(A.Score) as "TOTAL SCORE" from Student S inner join Attempt A on S.Student_ID = A.Student_ID where A.Score = (select SUM(A.Score) from Student S) group by S.Student_ID, S.Student_Name;
But I don't get any result and neither do I get any error, I see an empty table.
Thank You
Message was edited by: 3428849