Scribd
Upload
10 views10 pages

Master SQL

The document outlines the process of setting up and using XAMPP on Windows, specifically focusing on database operations in MariaDB. It includes commands for connecting to the database, inserting data into tables, and handling foreign key constraints. Additionally, it demonstrates querying and updating records within the database.

Uploaded by

dm.ppkd.jakpus
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
10 views10 pages

Master SQL

The document outlines the process of setting up and using XAMPP on Windows, specifically focusing on database operations in MariaDB. It includes commands for connecting to the database, inserting data into tables, and handling foreign key constraints. Additionally, it demonstrates querying and updating records within the database.

Uploaded by

dm.ppkd.jakpus
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

Setting environment for using XAMPP for Windows.

Acer@DESKTOP-INTVS8J c:\xampp
# mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 122
Server version: 10.4.28-MariaDB [Link] binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use fielddata1;


Database changed
MariaDB [fielddata1]> select *from well_test;
+---------+------------+------+-------+------+------+------+---------+-----------+
| FK_WELL | DATE | OIL | WATER | GAS | WHP | CHP | GL_RATE | WATER_CUT |
+---------+------------+------+-------+------+------+------+---------+-----------+
| 2 | 2020-06-04 | 321 | 455 | 186 | 137 | 150 | 192 | 59 |
| 4 | 2022-01-29 | 166 | 102 | 344 | 110 | 59 | 140 | 38 |
| 7 | 2006-10-20 | 29 | 465 | 415 | 65 | 89 | 174 | 94 |
| 8 | 2022-03-05 | 351 | 48 | 333 | 100 | 112 | 265 | 12 |
| 9 | 2008-10-01 | 68 | 458 | 132 | 106 | 97 | 238 | 87 |
| 901 | 2016-01-08 | 80 | 63 | 457 | 85 | 146 | 191 | 44 |
| 903 | 2021-01-08 | 254 | 354 | 320 | 59 | 102 | 132 | 58 |
+---------+------------+------+-------+------+------+------+---------+-----------+
7 rows in set (0.012 sec)

