Bab9 mYsql

mysql> insert into mahasiswa
-> values ('020013', 'hendriansah', 26, 164, 45, 1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into mahasiswa
-> values ('020014', 'sardi sudrajat', 22, 158, 50, 0);
Query OK, 1 row affected (0.03 sec)

mysql> insert into mahasiswa
-> values ('020015', 'imam sugenjo', 23, 162, 50, 0);
Query OK, 1 row affected (0.03 sec)

mysql> insert into mahasiswa
-> values ('020016', 'bunafit nugroho', 25, 165, 50, 24);
Query OK, 1 row affected (0.03 sec)

mysql> select count(*) from mahasiswa;
+----------+
| count(*) |
+----------+
| 16 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) as rekaman from mahasiswa;
+---------+
| rekaman |
+---------+
| 16 |
+---------+
1 row in set (0.00 sec)

mysql> select count(nim) as rekaman from mahasiswa;
+---------+
| rekaman |
+---------+
| 16 |
+---------+
1 row in set (0.00 sec)

mysql> select sum(karya) as karya from mahasiswa;
+-------+
| karya |
+-------+
| 31 |
+-------+
1 row in set (0.00 sec)

mysql> select sum(umur) /16 as umurrata from mahasiswa;
+----------+
| umurrata |
+----------+
| 25.0000 |
+----------+
1 row in set (0.05 sec)

mysql> select sum(berat) /16 as beratrata from mahasiswa;
+-----------+
| beratrata |
+-----------+
| 44.0000 |
+-----------+
1 row in set (0.00 sec)

mysql> select sum(berat)/count (*) as beratrata from mahasiswa;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '*) as
beratrata from mahasiswa' at line 1
mysql> select sum(berat)/count(*) as beratrata from mahasiswa;
+-----------+
| beratrata |
+-----------+
| 44.0000 |
+-----------+
1 row in set (0.00 sec)

mysql> select avg(umur) as umurrata from mahasiswa;
+----------+
| umurrata |
+----------+
| 25.0000 |
+----------+
1 row in set (0.02 sec)

mysql> select avg(berat) as beratrata from mahasiswa;
+-----------+
| beratrata |
+-----------+
| 44.0000 |
+-----------+
1 row in set (0.00 sec)

mysql> select avg(tinggi) as tinggirata from mahasiswa;
+------------+
| tinggirata |
+------------+
| 160.3125 |
+------------+
1 row in set (0.00 sec)

mysql> select min(umur) as termuda from mahasiswa;
+---------+
| termuda |
+---------+
| 21 |
+---------+
1 row in set (0.00 sec)

mysql> select min(berat) as terkurus from mahasiswa;
+----------+
| terkurus |
+----------+
| 35 |
+----------+
1 row in set (0.00 sec)

mysql> select min(tinggi) as terpendek from mahasiswa;
+-----------+
| terpendek |
+-----------+
| 150 |
+-----------+
1 row in set (0.00 sec)

mysql> select max(umur) as tertua from mahasiswa;
+--------+
| tertua |
+--------+
| 28 |
+--------+
1 row in set (0.00 sec)

mysql> select max(tinggi) as tertinggi from mahasiswa;
+-----------+
| tertinggi |
+-----------+
| 170 |
+-----------+
1 row in set (0.00 sec)

mysql> select max(berat) as terberat from mahasiswa;
+----------+
| terberat |
+----------+
| 60 |
+----------+
1 row in set (0.00 sec)

mysql> select max(karya) as terbanyak from mahasiswa;
+-----------+
| terbanyak |
+-----------+
| 24 |
+-----------+
1 row in set (0.00 sec)

mysql> select min(berat) as minimal,
-> max(berat) as maksimal from mahasiswa;
+---------+----------+
| minimal | maksimal |
+---------+----------+
| 35 | 60 |
+---------+----------+
1 row in set (0.00 sec)

mysql> select min(tinggi) as minimal,
-> max(tinggi) as maksimal from mahasiswa;
+---------+----------+
| minimal | maksimal |
+---------+----------+
| 150 | 170 |
+---------+----------+
1 row in set (0.00 sec)

mysql> select min(umur) as minimal,
-> max(umur) as maksimal from mahasiswa;
+---------+----------+
| minimal | maksimal |
+---------+----------+
| 21 | 28 |
+---------+----------+
1 row in set (0.00 sec)

