Basis Data SQL Praktikum 10

Basis Data SQL - Pada kali ini sudah memasuki praktikum terakhir, yaitu praktikum 10. Pada praktikum 10 kita membahas masalah Singgle Row SubQuery dan Multiple Row SubQuery.

Ini adalah hasil saya pada praktikum Basis Data 10 :

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 13

Server version: 5.1.41 Source distribution

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

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| cdcol              |

| mysql              |

| phpmyadmin         |

| praktikumku        |

| test               |

+--------------------+

6 rows in set (0.00 sec)

mysql> use praktikumku;

Database changed

mysql> show tables;

+-----------------------+

| Tables_in_praktikumku |

+-----------------------+

| ambil                 |

| dosen                 |

| mahasiswa             |

| makul                 |

+-----------------------+

4 rows in set (0.02 sec)

mysql> select * from ambil;

+----------+----------+-------+-------+

| nim      | kd_mk    | nilai | kelas |

+----------+----------+-------+-------+

| 05018001 | T0000001 |   100 | A     |

| 05018001 | T0000002 |    85 | A     |

| 05018001 | T0000003 |    75 | A     |

| 05018002 | T0000001 |    85 | A     |

| 05018002 | T0000003 |   100 | A     |

| 05018003 | T0000002 |    65 | B     |

+----------+----------+-------+-------+

6 rows in set (0.00 sec)

mysql> select * from mahasiswa;

+----------+------+----+------------+--------+

| nim      | nama | jk | alamat     | nip    |

+----------+------+----+------------+--------+

| 05018001 | Andi | L  | Yogyakarta | 101011 |

| 05018002 | Isna | P  | Magelang   | 101012 |

| 05018003 | Dion | L  | Yogyakarta | 101011 |

+----------+------+----+------------+--------+

3 rows in set (0.00 sec)

mysql> select max(nilai) from ambil;

+------------+

| max(nilai) |

+------------+

|        100 |

+------------+

1 row in set (0.00 sec)

mysql> delete from ambil where nim='05018002' and kd_mk='T0000003';

Query OK, 1 row affected (0.01 sec)

mysql> select * from ambil;

+----------+----------+-------+-------+

| nim      | kd_mk    | nilai | kelas |

+----------+----------+-------+-------+

| 05018001 | T0000001 |   100 | A     |

| 05018001 | T0000002 |    85 | A     |

| 05018001 | T0000003 |    75 | A     |

| 05018002 | T0000001 |    85 | A     |

| 05018003 | T0000002 |    65 | B     |

+----------+----------+-------+-------+

5 rows in set (0.00 sec)

mysql> select m.nama from mahasiswa m, ambil a where m.nim=a.nim and a.nilai=(se

lect max(nilai)from ambil);

+------+

| nama |

+------+

| Andi |

+------+

1 row in set (0.00 sec)

mysql> select m.nama from mahasiswa m, ambil a where m.nim=a.nim and a.nilai in

(select max(nilai)from ambil);

+------+

| nama |

+------+

| Andi |

+------+

1 row in set (0.00 sec)

mysql> select nim, nama from mahasiswa where nim in (select nim from ambil where

 kd_mk='t0000002');

+----------+------+

| nim      | nama |

+----------+------+

| 05018001 | Andi |

| 05018003 | Dion |

+----------+------+

2 rows in set (0.00 sec)

mysql> select * from ambil;

+----------+----------+-------+-------+

| nim      | kd_mk    | nilai | kelas |

+----------+----------+-------+-------+

| 05018001 | T0000001 |   100 | A     |

| 05018001 | T0000002 |    85 | A     |

| 05018001 | T0000003 |    75 | A     |

| 05018002 | T0000001 |    85 | A     |

| 05018003 | T0000002 |    65 | B     |

+----------+----------+-------+-------+

5 rows in set (0.02 sec)

mysql> select * from dosen;

+--------+-------------+----+------------+

| nip    | nama        | jk | alamat     |

+--------+-------------+----+------------+

| 101011 | Doni, S.T   | L  | Yogyakarta |

| 101012 | Shinta, S.T | P  | Bandung    |

+--------+-------------+----+------------+

2 rows in set (0.02 sec)

mysql> select * from mahasiswa;

+----------+------+----+------------+--------+

| nim      | nama | jk | alamat     | nip    |

+----------+------+----+------------+--------+

| 05018001 | Andi | L  | Yogyakarta | 101011 |

| 05018002 | Isna | P  | Magelang   | 101012 |

| 05018003 | Dion | L  | Yogyakarta | 101011 |

+----------+------+----+------------+--------+

3 rows in set (0.00 sec)

mysql> select * from makul;

+----------+--------------------+-----+

| kd_mk    | nm_mk              | sks |

+----------+--------------------+-----+

| T0000001 | Matematika Diskrit |   3 |

| T0000002 | Kalkulus Lanjut    |   3 |

| T0000003 | Basis Data         |   3 |

+----------+--------------------+-----+

3 rows in set (0.01 sec)

mysql> select m.nim, m.nama from mahasiswa m, makul ma, ambil a where m.nim=a.ni

m and a.kd_mk=ma.kd_mk and ma.nm_mk='Basis Data';

+----------+------+

| nim      | nama |

+----------+------+

| 05018001 | Andi |

+----------+------+

1 row in set (0.05 sec)

mysql> select makul.nm_mk from makul, mahasiswa, ambil where mahasiswa.nim=ambil

.nim and ambil.kd_mk=makul.kd_mk and mahasiswa.alamat='Yogyakarta';

+--------------------+

| nm_mk              |

+--------------------+

| Matematika Diskrit |

| Kalkulus Lanjut    |

| Basis Data         |

| Kalkulus Lanjut    |

+--------------------+

4 rows in set (0.00 sec)

mysql> select mahasiswa.nim from makul, mahasiswa, ambil where mahasiswa.nim=amb

il.nim and ambil.kd_mk=makul.kd_mk and mahasiswa.alamat='Yogyakarta' and makul.n

m_mk='Basis Data';

+----------+

| nim      |

+----------+

| 05018001 |

+----------+

1 row in set (0.00 sec)

mysql>select mahasiswa.nim from makul, mahasiswa, ambil where mahasiswa.nim=amb
il.nim and ambil.kd_mk=makul.kd_mk and makul.nm_mk='Basis Data' and mahasiswa.ni
m in(select nim from mahasiswa where alamat='yogyakarta');
+----------+
| nim      |
+----------+
| 05018001 |
+----------+
1 row in set (0.00 sec)

mysql>