Relational database with Student (Roll_no, Student_name) and Performance (Roll_no, Subject_code, Mar

Q: What is GATE-CS?

GATE-CS is a competitive examination conducted in India. This question is from the 2019 GATE-CS Database Management Systems paper.

Q: How can I prepare for GATE-CS Database Management Systems?

To prepare for GATE-CS Database Management Systems, practice previous year questions (PYQs), understand concepts thoroughly, and solve mock tests regularly. This 2019 question covers SQL (Joins, Subqueries, Group By, Aggregation) topic.

Q: What is the difficulty level of this question?

This GATE-CS 2019 Database Management Systems question has a hard difficulty level and is marked as an important question.

Q: Where can I find more GATE-CS previous year questions?

You can find more GATE-CS previous year questions on AspirantMitraa. Browse by subject, topic, year, and difficulty level to practice effectively.

Question

Relational database with Student (Roll_no, Student_name) and Performance (Roll_no, Subject_code, Marks). SQL: SELECT S.Student_name, sum(P.Marks) FROM Student S, Performance P WHERE P.Marks > 84 GROUP BY S.Student_name. The number of rows returned is _________

Accepted Answer

Answer: 5Concept: SQL Cross Join and GROUP BYThe query uses a comma-separated FROM clause (equivalent to CROSS JOIN) between Student and Performance. There is no join condition between S and P. This creates a cross product of all Student tuples and all Performance tuples where P.Marks > 84.Performance rows with Marks > 84: (1,A,86), (1,B,95), (1,C,90), (2,A,89), (2,C,92) — 5 rows satisfy Marks > 84.Cross product of 5 students × 5 qualifying performance rows = 25 rows, then GROUP BY Student_name. Student names are: Amit, Priya, Vinit, Rohan, Smita — 5 distinct names. Each name gets a group, so 5 rows are returned (regardless of whether a student has any matching Performance record, since the cross product includes all students with all qualifying performance rows).Definition: A CROSS JOIN produces a Cartesian product of two tables. GROUP BY collapses rows with the same value in the grouping column(s). The number of output rows equals the number of distinct values in the GROUP BY column(s).

Relational database with Student (Roll_no, Student_name) and Performance (Roll_no, Subject_code, Marks). SQL: SELECT S.Student_name, sum(P.Marks) FROM Student S, Performance P WHERE P.Marks > 84 GROUP BY S.Student_name. The number of rows returned is _________

Master GATE-CS with Our Test Series

More Questions from SQL (Joins, Subqueries, Group By, Aggregation)

Frequently Asked Questions

Relational database with Student (Roll_no, Student_name) and Performance (Roll_no, Subject_code, Marks). SQL: SELECT S.Student_name, sum(P.Marks) FROM Student S, Performance P WHERE P.Marks > 84 GROUP BY S.Student_name. The number of rows returned is _________

Master GATE-CS with Our Test Series

More Questions from SQL (Joins, Subqueries, Group By, Aggregation)

Frequently Asked Questions