Strict Standards: Declaration of mystique_CategoryWalker::start_el() should be compatible with Walker::start_el(&$output) in /home/blog/html/wp-content/themes/mystique/lib/core.php on line 71 Strict Standards: Declaration of mystique_CategoryWalker::end_el() should be compatible with Walker::end_el(&$output) in /home/blog/html/wp-content/themes/mystique/lib/core.php on line 71 Strict Standards: Declaration of mystique_PageWalker::start_el() should be compatible with Walker::start_el(&$output) in /home/blog/html/wp-content/themes/mystique/lib/core.php on line 127 Strict Standards: Declaration of mystique_PageWalker::end_el() should be compatible with Walker::end_el(&$output) in /home/blog/html/wp-content/themes/mystique/lib/core.php on line 127 Listing | Setetes Embun Yang Bermakna

Listing

Pemrosesan Tabel Jamak


mysql> select mhs.nama,matakuliah.nama_mk from mhs,matakuliah;
+--------+---------------------+
| nama   | nama_mk             |
+--------+---------------------+
| emy    | pemograman komputer |
| waldan | pemograman komputer |
| tasya  | pemograman komputer |
| rika   | pemograman komputer |
| thomas | pemograman komputer |
| emy    | basis data          |
| waldan | basis data          |
| tasya  | basis data          |
| rika   | basis data          |
| thomas | basis data          |
| emy    | sistem informasi    |
| waldan | sistem informasi    |
| tasya  | sistem informasi    |
| rika   | sistem informasi    |
| thomas | sistem informasi    |
| emy    | bahasa inggris      |
| waldan | bahasa inggris      |
| tasya  | bahasa inggris      |
| rika   | bahasa inggris      |
| thomas | bahasa inggris      |
| emy    | pemogramana web     |
| waldan | pemogramana web     |
| tasya  | pemogramana web     |
| rika   | pemogramana web     |
| thomas | pemogramana web     |
+--------+---------------------+
25 rows in set (0.00 sec)
mysql> select a.nim,a.nama,b.nama_mk,c.nilai
    -> from mhs a,matakuliah b,khs c;

mysql> select * from khs,matakuliah
    -> where khs.kd_mk=matakuliah.kd_mk;
+------------+-------+----------+-----------+-------+-------+----------------
--+------+
| nim        | kd_mk | semester | TA        | Nilai | kd_mk | nama_mk
  | sks  |
+------------+-------+----------+-----------+-------+-------+----------------
--+------+
| 0802100011 | DKT01 |        3 | 2007/2008 | A     | DKT01 | pemograman komp
r |    2 |
| 0802100012 | PKT21 |        5 | 2007/2008 | A     | PKT21 | bahasa inggris
  |    2 |
| 0802100013 | PKT21 |        5 | 2007/2008 | B     | PKT21 | bahasa inggris
  |    2 |
| 0802100014 | DKT01 |        5 | 2007/2008 | C     | DKT01 | pemograman komp
r |    2 |
| 0802100015 | KKT07 |        2 | 2007/2008 | B     | KKT07 | sistem informas
  |    2 |
+------------+-------+----------+-----------+-------+-------+----------------
--+------+
5 rows in set (0.01 sec)
mysql> select * from khs inner join matakuliah
    -> on khs.kd_mk=matakuliah.kd_mk;
+------------+-------+----------+-----------+-------+-------+----------------
--+------+
| nim        | kd_mk | semester | TA        | Nilai | kd_mk | nama_mk
  | sks  |
+------------+-------+----------+-----------+-------+-------+----------------
--+------+
| 0802100011 | DKT01 |        3 | 2007/2008 | A     | DKT01 | pemograman komp
r |    2 |
| 0802100012 | PKT21 |        5 | 2007/2008 | A     | PKT21 | bahasa inggris
  |    2 |
| 0802100013 | PKT21 |        5 | 2007/2008 | B     | PKT21 | bahasa inggris
  |    2 |
| 0802100014 | DKT01 |        5 | 2007/2008 | C     | DKT01 | pemograman komp
r |    2 |
| 0802100015 | KKT07 |        2 | 2007/2008 | B     | KKT07 | sistem informas
  |    2 |
