Query for listing rolls of students who got >50(greater than 50) in either mathematics or computer table

Transact-SQL
1

Mathematics. (Table)

Roll Mmarks Name

  1.    30.        X Roy

  2.    40.        P De

  3.    50.        Z roy

  4.    60.        P roy

  5.    75.        Q De

Computer(Table)

Roll Compmarks

  1.    70

2 50
3 60
4 80
5 90

Query.1
List roll of students who got > 50 either in mathematics or computer

Query.2
List roll of students who got >50 both in mathematics and computer

2

Welcome

This looks like homework?

3

Hi. Well my computer exam is the day after tomorrow and while practicing previous year questions I came across this query. And sadly out teacher is not able to help out. It would be a great if you were to help out.

Well for now the solution I came accross is:

Query-1

Select roll from computer
Where Compmarks>50
Union
Select roll from mathematics
Where Mmarks>50;

Query-2

Select computer.roll
From computer, mathematics
Where computer.roll=mathematics.roll and mmarks>50 and Compmarks>50;

Let me know if there is other proper syntax. And thenks for showing interest.

4

hi

another way for Query 1

select 
    a.Roll 
from 
    Mathematics a join Computer b on a.roll =b.roll 
where (a.Mmarks>50 OR b.CompMarks > 50)

another way for Query 2

select 
    a.Roll 
from 
    Mathematics a join Computer b on a.roll =b.roll 
where (a.Mmarks>50 AND b.CompMarks > 50)
5

Thanks for the help man.

6

there are lots of things Rad699

should you use JOIN ?
should you use WHERE clause
instead of where clause can you use join = table a Join table b ON a.Marks > 50
what should you use WHY and the benefits and downsides .. weighing the pros and cons
acceptable risks

Njoy .. since you are just starting out

7

I came across a problem on similar example. So in an another example the students has different rolls in Mathematics and computer table so when I'm trying to join the two tables based on a.roll=b.roll , the results that I'm getting is that the students with equal roll who got marks >50 only showing up, where i would like the result of roll of students in either table who got marks>50 to show up.
Could help me with that?
I mean i don't want to join based on a.roll=b.roll.

8

select roll from table1 where > 50
union
select roll from table2 where > 50