Problem in querying specific data based on values present in different rows of the same column

Question

I am creating a database of a college and the tables are given as:

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

create table students(
rollno number(2) primary key,
name char(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));

create table faculty(
fac_code char(2) primary key,
fac_name char(50) not null,
fac_dept char(3) references depts(deptcode)
on delete cascade);



//for courses offered by the college

create table crs_offrd(
crs_code char(5) primary key,
crs_name char(35) not null,
crs_credits number(2,1),
crs_fac_cd char(2) references faculty(fac_code)
on delete cascade);



// for course registered by students*

create table crs_regd(
crs_rollno number(2) references students(rollno),
crs_cd char(5) references crs_offrd(crs_code)
on delete cascade,
marks number(5,2),
primary key(crs_rollno,crs_cd));

I am trying to find out name , subject and marks of students who have marks more than rollno 92005102 for course CS103 and CS106.

I believe the table should look like this:

Name                               Subject                    Marks
XYZ                                 CS103                      92
XYZ                                 CS106                      95

I am confused how to check for marks in both CS103 and CS106 at the same time as the marks for two subjects are present in the same column in a different row, and query processes one row at a time.

I followed the question posted here but it works only for a column and it would list values separated by , but I need to get the corresponding subject name in which that marks was obtained.

If any other information required, please comment.

Answer 1

There are probably many ways to achieve this. One is this:

select
  crs_rollno,
  max(case when crs_cd = 'CS103' then marks end) as marks103,
  max(case when crs_cd = 'CS106' then marks end) as marks106
from crs_regd cr
where crs_cd in ('CS103', 'CS106')
and marks >
(
  select marks
  from crs_regd cr92005102
  where cr92005102.crs_rollno = 92005102 -- student 92005102
    and cr92005102.crs_cd = cr.crs_cd -- same class
)
group by crs_rollno
having count(*) = 2 /* both courses better than 92005102 */ ;

You can join to the students table to get their name.

Just for the fun of it another approach:

with cs103 as (select * from crs_regd where crs_cd = 'CS103')
   , cs106 as (select * from crs_regd where crs_cd = 'CS106')
select crs_rollno, cs103.marks as cs103_marks, cs106.marks as cs106_marks
from cs103 join cs106 using (crs_rollno)
where cs103.marks > (select marks from cs103 where crs_rollno = 92005102)
  and cs106.marks > (select marks from cs106 where crs_rollno = 92005102);

Answer 2

You can use group by with having as following:

-- CTE IS USED TO FETCH THE REQUIRED DATA FROM TABLE USING JOINS
WITH CTE AS (
    SELECT
        S.NAME,
        CO.CRS_NAME,
        CO.CRS_CODE,
        CR.MARKS,
        S.ROLLNO
    FROM
        STUDENTS S
        JOIN CRS_REGD CR ON ( S.ROLLNO = CR.CRS_ROLLNO )
        JOIN CRS_OFFRD CO ON ( CR.CRS_CD = CO.CRS_CODE )
    WHERE
        CO.CRS_NAME IN (
            'CS103',
            'CS106'
        )
)
-- ACTUAL LOGIC START FROM HERE
SELECT
    S.NAME,
    CO.CRS_NAME,
    CR.MARKS
FROM
    CTE C3
WHERE
    C3.ROLLNO IN (
        SELECT
            C2.ROLLNO
        FROM
            CTE C1
            JOIN CTE C2 ON ( C1.ROLLNO = 92005102
                             AND C1.ROLLNO <> C2.ROLLNO
                             AND C1.CRS_CODE = C2.CRS_CODE )
        GROUP BY
            C2.ROLLNO
        HAVING ( MAX(CASE
            WHEN C1.CRS_NAME = 'CS103'
                 AND C2.MARKS >= C1.MARKS THEN 1
        END) = 1
                 AND MAX(CASE
            WHEN C1.CRS_NAME = 'CS106'
                 AND C2.MARKS >= C1.MARKS THEN 1
        END) = 1 )
    );

Cheers!!