Examine the description of the MARKS table: STD_ID NUMBER(4) STUDENT_NAME VARCHAR2(30) SUBJ1 NUMBER(3) SUBJ2 NUMBER(3) SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects. Examine this SELECT statement based on the MARKS table: SELECT subj1+subj2 total_marks, std_id FROM marks WHERE subj1 > AVG(subj1) AND subj2 > AVG(subj2) ORDER BY total_marks; What is the result of the SELECT statement?

technology databases

  1. The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject.

  2. The statement returns an error at the SELECT clause.

  3. The statement returns an error at the WHERE clause.

  4. The statement returns an error at the ORDER BY clause.

Show answer
Correct Option: C

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject. This option is incorrect because the SELECT statement is calculating the total marks by adding subj1 and subj2, not the sum of all marks. Additionally, the WHERE clause is checking if each subject's mark is greater than the average mark for that subject, not the sum of all marks.

Option B) The statement returns an error at the SELECT clause. This option is incorrect because there is no error in the SELECT clause. The SELECT clause is calculating the total marks correctly by adding subj1 and subj2.

Option C) The statement returns an error at the WHERE clause. This option is correct. The WHERE clause is using the AVG function to calculate the average mark for each subject. However, you cannot use aggregate functions like AVG in the WHERE clause directly. To use aggregate functions in the WHERE clause, you need to use a subquery or a HAVING clause.

Option D) The statement returns an error at the ORDER BY clause. This option is incorrect because there is no error in the ORDER BY clause. The ORDER BY clause is ordering the result set based on the total_marks column.

The correct answer is C. The statement returns an error at the WHERE clause because you cannot use aggregate functions like AVG in the WHERE clause directly.

Find more quizzes: