SQL Query to Find all the Students with Marks Greater than Average Marks

Given task is to find all the students with marks greater than average marks using SQL. i.e. if the marks of students in a class are 65, 75, 89, 92, and, 60 then the average would be 76.2 and, the query should return records with the marks 89 and 92.

Let's start step by step:

Creating a Table

First of all, we need to create a table called Students using the CREATE statement. This table has three columns Student_Id, Subject, and Marks. This table stores three types of subjects, and marks range from 0 to 100 −

CREATE TABLE Students(
   Student_Id    INT NOT NULL,
   Subject       VARCHAR (20) NOT NULL,
   Marks         INT NOT NULL
);

Populate the Table

Let us insert records into this table using the INSERT INTO statement as follows −

INSERT INTO (Student_Id, Subject, Marks ) Students VALUES 
(1, 'Math', 75), 
(2, 'Math', 82),
(3, 'Math', 95),
(4, 'English', 67),
(5, 'English', 78),
(6, 'English', 88),
(7, 'Science', 100),
(8, 'Science', 92),
(9, 'Science', 79);

Displaying the Data

Now, let's display these 9 records using the SELECT query.

SELECT * FROM Students;

This query after executing will display all 9 records with their 3 columns as follows −

Student_Id	Subject	Marks
1	Math	75
2	Math	82
3	Math	95
4	English	67
5	English	78
6	English	88
7	Science	100
8	Science	92
9	Science	79

The above is the detailed content of SQL Query to Find all the Students with Marks Greater than Average Marks. For more information, please follow other related articles on the PHP Chinese website!