MySQL CREATE FUNCTION Statement | MySQL Creating stored function | Create Functions My SQL
Step 1: Create a New Query
First of all, open MySQL Workbench.
After opening MySQL Workbench, go to the SQL Editor and open a new Query Tab.
This is where we are going to write all of our SQL commands.
The first thing we need to do is create a database.
After opening MySQL Workbench, go to the SQL Editor and open a new Query Tab.
This is where we are going to write all of our SQL commands.
The first thing we need to do is create a database.
Step 2: Create the Database
Since we are creating a marksheet for students, let's create a database named school.
Write the following command:
CREATE DATABASE school;
After writing the command, select it and click on the Run button.
If the query runs successfully, our database has been created.
So now we have a database called school, and we will use this database for our student marksheet.
Write the following command:
CREATE DATABASE school;
After writing the command, select it and click on the Run button.
If the query runs successfully, our database has been created.
So now we have a database called school, and we will use this database for our student marksheet.
Step 3: Select the Database
Creating the database is not enough.
We also need to tell MySQL that we want to work inside this particular database.
For that, we use the USE command.
Write:
USE school;
Now run the query.
After running this command, the school database becomes our active database.
From this point onward, the tables that we create will be created inside the school database.
We also need to tell MySQL that we want to work inside this particular database.
For that, we use the USE command.
Write:
USE school;
Now run the query.
After running this command, the school database becomes our active database.
From this point onward, the tables that we create will be created inside the school database.
Step 4: Create the Student Marks Table
Now let's create a table where we can store the students' information and marks.
We will call this table marks.
Before creating the table, let's decide what information we want to store.
We need the student's roll number, name, marks in Hindi, Maths, Science, and English.
Apart from these subjects, we also need two more columns.
One will be Total, where we will calculate the total marks.
The second will be Average, where we will calculate the student's percentage.
So, our table will have eight columns in total.
Now write the following command:
CREATE TABLE marks (
roll_no INT,
name VARCHAR(50),
hindi INT,
maths INT,
science INT,
english INT,
total INT,
average DECIMAL(5,2)
);
Let's understand this command.
The first column is roll_no.
We are using INT because the roll number will contain a number.
The second column is name.
For the name, we are using VARCHAR(50), which means we can store text with a maximum length of 50 characters.
Next, we have Hindi, Maths, Science, and English.
All of these columns are INT because they will contain numerical marks.
After that, we have the total column.
This column will be used for the total marks.
Finally, we have the average column.
We are using DECIMAL(5,2) here because the average can contain decimal values such as 72.25 or 85.50.
Now run the query.
Our marks table has been created successfully.
We will call this table marks.
Before creating the table, let's decide what information we want to store.
We need the student's roll number, name, marks in Hindi, Maths, Science, and English.
Apart from these subjects, we also need two more columns.
One will be Total, where we will calculate the total marks.
The second will be Average, where we will calculate the student's percentage.
So, our table will have eight columns in total.
Now write the following command:
CREATE TABLE marks (
roll_no INT,
name VARCHAR(50),
hindi INT,
maths INT,
science INT,
english INT,
total INT,
average DECIMAL(5,2)
);
Let's understand this command.
The first column is roll_no.
We are using INT because the roll number will contain a number.
The second column is name.
For the name, we are using VARCHAR(50), which means we can store text with a maximum length of 50 characters.
Next, we have Hindi, Maths, Science, and English.
All of these columns are INT because they will contain numerical marks.
After that, we have the total column.
This column will be used for the total marks.
Finally, we have the average column.
We are using DECIMAL(5,2) here because the average can contain decimal values such as 72.25 or 85.50.
Now run the query.
Our marks table has been created successfully.
Step 5: Check the Table Structure
Before entering any data, let's check whether our table has been created correctly.
For this, we use the DESCRIBE command.
Write:
DESCRIBE marks;
Run the query.
Now MySQL will show us the complete structure of the table.
Here we can see all the columns that we created, along with their data types.
This is useful because if we accidentally create a wrong column name or data type, we can identify the problem here.
For this, we use the DESCRIBE command.
Write:
DESCRIBE marks;
Run the query.
Now MySQL will show us the complete structure of the table.
Here we can see all the columns that we created, along with their data types.
This is useful because if we accidentally create a wrong column name or data type, we can identify the problem here.
Step 6: Insert Student Data
Now our table is ready, so the next step is to enter student records.
We could insert each student one by one, but MySQL also allows us to insert multiple records using a single INSERT query.
Let's enter five students.
Write:
INSERT INTO marks
(roll_no, name, hindi, maths, science, english, total, average)
VALUES
(1, 'Raman', 78, 89, 66, 56, 0, 0),
(2, 'Manish', 85, 76, 90, 80, 0, 0),
(3, 'Rahul', 99, 88, 75, 85, 0, 0),
(4, 'Aman', 70, 82, 65, 78, 0, 0),
(5, 'Rohit', 90, 95, 85, 88, 0, 0);
Let's understand what is happening here.
First, we mention the table name, which is marks.
Then we mention all the column names in the same order in which we are going to enter the data.
After VALUES, we enter the information for each student.
For example, the first record belongs to Raman.
His roll number is 1.
His Hindi marks are 78.
His Maths marks are 89.
His Science marks are 66.
And his English marks are 56.
At the end, we have entered zero for Total and zero for Average.
We are doing this because we will calculate these values later using formulas.
The same thing has been done for the other four students.
Notice one important thing here.
When inserting multiple records, we separate each student's record using a comma.
Only the final record ends with a semicolon.
Now run the query.
Our five student records should now be inserted into the table.
We could insert each student one by one, but MySQL also allows us to insert multiple records using a single INSERT query.
Let's enter five students.
Write:
INSERT INTO marks
(roll_no, name, hindi, maths, science, english, total, average)
VALUES
(1, 'Raman', 78, 89, 66, 56, 0, 0),
(2, 'Manish', 85, 76, 90, 80, 0, 0),
(3, 'Rahul', 99, 88, 75, 85, 0, 0),
(4, 'Aman', 70, 82, 65, 78, 0, 0),
(5, 'Rohit', 90, 95, 85, 88, 0, 0);
Let's understand what is happening here.
First, we mention the table name, which is marks.
Then we mention all the column names in the same order in which we are going to enter the data.
After VALUES, we enter the information for each student.
For example, the first record belongs to Raman.
His roll number is 1.
His Hindi marks are 78.
His Maths marks are 89.
His Science marks are 66.
And his English marks are 56.
At the end, we have entered zero for Total and zero for Average.
We are doing this because we will calculate these values later using formulas.
The same thing has been done for the other four students.
Notice one important thing here.
When inserting multiple records, we separate each student's record using a comma.
Only the final record ends with a semicolon.
Now run the query.
Our five student records should now be inserted into the table.
Step 7: Display the Student Data
Now let's check whether our records have been inserted successfully.
Write:
SELECT * FROM marks;
Run the query.
Now all five student records will appear on the screen.
We can see the roll number, name, marks for each subject, Total, and Average.
At the moment, Total and Average are showing zero.
That is completely fine because we have not applied our formulas yet.
Now let's calculate them.
Write:
SELECT * FROM marks;
Run the query.
Now all five student records will appear on the screen.
We can see the roll number, name, marks for each subject, Total, and Average.
At the moment, Total and Average are showing zero.
That is completely fine because we have not applied our formulas yet.
Now let's calculate them.
Step 8: Calculate the Total Marks
First, we will calculate the total marks.
We have four subjects:
Hindi, Maths, Science, and English.
So, to calculate the total, we simply need to add the marks of these four subjects.
Write:
SELECT roll_no, name, hindi, maths, science, english,
(hindi + maths + science + english) AS total
FROM marks;
Now run the query.
Let's understand this formula.
Inside the brackets, we are adding:
Hindi + Maths + Science + English.
The AS total part gives this calculated value the name total.
For example, let's take Raman's marks.
Hindi is 78.
Maths is 89.
Science is 66.
English is 56.
So the calculation will be:
78 + 89 + 66 + 56 = 289
Therefore, Raman's total marks are 289.
MySQL will perform the same calculation automatically for every student in our table.
We have four subjects:
Hindi, Maths, Science, and English.
So, to calculate the total, we simply need to add the marks of these four subjects.
Write:
SELECT roll_no, name, hindi, maths, science, english,
(hindi + maths + science + english) AS total
FROM marks;
Now run the query.
Let's understand this formula.
Inside the brackets, we are adding:
Hindi + Maths + Science + English.
The AS total part gives this calculated value the name total.
For example, let's take Raman's marks.
Hindi is 78.
Maths is 89.
Science is 66.
English is 56.
So the calculation will be:
78 + 89 + 66 + 56 = 289
Therefore, Raman's total marks are 289.
MySQL will perform the same calculation automatically for every student in our table.
Step 9: Calculate the Average Percentage
Now that we have calculated the total, let's calculate the average percentage.
Each subject has a maximum of 100 marks.
We have four subjects.
So the maximum possible marks are:
100 + 100 + 100 + 100 = 400.
To calculate the percentage, we use this formula:
Total Marks × 100 ÷ Maximum Marks
In our case, the maximum marks are 400.
So our formula becomes:
Total Marks × 100 ÷ 400
Now write the following query:
SELECT roll_no, name, hindi, maths, science, english,
(hindi + maths + science + english) AS total,
((hindi + maths + science + english) * 100 / 400) AS average
FROM marks;
Run the query.
Now we will get both Total and Average in the result.
Let's take Raman's example again.
His total marks are 289.
So we calculate:
289 × 100 ÷ 400
The result is 72.25.
Therefore, Raman's percentage is 72.25%.
MySQL will automatically perform this calculation for all the students.
Each subject has a maximum of 100 marks.
We have four subjects.
So the maximum possible marks are:
100 + 100 + 100 + 100 = 400.
To calculate the percentage, we use this formula:
Total Marks × 100 ÷ Maximum Marks
In our case, the maximum marks are 400.
So our formula becomes:
Total Marks × 100 ÷ 400
Now write the following query:
SELECT roll_no, name, hindi, maths, science, english,
(hindi + maths + science + english) AS total,
((hindi + maths + science + english) * 100 / 400) AS average
FROM marks;
Run the query.
Now we will get both Total and Average in the result.
Let's take Raman's example again.
His total marks are 289.
So we calculate:
289 × 100 ÷ 400
The result is 72.25.
Therefore, Raman's percentage is 72.25%.
MySQL will automatically perform this calculation for all the students.
Step 10: Understand Why We Use 400
Now let's understand one important point about the number 400.
We are using 400 because we have four subjects, and every subject has a maximum of 100 marks.
So:
4 subjects × 100 marks = 400 marks.
If you have five subjects, you will use 500 instead of 400.
If you have six subjects, you will use 600.
For example, with five subjects, the formula would be:
Total Marks × 100 ÷ 500
So whenever you add or remove subjects, remember to change the maximum marks in your formula.
We are using 400 because we have four subjects, and every subject has a maximum of 100 marks.
So:
4 subjects × 100 marks = 400 marks.
If you have five subjects, you will use 500 instead of 400.
If you have six subjects, you will use 600.
For example, with five subjects, the formula would be:
Total Marks × 100 ÷ 500
So whenever you add or remove subjects, remember to change the maximum marks in your formula.
Step 11: Display the Complete Marksheet
Now let's create one final query that displays everything together.
Write:
SELECT
roll_no,
name,
hindi,
maths,
science,
english,
(hindi + maths + science + english) AS total,
((hindi + maths + science + english) * 100 / 400) AS average
FROM marks;
Now run the query.
Here we can see the complete marksheet.
We have the Roll Number, Student Name, Hindi Marks, Maths Marks, Science Marks, English Marks, Total Marks, and Average Percentage.
The biggest advantage here is that we don't have to manually calculate the total or percentage for every student.
MySQL performs the calculation automatically using the formula.
Write:
SELECT
roll_no,
name,
hindi,
maths,
science,
english,
(hindi + maths + science + english) AS total,
((hindi + maths + science + english) * 100 / 400) AS average
FROM marks;
Now run the query.
Here we can see the complete marksheet.
We have the Roll Number, Student Name, Hindi Marks, Maths Marks, Science Marks, English Marks, Total Marks, and Average Percentage.
The biggest advantage here is that we don't have to manually calculate the total or percentage for every student.
MySQL performs the calculation automatically using the formula.
Step 12: Understand How MySQL Performs the Calculation
Let's understand the most important part once again.
This formula:
hindi + maths + science + english
adds the marks of all four subjects.
Then we use:
(hindi + maths + science + english) * 100 / 400
This converts the total marks into a percentage.
For example, if a student gets 320 marks out of 400:
320 × 100 ÷ 400 = 80
So the student's percentage will be 80%.
This is how we can use mathematical operations directly inside a SQL SELECT statement.
This formula:
hindi + maths + science + english
adds the marks of all four subjects.
Then we use:
(hindi + maths + science + english) * 100 / 400
This converts the total marks into a percentage.
For example, if a student gets 320 marks out of 400:
320 × 100 ÷ 400 = 80
So the student's percentage will be 80%.
This is how we can use mathematical operations directly inside a SQL SELECT statement.
Step 13: Important Point About the Total and Average Columns
There is one thing you should understand.
In our table, we created Total and Average columns and initially inserted zero into them.
However, in our SELECT query, we are calculating Total and Average dynamically.
That means the values shown in the result are calculated when the query runs.
We are not permanently changing the zero values stored in the table.
For learning how formulas work in MySQL, this method is simple and useful.
In our table, we created Total and Average columns and initially inserted zero into them.
However, in our SELECT query, we are calculating Total and Average dynamically.
That means the values shown in the result are calculated when the query runs.
We are not permanently changing the zero values stored in the table.
For learning how formulas work in MySQL, this method is simple and useful.
Step 14: Final Result
After running the final query, we will have a marksheet showing all the student information along with the calculated Total and Average.
For example, the result will look something like this:
Roll Number | Name | Hindi | Maths | Science | English | Total | Average
1 | Raman | 78 | 89 | 66 | 56 | 289 | 72.25
The same calculation will be performed for all the other students.
For example, the result will look something like this:
Roll Number | Name | Hindi | Maths | Science | English | Total | Average
1 | Raman | 78 | 89 | 66 | 56 | 289 | 72.25
The same calculation will be performed for all the other students.
Step 1: New Query Open करें
सबसे पहले MySQL Workbench को open कर लीजिए।
MySQL Workbench open करने के बाद SQL Editor में एक नया Query Tab open करें।
इसी Query Tab के अंदर हम अपने सारे SQL commands लिखेंगे।
सबसे पहले हमें एक database create करना होगा।Step 2: Database Create करें
क्योंकि हम students की marksheet बनाने वाले हैं, इसलिए हम एक database बनाएंगे जिसका नाम हम school रखेंगे।
इसके लिए हमें यह command लिखनी है:
CREATE DATABASE school;
अब इस query को select करके Run button पर click करें।
अगर query successfully run हो जाती है, तो हमारा school database create हो चुका है।
तो अब हमारे पास school नाम का database तैयार है और इसी database के अंदर हम अपनी marksheet से related table बनाएंगे।Step 3: Database Select करें
Database create करने के बाद हमें MySQL को बताना होगा कि हम किस database के अंदर काम करना चाहते हैं।
इसके लिए हम USE command का इस्तेमाल करेंगे।
लिखिए:
USE school;
अब इस query को run करें।
अब school database हमारा active database बन चुका है।
इसका मतलब अब हम जो भी table create करेंगे, वह इसी school database के अंदर create होगा।Step 4: Marks Table Create करें
अब हमें एक ऐसी table बनानी है जिसमें हम students की information और उनके marks को store कर सकें।
हम इस table का नाम marks रखेंगे।
Table बनाने से पहले हमें यह decide करना होगा कि हमें कौन-कौन सी information store करनी है।
हमें student का Roll Number चाहिए।
हमें Student Name चाहिए।
इसके बाद हमें Hindi, Maths, Science और English के marks चाहिए।
इसके अलावा हमें दो और columns चाहिए।
पहला column Total होगा, जिसमें हम सभी subjects के marks को जोड़कर total निकालेंगे।
दूसरा column Average होगा, जिसमें हम student's average percentage calculate करेंगे।
इस तरह हमारी table में कुल आठ columns होंगे।
अब यह command लिखें:
CREATE TABLE marks (
roll_no INT,
name VARCHAR(50),
hindi INT,
maths INT,
science INT,
english INT,
total INT,
average DECIMAL(5,2)
);
अब इस command को थोड़ा समझ लेते हैं।
सबसे पहला column है roll_no।
हम Roll Number के लिए INT data type इस्तेमाल कर रहे हैं क्योंकि Roll Number एक number होगा।
इसके बाद हमारा name column है।
Student का नाम text में होगा, इसलिए यहां हमने VARCHAR(50) इस्तेमाल किया है।
इसके बाद Hindi, Maths, Science और English के columns हैं।
इन सभी में marks store होंगे, इसलिए हमने इनके लिए INT data type इस्तेमाल किया है।
इसके बाद total column है।
इसमें हम student के total marks को calculate करेंगे।
और सबसे आखिर में average column है।
Average में decimal value भी आ सकती है, जैसे 72.25 या 85.50।
इसलिए हमने यहां DECIMAL(5,2) का इस्तेमाल किया है।
अब इस query को run करें।
हमारी marks table successfully create हो चुकी है।Step 5: Table की Structure Check करें
अब data enter करने से पहले एक बार check कर लेते हैं कि हमारी table सही तरीके से create हुई है या नहीं।
इसके लिए हम DESCRIBE command का इस्तेमाल करेंगे।
लिखिए:
DESCRIBE marks;
अब इस query को run करें।
आपको यहां पूरी table की structure दिखाई देगी।
यहां आप सभी column names और उनके data types देख सकते हैं।
यह step इसलिए useful है क्योंकि अगर हमने कोई column गलत बनाया है या किसी column का नाम गलत लिखा है, तो हम उसे यहीं से check कर सकते हैं।Step 6: Multiple Student Records Insert करें
अब हमारी table तैयार है।
अगला काम है students का data और उनके marks table के अंदर insert करना।
हम हर student को अलग-अलग insert कर सकते हैं, लेकिन MySQL में हम एक ही query के अंदर multiple records भी insert कर सकते हैं।
अब हम example के लिए पांच students का data insert करेंगे।
लिखिए:
INSERT INTO marks
(roll_no, name, hindi, maths, science, english, total, average)
VALUES
(1, 'Raman', 78, 89, 66, 56, 0, 0),
(2, 'Manish', 85, 76, 90, 80, 0, 0),
(3, 'Rahul', 99, 88, 75, 85, 0, 0),
(4, 'Aman', 70, 82, 65, 78, 0, 0),
(5, 'Rohit', 90, 95, 85, 88, 0, 0);
अब इस query को समझते हैं।
सबसे पहले हमने table का नाम marks दिया।
इसके बाद हमने सभी column names लिखे हैं।
फिर VALUES के बाद हम students की information enter कर रहे हैं।
उदाहरण के लिए पहला record Raman का है।
उसका Roll Number 1 है।
Hindi में उसके 78 marks हैं।
Maths में 89 marks हैं।
Science में 66 marks हैं।
और English में 56 marks हैं।
सबसे आखिर में हमने Total और Average के लिए zero लिखा है।
अभी हम Total और Average को manually calculate नहीं कर रहे हैं।
हम बाद में MySQL के formula का इस्तेमाल करके इन दोनों values को calculate करेंगे।
इसी तरह हमने बाकी चार students का data भी enter किया है।
यहां एक important बात ध्यान रखें।
जब हम multiple records insert करते हैं, तो हर student के record के बाद comma लगाना होता है।
लेकिन आखिरी record के बाद semicolon लगाया जाता है।
अब query को run करें।
हमारे पांचों student records table के अंदर insert हो जाएंगे।Step 7: Insert किया हुआ Data देखें
अब हम check करते हैं कि हमारा data successfully insert हुआ है या नहीं।
इसके लिए लिखिए:
SELECT * FROM marks;
अब query को run करें।
आपको table के अंदर मौजूद सभी student records दिखाई देंगे।
यहां आपको Roll Number, Name, सभी subjects के marks, Total और Average दिखाई देंगे।
फिलहाल Total और Average के अंदर zero दिखाई देगा।
इसका कारण यह है कि हमने अभी तक कोई calculation नहीं की है।
अब हम इन्हीं columns के लिए formulas का इस्तेमाल करेंगे।Step 8: Total Marks Calculate करें
अब हम सबसे पहले हर student के Total Marks calculate करेंगे।
हमारे पास चार subjects हैं:- Hindi
- Maths
- Science
- English
इसके लिए लिखिए:
SELECT roll_no, name, hindi, maths, science, english,
(hindi + maths + science + english) AS total
FROM marks;
अब इस query को run करें।
यहां हमने Hindi, Maths, Science और English के marks को plus करके जोड़ा है।
AS total का मतलब है कि इस calculated value को हम total नाम देंगे।
अब Raman का example लेते हैं।
Raman के marks हैं:
Hindi = 78
Maths = 89
Science = 66
English = 56
अब इन सभी को add करेंगे:
78 + 89 + 66 + 56 = 289
इसलिए Raman के Total Marks 289 होंगे।
इसी तरह MySQL automatically बाकी सभी students के Total Marks भी calculate कर देगा।
हमें हर student का calculation manually करने की जरूरत नहीं है।Step 9: Average Percentage Calculate करें
अब हम Average Percentage calculate करेंगे।
सबसे पहले यह समझना जरूरी है कि हमारे पास चार subjects हैं और हर subject के maximum marks 100 हैं।
इसलिए चार subjects के maximum marks होंगे:
100 + 100 + 100 + 100 = 400
Percentage निकालने का formula होता है:
Total Marks × 100 ÷ Maximum Marks
हमारे case में Maximum Marks 400 हैं।
इसलिए formula होगा:
Total Marks × 100 ÷ 400
अब यह query लिखिए:
SELECT roll_no, name, hindi, maths, science, english,
(hindi + maths + science + english) AS total,
((hindi + maths + science + english) * 100 / 400) AS average
FROM marks;
अब query को run करें।
अब result में हमें Total के साथ Average भी दिखाई देगा।
एक बार फिर Raman का example लेते हैं।
Raman के Total Marks 289 हैं।
तो हम calculation करेंगे:
289 × 100 ÷ 400
इसका result होगा:
72.25
इसलिए Raman की percentage 72.25% होगी।
MySQL यही calculation automatically बाकी सभी students के लिए भी करेगा।Step 10: 400 क्यों इस्तेमाल किया है?
अब यहां एक important point समझना बहुत जरूरी है।
हमने formula के अंदर 400 का इस्तेमाल किया है।
लेकिन 400 ही क्यों?
क्योंकि हमारे पास चार subjects हैं और हर subject के maximum marks 100 हैं।
इसलिए:
4 Subjects × 100 Marks = 400
इसलिए हमने formula में 400 इस्तेमाल किया।
अगर आपके पास पांच subjects हैं, तो maximum marks होंगे:
5 × 100 = 500
उस situation में formula होगा:
Total Marks × 100 ÷ 500
और अगर आपके पास छह subjects हैं, तो maximum marks होंगे:
6 × 100 = 600
तो formula में 600 इस्तेमाल करेंगे।
इसलिए subjects की संख्या के हिसाब से maximum marks को बदलना जरूरी है।Step 11: Complete Marksheet Display करें
अब हम एक ऐसी final query बनाएंगे जिसमें हमें एक साथ पूरी marksheet दिखाई देगी।
इसके लिए लिखिए:
SELECT
roll_no,
name,
hindi,
maths,
science,
english,
(hindi + maths + science + english) AS total,
((hindi + maths + science + english) * 100 / 400) AS average
FROM marks;
अब इस query को run करें।
अब आपको एक complete marksheet दिखाई देगी।
इसमें आपको Roll Number मिलेगा।
Student Name मिलेगा।
Hindi के marks मिलेंगे।
Maths के marks मिलेंगे।
Science के marks मिलेंगे।
English के marks मिलेंगे।
इसके साथ Total Marks और Average Percentage भी दिखाई देगा।
सबसे अच्छी बात यह है कि हमें Total और Average को manually calculate करने की जरूरत नहीं है।
MySQL हमारे लिए यह calculation automatically कर रहा है।Step 12: Formula को एक बार फिर समझें
अब एक बार सबसे important formulas को समझ लेते हैं।
Total निकालने के लिए हमने यह formula इस्तेमाल किया:
hindi + maths + science + english
इसका काम है चारों subjects के marks को जोड़ना और Total निकालना।
इसके बाद Percentage निकालने के लिए हमने यह formula इस्तेमाल किया:
(hindi + maths + science + english) × 100 ÷ 400
यह formula Total Marks को percentage में convert करता है।
उदाहरण के लिए अगर किसी student के Total Marks 320 हैं और maximum marks 400 हैं, तो:
320 × 100 ÷ 400 = 80
इसका मतलब student की percentage 80% होगी।
इस तरह हम SQL query के अंदर ही mathematical calculations कर सकते हैं।Step 13: Total और Average Columns को समझें
अब यहां एक और important बात समझना जरूरी है।
हमने table create करते समय Total और Average के columns बनाए थे और data insert करते समय उनमें zero डाल दिया था।
लेकिन जब हम SELECT query के अंदर Total और Average calculate कर रहे हैं, तो हम table में stored zero values को permanently change नहीं कर रहे हैं।
हम query के result में calculation करके value दिखा रहे हैं।
मतलब जब भी हम SELECT query चलाएंगे, MySQL formula के according Total और Average calculate करके result दिखाएगा।
यह formulas को समझने और calculations करने का एक simple और useful तरीका है।Step 14: Final Result देखें
अब जब हम अपनी final query run करेंगे, तो हमें एक complete marksheet दिखाई देगी।
उदाहरण के लिए result कुछ इस तरह दिखाई देगा:
Roll Number | Name | Hindi | Maths | Science | English | Total | Average
1 | Raman | 78 | 89 | 66 | 56 | 289 | 72.25
इसी तरह बाकी students के Total और Average भी automatically calculate होकर दिखाई देंगे।