PERINTAH SELECT GROUP BY, DAN ORDER BY

PERINTAH QUERY SELECT GROUP BY, & ORDER BY  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 1
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.20 sec)

mysql> desc nilai_uts;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| kode_nilai  | int(11)     | NO   | PRI | NULL    | auto_increment |
| kode_matkul | varchar(10) | YES  | MUL | NULL    |                |
| kode_dosen  | varchar(10) | YES  | MUL | NULL    |                |
| nim         | varchar(14) | YES  | MUL | NULL    |                |
| nilai       | float       | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
5 rows in set (0.06 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)



 ((PERINTAH MENGELOMPOKAN JUMLAH KARAKTER NIM / NIM YANG SAMA  DARI TABEL NILAI_UTS))

mysql> select nim,AVG(nilai) from nilai_uts GROUP BY nim;
+------------+------------+
| nim        | AVG(nilai) |
+------------+------------+
| 43A8700613 |         99 |
| 43A8700614 |        100 |
| 43A8700615 |         98 |
| 43A8700616 |         90 |
| 43A8700617 |         85 |
+------------+------------+
5 rows in set (0.00 sec)



  ((PERINTAH MENGURUTKAN NILAI DARI YANG KECIL SAMPAI KE YANG BESAR))

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


  ((PERINTAH MENGURUT KAN NILAI DARI YANG BESAR SAMPAI KE YANG KECIL))

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



mysql> select * from mahasiswa;
+----------------+----------------+--------------+---------------+----------------+----------+
| nim            | nama_mahasiswa | tempat_lahir | tanggal_lahir | alamat         | no_telp  |
+----------------+----------------+--------------+---------------+----------------+----------+
| 43A8700613     | M.EDI.S        | Cikarang     | 1992-10-25    | Ds.Sukaslamet  | 087xxxx  |
| 43A87006135678 | YUDHISTIRA     | Cikarang     | 1991-10-26    | DS.Sukaslame   | 087xxxx  |
| 43A87006136788 | BIMA           | Cikarang     | 1992-10-13    | Kp.Rindu Cinta | 089xxxxx |
| 43A87006136789 | ARJUNA         | Cikarang     | 1992-10-25    | DS.Sukaslamet  | 0877xxxx |
| 43A8700614     | Nita Fay Fay   | 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 |
+----------------+----------------+--------------+---------------+----------------+----------+
8 rows in set (0.05 sec)


  ((PERINTAH MENGURUTKAN NIM DARI YANG KECIL SAMPAI KE YANG BESAR))

mysql> select * from mahasiswa order by nim asc;
+----------------+----------------+--------------+---------------+----------------+----------+
| nim            | nama_mahasiswa | tempat_lahir | tanggal_lahir | alamat         | no_telp  |
+----------------+----------------+--------------+---------------+----------------+----------+
| 43A8700613     | M.EDI.S        | Cikarang     | 1992-10-25    | Ds.Sukaslamet  | 087xxxx  |
| 43A87006135678 | YUDHISTIRA     | Cikarang     | 1991-10-26    | DS.Sukaslamet  | 087xxxx  |
| 43A87006136788 | BIMA           | Cikarang     | 1992-10-13    | Kp.Rindu Cinta | 089xxxxx |
| 43A87006136789 | ARJUNA         | Cikarang     | 1992-10-25    | DS.Sukaslamet  | 0877xxxx |
| 43A8700614     | Nita Fay Fay   | 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 |
+----------------+----------------+--------------+---------------+----------------+----------+
8 rows in set (0.00 sec)

   
((PERINTAH MENGURUTKAN NIM DARI YANG TERBESAR SAMPAI KE YANG KECIL))

mysql> select * from mahasiswa order by nim desc;
+----------------+----------------+--------------+---------------+----------------+----------+
| nim            | nama_mahasiswa | tempat_lahir | tanggal_lahir | alamat         | no_telp  |
|----------------+----------------+--------------+---------------+----------------+----------+
| 43A8700617     | Linah.K        | Jakarta      | 1996-05-29    | Kp.SukaMaju    | 085xxxxx |
| 43A8700616     | Ahmad Yasir    | Bekasi       | 1992-07-13    | Ds.Jati Rawa   | 081xxxxx |
| 43A8700615     | Erni.N         | Indramayu    | 1994-07-07    | Kp.Jati        | 081xxxxx |
| 43A8700614     | Nita Fay Fay   | Jakarta      | 1994-04-14    | Kp.SukaHati    | 085xxxxx |
| 43A87006136789 | ARJUNA         | Cikarang     | 1992-10-25    | DS.Sukaslamet  | 0877xxxx |
| 43A87006136788 | BIMA           | Cikarang     | 1992-10-13    | Kp.Rindu Cinta | 089xxxxx |
| 43A87006135678 | YUDHISTIRA     | Cikarang     | 1991-10-26    | DS.Sukaslamet  | 087xxxx  |
| 43A8700613     | M.EDI.S        | Cikarang     | 1992-10-25    | Ds.Sukaslamet  | 087xxxx  |
+----------------+----------------+--------------+---------------+----------------+----------+
8 rows 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)



 ((PERINTAH MENCARI NILAI TERBESAR DI KOLOM NILai,,YANG ADA DI TABEL NILAI_UTS))