mysql> select min(karya) as minimal,
-> max(karya) as maksimal from mahasiswa;
+---------+----------+
| minimal | maksimal |
+---------+----------+
| 0 | 24 |
+---------+----------+
1 row in set (0.00 sec)

mysql> select nim,nama,umur from mahasiswa
-> where umur >= 22 and umur <= 25;
+--------+------------------+------+
| nim | nama | umur |
+--------+------------------+------+
| 020001 | indah indriyanna | 23 |
| 020003 | iis suwindri | 24 |
| 020005 | mawardi budiono | 25 |
| 020008 | sariyanto | 25 |
| 020012 | wagimin | 25 |
| 020014 | sardi sudrajat | 22 |
| 020015 | imam sugenjo | 23 |
| 020016 | bunafit nugroho | 25 |
+--------+------------------+------+
8 rows in set (0.03 sec)

mysql> select nim,nama,berat from mahasiswa
-> where berat >= 45 and berat <= 50;
+--------+------------------+-------+
| nim | nama | berat |
+--------+------------------+-------+
| 020001 | indah indriyanna | 45 |
| 020009 | bambang hermawan | 45 |
| 020012 | wagimin | 45 |
| 020013 | hendriansah | 45 |
| 020014 | sardi sudrajat | 50 |
| 020015 | imam sugenjo | 50 |
| 020016 | bunafit nugroho | 50 |
+--------+------------------+-------+
7 rows in set (0.00 sec)

mysql> select nim,nama,berat from mahasiswa
-> where not(berat >= 45 and berat <= 50);
+--------+------------------+-------+
| nim | nama | berat |
+--------+------------------+-------+
| 020002 | septi suhesti | 35 |
| 020003 | iis suwindri | 40 |
| 020004 | sugeng fitriyadi | 42 |
| 020005 | mawardi budiono | 43 |
| 020006 | arief saifullah | 60 |
| 020007 | juwanto | 40 |
| 020008 | sariyanto | 42 |
| 020010 | waldi lita | 37 |
| 020011 | sukerno | 35 |
+--------+------------------+-------+
9 rows in set (0.00 sec)

mysql> select nim,nama,umur from mahasiswa
-> where umur between 27 and 30;
+--------+------------------+------+
| nim | nama | umur |
+--------+------------------+------+
| 020006 | arief saifullah | 27 |
| 020007 | juwanto | 27 |
| 020009 | bambang hermawan | 27 |
| 020010 | waldi lita | 28 |
+--------+------------------+------+
4 rows in set (0.05 sec)

mysql> select nim,nama,berat from mahasiswa
-> where berat between 45 and 50;
+--------+------------------+-------+
| nim | nama | berat |
+--------+------------------+-------+
| 020001 | indah indriyanna | 45 |
| 020009 | bambang hermawan | 45 |
| 020012 | wagimin | 45 |
| 020013 | hendriansah | 45 |
| 020014 | sardi sudrajat | 50 |
| 020015 | imam sugenjo | 50 |
| 020016 | bunafit nugroho | 50 |
+--------+------------------+-------+
7 rows in set (0.00 sec)

mysql> select nim,nama,tinggi from mahasiswa
-> where tinggi between 160 and 170;
+--------+------------------+--------+
| nim | nama | tinggi |
+--------+------------------+--------+
| 020001 | indah indriyanna | 165 |
| 020002 | septi suhesti | 168 |
| 020003 | iis suwindri | 160 |
| 020004 | sugeng fitriyadi | 162 |
| 020006 | arief saifullah | 170 |
| 020008 | sariyanto | 160 |
| 020010 | waldi lita | 161 |
| 020013 | hendriansah | 164 |
| 020015 | imam sugenjo | 162 |
| 020016 | bunafit nugroho | 165 |
+--------+------------------+--------+
10 rows in set (0.00 sec)

mysql> select nim,nama,tinggi from mahasiswa
-> where not tinggi between 160 and 170;
+--------+------------------+--------+
| nim | nama | tinggi |
+--------+------------------+--------+
| 020005 | mawardi budiono | 155 |
| 020007 | juwanto | 155 |
| 020009 | bambang hermawan | 155 |
| 020011 | sukerno | 150 |
| 020012 | wagimin | 155 |
| 020014 | sardi sudrajat | 158 |
+--------+------------------+--------+
6 rows in set (0.00 sec)

mysql>