Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to change a table (create/alter) so that the calculated “Average score” field is shown when querying the entire table without using MySQL INSERT, UPDATE?
Following is the syntax −
alter table yourTableName add column yourColumnName yourDataType generated always as ((yourColumName1+yourColumName2+....N) / N) virtual;
Let us create a table −
mysql> create table demo32 −> ( −> value1 int, −> value2 int −> ); Query OK, 0 rows affected (1.42 sec)
Insert some records into the table with the help of insert command −
mysql> insert into demo32 values(30,60); Query OK, 1 row affected (0.16 sec) mysql> insert into demo32 values(20,40); Query OK, 1 row affected (0.15 sec) mysql> insert into demo32 values(35,35); Query OK, 1 row affected (0.08 sec)
Display records from the table using select statement −
mysql> select *from demo32;
This will produce the following output −
+--------+--------+ | value1 | value2 | +--------+--------+ | 30 | 60 | | 20 | 40 | | 35 | 35 | +--------+--------+ 3 rows in set (0.00 sec)
Following is the query to a table (create/alter) so that the calculated “Average score” field is shown when querying the entire table without using INSERT, UPDATE −
mysql> alter table demo32 add column `Average Score` float −> generated always as ((value1+value2) / 2) virtual; Query OK, 0 rows affected (1.57 sec) Records: 0 Duplicates: 0 Warnings: 0
Display records from the table using select statement −
mysql> select *from demo32;
This will produce the following output −
+--------+--------+---------------+ | value1 | value2 | Average Score | +--------+--------+---------------+ | 30 | 60 | 45 | | 20 | 40 | 30 | | 35 | 35 | 35 | +--------+--------+---------------+ 3 rows in set (0.00 sec)
Updated on: 19-Nov-2020
69 Views
Advertisements