MariaDB [fielddata1]> insert into well_test values('802','2010-01-


19','321','378','202','71','131','51','54');
Query OK, 1 row affected (0.002 sec)

MariaDB [fielddata1]> insert into well_test values('401','2022-02-


09','338','254','434','100','83','82','43');
Query OK, 1 row affected (0.002 sec)

MariaDB [fielddata1]> insert into well_test values('801','2016-07-


16','63','320','242','57','105','6','84');
Query OK, 1 row affected (0.011 sec)

MariaDB [fielddata1]> insert into well_test values('10','2019-06-


27','418','209','241','119','79','194','33');
Query OK, 1 row affected (0.013 sec)

MariaDB [fielddata1]> insert into well_test values('402','2022-05-


23','468','394','318','89','146','75','46');
Query OK, 1 row affected (0.002 sec)

MariaDB [fielddata1]> insert into well_test values('902','2016-08-


05','129','92','81','99','129','263','42');
Query OK, 1 row affected (0.013 sec)

MariaDB [fielddata1]> insert into well_test values('701','2012-02-


23','214','468','168','51','127','291','69');
Query OK, 1 row affected (0.013 sec)

MariaDB [fielddata1]> select *from well_test;


+---------+------------+------+-------+------+------+------+---------+-----------+
| FK_WELL | DATE | OIL | WATER | GAS | WHP | CHP | GL_RATE | WATER_CUT |
+---------+------------+------+-------+------+------+------+---------+-----------+
| 2 | 2020-06-04 | 321 | 455 | 186 | 137 | 150 | 192 | 59 |
| 4 | 2022-01-29 | 166 | 102 | 344 | 110 | 59 | 140 | 38 |
| 7 | 2006-10-20 | 29 | 465 | 415 | 65 | 89 | 174 | 94 |
| 8 | 2022-03-05 | 351 | 48 | 333 | 100 | 112 | 265 | 12 |
| 9 | 2008-10-01 | 68 | 458 | 132 | 106 | 97 | 238 | 87 |
| 10 | 2019-06-27 | 418 | 209 | 241 | 119 | 79 | 194 | 33 |
| 401 | 2022-02-09 | 338 | 254 | 434 | 100 | 83 | 82 | 43 |
| 402 | 2022-05-23 | 468 | 394 | 318 | 89 | 146 | 75 | 46 |
| 701 | 2012-02-23 | 214 | 468 | 168 | 51 | 127 | 291 | 69 |
| 801 | 2016-07-16 | 63 | 320 | 242 | 57 | 105 | 6 | 84 |
| 802 | 2010-01-19 | 321 | 378 | 202 | 71 | 131 | 51 | 54 |
| 901 | 2016-01-08 | 80 | 63 | 457 | 85 | 146 | 191 | 44 |
| 902 | 2016-08-05 | 129 | 92 | 81 | 99 | 129 | 263 | 42 |
| 903 | 2021-01-08 | 254 | 354 | 320 | 59 | 102 | 132 | 58 |
+---------+------------+------+-------+------+------+------+---------+-----------+
14 rows in set (0.000 sec)

MariaDB [fielddata1]> create table well_data(


-> FK_WELL int,
-> WELL varchar(30),
-> POP_DATE date,
-> primary key(FK_WELL),
-> foreign key(FK_WELL) references well_test(FK_WELL));
Query OK, 0 rows affected (0.031 sec)

MariaDB [fielddata1]> desc well_data;


+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| FK_WELL | int(11) | NO | PRI | NULL | |
| WELL | varchar(30) | YES | | NULL | |
| POP_DATE | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.025 sec)

MariaDB [fielddata1]> insert into well_data values('1','GAJAH-5','2005-05-05');


ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`fielddata1`.`well_data`, CONSTRAINT `well_data_ibfk_1` FOREIGN KEY
(`FK_WELL`) REFERENCES `well_test` (`FK_WELL`))
MariaDB [fielddata1]> insert into well_data values('2','SAPI-2','2016-10-13');
Query OK, 1 row affected (0.013 sec)

MariaDB [fielddata1]> insert into well_data values('3','HARIMAU-9','2009-12-15');


ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`fielddata1`.`well_data`, CONSTRAINT `well_data_ibfk_1` FOREIGN KEY
(`FK_WELL`) REFERENCES `well_test` (`FK_WELL`))
MariaDB [fielddata1]> insert into well_data values('4','HARIMAU-1','2022-01-23');
Query OK, 1 row affected (0.001 sec)

MariaDB [fielddata1]> insert into well_data values('5','GAJAH-2','2015-12-21');


ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`fielddata1`.`well_data`, CONSTRAINT `well_data_ibfk_1` FOREIGN KEY
(`FK_WELL`) REFERENCES `well_test` (`FK_WELL`))
MariaDB [fielddata1]> insert into well_data values('6','SAPI-3','2009-07-07');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`fielddata1`.`well_data`, CONSTRAINT `well_data_ibfk_1` FOREIGN KEY
(`FK_WELL`) REFERENCES `well_test` (`FK_WELL`))
MariaDB [fielddata1]> insert into well_data values('7','SAPI-4','2006-12-24');
Query OK, 1 row affected (0.013 sec)

MariaDB [fielddata1]> insert into well_data values('8','SAPI-5','2006-12-24');


Query OK, 1 row affected (0.012 sec)

MariaDB [fielddata1]> insert into well_data values('9','HARIMAU-2','2008-05-31');


Query OK, 1 row affected (0.001 sec)

MariaDB [fielddata1]> insert into well_data values('10','GAJAH-6','2010-06-18');


Query OK, 1 row affected (0.013 sec)

MariaDB [fielddata1]> select *from well_data;


+---------+-----------+------------+
| FK_WELL | WELL | POP_DATE |
+---------+-----------+------------+
| 2 | SAPI-2 | 2016-10-13 |
| 4 | HARIMAU-1 | 2022-01-23 |
| 7 | SAPI-4 | 2006-12-24 |
| 8 | SAPI-5 | 2006-12-24 |
| 9 | HARIMAU-2 | 2008-05-31 |
| 10 | GAJAH-6 | 2010-06-18 |
+---------+-----------+------------+
6 rows in set (0.000 sec)

MariaDB [fielddata1]> select *from well_test;


+---------+------------+------+-------+------+------+------+---------+-----------+
| FK_WELL | DATE | OIL | WATER | GAS | WHP | CHP | GL_RATE | WATER_CUT |
+---------+------------+------+-------+------+------+------+---------+-----------+
| 2 | 2020-06-04 | 321 | 455 | 186 | 137 | 150 | 192 | 59 |
| 4 | 2022-01-29 | 166 | 102 | 344 | 110 | 59 | 140 | 38 |
| 7 | 2006-10-20 | 29 | 465 | 415 | 65 | 89 | 174 | 94 |
| 8 | 2022-03-05 | 351 | 48 | 333 | 100 | 112 | 265 | 12 |
| 9 | 2008-10-01 | 68 | 458 | 132 | 106 | 97 | 238 | 87 |
| 10 | 2019-06-27 | 418 | 209 | 241 | 119 | 79 | 194 | 33 |
| 401 | 2022-02-09 | 338 | 254 | 434 | 100 | 83 | 82 | 43 |
| 402 | 2022-05-23 | 468 | 394 | 318 | 89 | 146 | 75 | 46 |
| 701 | 2012-02-23 | 214 | 468 | 168 | 51 | 127 | 291 | 69 |
| 801 | 2016-07-16 | 63 | 320 | 242 | 57 | 105 | 6 | 84 |
| 802 | 2010-01-19 | 321 | 378 | 202 | 71 | 131 | 51 | 54 |
| 901 | 2016-01-08 | 80 | 63 | 457 | 85 | 146 | 191 | 44 |
| 902 | 2016-08-05 | 129 | 92 | 81 | 99 | 129 | 263 | 42 |
| 903 | 2021-01-08 | 254 | 354 | 320 | 59 | 102 | 132 | 58 |
+---------+------------+------+-------+------+------+------+---------+-----------+
14 rows in set (0.012 sec)

MariaDB [fielddata1]> mysql> SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW());


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near 'mysql>
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW())' at line 1
MariaDB [fielddata1]> mysql SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW() *FROM
well_test);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near 'mysql
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW() *FROM well_test)' at line 1
MariaDB [fielddata1]> UPDATE well_test SET FK_WELL = '9' WHERE FK_WELL='901' OR
FK_WELL='902' OR FK_WELL='903';
Query OK, 3 rows affected (0.004 sec)
Rows matched: 3 Changed: 3 Warnings: 0
MariaDB [fielddata1]> select *from well_test;
+---------+------------+------+-------+------+------+------+---------+-----------+
| FK_WELL | DATE | OIL | WATER | GAS | WHP | CHP | GL_RATE | WATER_CUT |
+---------+------------+------+-------+------+------+------+---------+-----------+
| 2 | 2020-06-04 | 321 | 455 | 186 | 137 | 150 | 192 | 59 |
| 4 | 2022-01-29 | 166 | 102 | 344 | 110 | 59 | 140 | 38 |
| 7 | 2006-10-20 | 29 | 465 | 415 | 65 | 89 | 174 | 94 |
| 8 | 2022-03-05 | 351 | 48 | 333 | 100 | 112 | 265 | 12 |
| 9 | 2008-10-01 | 68 | 458 | 132 | 106 | 97 | 238 | 87 |
| 10 | 2019-06-27 | 418 | 209 | 241 | 119 | 79 | 194 | 33 |
| 401 | 2022-02-09 | 338 | 254 | 434 | 100 | 83 | 82 | 43 |
| 402 | 2022-05-23 | 468 | 394 | 318 | 89 | 146 | 75 | 46 |
| 701 | 2012-02-23 | 214 | 468 | 168 | 51 | 127 | 291 | 69 |
| 801 | 2016-07-16 | 63 | 320 | 242 | 57 | 105 | 6 | 84 |
| 802 | 2010-01-19 | 321 | 378 | 202 | 71 | 131 | 51 | 54 |
| 9 | 2016-01-08 | 80 | 63 | 457 | 85 | 146 | 191 | 44 |
| 9 | 2016-08-05 | 129 | 92 | 81 | 99 | 129 | 263 | 42 |
| 9 | 2021-01-08 | 254 | 354 | 320 | 59 | 102 | 132 | 58 |
+---------+------------+------+-------+------+------+------+---------+-----------+
14 rows in set (0.012 sec)

