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.

Student 92005102 has subjects CS103 and CS106 and you are looking for all students that have these courses too and are better than student 92005102 in both courses? — Thorsten Kettner, Aug 21 '19 at 14:03

Thorsten Kettner · Answer 1 · 2019-08-21T15:20:44.140

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);

Popeye · Answer 2 · 2019-08-21T14:38:32.543

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!!

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

2 Answers2