SQL Query to Find all the Students with Marks Greater than Average Marks
Query in SQL is like a statement that performs a task. Here, we need to write a query to find all the students whose marks are greater than the average marks of students.
We will first create a database named “Beginner” then we will create a table “Students” in that database.
Create a Database:
We can create a Database using the command:
Syntax: CREATE DATABASE DATABASE_NAME;
So let’s create a Beginner database as shown below:
CREATE DATABASE Beginner;
Using Database:
Use the below command to use the Beginner database:
USE Beginner;
Adding table into Database:
To add a table into the database we use the below command:
Syntax: CREATE TABLE table_name (Attribute_name datatype...);
So, let’s create a students table within the Beginner database as shown below:
CREATE TABLE Students( Id int, Name varchar(20), TotalMarks int);
Here Table Added Successfully.
To see the description of the table:
EXEC sp_columns Students;
Inserting values into Tables:
For inserting records into the table we can use the below command:
Syntax: INSERT INTO table_name(column1, column2, column 3,.....) VALUES( value1, value2, value3,.....);
So let’s add some records to the students table:
INSERT INTO Students VALUES (1,'Neha',90); INSERT INTO Students VALUES (2,'Sahil',50); INSERT INTO Students VALUES (3,'Rohan',70); INSERT INTO Students VALUES (4,'Ankita',80); INSERT INTO Students VALUES (5,'Rahul',65); INSERT INTO Students VALUES (6,'Swati',55); INSERT INTO Students VALUES (7,'Alka',75);
Now let us print the data available in the table using the SELECT statement as shown below:
SELECT * FROM department;
Note: Here * represents all. If we execute this query, the entire table will be displayed.
Output :
The query for the data:
Use the below syntax for querying for all students with greater marks than the average of the class:
Syntax: SELECT column1 FROM table_name WHERE column2 > (SELECT AVG( column2) FROM table_name);
Now use the above syntax to make the query on our students table as shown below:
SELECT Name FROM Students WHERE TotalMarks > (SELECT AVG(TotalMarks) FROM Students);
Output:
Contact Us