MariaDB [fielddata1]> SELECT * FROM well_test JOIN well_data ON


(well_data.FK_WELL=well_test.FK_WELL) order by date desc;
+---------+------------+------+-------+------+------+------+---------+-----------
+---------+-----------+------------+
| FK_WELL | DATE | OIL | WATER | GAS | WHP | CHP | GL_RATE | WATER_CUT |
FK_WELL | WELL | POP_DATE |
+---------+------------+------+-------+------+------+------+---------+-----------
+---------+-----------+------------+
| 8 | 2022-03-05 | 351 | 48 | 333 | 100 | 112 | 265 | 12 |
8 | SAPI-5 | 2006-12-24 |
| 4 | 2022-01-29 | 166 | 102 | 344 | 110 | 59 | 140 | 38 |
4 | HARIMAU-1 | 2022-01-23 |
| 9 | 2021-01-08 | 254 | 354 | 320 | 59 | 102 | 132 | 58 |
9 | HARIMAU-2 | 2008-05-31 |
| 2 | 2020-06-04 | 321 | 455 | 186 | 137 | 150 | 192 | 59 |
2 | SAPI-2 | 2016-10-13 |
| 10 | 2019-06-27 | 418 | 209 | 241 | 119 | 79 | 194 | 33 |
10 | GAJAH-6 | 2010-06-18 |
| 9 | 2016-08-05 | 129 | 92 | 81 | 99 | 129 | 263 | 42 |
9 | HARIMAU-2 | 2008-05-31 |
| 9 | 2016-01-08 | 80 | 63 | 457 | 85 | 146 | 191 | 44 |
9 | HARIMAU-2 | 2008-05-31 |
| 9 | 2008-10-01 | 68 | 458 | 132 | 106 | 97 | 238 | 87 |
9 | HARIMAU-2 | 2008-05-31 |
| 7 | 2006-10-20 | 29 | 465 | 415 | 65 | 89 | 174 | 94 |
7 | SAPI-4 | 2006-12-24 |
+---------+------------+------+-------+------+------+------+---------+-----------
+---------+-----------+------------+
9 rows in set (0.001 sec)

