Nomor 1
mysql> SHOW
DATABASES;
+--------------------+
| Database |
+--------------------+
|
information_schema |
| bandara |
|
dbperpustakaan |
| mysql |
|
performance_schema |
| pethouse |
| prak1 |
| prak2 |
| smbd |
| test |
+--------------------+
10 rows in set
(0.00 sec)
mysql> CREATE
DATABASE kuliah;
Query OK, 1 row
affected (0.00 sec)
mysql> USE
kuliah;
Database changed
mysql> CREATE
TABLE instruktur(nip char(1) primary key, namains varchar(30) not
null, jurusan
varchar(30) not null, asalkota varchar(30) not null);
Query OK, 0 rows
affected (0.08 sec)
mysql> DESC
instruktur;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| nip | char(1) | NO
| PRI | NULL | |
| namains | varchar(30) | NO |
| NULL | |
| jurusan | varchar(30) | NO |
| NULL | |
| asalkota |
varchar(30) | NO | | NULL
| |
+----------+-------------+------+-----+---------+-------+
4 rows in set
(0.02 sec)
mysql> CREATE
TABLE matakuliah(nomk varchar(6) primary key, namamk varchar(50) n
ot null, sks int
not null);
Query OK, 0 rows
affected (0.09 sec)
mysql> DESC
matakuliah;
+--------+-------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| nomk | varchar(6)
| NO | PRI | NULL |
|
| namamk |
varchar(50) | NO | | NULL
| |
| sks | int(11) | NO
| | NULL |
|
+--------+-------------+------+-----+---------+-------+
3 rows in set
(0.01 sec)
mysql> CREATE
TABLE kuliah(nip char(1) not null references instruktur(nip), nomk
varchar(6) not null references
matakuliah(nomk), ruangan varchar(3) not null, j
mlmhs int not
null);
Query OK, 0 rows
affected (0.09 sec)
mysql> DESC
kuliah;
+---------+------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| nip | char(1) | NO
| | NULL |
|
| nomk | varchar(6) | NO |
| NULL | |
| ruangan |
varchar(3) | NO | | NULL
| |
| jmlmhs | int(11)
| NO | | NULL
| |
+---------+------------+------+-----+---------+-------+
4 rows in set
(0.00 sec)
mysql> SELECT
* FROM instruktur;
+-----+---------------+---------------+----------+
| nip |
namains | jurusan | asalkota |
+-----+---------------+---------------+----------+
| 1 | Steve Wozniak | Ilmu Komputer |
Bantul |
| 2 | Steve Jobs | Seni Rupa | Solo
|
| 3 | James Gosling | Ilmu Komputer |
Klaten |
| 4 | Bill Gates | Ilmu Komputer | Magelang |
+-----+---------------+---------------+----------+
4 rows in set
(0.00 sec)
mysql> SELECT
* FROM matakuliah;
+--------+--------------------------------+-----+
| nomk | namamk
| sks |
+--------+--------------------------------+-----+
| KOM101 |
Algoritma dan Pemrograman | 3 |
| KOM102 | Basis
Data | 3 |
| KOM201 |
Pemrograman Berorientasi Objek | 3 |
| SR101 | Desain Elementer | 3 |
+--------+--------------------------------+-----+
4 rows in set
(0.00 sec)
mysql> SELECT
* FROM kuliah;
+-----+--------+---------+--------+
| nip |
nomk | ruangan | jmlmhs |
+-----+--------+---------+--------+
| 1 | KOM101 | 101 |
50 |
| 1 | KOM102 | 102 |
35 |
| 2 | SR101
| 101 | 45 |
| 3 | KOM201 | 101 |
55 |
+-----+--------+---------+--------+
4 rows in set
(0.00 sec)
Nomor 2
Nomor a
mysql> SELECT
* FROM instruktur;
+-----+---------------+---------------+----------+
| nip |
namains | jurusan | asalkota |
+-----+---------------+---------------+----------+
| 1 | Steve Wozniak | Ilmu Komputer |
Bantul |
| 2 | Steve Jobs | Seni Rupa | Solo
|
| 3 | James Gosling | Ilmu Komputer |
Klaten |
| 4 | Bill Gates | Ilmu Komputer | Magelang |
+-----+---------------+---------------+----------+
4 rows in set
(0.00 sec)
Nomor b
mysql> SELECT
matakuliah.nomk FROM matakuliah JOIN kuliah ON matakuliah.nomk = k
uliah.nomk WHERE
kuliah.jmlmhs > 40;
+--------+
| nomk |
+--------+
| KOM101 |
| SR101 |
| KOM201 |
+--------+
3 rows in set
(0.06 sec)
Nomor c
mysql> SELECT
matakuliah.nomk, matakuliah.namamk FROM matakuliah JOIN kuliah ON
matakuliah.nomk
= kuliah.nomk WHERE kuliah.jmlmhs > 40;
+--------+--------------------------------+
| nomk | namamk |
+--------+--------------------------------+
| KOM101 |
Algoritma dan Pemrograman |
| KOM201 |
Pemrograman Berorientasi Objek |
| SR101 | Desain Elementer |
+--------+--------------------------------+
3 rows in set
(0.05 sec)
Nomor d
mysql> SELECT
kuliah.nip FROM kuliah WHERE nomk = 'KOM102';
+-----+
| nip |
+-----+
| 1 |
+-----+
1 row in set
(0.00 sec)
Nomor e
mysql> SELECT
kuliah.nip FROM kuliah JOIN matakuliah USING(nomk) WHERE namamk =
'Basis Data';
+-----+
| nip |
+-----+
| 1 |
+-----+
1 row in set
(0.00 sec)
Nomor f
mysql> SELECT
kuliah.nip, instruktur.namains FROM kuliah JOIN matakuliah USING(n
omk) JOIN
instruktur USING(nip) WHERE namamk = 'Basis Data';
+-----+---------------+
| nip |
namains |
+-----+---------------+
| 1 | Steve Wozniak |
+-----+---------------+
1 row in set
(0.05 sec)
Nomor g
mysql> SELECT
matakuliah.namamk, kuliah.ruangan FROM kuliah JOIN matakuliah USIN
G(nomk) JOIN
instruktur USING(nip) WHERE instruktur.namains = 'Steve Jobs';
+------------------+---------+
| namamk | ruangan |
+------------------+---------+
| Desain
Elementer | 101 |
+------------------+---------+
1 row in set
(0.00 sec)
Nomor h
mysql> SELECT
SUM(kuliah.jmlmhs) FROM kuliah JOIN instruktur USING(nip) JOIN mat
akuliah
USING(nomk) WHERE instruktur.namains = 'Steve Wozniak';
+--------------------+
|
SUM(kuliah.jmlmhs) |
+--------------------+
| 85 |
+--------------------+
1 row in set
(0.08 sec)
Nomor i
mysql> SELECT
instruktur.nip, instruktur.namains FROM instruktur JOIN kuliah USI
NG(nip) WHERE
jmlmhs IN (SELECT MAX(jmlmhs) FROM kuliah);
+-----+---------------+
| nip |
namains |
+-----+---------------+
| 3 | James Gosling |
+-----+---------------+
1 row in set
(0.00 sec)
Nomor j
mysql> SELECT
instruktur.nip, instruktur.namains FROM instruktur WHERE nip NOT I
N (SELECT nip
FROM kuliah);
+-----+------------+
| nip |
namains |
+-----+------------+
| 4 | Bill Gates |
+-----+------------+
1 row in set
(0.00 sec)
Nomor 3
Nomor a
mysql> CREATE
VIEW view1 AS SELECT instruktur.nip, instruktur.namains FROM instr
uktur WHERE nip
NOT IN (SELECT nip FROM kuliah);
Query OK, 0 rows
affected (0.11 sec)
mysql> SELECT
* FROM view1;
+-----+------------+
| nip |
namains |
+-----+------------+
| 4 | Bill Gates |
+-----+------------+
1 row in set
(0.04 sec)
Nomor b
mysql> SELECT
kuliah.nip, instruktur.namains, count(nomk) FROM kuliah JOIN instr
uktur USING(nip)
GROUP BY nip;
+-----+---------------+-------------+
| nip |
namains | count(nomk) |
+-----+---------------+-------------+
| 1 | Steve Wozniak | 2 |
| 2 | Steve Jobs |
1 |
| 3 | James Gosling | 1 |
+-----+---------------+-------------+
3 rows in set
(0.00 sec)
Nomor 4
mysql>
DELIMITER //
mysql> CREATE
TRIGGER up_data AFTER UPDATE
-> ON kuliah
-> FOR EACH ROW
-> BEGIN
-> INSERT INTO log
-> VALUES(user(), concat('merubah
ruangan ',NEW.nomk,' dari ruang ',OLD.ruan
gan ,' ke
',NEW.ruangan));
-> END //
Query OK, 0 rows
affected (0.11 sec)
mysql> SELECT
* FROM kuliah;
-> //
+-----+--------+---------+--------+
| nip |
nomk | ruangan | jmlmhs |
+-----+--------+---------+--------+
| 1 | KOM101 | 101 |
50 |
| 1 | KOM102 | 102 |
35 |
| 2 | SR101
| 101 | 45 |
| 3 | KOM201 | 101 |
55 |
+-----+--------+---------+--------+
4 rows in set
(0.00 sec)
mysql> UPDATE
kuliah SET ruangan='102' WHERE nomk = 'KOM101';
-> //
Query OK, 1 row
affected (0.08 sec)
Rows matched:
1 Changed: 1 Warnings: 0
mysql>
DELIMITER ;
mysql> SELECT
* FROM log;
+----------------+----------------------------------------------+
| user_id | deskripsi |
+----------------+----------------------------------------------+
| root@localhost
| merubah ruangan KOM101 dari ruang 101 ke 102 |
+----------------+----------------------------------------------+
1 row in set
(0.00 sec)
Nomor 5
Nomor c
mysql>
DELIMITER //
mysql> CREATE
PROCEDURE showRoom (IN nama varchar(30))
-> BEGIN
-> SELECT matakuliah.namamk,
kuliah.ruangan FROM kuliah JOIN matakuliah USIN
G(nomk) JOIN
instruktur USING(nip) WHERE instruktur.namains LIKE nama;
-> END //
Query OK, 0 rows
affected (0.04 sec)
mysql>
DELIMITER ;
mysql> CALL
showRoom('Steve Jobs');
+------------------+---------+
| namamk | ruangan |
+------------------+---------+
| Desain
Elementer | 101 |
+------------------+---------+
1 row in set
(0.05 sec)
Query OK, 0 rows
affected (0.05 sec)
mysql> CALL
showRoom('Steve Wozniak');
+---------------------------+---------+
| namamk | ruangan |
+---------------------------+---------+
| Algoritma dan
Pemrograman | 102 |
| Basis
Data | 102 |
+---------------------------+---------+
2 rows in set
(0.00 sec)
Query OK, 0 rows
affected (0.00 sec)
Nomor d
mysql>
DELIMITER //
mysql> CREATE
PROCEDURE getSks(IN nama varchar(30))
-> BEGIN
-> SELECT SUM(matakuliah.sks) FROM
kuliah JOIN matakuliah USING(nomk) JOIN i
nstruktur
USING(nip) WHERE instruktur.namains LIKE nama;
-> END //
Query OK, 0 rows
affected (0.00 sec)
mysql> CALL
getSks('Steve Jobs');
-> //
+---------------------+
| SUM(matakuliah.sks)
|
+---------------------+
| 3 |
+---------------------+
1 row in set
(0.00 sec)
Query OK, 0 rows
affected (0.00 sec)
mysql>
DELIMITER ;
mysql> CALL
getSks('Steve Wozniak');
+---------------------+
|
SUM(matakuliah.sks) |
+---------------------+
| 6 |
+---------------------+
1 row in set
(0.00 sec)
Query OK, 0 rows
affected (0.01 sec)
Tidak ada komentar:
Posting Komentar