KUIS PRA UTS

Nama  : Mohamad Habib

NPM    : 19.1.03.02.0126

Kelas  : 2C Teknik Informatika

KELOMPOK 3

SCRIPT SQL UTS BASIS DATA

Microsoft Windows [Version 10.0.14393]

(c) 2016 Microsoft Corporation. All rights reserved.

C:\Users\toshiba>cd..

C:\Users>cd..

C:\>cd\xampp\mysql\bin

C:\xampp\mysql\bin>mysql -u root -p

Enter password:

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

Your MariaDB connection id is 2

Server version: 10.1.36-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> create database bp_utsbasdat;

Query OK, 1 row affected (0.04 sec)

MariaDB [(none)]> use bp_utsbasdat;

Database changed

MariaDB [bp_utsbasdat]> create table Mahasiswa (NPM int(11) primary key,

    -> no_absen int(4),

    -> nama char(50));

Query OK, 0 rows affected (0.31 sec)

MariaDB [bp_utsbasdat]> create table Matkul(NPM int(11) references Mahasiswa,

    -> kode_matkul int(4),

    -> matkul_1 char(10),

    -> matkul_2 char(10),

    -> matkul_3 char(10),

    -> primary key(NPM,kode_matkul),

    -> foreign key fka_Matkul(NPM) references Mahasiswa (NPM) ON DELETE CASCADE ON UPDATE CASCADE)

    -> ENGINE=InnoDB;

Query OK, 0 rows affected (0.40 sec)

MariaDB [bp_utsbasdat]> show tables;

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

| Tables_in_bp_utsbasdat |

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

| mahasiswa              |

| matkul                 |

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

2 rows in set (0.07 sec)

MariaDB [bp_utsbasdat]> DESC Matkul;

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

| Field       | Type     | Null | Key | Default | Extra |

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

| NPM         | int(11)  | NO   | PRI | NULL    |       |

| kode_matkul | int(4)   | NO   | PRI | NULL    |       |

| matkul_1    | char(10) | YES  |     | NULL    |       |

| matkul_2    | char(10) | YES  |     | NULL    |       |

| matkul_3    | char(10) | YES  |     | NULL    |       |

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

5 rows in set (0.07 sec)

MariaDB [bp_utsbasdat]> DESC mahasiswa;

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

| Field    | Type     | Null | Key | Default | Extra |

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

| NPM      | int(11)  | NO   | PRI | NULL    |       |

| no_absen | int(4)   | YES  |     | NULL    |       |

| nama     | char(50) | YES  |     | NULL    |       |

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

3 rows in set (0.03 sec)

MariaDB [bp_utsbasdat]> INSERT INTO mahasiswa VALUES ('0101','20','M Agung');

Query OK, 1 row affected (0.13 sec)

MariaDB [bp_utsbasdat]> select * from mahasiswa;

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

| NPM | no_absen | nama    |

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

| 101 |       20 | M Agung |

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

1 row in set (0.00 sec)

MariaDB [bp_utsbasdat]> DESC Matkul;

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

| Field       | Type     | Null | Key | Default | Extra |

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

| NPM         | int(11)  | NO   | PRI | NULL    |       |

| kode_matkul | int(4)   | NO   | PRI | NULL    |       |

| matkul_1    | char(10) | YES  |     | NULL    |       |

| matkul_2    | char(10) | YES  |     | NULL    |       |

| matkul_3    | char(10) | YES  |     | NULL    |       |

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

5 rows in set (0.03 sec)

MariaDB [bp_utsbasdat]> INSERT INTO Matkul VALUES ('0101','9','basdat','pbo','imk');

Query OK, 1 row affected (0.07 sec)

MariaDB [bp_utsbasdat]> select * from Matkul;

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

| NPM | kode_matkul | matkul_1 | matkul_2 | matkul_3 |

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

| 101 |           9 | basdat   | pbo      | imk      |

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

1 row in set (0.00 sec)

MariaDB [bp_utsbasdat]> INSERT INTO mahasiswa VALUES ('0126','22','M Habib');

Query OK, 1 row affected (0.07 sec)

MariaDB [bp_utsbasdat]> INSERT INTO mahasiswa VALUES ('0118','23','M Toha');

Query OK, 1 row affected (0.07 sec)

MariaDB [bp_utsbasdat]> INSERT INTO mahasiswa VALUES ('0097','21','bima bagus');

Query OK, 1 row affected (0.06 sec)

MariaDB [bp_utsbasdat]> INSERT INTO Matkul VALUES ('0126','10','basdat','pbo','imk');

Query OK, 1 row affected (0.07 sec)

MariaDB [bp_utsbasdat]> INSERT INTO Matkul VALUES ('0118','11','basdat','pbo','imk');

Query OK, 1 row affected (0.06 sec)

MariaDB [bp_utsbasdat]> INSERT INTO Matkul VALUES ('0097','12','basdat','pbo','imk');

Query OK, 1 row affected (0.07 sec)

MariaDB [bp_utsbasdat]> DESC mahasiswa;

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

| Field    | Type     | Null | Key | Default | Extra |

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

| NPM      | int(11)  | NO   | PRI | NULL    |       |

| no_absen | int(4)   | YES  |     | NULL    |       |

| nama     | char(50) | YES  |     | NULL    |       |

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

3 rows in set (0.10 sec)

MariaDB [bp_utsbasdat]> select * from mahasiswa;

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

| NPM | no_absen | nama       |

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

|  97 |       21 | bima bagus |

| 101 |       20 | M Agung    |

| 118 |       23 | M Toha     |

| 126 |       22 | M Habib    |

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

4 rows in set (0.00 sec)

MariaDB [bp_utsbasdat]> select * from Matkul;

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

| NPM | kode_matkul | matkul_1 | matkul_2 | matkul_3 |

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

|  97 |          12 | basdat   | pbo      | imk      |

| 101 |           9 | basdat   | pbo      | imk      |

| 118 |          11 | basdat   | pbo      | imk      |

| 126 |          10 | basdat   | pbo      | imk      |

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

4 rows in set (0.00 sec)

MariaDB [bp_utsbasdat]>