MariaDB [fielddata1]> UPDATE well_test SET FK_WELL = '4' WHERE FK_WELL='401' OR


FK_WELL='402';
Query OK, 2 rows affected (0.013 sec)
Rows matched: 2 Changed: 2 Warnings: 0

MariaDB [fielddata1]> UPDATE well_test SET FK_WELL = '8' WHERE FK_WELL='801' OR


FK_WELL='802';
Query OK, 2 rows affected (0.001 sec)
Rows matched: 2 Changed: 2 Warnings: 0

MariaDB [fielddata1]> Select *from well_test;


+---------+------------+------+-------+------+------+------+---------+-----------+
| FK_WELL | DATE | OIL | WATER | GAS | WHP | CHP | GL_RATE | WATER_CUT |
+---------+------------+------+-------+------+------+------+---------+-----------+
| 2 | 2020-06-04 | 321 | 455 | 186 | 137 | 150 | 192 | 59 |
| 4 | 2022-01-29 | 166 | 102 | 344 | 110 | 59 | 140 | 38 |
| 7 | 2006-10-20 | 29 | 465 | 415 | 65 | 89 | 174 | 94 |
| 8 | 2022-03-05 | 351 | 48 | 333 | 100 | 112 | 265 | 12 |
| 9 | 2008-10-01 | 68 | 458 | 132 | 106 | 97 | 238 | 87 |
| 10 | 2019-06-27 | 418 | 209 | 241 | 119 | 79 | 194 | 33 |
| 4 | 2022-02-09 | 338 | 254 | 434 | 100 | 83 | 82 | 43 |
| 4 | 2022-05-23 | 468 | 394 | 318 | 89 | 146 | 75 | 46 |
| 701 | 2012-02-23 | 214 | 468 | 168 | 51 | 127 | 291 | 69 |
| 8 | 2016-07-16 | 63 | 320 | 242 | 57 | 105 | 6 | 84 |
| 8 | 2010-01-19 | 321 | 378 | 202 | 71 | 131 | 51 | 54 |
| 9 | 2016-01-08 | 80 | 63 | 457 | 85 | 146 | 191 | 44 |
| 9 | 2016-08-05 | 129 | 92 | 81 | 99 | 129 | 263 | 42 |
| 9 | 2021-01-08 | 254 | 354 | 320 | 59 | 102 | 132 | 58 |
+---------+------------+------+-------+------+------+------+---------+-----------+
14 rows in set (0.000 sec)

