CODEWITHSUNDEEP
oracle-database
Sakshi Negi
Sakshi Negi

Reputation: 15

students names and their marks in Computer Programming

Write a query to display the students names and their marks in Computer Programming order by marks in ascending order and then ordered by name in descending order. Give an alias to marks as CP_MARKS.

i have tried this one.

     select student_name, value from
        (select subject_id, student_id 
        from student s, subject su, mark m 
        where s.student_id=m.student_id 
        and su.subject_id=m.subject_id
        group by student_id
        order by student_id)
        where subject_name='Computer Programming'
        order by value;

This is the schema : enter image description here

Upvotes: 0

Views: 5190

Answers (3)

SALAI ARUNMANI J B
SALAI ARUNMANI J B

Reputation: 1

select s.student_name,m.value as cp_marks from student s join mark m on s.student_id=m.student_id join subject su on su.subject_id=m.subject_id where s.subject_name='COMPUTER PROGRAMMING' or 'computer programming' order by m.value,s.student_name desc;

Upvotes: 0

omkar
omkar

Reputation: 1

select student_name,value as CP_MARKS
    from mark m inner join student s
    on s.student_id=m.student_id
    where subject_id=(select subject_id from subject where lower(subject_name)='computer_programming')
    order by value asc ,student_name desc;

Upvotes: 0

forpas
forpas

Reputation: 164099

You need to join the tables mark, student and subject, by using proper joins.
Then apply the condition subject_name = 'Computer Programming' and finally order the result:

select 
  st.student_name,
  m.value CP_MARKS
from mark m
inner join student st on st.student_id = m.student_id
inner join subject su on su.subject_id = m.subject_id
where su.subject_name = 'Computer Programming'
order by m.value, st.student_name desc

Upvotes: 1

Related Questions