6

How to Find Average Marks of Each Student in SQL?

 11 months ago
source link: https://www.geeksforgeeks.org/how-to-find-average-marks-of-each-student-in-sql/
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

How to Find Average Marks of Each Student in SQL?

In SQL, sometimes we need to find the average value of a column based on another column of the table such as finding the student-wise average marks scored by him/her in all the subjects. This involves the use of the GROUP BY clause along with the AGGREGATE function like AVG. The same is depicted in the below article. For this article, we will be using the Microsoft SQL Server as our database.

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

gfg1.jpeg

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

gfg2.jpeg

Step 3: Create a table of STDMARKS inside the database GeeksForGeeks. This table has 4 columns namely ROLL_NO, STUDENT_NAME, SUBJECT, and MARKS containing the roll number and name of the students and the subject name and marks scored by the student in the respective subject.

Query:

CREATE TABLE STDMARKS(
ROLL_NO INT,
STUDENT_NAME VARCHAR(10),
SUBJECT VARCHAR(10),
MARKS INT);

Output:

WhatsAppImage20211125at143254.jpeg

Step 4: Describe the structure of the table STDMARKS.

Query:

EXEC SP_COLUMNS STDMARKS;

Output:

WhatsAppImage20211125at144437.jpeg

Step 5: Insert 16 rows into the STDMARKS table.

Query:

INSERT INTO STDMARKS VALUES(1,'RAVI','C++',75);
INSERT INTO STDMARKS VALUES(1,'RAVI','ORACLE',84);
INSERT INTO STDMARKS VALUES(1,'RAVI','JAVA',95);
INSERT INTO STDMARKS VALUES(1,'RAVI','OS',62);
INSERT INTO STDMARKS VALUES(2,'VIVEK','C++',46);
INSERT INTO STDMARKS VALUES(2,'VIVEK','ORACLE',70);
INSERT INTO STDMARKS VALUES(2,'VIVEK','JAVA',55);
INSERT INTO STDMARKS VALUES(2,'VIVEK','OS',56);
INSERT INTO STDMARKS VALUES(3,'RAJ','C++',82);
INSERT INTO STDMARKS VALUES(3,'RAJ','ORACLE',84);
INSERT INTO STDMARKS VALUES(3,'RAJ','JAVA',74);
INSERT INTO STDMARKS VALUES(3,'RAJ','OS',92);
INSERT INTO STDMARKS VALUES(4,'MAHESH','C++',43);
INSERT INTO STDMARKS VALUES(4,'MAHESH','ORACLE',49);
INSERT INTO STDMARKS VALUES(4,'MAHESH','JAVA',68);
INSERT INTO STDMARKS VALUES(4,'MAHESH','OS',35);

Output:

WhatsAppImage20211125at143746.jpeg

Step 6: Display all the rows of the STDMARKS table.

Query:

SELECT * FROM STDMARKS;

Output:

WhatsAppImage20211125at143843.jpeg

Step 7: Display the ROLL_NO of the student and the average marks scored by that student in all subjects from the table STDMARKS. For achieving the aggregate value of a column according to another column, we need to use the GROUP BY clause along with the aggregate function such as AVG. The column mentioned after the GROUP BY clause is the basis for our output. The student-wise average marks are achieved in this manner.

Syntax:

SELECT COLUMN_NAME1,AVG(COLUMN_NAME2)
AS ALIAS FROM TABLE_NAME GROUP BY COLUMN_NAME1;

Query:

SELECT ROLL_NO,AVG(MARKS) AS "AVERAGE MARKS"
FROM STDMARKS GROUP BY ROLL_NO;

Output:

WhatsAppImage20211125at144335.jpeg
Last Updated : 28 Nov, 2021
Like Article
Save Article

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK