Oracle SQL nested subquery-CodePudding

Write a query to display list of subject names and minimum mark scored in 'Software Engineering' and 'Computer Programming' . Give an alias name as MIN_MARK for minimum mark column. Sort the result based on subject name in descending order.

 select subject_name , value as MIN_MARK from mark,subject where
 mark.subject_id = subject.subject_id and value in 
 (select min(value) from mark,subject where
 mark.subject_id = subject.subject_id and
 lower(subject_name) = 'computer engineering' and
 lower(subject_name) = 'software engineering')
 order by subject_name desc;

the required answer is

subject_name	MIN_MARK
Computer Programming	65
Software Engineering	61

dont worry about contents of the db,help me get both the subject names in the output

CodePudding user response：

Your main error: The WHERE clause looks at one row at a time. There can't be a row in the table that matches both subjects:

where ...
and lower(subject_name) = 'computer engineering'
and lower(subject_name) = 'software engineering'

It must be

where ...
  and 
  (
      lower(subject_name) = 'computer engineering'
    or
      lower(subject_name) = 'software engineering'
  )

or simply

where ...
and lower(subject_name) in ('computer engineering', 'software engineering')

Then, you are making things more complicated than necessary. If you want one result row per subject_name, then GROUP BY subject_name.

The join syntax you are using has been deprecated for thirty years. Don't use it. If you are taught this syntax, quit that class, book or tutorial. Use explicit ANSI joins that got introduced in standard SQL in 1992.

select
  s.subject_name,
  min(m.value) as min_mark
from subject s
left outer join mark m on m.subject_id = s.subject_id
where lower(s.subject_name) in ('computer engineering', 'software engineering')
group by s.subject_name
order by s.subject_name desc;

I am using an outer join here for the case a subject does not have marks yet, but shall still be shown in the output. If this is not desired, change LEFT OUTER JOIN to INNER JOIN.

CodePudding user response：

The WITH clause is here just to generate some sample data and, as such, it is not a part of the answer. Sample data:

WITH
    subject AS
        (
            Select 1 "SUBJ_ID", 'Software Engineering'     "SUBJ_NAME", 'A1' "SUBJ_CODE", 11 "STAFF_ID" From Dual Union All
            Select 2 "SUBJ_ID", 'Computer Programming'     "SUBJ_NAME", 'A2' "SUBJ_CODE", 12 "STAFF_ID" From Dual Union All
            Select 3 "SUBJ_ID", 'Regenerative Cybernetics' "SUBJ_NAME", 'A3' "SUBJ_CODE", 13 "STAFF_ID" From Dual 
        ),
    mark AS
        (
            Select 61 "VALUE", 1 "SUBJ_ID", 101 "STUDENT_ID" From Dual Union All
            Select 65 "VALUE", 2 "SUBJ_ID", 101 "STUDENT_ID" From Dual Union All
            Select 74 "VALUE", 3 "SUBJ_ID", 101 "STUDENT_ID" From Dual Union ALL
            Select 72 "VALUE", 1 "SUBJ_ID", 102 "STUDENT_ID" From Dual Union All
            Select 69 "VALUE", 2 "SUBJ_ID", 102 "STUDENT_ID" From Dual Union All
            Select 66 "VALUE", 3 "SUBJ_ID", 102 "STUDENT_ID" From Dual Union ALL
            Select 91 "VALUE", 1 "SUBJ_ID", 103 "STUDENT_ID" From Dual Union All
            Select 67 "VALUE", 2 "SUBJ_ID", 103 "STUDENT_ID" From Dual Union All
            Select 70 "VALUE", 3 "SUBJ_ID", 103 "STUDENT_ID" From Dual 
        )

If you need just subject name and minimum value then you don't have to use nested query. Also, in your question, ordering should be on subject name descending but in your required answer the order is by minimum value descending.
Hope this could help you get it done:

Select 
    s.SUBJ_NAME "SUBJ_NAME",
    Min(m.VALUE) "MIN_MARK"
From 
    MARK m
Inner Join
    SUBJECT s ON(m.SUBJ_ID = s.SUBJ_ID)
Where
    s.SUBJ_NAME = 'Software Engineering' 
    OR 
    s.SUBJ_NAME = 'Computer Programming'
Group By
    s.SUBJ_NAME
Order By
    s.SUBJ_NAME DESC

... resulting as:

--  R e s u l t :
--  
--  SUBJ_NAME                  MIN_MARK
--  ------------------------ ----------
--  Software Engineering             61 
--  Computer Programming             65

Better way of filtering that would end up with the same result (if you know IDs) is:

Where
    s.SUBJ_ID IN(1, 2)

CodePudding user response：

I solved the above question

select subject_name,min(value) as MIN_MARK from mark,subject where
mark.subject_id = subject.subject_id and 
lower(subject_name) in ('computer programming' , 'software engineering')
group by subject_name
order by subject_name desc;

The output: