CODEWITHSUNDEEP
sqloracle-databasejoinsum
Hamsa
Hamsa

Reputation: 483

Writing an sql query for name of the youngest student(s) from the ‘Computer Science’ department along with the total marks obtained by him (them)."

I have been given a table of students data and I have to write query for the following:

Retrieve the name of the youngest student(s) from the ‘Computer Science’ department along with the total marks of all subjects obtained by him (them).

The database schema are as follows:

depts(
deptcode char(3) primary key,
deptname char(70) not null);

students(
rollno number(2) primary key,
name varchar(50),
bdate date check(bdate < TO_DATE('2004-01-01','YYYY-MM-DD')),
deptcode char(3) references depts(deptcode)
on delete cascade,
hostel number check(hostel<20),
parent_inc number(8,1));

crs_regd(
crs_rollno number(2) references students(rollno) on delete cascade,
on delete cascade,
marks number(5,2),
primary key(crs_rollno));

I wrote the query as

select s.name, s.bdate, sum(c.marks) as marks from students s
inner join crs_regd c
on s.rollno=c.crs_rollno
inner join (
select st.name, min(st.bdate) as bdate from students st, depts d
where st.deptcode=d.deptcode and d.deptname='Computer Science and Technology' group by st.name) x
on x.name=s.name and x.bdate=s.bdate group by s.name,s.bdate;

The obtained output is :

JOSH MARTIN                                14-JUN-99         274
GARY LEWIS                                 30-JUN-99         90
SUSANE SHAW                                26-JAN-99         185

(where each column represents the marks obtained in a different subject, all belong to `Computer Science' department)

Expected output 

SUSANE SHAW                                26-JAN-99         185

Where am I making a mistake? If I am wrong please inform. If any other information required please drop a comment below. If there is other way to write this query please discuss.

Upvotes: 0

Views: 1132

Answers (2)

Fahmi
Fahmi

Reputation: 37473

You can try below -

select s.name, s.bdate, sum(c.marks) as marks from students s
inner join crs_regd c on s.rollno=c.crs_rollno
where s.bdate in 
(select min(st.bdate) as bdate from students st inner join depts d
on st.deptcode=d.deptcode and d.deptname='Computer Science and Technology')
group by s.name, s.bdate;

Upvotes: 1

Olivier Depriester
Olivier Depriester

Reputation: 1625

The group by in the subquery is wrong.
EDIT : As I do agree with the join usage comment, I rewrote the query

SELECT s.name, s.bdate, SUM(c.marks) AS marks 
FROM students s
        INNER JOIN crs_regd c ON s.rollno=c.crs_rollno
        INNER JOIN (
            SELECT MIN(st.bdate) AS bdate
            FROM students st
                    INNER JOIN depts d ON st.deptcode=d.deptcode
            WHERE d.deptname='Computer Science and Technology'
        ) x ON x.bdate = s.bdate
GROUP BY s.name, s.bdate;

The subquery you wrote returns the min birth date for each student instead of the min birthdate for the whole department

Upvotes: 1

Related Questions