Tugas Basis Data
Nama : Raqwel Krisdiva
Kelas : XI PPLG A
1. Relasi One to One
MariaDB [Raqwel]> CREATE TABLE USER (
-> user_id INT PRIMARY KEY,
-> username VARCHAR(50),
-> email VARCHAR(100)
-> );
Query OK, 0 rows affected (0.031 sec)
MariaDB [Raqwel]> CREATE TABLE USERPROFILE (
-> profile_id INT PRIMARY KEY,
-> user_id INT UNIQUE,
-> nama VARCHAR(50),
-> alamat VARCHAR(255),
-> FOREIGN KEY (user_id) REFERENCES USER(user_id)
-> );
Query OK, 0 rows affected (0.036 sec)
MariaDB [Raqwel]> INSERT INTO USER (user_id, username, email) VALUES
-> (1, 'user1','user1@example.com'),
-> (2, 'user2','user2@example.com'),
-> (3, 'user3','user3@example.com'),
-> (4, 'user4','user4@example.com'),
-> (5, 'user5','user5@example.com');
Query OK, 5 rows affected (0.006 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [Raqwel]> INSERT INTO USERPROFILE (profile_id, user_id, nama,
alamat) VALUES
-> (1, 1, 'Raqwel', 'Palu'),
-> (2, 2, 'Dika', 'Palu'),
-> (3, 3, 'Fatir', 'Palu'),
-> (4, 4, 'Yusfi', 'Palu'),
-> (5, 5, 'Anca', 'Palu');
Query OK, 5 rows affected (0.006 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [Raqwel]> SELECT USER.user_id, USER.username, USER.email,
USERPROFILE.nama, USERPROFILE.alamat
-> FROM USER
-> INNER JOIN USERPROFILE ON USER.user_id = USERPROFILE.user_id;
+---------+----------+------------------+-----------+--------+
| user_id | username | email | nama | alamat |
+---------+----------+------------------+-----------+--------+
| 1 | user1 | user1@example.com | Raqwel | Palu |
| 2 | user2 | user2@example.com | Dika | Palu |
| 3 | user3 | user3@example.com | Fatir | Palu |
| 4 | user4 | user4@example.com | Yusfi | Palu |
| 5 | user5 | user5@example.com | Anca | Palu |
+---------+----------+------------------+-----------+--------+
5 rows in set (0.002 sec)
2.Relasi One to Many
MariaDB [Raqwel]> CREATE TABLE PEKERJAAN (
-> pekerjaan_id INT PRIMARY KEY,
-> pekerjaan_name VARCHAR(100)
-> );
Query OK, 0 rows affected (0.030 sec)
MariaDB [Raqwel]> CREATE TABLE KARYAWAN (
-> karyawan_id INT PRIMARY KEY,
-> pekerjaan_id INT,
-> karyawan_name VARCHAR (100),
-> FOREIGN KEY (pekerjaan_id) REFERENCES PEKERJAAN(pekerjaan_id)
-> );
Query OK, 0 rows affected (0.033 sec)
MariaDB [Raqwel]> INSERT INTO PEKERJAAN(pekerjaan_id, pekerjaan_name)
VALUES
-> (1, 'PROGRAMMER’),
-> (2, 'MARKETING'),
-> (3, 'FINANCE'),
-> (4, 'MANAGER'),
-> (5, 'IT');
Query OK, 5 rows affected (0.005 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [Raqwel]> INSERT INTO KARYAWAN (karyawan_id, pekerjaan_id,
karyawan_name) VALUES
-> (1, 1, 'Raqwel'),
-> (2, 2, 'Anca'),
-> (3, 3, 'Radif’),
-> (4, 4, 'Yusfi'),
-> (5, 5, 'Fatir');
Query OK, 5 rows affected (0.006 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [Raqwel]> SELECT KARYAWAN.karyawan_id,
KARYAWAN.karyawan_name, PEKERJAAN.pekerjaan_name
-> FROM KARYAWAN
-> INNER JOIN PEKERJAAN ON KARYAWAN.pekerjaan_id =
PEKERJAAN.pekerjaan_id;
+-------------+---------------+-----------------+
| karyawan_id | karyawan_name | pekerjaan_name |
+-------------+---------------+-----------------+
| 1 | Raqwel | PROGRAMMER |
| 2 | Anca | MARKETING |
| 3 | Radif | FINANCE |
| 4 | Yusfi | MANAGER |
| 5 | Fatir | IT |
+-------------+---------------+-----------------+
5 rows in set (0.001 sec)
3.Relasi Many to Many
MariaDB [Raqwel]> CREATE TABLE PELAJAR (
-> pelajar_id INT PRIMARY KEY,
-> pelajar_name VARCHAR(100)
-> );
Query OK, 0 rows affected (0.034 sec)
MariaDB [Raqwel]> CREATE TABLE MAPEL (
-> mapel_id INT PRIMARY KEY,
-> mapel_name VARCHAR (100)
-> );
Query OK, 0 rows affected (0.042 sec)
MariaDB [Raqwel]> CREATE TABLE NILAI (
-> nilai_id INT PRIMARY KEY,
-> pelajar_id INT,
-> mapel_id INT,
-> FOREIGN KEY (pelajar_id) REFERENCES PELAJAR(pelajar_id),
-> FOREIGN KEY (mapel_id) REFERENCES MAPEL(mapel_id)
->
-> );
Query OK, 0 rows affected (0.040 sec)
MariaDB [Raqwel]> INSERT INTO PELAJAR (pelajar_id, pelajar_name) VALUES
-> (1, 'Raqwel’),
-> (2, 'Anca'),
-> (3, 'Yusfi'),
-> (4, 'Radif'),
-> (5, 'Fatir');
Query OK, 5 rows affected (0.005 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [Raqwel]> INSERT INTO MAPEL (mapel_id, mapel_name) VALUES
-> (1, 'PKK'),
-> (2, 'BINDO'),
-> (3, 'BASIS DATA'),
-> (4, 'SEJARAH'),
-> (5, 'BING');
Query OK, 5 rows affected (0.006 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [Raqwel]> INSERT INTO NILAI (nilai_id, pelajar_id, mapel_id) VALUES
-> (4, 1, 1),
-> (5, 2, 2),
-> (6, 3, 3),
-> (7, 4, 4),
-> (8, 5, 5);
Query OK, 5 rows affected (0.005 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [Raqwel]> SELECT PELAJAR.pelajar_id, PELAJAR.pelajar_name,
MAPEL.mapel_name
-> FROM PELAJAR
-> INNER JOIN NILAI ON PELAJAR.pelajar_id =NILAI.pelajar_id
-> INNER JOIN MAPEL ON NILAI.mapel_id = MAPEL.mapel_id;
+------------+--------------+----------------------+
| pelajar_id | pelajar_name | mapel_name |
+------------+--------------+----------------------+
| 1 | Raqwel | PKK |
| 2 | Anca | BASIS DATA |
| 3 | Yusfi | BINDO |
| 4 | Radif | SEJARAH |
| 5 | Fatir | BING |
+------------+--------------+----------------------+
5 rows in set (0.000 sec)Tugas Basis Data
Nama : Moh Rizki Ananda
Kelas : XI PPLG A
1. Relasi One to One
MariaDB [rizki]> CREATE TABLE USER (
-> user_id INT PRIMARY KEY,
-> username VARCHAR(50),
-> email VARCHAR(100)
-> );
Query OK, 0 rows affected (0.031 sec)
MariaDB [rizki]> CREATE TABLE USERPROFILE (
-> profile_id INT PRIMARY KEY,
-> user_id INT UNIQUE,
-> nama VARCHAR(50),
-> alamat VARCHAR(255),
-> FOREIGN KEY (user_id) REFERENCES USER(user_id)
-> );
Query OK, 0 rows affected (0.036 sec)
MariaDB [rizki]> INSERT INTO USER (user_id, username, email) VALUES
-> (1, 'user1','user1@example.com'),
-> (2, 'user2','user2@example.com'),
-> (3, 'user3','user3@example.com'),
-> (4, 'user4','user4@example.com'),
-> (5, 'user5','user5@example.com');
Query OK, 5 rows affected (0.006 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [rizki]> INSERT INTO USERPROFILE (profile_id, user_id, nama, alamat)
VALUES
-> (1, 1, 'Rizki', 'Palu'),
-> (2, 2, 'Dika', 'Palu'),
-> (3, 3, 'Fatir', 'Palu'),
-> (4, 4, 'Yusfi', 'Palu'),
-> (5, 5, 'Anca', 'Palu');
Query OK, 5 rows affected (0.006 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [rizki]> SELECT USER.user_id, USER.username, USER.email,
USERPROFILE.nama, USERPROFILE.alamat
-> FROM USER
-> INNER JOIN USERPROFILE ON USER.user_id = USERPROFILE.user_id;
+---------+----------+------------------+-----------+--------+
| user_id | username | email | nama | alamat |
+---------+----------+------------------+-----------+--------+
| 1 | user1 | user1@example.com | Rizki | Palu |
| 2 | user2 | user2@example.com | Dika | Palu |
| 3 | user3 | user3@example.com | Fatir | Palu |
| 4 | user4 | user4@example.com | Yusfi | Palu |
| 5 | user5 | user5@example.com | Anca | Palu |
+---------+----------+------------------+-----------+--------+
5 rows in set (0.002 sec)
2.Relasi One to Many
MariaDB [rizki]> CREATE TABLE PEKERJAAN (
-> pekerjaan_id INT PRIMARY KEY,
-> pekerjaan_name VARCHAR(100)
-> );
Query OK, 0 rows affected (0.030 sec)
MariaDB [rizki]> CREATE TABLE KARYAWAN (
-> karyawan_id INT PRIMARY KEY,
-> pekerjaan_id INT,
-> karyawan_name VARCHAR (100),
-> FOREIGN KEY (pekerjaan_id) REFERENCES PEKERJAAN(pekerjaan_id)
-> );
Query OK, 0 rows affected (0.033 sec)
MariaDB [rizki]> INSERT INTO PEKERJAAN(pekerjaan_id, pekerjaan_name)
VALUES
-> (1, 'PROGRAMMER’),
-> (2, 'MARKETING'),
-> (3, 'FINANCE'),
-> (4, 'MANAGER'),
-> (5, 'IT');
Query OK, 5 rows affected (0.005 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [rizki]> INSERT INTO KARYAWAN (karyawan_id, pekerjaan_id,
karyawan_name) VALUES
-> (1, 1, 'Rizki'),
-> (2, 2, 'Anca'),
-> (3, 3, 'Radif’),
-> (4, 4, 'Yusfi'),
-> (5, 5, 'Fatir');
Query OK, 5 rows affected (0.006 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [rizki]> SELECT KARYAWAN.karyawan_id, KARYAWAN.karyawan_name,
PEKERJAAN.pekerjaan_name
-> FROM KARYAWAN
-> INNER JOIN PEKERJAAN ON KARYAWAN.pekerjaan_id =
PEKERJAAN.pekerjaan_id;
+-------------+---------------+-----------------+
| karyawan_id | karyawan_name | pekerjaan_name |
+-------------+---------------+-----------------+
| 1 | Rizki | PROGRAMMER |
| 2 | Anca | MARKETING |
| 3 | Radif | FINANCE |
| 4 | Yusfi | MANAGER |
| 5 | Fatir | IT |
+-------------+---------------+-----------------+
5 rows in set (0.001 sec)
3.Relasi Many to Many
MariaDB [rizki]> CREATE TABLE PELAJAR (
-> pelajar_id INT PRIMARY KEY,
-> pelajar_name VARCHAR(100)
-> );
Query OK, 0 rows affected (0.034 sec)
MariaDB [rizki]> CREATE TABLE MAPEL (
-> mapel_id INT PRIMARY KEY,
-> mapel_name VARCHAR (100)
-> );
Query OK, 0 rows affected (0.042 sec)
MariaDB [rizki]> CREATE TABLE NILAI (
-> nilai_id INT PRIMARY KEY,
-> pelajar_id INT,
-> mapel_id INT,
-> FOREIGN KEY (pelajar_id) REFERENCES PELAJAR(pelajar_id),
-> FOREIGN KEY (mapel_id) REFERENCES MAPEL(mapel_id)
->
-> );
Query OK, 0 rows affected (0.040 sec)
MariaDB [rizki]> INSERT INTO PELAJAR (pelajar_id, pelajar_name) VALUES
-> (1, 'Rizki’),
-> (2, 'Anca'),
-> (3, 'Yusfi'),
-> (4, 'Radif'),
-> (5, 'Fatir');
Query OK, 5 rows affected (0.005 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [rizki]> INSERT INTO MAPEL (mapel_id, mapel_name) VALUES
-> (1, 'PKK'),
-> (2, 'BINDO'),
-> (3, 'BASIS DATA'),
-> (4, 'SEJARAH'),
-> (5, 'BING');
Query OK, 5 rows affected (0.006 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [rizki]> INSERT INTO NILAI (nilai_id, pelajar_id, mapel_id) VALUES
-> (4, 1, 1),
-> (5, 2, 2),
-> (6, 3, 3),
-> (7, 4, 4),
-> (8, 5, 5);
Query OK, 5 rows affected (0.005 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [rizki]> SELECT PELAJAR.pelajar_id, PELAJAR.pelajar_name,
MAPEL.mapel_name
-> FROM PELAJAR
-> INNER JOIN NILAI ON PELAJAR.pelajar_id =NILAI.pelajar_id
-> INNER JOIN MAPEL ON NILAI.mapel_id = MAPEL.mapel_id;
+------------+--------------+----------------------+
| pelajar_id | pelajar_name | mapel_name |
+------------+--------------+----------------------+
| 1 | Rizki | PKK |
| 2 | Anca | BASIS DATA |
| 3 | Yusfi | BINDO |
| 4 | Radif | SEJARAH |
| 5 | Fatir | BING |
+------------+--------------+----------------------+
5 rows in set (0.000 sec)