mysql> select max(nilai) from nilai_uts;
+------------+
| max(nilai) |
+------------+
|        100 |
+------------+
1 row in set (0.02 sec)


((PERINTAH MENCARI NILAI TERKECIL DI KOLOM NILAI,,YANG ADA DI TABEL NILAI_UTS))

mysql> select min(nilai) from nilai_uts;
+------------+
| min(nilai) |
+------------+
|         85 |
+------------+
1 row in set (0.00 sec)



 ((PERINTAH MENJUMLAHKAN SEMUA NILAI YANG ADA DI KOLOM NILAI,,DARI TABEL NILAI_UTS))

mysql> select sum(nilai) from nilai_uts;
+------------+
| sum(nilai) |
+------------+
|        472 |
+------------+
1 row in set (0.00 sec)


  ((PERINTAH MENGHITUNG RATA22 SEMUA NILAI YANG ADA DI TABEL NILAI_UTS))

mysql> select avg(nilai) from nilai_uts;
+------------+
| avg(nilai) |
+------------+
|       94.4 |
+------------+
1 row in set (0.00 sec)




mysql> desc nilai_uts;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| kode_nilai  | int(11)     | NO   | PRI | NULL    | auto_increment |
| kode_matkul | varchar(10) | YES  | MUL | NULL    |                |
| kode_dosen  | varchar(10) | YES  | MUL | NULL    |                |
| nim         | varchar(14) | YES  | MUL | NULL    |                |
| nilai       | float       | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)


   ((BUAT PERINTAH INNER JOIN MATKUL,DOSEN DARI TABEL NILAI_UTS))

mysql> select nilai_uts.kode_nilai , nilai_uts.kode_matkul , matkul.nama_matkul,
    -> nilai_uts.kode_dosen , dosen.nama_dosen , nilai_uts.nim , nilai_uts.nilai
    -> from nilai_uts
    -> inner join matkul on nilai_uts.kode_matkul = matkul.kode_matkul
    -> inner join dosen on nilai_uts.kode_dosen = dosen.kode_dosen;
+------------+-------------+---------------------------------+------------+-------------------------+------------+-------+
| kode_nilai | kode_matkul | nama_matkul                     | kode_dosen | nama_dosen              | nim        | nilai |
+------------+-------------+---------------------------------+------------+-------------------------+------------+-------+
|          1 | 01SDB       | Sistem Dan Teknologi Basis Data | 001        | Carli,S.Kom             | 43A8700613 |    99 |
|          2 | 02BP        | Bahasa Pemrograman 2            | 002        | Budi,S.Kom,M.Kom        | 43A8700614 |   100 |
|          3 | 03ASD       | Algoritma Dan Struktur Data     | 003        | H.Tukino,S.Kom,M.Msi    | 43A8700615 |    98 |
|          4 | 04SD        | Struktur Diskrit 2              | 004        | Hafidzah,M.T            | 43A8700616 |    90 |
|          5 | 05MP        | Mikroprosessor                  | 005        | AdiSuwarno,S.Kom,M.Kom  | 43A8700617 |    85 |
+------------+-------------+---------------------------------+------------+-------------------------+------------+-------+
5 rows in set (0.05 sec)



   ((BUAT PERINTAH BIKIN INNER JOIN MATKUL,DOSEN,MAHASISWA DARI TABEL NILAI_UTS))

mysql> select nilai_uts.kode_nilai , nilai_uts.kode_matkul , matkul.nama_matkul,
    -> nilai_uts.kode_dosen , dosen.nama_dosen , nilai_uts.nim ,mahasiswa.nama_mahasiswa ,
    -> nilai_uts.nilai from nilai_uts
    -> inner join matkul on nilai_uts.kode_matkul = matkul.kode_matkul
    -> inner join dosen on nilai_uts.kode_dosen = dosen.kode_dosen
    -> inner join mahasiswa on nilai_uts.nim = mahasiswa.nim;
+---------+------------+----------------------------+----------+-----------------------+----------+--------------+------+
|kode_nilai|kode_matkul| nama_matkul                |kode_dosen| nama_dosen            |nim       |nama_mahasiswa|nilai |
+---------+------------+----------------------------+----------+-----------------------+----------+--------------+------+
|        1| 01SDB      | Sistem Dan Teknologi Basis |001       | Carli,S.Kom           |43A8700613| M.EDI.S      |   99 |
|        2| 02BP       | Bahasa Pemrograman 2       |002       | Budi,S.Kom,M.Kom      |43A8700614| Nita Fay Fay |  100 |
|        3| 03ASD      | Algoritma Dan Struktur Data|003       | H.Tukino,S.Kom,M.Msi  |43A8700615| Erni.N       |   98 |
|        4| 04SD       | Struktur Diskrit 2         |004       | Hafidzah,M.T          |43A8700616| Ahmad Yasir  |   90 |
|        5| 05MP       | Mikroprosessor             |005       | AdiSuwarno,S.Kom,M.Kom|43A8700617| Linah.K      |   85 |
+---------+------------+----------------------------+----------+-----------------------+----------+--------------+------+
5 rows in set (0.00 sec)






