Can anyone explain this question?

Question

https://www.sololearn.com/post/163015/?ref=app

Answer 1

Preity

1 - If you use this query then results will come wrong and it will display Students name with total marks which is greater than 84.

Select S.Student_name, sum(P.Marks)
from Student S, Performance P
Where P.marks>84
Group BY S.Student_name;

2- if you use this query then Actual results will come

Select S.Student_name, sum(P.Marks)
from Student S, Performance P
Where P.marks>84 and S.Roll_no = p.Roll_no
Group BY S.Student_name;

3 - if you remove GROUP BY then this error will come.

Column 'Student.Student_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 2

Ipang I think you didn't understand my previous reply.😂😂 Let me change it.

"Primary key of one table can contains duplicate records in other tables but not in Self Table but for other table Primary Key can be foreign key."

So Roll_No is Foreign Key in Performance table.

Answer 3

Ipang Primary key can contains duplicate records in other tables but not in Primary Table but for other table primary key can be foreign key. In Performance table roll_no is Foreign Key

Answer 4

Ipang Performance.Roll_no is not Primary Key in Performance table that's Foreign Key which refers Table Students. So in table Performance Roll_No can have multiple records.

Answer 5

Ipang Check again. I posted.

Answer 6

Is it possible and is it a good idea to use digest as primary key in the database?

Answer 7

Ipang Check this what I said

"Ipang I was not saying that Roll_No in Performance Table is Primary Key.

Let me explain this line :-

"But for other table Primary key can be foreign key".

But for Performance Table, Primary Key Roll_No of Students Table can be Foreign Key."

Check in above statement I said "Primary Key Roll_No of Students Table" - you didn't get this?

Answer 8

Ipang with group by it gives 5 rows but without group by I don't know what will it gives because I'm assuming without group by statement error will be generated

Answer 9

AJ || ANANT || AC || ANANY || AY
Then help me understand your previous reply?

Answer 10

A J
Primary key should never have duplicates. What can have duplicates are foreign key in other tables that references the primary key.

In this case Students.Roll_no is primary key, each record has unique value. While Performance.Roll_no is a foreign key that references Students.Roll_no.

You cannot say primary key can contain duplicate in whichever table, because it is only a foreign key that can have duplicates in the tables, not the table's primary key.

"But for other table Primary key can be foreign key". How can that be?. A column is said a foreign key when its data references/looks up to another table. How can a column be primary key when it references other table's column?

Answer 11

Ipang if we do Cartesian product in above query like full join then too the same result will be there I'll try and let you know about that

Answer 12

Ipang I agree Primary Key never have duplicate. But you still didn't understand my reply.

I said Primary Key of Student table can have duplicate records in Performance Table but for Performance table Primary Key of Student table is Foreign Key.

So Roll_No in Performance Table is Foreign Key which refers The table Students in which Roll_No is Primary Key.

Now understood? Or still confusion?

Answer 13

Ipang I was not saying that Roll_No in Performance Table is Primary Key.

Let me explain this line :-

"But for other table Primary key can be foreign key".

But for Performance Table, Primary Key Roll_No of Students Table can be Foreign Key.

Answer 14

Rafet Eyup Wrong place.

Answer 15

A J
The way you wrote your explanation may mislead those who doesn't know to believe that primary key can.have duplicate data.

"But for Performance table, Primary key Roll_no of Students Table can be Foreign Key."

But for Performance table, the Performance.Roll_no column is a foreign key that references the Students.Roll_no column.

And the Students.Roll_no column is the Students table primary key, it is not a foreign key.

Answer 16

A J
I already read that, however as I said, the way you wrote the explanation can be misleading, especially for those who didn't know.

Answer 17

A J
Please understand, none of what I said was pointed at you. Just how the explanation was written that's all. I got nothing against you and I meant no offense nor (as some said or think) disrespect.

And it was a good discussion indeed.

Thanks bro ✌

Answer 18

Query will not work if you remove group by. Try once. It may give error.

Answer 19

AJ || ANANT || AC || ANANY || AY do you have tried that removing last line from query.
As there are roll number by which query can be access?

Answer 20

I am not using system this time but I know that it can give error.