+------------+-------+----------+-----------+-------+-------+----------------
--+------+
5 rows in set (0.00 sec)
mysql> select k.nim,k.kd_mk,m.nama_mk,m.sks,k.semester,k.TA,k.nilai
    -> from khs k,matakuliah m where k.kd_mk=m.kd_mk;
+------------+-------+---------------------+------+----------+-----------+---
-+
| nim        | kd_mk | nama_mk             | sks  | semester | TA        | ni
 |
+------------+-------+---------------------+------+----------+-----------+---
-+
| 0802100011 | DKT01 | pemograman komputer |    2 |        3 | 2007/2008 | A
 |
| 0802100012 | PKT21 | bahasa inggris      |    2 |        5 | 2007/2008 | A
 |
| 0802100013 | PKT21 | bahasa inggris      |    2 |        5 | 2007/2008 | B
 |
| 0802100014 | DKT01 | pemograman komputer |    2 |        5 | 2007/2008 | C
 |
| 0802100015 | KKT07 | sistem informasi    |    2 |        2 | 2007/2008 | B
 |
+------------+-------+---------------------+------+----------+-----------+---
-+
5 rows in set (0.00 sec)

mysql> select k.nim,m.nama_mk,m.sks,k.nilai as nilai,
    -> case
    -> when nilai='A' then 4
    -> when nilai='B' then 3
    -> when nilai='C' then 2
    -> when nilai='D' then 1
    -> else 0
    -> end as nilai_angka
    -> from khs k inner join matakuliah m on k.kd_mk=m.kd_mk;
+------------+---------------------+------+-------+-------------+
| nim        | nama_mk             | sks  | nilai | nilai_angka |
+------------+---------------------+------+-------+-------------+
| 0802100011 | pemograman komputer |    2 | A     |           4 |
| 0802100012 | bahasa inggris      |    2 | A     |           4 |
| 0802100013 | bahasa inggris      |    2 | B     |           3 |
| 0802100014 | pemograman komputer |    2 | C     |           2 |
| 0802100015 | sistem informasi    |    2 | B     |           3 |
+------------+---------------------+------+-------+-------------+
5 rows in set (0.00 sec)

mysql> select k.nim,m.nama_mk,m.sks,k.nilai,
    -> case
    -> when nilai='A' then 4*m.sks
    -> when nilai='B' then 3*m.sks
    -> when nilai='C' then 2*m.sks
    -> when nilai='D' then 1*m.sks
    -> else 0
    -> end as point
    -> from khs k inner join matakuliah m on k.kd_mk = m.kd_mk;
+------------+---------------------+------+-------+-------+
| nim        | nama_mk             | sks  | nilai | point |
+------------+---------------------+------+-------+-------+
| 0802100011 | pemograman komputer |    2 | A     |     8 |
| 0802100012 | bahasa inggris      |    2 | A     |     8 |
| 0802100013 | bahasa inggris      |    2 | B     |     6 |
| 0802100014 | pemograman komputer |    2 | C     |     4 |
| 0802100015 | sistem informasi    |    2 | B     |     6 |
+------------+---------------------+------+-------+-------+
5 rows in set (0.00 sec)

mysql> select mhs.nim,mhs.nama
    -> from mhs inner join khs on mhs.nim=khs.nim;
+------------+--------+
| nim        | nama   |
+------------+--------+
| 0802100011 | emy    |
| 0802100012 | waldan |
| 0802100013 | tasya  |
| 0802100014 | rika   |
| 0802100015 | thomas |
+------------+--------+
5 rows in set (0.00 sec)
mysql> select a.nim,a.nama,b.nama_mk,b.sks,c.semester,c.nilai,c.TA
    -> from mhs a inner join khs c on a.nim=c.nim
    -> inner join matakuliah b on c.kd_mk=b.kd_mk;
+------------+--------+---------------------+------+----------+-------+-----
--+
| nim        | nama   | nama_mk             | sks  | semester | nilai | TA
  |
+------------+--------+---------------------+------+----------+-------+-----
--+
| 0802100011 | emy    | pemograman komputer |    2 |        3 | A     | 2007
8 |
| 0802100012 | waldan | bahasa inggris      |    2 |        5 | A     | 2007
8 |
| 0802100013 | tasya  | bahasa inggris      |    2 |        5 | B     | 2007
8 |
| 0802100014 | rika   | pemograman komputer |    2 |        5 | C     | 2007
8 |
| 0802100015 | thomas | sistem informasi    |    2 |        2 | B     | 2007
8 |
+------------+--------+---------------------+------+----------+-------+-----
--+
5 rows in set (0.00 sec)