MariaDB [fielddata1]> UPDATE well_test SET FK_WELL = '7' WHERE FK_WELL='701';


Query OK, 1 row affected (0.014 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [fielddata1]> SELECT * FROM well_test JOIN well_data ON


(well_data.FK_WELL=well_test.FK_WELL) where well_data.FK_WELL='9' order by date
desc;
+---------+------------+------+-------+------+------+------+---------+-----------
+---------+-----------+------------+
| FK_WELL | DATE | OIL | WATER | GAS | WHP | CHP | GL_RATE | WATER_CUT |
FK_WELL | WELL | POP_DATE |
+---------+------------+------+-------+------+------+------+---------+-----------
+---------+-----------+------------+
| 9 | 2021-01-08 | 254 | 354 | 320 | 59 | 102 | 132 | 58 |
9 | HARIMAU-2 | 2008-05-31 |
| 9 | 2016-08-05 | 129 | 92 | 81 | 99 | 129 | 263 | 42 |
9 | HARIMAU-2 | 2008-05-31 |
| 9 | 2016-01-08 | 80 | 63 | 457 | 85 | 146 | 191 | 44 |
9 | HARIMAU-2 | 2008-05-31 |
| 9 | 2008-10-01 | 68 | 458 | 132 | 106 | 97 | 238 | 87 |
9 | HARIMAU-2 | 2008-05-31 |
+---------+------------+------+-------+------+------+------+---------+-----------
+---------+-----------+------------+
4 rows in set (0.001 sec)

MariaDB [fielddata1]> SELECT GL_RATE FROM well_test WHERE GL_RATE >= '100';
+---------+
| GL_RATE |
+---------+
| 192 |
| 140 |
| 174 |
| 265 |
| 238 |
| 194 |
| 82 |
| 75 |
| 291 |
| 6 |
| 51 |
| 191 |
| 263 |
| 132 |
+---------+
14 rows in set (0.002 sec)

MariaDB [fielddata1]> SELECT GL_RATE FROM well_test WHERE GL_RATE > '100';
+---------+
| GL_RATE |
+---------+
| 192 |
| 140 |
| 174 |
| 265 |
| 238 |
| 194 |
| 82 |
| 75 |
| 291 |
| 6 |
| 51 |
| 191 |
| 263 |
| 132 |
+---------+
14 rows in set (0.011 sec)

MariaDB [fielddata1]> SELECT GL_RATE FROM well_test WHERE GL_RATE >= 100;
+---------+
| GL_RATE |
+---------+
| 192 |
| 140 |
| 174 |
| 265 |
| 238 |
| 194 |
| 291 |
| 191 |
| 263 |
| 132 |
+---------+
10 rows in set (0.001 sec)

MariaDB [fielddata1]> select *from well_test;


+---------+------------+------+-------+------+------+------+---------+-----------+
| FK_WELL | DATE | OIL | WATER | GAS | WHP | CHP | GL_RATE | WATER_CUT |
+---------+------------+------+-------+------+------+------+---------+-----------+
| 2 | 2020-06-04 | 321 | 455 | 186 | 137 | 150 | 192 | 59 |
| 4 | 2022-01-29 | 166 | 102 | 344 | 110 | 59 | 140 | 38 |
| 7 | 2006-10-20 | 29 | 465 | 415 | 65 | 89 | 174 | 94 |
| 8 | 2022-03-05 | 351 | 48 | 333 | 100 | 112 | 265 | 12 |
| 9 | 2008-10-01 | 68 | 458 | 132 | 106 | 97 | 238 | 87 |
| 10 | 2019-06-27 | 418 | 209 | 241 | 119 | 79 | 194 | 33 |
| 4 | 2022-02-09 | 338 | 254 | 434 | 100 | 83 | 82 | 43 |
| 4 | 2022-05-23 | 468 | 394 | 318 | 89 | 146 | 75 | 46 |
| 7 | 2012-02-23 | 214 | 468 | 168 | 51 | 127 | 291 | 69 |
| 8 | 2016-07-16 | 63 | 320 | 242 | 57 | 105 | 6 | 84 |
| 8 | 2010-01-19 | 321 | 378 | 202 | 71 | 131 | 51 | 54 |
| 9 | 2016-01-08 | 80 | 63 | 457 | 85 | 146 | 191 | 44 |
| 9 | 2016-08-05 | 129 | 92 | 81 | 99 | 129 | 263 | 42 |
| 9 | 2021-01-08 | 254 | 354 | 320 | 59 | 102 | 132 | 58 |
+---------+------------+------+-------+------+------+------+---------+-----------+
14 rows in set (0.001 sec)

MariaDB [fielddata1]> SELECT AVG(OIL), AVG(WATER), AVG(GAS) FROM well_test;


+----------+------------+--------------------+
| AVG(OIL) | AVG(WATER) | AVG(GAS) |
+----------+------------+--------------------+
| 230 | 290 | 276.64285714285717 |
+----------+------------+--------------------+
1 row in set (0.016 sec)

MariaDB [fielddata1]> SELECT AVG(OIL), AVG(WATER), AVG(GAS) FROM well_test WHERE


FK_WELL='8';
+----------+--------------------+----------+
| AVG(OIL) | AVG(WATER) | AVG(GAS) |
+----------+--------------------+----------+
| 245 | 248.66666666666666 | 259 |
+----------+--------------------+----------+
1 row in set (0.001 sec)

MariaDB [fielddata1]> select *from well_data;


+---------+-----------+------------+
| FK_WELL | WELL | POP_DATE |
+---------+-----------+------------+
| 2 | SAPI-2 | 2016-10-13 |
| 4 | HARIMAU-1 | 2022-01-23 |
| 7 | SAPI-4 | 2006-12-24 |
| 8 | SAPI-5 | 2006-12-24 |
| 9 | HARIMAU-2 | 2008-05-31 |
| 10 | GAJAH-6 | 2010-06-18 |
+---------+-----------+------------+
6 rows in set (0.001 sec)

MariaDB [fielddata1]> SELECT well_data.WELL, OIL + WATER + GAS as Total FROM


well_test JOIN well_data ON (well_data.FK_WELL=well_test.FK_WELL);
+-----------+-------+
| WELL | Total |
+-----------+-------+
| SAPI-2 | 962 |
| HARIMAU-1 | 612 |
| SAPI-4 | 909 |
| SAPI-5 | 732 |
| HARIMAU-2 | 658 |
| GAJAH-6 | 868 |
| HARIMAU-1 | 1026 |
| HARIMAU-1 | 1180 |
| SAPI-4 | 850 |
| SAPI-5 | 625 |
| SAPI-5 | 901 |
| HARIMAU-2 | 600 |
| HARIMAU-2 | 302 |
| HARIMAU-2 | 928 |
+-----------+-------+
14 rows in set (0.002 sec)

MariaDB [fielddata1]> SELECT well_data.WELL, OIL + WATER + GAS as Total FROM


well_test JOIN well_data ON (well_data.FK_WELL=well_test.FK_WELL) GROUP BY
well_data.WELL;
+-----------+-------+
| WELL | Total |
+-----------+-------+
| GAJAH-6 | 868 |
| HARIMAU-1 | 612 |
| HARIMAU-2 | 658 |
| SAPI-2 | 962 |
| SAPI-4 | 909 |
| SAPI-5 | 732 |
+-----------+-------+
6 rows in set (0.013 sec)

MariaDB [fielddata1]> SELECT well_data.WELL, sum(OIL + WATER + GAS) as Total FROM


well_test JOIN well_data ON (well_data.FK_WELL=well_test.FK_WELL) GROUP BY
well_data.WELL;
+-----------+-------+
| WELL | Total |
+-----------+-------+
| GAJAH-6 | 868 |
| HARIMAU-1 | 2818 |
| HARIMAU-2 | 2488 |
| SAPI-2 | 962 |
| SAPI-4 | 1759 |
| SAPI-5 | 2258 |
+-----------+-------+
6 rows in set (0.001 sec)

MariaDB [fielddata1]> select *from well_test;


+---------+------------+------+-------+------+------+------+---------+-----------+
| FK_WELL | DATE | OIL | WATER | GAS | WHP | CHP | GL_RATE | WATER_CUT |
+---------+------------+------+-------+------+------+------+---------+-----------+
| 2 | 2020-06-04 | 321 | 455 | 186 | 137 | 150 | 192 | 59 |
| 4 | 2022-01-29 | 166 | 102 | 344 | 110 | 59 | 140 | 38 |
| 7 | 2006-10-20 | 29 | 465 | 415 | 65 | 89 | 174 | 94 |
| 8 | 2022-03-05 | 351 | 48 | 333 | 100 | 112 | 265 | 12 |
| 9 | 2008-10-01 | 68 | 458 | 132 | 106 | 97 | 238 | 87 |
| 10 | 2019-06-27 | 418 | 209 | 241 | 119 | 79 | 194 | 33 |
| 4 | 2022-02-09 | 338 | 254 | 434 | 100 | 83 | 82 | 43 |
| 4 | 2022-05-23 | 468 | 394 | 318 | 89 | 146 | 75 | 46 |
| 7 | 2012-02-23 | 214 | 468 | 168 | 51 | 127 | 291 | 69 |
| 8 | 2016-07-16 | 63 | 320 | 242 | 57 | 105 | 6 | 84 |
| 8 | 2010-01-19 | 321 | 378 | 202 | 71 | 131 | 51 | 54 |
| 9 | 2016-01-08 | 80 | 63 | 457 | 85 | 146 | 191 | 44 |
| 9 | 2016-08-05 | 129 | 92 | 81 | 99 | 129 | 263 | 42 |
| 9 | 2021-01-08 | 254 | 354 | 320 | 59 | 102 | 132 | 58 |
+---------+------------+------+-------+------+------+------+---------+-----------+
14 rows in set (0.001 sec)

MariaDB [fielddata1]> SELECT well_data.WELL, sum(WATER_CUT) as Total FROM well_test


JOIN well_data ON (well_data.FK_WELL=well_test.FK_WELL) GROUP BY well_data.WELL;
+-----------+-------+
| WELL | Total |
+-----------+-------+
| GAJAH-6 | 33 |
| HARIMAU-1 | 127 |
| HARIMAU-2 | 231 |
| SAPI-2 | 59 |
| SAPI-4 | 163 |
| SAPI-5 | 150 |
+-----------+-------+
6 rows in set (0.001 sec)

MariaDB [fielddata1]> SELECT well_data.WELL, MAX(OIL) as Max FROM well_test JOIN


well_data ON (well_data.FK_WELL=well_test.FK_WELL);
+--------+------+
| WELL | Max |
+--------+------+
| SAPI-2 | 80 |
+--------+------+
1 row in set (0.001 sec)

MariaDB [fielddata1]> SELECT well_data.WELL, MAX(OIL) as Max FROM well_test JOIN


well_data ON (well_data.FK_WELL=well_test.FK_WELL) GROUP BY well_data.WELL;
+-----------+------+
| WELL | Max |
+-----------+------+
| GAJAH-6 | 418 |
| HARIMAU-1 | 468 |
| HARIMAU-2 | 80 |
| SAPI-2 | 321 |
| SAPI-4 | 29 |
| SAPI-5 | 63 |
+-----------+------+
6 rows in set (0.001 sec)

MariaDB [fielddata1]> SELECT well_data.WELL, MAX(OIL) FROM well_test JOIN well_data


ON (well_data.FK_WELL=well_test.FK_WELL);
+--------+----------+
| WELL | MAX(OIL) |
+--------+----------+
| SAPI-2 | 80 |
+--------+----------+
1 row in set (0.001 sec)

MariaDB [fielddata1]> SELECT MAX(OIL) FROM well_test;


+----------+
| MAX(OIL) |
+----------+
| 80 |
+----------+
1 row in set (0.000 sec)

MariaDB [fielddata1]> select *from well_test;


+---------+------------+------+-------+------+------+------+---------+-----------+
| FK_WELL | DATE | OIL | WATER | GAS | WHP | CHP | GL_RATE | WATER_CUT |
+---------+------------+------+-------+------+------+------+---------+-----------+
| 2 | 2020-06-04 | 321 | 455 | 186 | 137 | 150 | 192 | 59 |
| 4 | 2022-01-29 | 166 | 102 | 344 | 110 | 59 | 140 | 38 |
| 7 | 2006-10-20 | 29 | 465 | 415 | 65 | 89 | 174 | 94 |
| 8 | 2022-03-05 | 351 | 48 | 333 | 100 | 112 | 265 | 12 |
| 9 | 2008-10-01 | 68 | 458 | 132 | 106 | 97 | 238 | 87 |
| 10 | 2019-06-27 | 418 | 209 | 241 | 119 | 79 | 194 | 33 |
| 4 | 2022-02-09 | 338 | 254 | 434 | 100 | 83 | 82 | 43 |
| 4 | 2022-05-23 | 468 | 394 | 318 | 89 | 146 | 75 | 46 |
| 7 | 2012-02-23 | 214 | 468 | 168 | 51 | 127 | 291 | 69 |
| 8 | 2016-07-16 | 63 | 320 | 242 | 57 | 105 | 6 | 84 |
| 8 | 2010-01-19 | 321 | 378 | 202 | 71 | 131 | 51 | 54 |
| 9 | 2016-01-08 | 80 | 63 | 457 | 85 | 146 | 191 | 44 |
| 9 | 2016-08-05 | 129 | 92 | 81 | 99 | 129 | 263 | 42 |
| 9 | 2021-01-08 | 254 | 354 | 320 | 59 | 102 | 132 | 58 |
+---------+------------+------+-------+------+------+------+---------+-----------+
14 rows in set (0.000 sec)

MariaDB [fielddata1]> select *from well_data;


+---------+-----------+------------+
| FK_WELL | WELL | POP_DATE |
+---------+-----------+------------+
| 2 | SAPI-2 | 2016-10-13 |
| 4 | HARIMAU-1 | 2022-01-23 |
| 7 | SAPI-4 | 2006-12-24 |
| 8 | SAPI-5 | 2006-12-24 |
| 9 | HARIMAU-2 | 2008-05-31 |
| 10 | GAJAH-6 | 2010-06-18 |
+---------+-----------+------------+
6 rows in set (0.001 sec)

MariaDB [fielddata1]> SELECT well_data.WELL, well_data.POP_DATE, well_test.OIL,


well_test.WATER, well_test.GAS, well_test.WHP, well_test.CHP, well_test.GL_RATE
FROM well_test JOIN well_data ON (well_data.FK_WELL=well_test.FK_WELL);
+-----------+------------+------+-------+------+------+------+---------+
| WELL | POP_DATE | OIL | WATER | GAS | WHP | CHP | GL_RATE |
+-----------+------------+------+-------+------+------+------+---------+
| SAPI-2 | 2016-10-13 | 321 | 455 | 186 | 137 | 150 | 192 |
| HARIMAU-1 | 2022-01-23 | 166 | 102 | 344 | 110 | 59 | 140 |
| SAPI-4 | 2006-12-24 | 29 | 465 | 415 | 65 | 89 | 174 |
| SAPI-5 | 2006-12-24 | 351 | 48 | 333 | 100 | 112 | 265 |
| HARIMAU-2 | 2008-05-31 | 68 | 458 | 132 | 106 | 97 | 238 |
| GAJAH-6 | 2010-06-18 | 418 | 209 | 241 | 119 | 79 | 194 |
| HARIMAU-1 | 2022-01-23 | 338 | 254 | 434 | 100 | 83 | 82 |
| HARIMAU-1 | 2022-01-23 | 468 | 394 | 318 | 89 | 146 | 75 |
| SAPI-4 | 2006-12-24 | 214 | 468 | 168 | 51 | 127 | 291 |
| SAPI-5 | 2006-12-24 | 63 | 320 | 242 | 57 | 105 | 6 |
| SAPI-5 | 2006-12-24 | 321 | 378 | 202 | 71 | 131 | 51 |
| HARIMAU-2 | 2008-05-31 | 80 | 63 | 457 | 85 | 146 | 191 |
| HARIMAU-2 | 2008-05-31 | 129 | 92 | 81 | 99 | 129 | 263 |
| HARIMAU-2 | 2008-05-31 | 254 | 354 | 320 | 59 | 102 | 132 |
+-----------+------------+------+-------+------+------+------+---------+
14 rows in set (0.001 sec)

MariaDB [fielddata1]>

You might also like