7

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

 2 years ago
source link: https://www.geeksforgeeks.org/sql-query-to-find-all-the-students-with-marks-greater-than-average-marks/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

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

  • Last Updated : 19 Apr, 2021

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 “geeks” 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 geeks database as shown below:

CREATE DATABASE geeks;
nik1.PNG

Using Database:

Use the below command to use the geeks database:

USE geeks;
nik2.PNG

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 geeks 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;
studenttable-660x106.PNG

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);
insertvalue.PNG

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 :

Studenttablevalues.PNG

The queryfor 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:

studenttableoutput.PNG

2022-05-24-14-39-28-SQL%20_.webp

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK