PERINTAH QUERY COUNT, SUM, & AVRAGE (AVG)

PERINTAH QUERY COUNT, SUM, & AVG DI CMD

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\EDI SANTOSO>cd/xampp/mysql/bin

C:\xampp\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.5.8 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use akademik;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_akademik |
+--------------------+
| dosen              |
| frs                |
| jurusan            |
| mahasiswa          |
| matkul             |
| nilai_uas          |
| nilai_uts          |
| semester           |
+--------------------+
8 rows in set (0.02 sec)


mysql> select* from nilai_uts;
+------------+-------------+------------+------------+-------+
| kode_nilai | kode_matkul | kode_dosen | nim        | nilai |
+------------+-------------+------------+------------+-------+
|          1 | 01SDB       | 001        | 43A8700613 |    99 |
|          2 | 02BP        | 002        | 43A8700614 |   100 |
|          3 | 03ASD       | 003        | 43A8700615 |    98 |
|          4 | 04SD        | 004        | 43A8700616 |    90 |
|          5 | 05MP        | 005        | 43A8700617 |    85 |
+------------+-------------+------------+------------+-------+
5 rows in set (0.36 sec)


  Buat menghitung record/Baris di kolom dari tabel nilai_uts;
mysql> select count(*)from nilai_uts;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)


  Buat menghitung record/Baris di kolom dari tabel nilai_uas;
mysql> select count(*)from nilai_uas;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.03 sec)

mysql> select * from mahasiswa;
+----------------+----------------+--------------+---------------+--------------
-+----------+
| nim            | nama_mahasiswa | tempat_lahir | tanggal_lahir | alamat
 | no_telp  |
+----------------+----------------+--------------+---------------+--------------
-+----------+
| 43A8700613     | M.EDI.S        | Indramayu    | 1992-10-25    | Ds.Sukaslamet
 | 087xxxx  |
| 43A87006133456 | Srikandhi      | NULL         | NULL          | NULL
 | NULL     |
| 43A87006136789 | ARJUNA         | NULL         | NULL          | NULL
 | NULL     |
| 43A8700614     | Yunita         | Jakarta      | 1994-04-14    | Kp.SukaHati
 | 085xxxxx |
| 43A8700615     | Erni.N         | Indramayu    | 1994-07-07    | Kp.Jati
 | 081xxxxx |
| 43A8700616     | Ahmad Yasir    | Bekasi       | 1992-07-13    | Ds.Jati Rawa
 | 081xxxxx |
| 43A8700617     | Linah.K        | Jakarta      | 1996-05-29    | Kp.SukaMaju
 | 085xxxxx |
+----------------+----------------+--------------+---------------+--------------
-+----------+
7 rows in set (0.00 sec)


  Buat menghitung kolom dari Tabel mahasiswa dengan kondisi alamat=' ';
mysql> select count(*)from mahasiswa where alamat='Kp.Jati';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)



mysql> select * from nilai_uts;
+------------+-------------+------------+------------+-------+
| kode_nilai | kode_matkul | kode_dosen | nim        | nilai |
+------------+-------------+------------+------------+-------+
|          1 | 01SDB       | 001        | 43A8700613 |    99 |
|          2 | 02BP        | 002        | 43A8700614 |   100 |
|          3 | 03ASD       | 003        | 43A8700615 |    98 |
|          4 | 04SD        | 004        | 43A8700616 |    90 |
|          5 | 05MP        | 005        | 43A8700617 |    85 |
+------------+-------------+------------+------------+-------+
5 rows in set (0.00 sec)


 Buat Menjumlah kan Nilai dari Tabel nilai_uts;
mysql> select SUM(nilai) from nilai_uts;
+------------+
| SUM(nilai) |
+------------+
|        472 |
+------------+
1 row in set (0.00 sec)


  Buat menjumlah kan nilai dari nilai_uts dengan kondisi nim=' ';
mysql> select sum(nilai) from nilai_uts where nim='43A8700613';
+------------+
| sum(nilai) |
+------------+
|         99 |
+------------+
1 row in set (0.00 sec)



  Buat menghitung record/Baris dari tabel nilai_uts dengan kondisi nim='  ';
mysql> select count(*) from nilai_uts where nim='43A8700613';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)



   Buat menghitung Rata-Rata dengan kondisi nim=' ';
mysql> select sum(nilai)/count(*) from nilai_uts where nim='43A8700613';
+---------------------+
| sum(nilai)/count(*) |
+---------------------+
|                  99 |
+---------------------+
1 row in set (0.13 sec)
  

  Buat menghgitung rata-rata keseluruhan nilai,yang terdapat di tabel nilai_uts,,menggunakan perintah AVG.
mysql> select AVG(nilai) from nilai_uts;
+------------+
| AVG(nilai) |
+------------+
|       94.4 |
+------------+
1 row in set (0.00 sec)