mysql> desc nilai_uts;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| kode_nilai  | int(11)     | NO   | PRI | NULL    | auto_increment |
| kode_matkul | varchar(10) | YES  | MUL | NULL    |                |
| kode_dosen  | varchar(10) | YES  | MUL | NULL    |                |
| nim         | varchar(14) | YES  | MUL | NULL    |                |
| nilai       | float       | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
5 rows in set (0.05 sec)


   ((TAMPILAN SEBELUM DI INSERT))
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)


  ((INSERT BUAT KODE_MATKUL,KODE_DOSEN,,NIM YANG SAMA DI TABLE NILAI_UTS))

mysql> insert into nilai_uts value ('F15','05MP','005','43A8700613','100');
Query OK, 1 row affected, 1 warning (0.39 sec)


mysql> insert into nilai_uts value ('F16','05MP','005','43A8700613','95');
Query OK, 1 row affected, 1 warning (0.36 sec)


   ((TAMPILAN SETELAH DI INSERT))
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 |
|          6 | 05MP        | 005        | 43A8700613 |   100 |
|          7 | 05MP        | 005        | 43A8700613 |    95 |
+------------+-------------+------------+------------+-------+
7 rows in set (0.00 sec)


   ((BUAT PERINTAH QUERY GROUP BY))

mysql> select nim,AVG(nilai) from nilai_uts GROUP BY nim;
+------------+------------+
| nim        | AVG(nilai) |
+------------+------------+
| 43A8700613 |         98 |
| 43A8700614 |        100 |
| 43A8700615 |         98 |
| 43A8700616 |         90 |
| 43A8700617 |         85 |
+------------+------------+
5 rows in set (0.01 sec)


mysql> select nim,avg(nilai) from nilai_uts GROUP BY nim='43A8700613';
+------------+------------+
| nim        | avg(nilai) |
+------------+------------+
| 43A8700614 |      93.25 |
| 43A8700613 |         98 |
+------------+------------+
2 rows in set (0.00 sec)

mysql> select nim,avg(nilai) from nilai_uts GROUP BY nim='43A8700614';
+------------+------------+
| nim        | avg(nilai) |
+------------+------------+
| 43A8700613 |       94.5 |
| 43A8700614 |        100 |
+------------+------------+
2 rows in set (0.00 sec)



   ((BUAT MENJUMLAHKAN NILAI DI TABEL NILAI_UTS DENGAN KONDISI NIM='43A8700613';

mysql> select sum(nilai) from nilai_uts where nim='43A8700613';
+------------+
| sum(nilai) |
+------------+
|        294 |
+------------+
1 row in set (0.00 sec)




 ((BUAT MENAMPIL KAN BEBERAPA RECORD DARI TABEL NILAI_UTS ))
            DENGAN KONDISI NIM='43A8700613';

mysql> select * from nilai_uts where nim='43A8700613';
+------------+-------------+------------+------------+-------+
| kode_nilai | kode_matkul | kode_dosen | nim        | nilai |
+------------+-------------+------------+------------+-------+
|          1 | 01SDB       | 001        | 43A8700613 |    99 |
|          6 | 05MP        | 005        | 43A8700613 |   100 |
|          7 | 05MP        | 005        | 43A8700613 |    95 |
+------------+-------------+------------+------------+-------+
3 rows in set (0.00 sec)






   ((BUAT MENJUMLAHKAN DAN DI BAGI 3 DARI TABLE NILAI_UTS))
          DENGAN KONDISI NIM='43A8700613';

mysql> select sum(nilai)/3 from nilai_uts where nim='43A8700613';
+--------------+
| sum(nilai)/3 |
+--------------+
|           98 |
+--------------+
1 row in set (0.36 sec)




((BUAT MENJUMLAHKAN NILAI DAN DI BAGI JUMLAH NIM DARI TABLE NILAI_UTS))
              DENGAN KONDISI NIM='43A8700613';

mysql> select sum(nilai)/count(nim) from nilai_uts where nim='43A8700613';
+-----------------------+
| sum(nilai)/count(nim) |
+-----------------------+
|                    98 |
+-----------------------+
1 row in set (0.00 sec)




      ((BUAT MEMBAGI NILAI  DI KOLOM NILAI DARI TABEL NILAI_UTS))
            DENGAN KONDISI NIM='43A8700613';

mysql> select AVG(nilai) from nilai_uts where nim='43A8700613';
+------------+
| AVG(nilai) |
+------------+
|         98 |
+------------+
1 row in set (0.00 sec)



   ((MENAMPILKAN HASIL RATA22 DARI TABEL NILAI_UTS))

mysql> select avg(nilai) from nilai_uts;
+-------------------+
| avg(nilai)        |
+-------------------+
| 95.28571428571429 |
+-------------------+
1 row in set (0.00 sec)