mysql> select khs.nim,mhs.nama,(sum(
    -> case
    -> when khs.nilai='A' then 4*matakuliah.sks
    -> when khs.nilai='B' then 3*matakuliah.sks
    -> when khs.nilai='C' then 2*matakuliah.sks
    -> when khs.nilai='D' then 1*matakuliah.sks
    -> else 0
    -> end )/sum(matakuliah.sks)) as ipk
    -> from khs inner join matakuliah on khs.kd_mk=matakuliah.kd_mk
    -> inner join mhs on khs.nim=mhs.nim
    -> group by khs.nim,mhs.nama;
+------------+--------+--------+
| nim        | nama   | ipk    |
+------------+--------+--------+
| 0802100011 | emy    | 4.0000 |
| 0802100012 | waldan | 4.0000 |
| 0802100013 | tasya  | 3.0000 |
| 0802100014 | rika   | 2.0000 |
| 0802100015 | thomas | 3.0000 |
+------------+--------+--------+
5 rows in set (0.01 sec)

Penggunaan Sub query

mysql> use mahasiswa;
Database changed

mysql> select nim,nama from mhs where exists
    -> (select * from khs where nim = mhs.nim);
+------------+--------+
| nim        | nama   |
+------------+--------+
| 0802100011 | emy    |
| 0802100012 | waldan |
| 0802100013 | tasya  |
| 0802100014 | rika   |
| 0802100015 | thomas |
+------------+--------+
5 rows in set (0.14 sec)

mysql> select nim,nama from mhs
    -> where nim = any(select distinct nim from khs);
+------------+--------+
| nim        | nama   |
+------------+--------+
| 0802100011 | emy    |
| 0802100012 | waldan |
| 0802100013 | tasya  |
| 0802100014 | rika   |
| 0802100015 | thomas |
+------------+--------+
5 rows in set (0.00 sec)

mysql> select nim,nama from mhs
    -> where nim in(select distinct nim from khs);
+------------+--------+
| nim        | nama   |
+------------+--------+
| 0802100011 | emy    |
| 0802100012 | waldan |
| 0802100013 | tasya  |
| 0802100014 | rika   |
| 0802100015 | thomas |
+------------+--------+
5 rows in set (0.01 sec)

mysql> select nim,nama from mhs
    -> where not exists(select * from khs where nim = mhs.nim);
Empty set (0.00 sec)

DATABASE PERUSAHAAN

mysql> select no_karyawan,Nama from Karyawan where exists
    -> (select * from Teknisi_proyek where no_karyawan = Teknisi_proyek.no_karya
wan);
+-------------+---------+
| no_karyawan | Nama    |
+-------------+---------+
| A           | mega    |
| B           | andi    |
| C           | sinta   |
| D           | Alisa   |
| E           | Angkasa |
+-------------+---------+
5 rows in set (0.00 sec)

mysql> select no_karyawan,Nama from Karyawan where no_karyawan = any
    -> (select distinct no_karyawan from Teknisi_proyek);
+-------------+---------+
| no_karyawan | Nama    |
+-------------+---------+
| A           | mega    |
| B           | andi    |
| C           | sinta   |
| D           | Alisa   |
| E           | Angkasa |
+-------------+---------+
5 rows in set (0.00 sec)
mysql> select no_karyawan,Nama from Karyawan where no_karyawan in
    -> (select distinct no_karyawan from Teknisi_proyek);

+-------------+---------+
| no_karyawan | Nama    |
+-------------+---------+
| A           | mega    |
| B           | andi    |
| C           | sinta   |
| D           | Alisa   |
| E           | Angkasa |
+-------------+---------+
5 rows in set (0.00 sec)

mysql> select no_karyawan,Nama from Karyawan where not exists
    -> (select * from Teknisi_proyek where no_karyawan=Karyawan.no_Karyawan);
Empty set (0.00 sec)

Kesimpulan :
Subquery IN : subquery yang digunakan untuk menghasilkan 1(satu) kolom tapi boleh beberapa baris.
Subquery EXISTS : subquery yang dapat menghasilkan satu atau lebih kolom.
Subquery ALL/SOME : Subquery yang mana hasil yang dikembalikan lebih dari satu.