Database-Management-System
March 27, 2024UGC NET CS 2015 June Paper-3
March 27, 2024Database-Management-System
Question 443
|
The STUDENT information in a university stored in the relation STUDENT (Name, SEX, Marks, DEPT_Name) Consider the following SQL Query SELECT DEPT_Name from STUDENT where SEX = ‘M’ group by DEPT_Name having avg (Marks)>SELECT avg (Marks) from STUDENT. It Returns the Name of the Department for which:
The Average marks of Male students is more than the average marks of students in the same Department
|
|
The average marks of male students is more than the average marks of students in the University
|
|
The average marks of male students is more than the average marks of male students in the University
|
|
The average marks of students is more than the average marks of male students in the University
|
Question 443 Explanation:
The query is co-related( same table is used in outer and inner query) so compare each entry of Student table 1 with each entry of Student table 2.
![](https://solutionsadda.in/wp-content/uploads/2019/08/Screenshot-from-2019-08-26-15-36-12.png)
⇒ Consider this table for outer query i.e., LHS query of “>” symbol and here find avg. marks of a department because of group by constraint
i.e., avg. marks for CSE = 87.5
avg. marks for IT = 90
![](https://solutionsadda.in/wp-content/uploads/2019/08/Screenshot-from-2019-08-26-15-36-12.png)
![](https://solutionsadda.in/wp-content/uploads/2019/08/Screenshot-from-2019-08-26-15-37-19.png)
⇒ Consider this table for inner query i.e., RHS query of “>” symbol and here find avg. of total marks
i.e., avg. marks = 88.7
⇓ O/P of given query is
![](https://solutionsadda.in/wp-content/uploads/2019/08/Screenshot-from-2019-08-26-15-39-22.png)
![](https://solutionsadda.in/wp-content/uploads/2019/08/Screenshot-from-2019-08-26-15-36-12.png)
⇒ Consider this table for outer query i.e., LHS query of “>” symbol and here find avg. marks of a department because of group by constraint
i.e., avg. marks for CSE = 87.5
avg. marks for IT = 90
![](https://solutionsadda.in/wp-content/uploads/2019/08/Screenshot-from-2019-08-26-15-36-12.png)
![](https://solutionsadda.in/wp-content/uploads/2019/08/Screenshot-from-2019-08-26-15-37-19.png)
⇒ Consider this table for inner query i.e., RHS query of “>” symbol and here find avg. of total marks
i.e., avg. marks = 88.7
⇓ O/P of given query is
![](https://solutionsadda.in/wp-content/uploads/2019/08/Screenshot-from-2019-08-26-15-39-22.png)
Correct Answer: B
Question 443 Explanation:
The query is co-related( same table is used in outer and inner query) so compare each entry of Student table 1 with each entry of Student table 2.
![](https://solutionsadda.in/wp-content/uploads/2019/08/Screenshot-from-2019-08-26-15-36-12.png)
⇒ Consider this table for outer query i.e., LHS query of “>” symbol and here find avg. marks of a department because of group by constraint
i.e., avg. marks for CSE = 87.5
avg. marks for IT = 90
![](https://solutionsadda.in/wp-content/uploads/2019/08/Screenshot-from-2019-08-26-15-36-12.png)
![](https://solutionsadda.in/wp-content/uploads/2019/08/Screenshot-from-2019-08-26-15-37-19.png)
⇒ Consider this table for inner query i.e., RHS query of “>” symbol and here find avg. of total marks
i.e., avg. marks = 88.7
⇓ O/P of given query is
![](https://solutionsadda.in/wp-content/uploads/2019/08/Screenshot-from-2019-08-26-15-39-22.png)
![](https://solutionsadda.in/wp-content/uploads/2019/08/Screenshot-from-2019-08-26-15-36-12.png)
⇒ Consider this table for outer query i.e., LHS query of “>” symbol and here find avg. marks of a department because of group by constraint
i.e., avg. marks for CSE = 87.5
avg. marks for IT = 90
![](https://solutionsadda.in/wp-content/uploads/2019/08/Screenshot-from-2019-08-26-15-36-12.png)
![](https://solutionsadda.in/wp-content/uploads/2019/08/Screenshot-from-2019-08-26-15-37-19.png)
⇒ Consider this table for inner query i.e., RHS query of “>” symbol and here find avg. of total marks
i.e., avg. marks = 88.7
⇓ O/P of given query is
![](https://solutionsadda.in/wp-content/uploads/2019/08/Screenshot-from-2019-08-26-15-39-22.png)
Subscribe
Login
0 Comments