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 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 name, bdate, sum(marks) from students s inner join crs_regd c on s.rollno=c.crs_rollno inner join ( select name, min(bdate) as bdate from students st, depts d where st.deptcode=d.deptcode and d.deptname="Computer Science") x on x.name=s.name and x.bdate=s.bdate;
but it gives the error as
identifier is too long in the line
where st.deptcode=d.deptcode and d.deptname="Computer Science") x
I think the problem is in joining the join of two tables with already joined two tables as the error points out. Correct me if I am wrong. If the query is incorrect please inform.
Also, if there is a way to write this query please discuss. If any other information needed please drop a comment below.