LAPORAN PROJECT MANDIRI SISTEM BASIS DATA

Disusun Oleh:
Gideon Adventus Simanungkalit
NPM : 220840144

Dosen Pengampu:
MASDIANA SAGALA

PROGRAM STUDI TEKNIK INFORMATIKA

FAKULTAS ILMU KOMPUTER
UNIVERSITAS KATOLIK SANTO THOMAS MEDAN
2024
 Login ke MySQL
Microsoft Windows [Version 10.0.22621.3007]
(c) Microsoft Corporation. All rights reserved.

C:\Users\LENOVO>cd\
C:\>cd xampp
C:\xampp>cd mysq
C:\xampp\mysql>cd bin
C:\xampp\mysql\bin>mysql -u root

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

Your MariaDB connection id is 8
Server version: 10.4.28-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.

Pembuatan Database Baru

MariaDB [(none)]> create database penjualan_sepatu;

Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> use penjualan_sepatu;

Database changed

Pembuatan Table

MariaDB [penjualan_sepatu]> CREATE TABLE Pelanggan (

-> id_pelanggan INT PRIMARY KEY,
-> nama_pelanggan VARCHAR(50),
-> alamat_pelanggan VARCHAR(100),
 -> telepon_pelanggan VARCHAR(15)
-> );
Query OK, 0 rows affected (0.015 sec)

MariaDB [penjualan_sepatu]> CREATE TABLE Produk (

-> id_produk INT PRIMARY KEY,
-> nama_produk VARCHAR(50),
-> harga_produk DECIMAL(10, 2),
-> stok_produk INT
-> );
Query OK, 0 rows affected (0.027 sec)

MariaDB [penjualan_sepatu]> CREATE TABLE Penjualan (

-> id_penjualan INT PRIMARY KEY,
-> tanggal_penjualan DATE,
-> id_pelanggan INT,
-> FOREIGN KEY (id_pelanggan) REFERENCES Pelanggan(id_pelanggan)
-> );
Query OK, 0 rows affected (0.084 sec)

MariaDB [penjualan_sepatu]> CREATE TABLE Owner (

-> id_owner INT PRIMARY KEY,
-> nama_owner VARCHAR(50),
-> alamat_owner VARCHAR(100),
-> telepon_owner VARCHAR(15)
-> );
Query OK, 0 rows affected (0.026 sec)

MariaDB [penjualan_sepatu]> CREATE TABLE DetailPenjualan (

-> id_detail_penjualan INT PRIMARY KEY,
-> id_penjualan INT,
-> id_produk INT,
-> id_owner INT,
-> jumlah_produk INT,
-> harga_produk DECIMAL(10, 2),
-> FOREIGN KEY (id_penjualan) REFERENCES Penjualan(id_penjualan),
-> FOREIGN KEY (id_produk) REFERENCES Produk(id_produk),
 -> FOREIGN KEY (id_owner) REFERENCES Owner(id_owner)
-> );
Query OK, 0 rows affected (0.125 sec)

SHOW TABLES

Pengisian Data Kedalam Table

MariaDB [penjualan_sepatu]>MariaDB [penjualan_sepatu]> insert into owner

value('11.111','Gideon Simanungkalit','Pematang Siantar','+628220774');
Query OK, 1 row affected (0.031 sec)
MariaDB [penjualan_sepatu]> insert into owner
value('12.112','Yohanes','Medan','822432');
Query OK, 1 row affected (0.012 sec)

insert into pelanggan values('111','Abraham','Pancur Batu','+621234');

Query OK, 1 row affected (0.013 sec)

MariaDB [penjualan_sepatu]> insert into pelanggan

values('112','Juberto','Samosir','+621236');
Query OK, 1 row affected (0.013 sec)

MariaDB [penjualan_sepatu]> insert into pelanggan

values('113','Johanes','Medan','+6212765');
Query OK, 1 row affected (0.013 sec)
MariaDB [penjualan_sepatu]> insert into pelanggan
values('114','Rafael','Medan','+62127768');
Query OK, 1 row affected (0.013 sec)

MariaDB [penjualan_sepatu]> insert into pelanggan

values('115','Gindo','Samosir','+62127768');
Query OK, 1 row affected (0.013 sec)

MariaDB [penjualan_sepatu]> insert into pelanggan

values('116','Nofiria','Simalingkar','+6216544');
Query OK, 1 row affected (0.014 sec)

MariaDB [penjualan_sepatu]> insert into pelanggan

values('117','Rafita','Siantar','+62169544');
Query OK, 1 row affected (0.017 sec)

MariaDB [penjualan_sepatu]> insert into pelanggan

values('118','Daniel','Medan','+6216745');
Query OK, 1 row affected (0.014 sec)

MariaDB [penjualan_sepatu]> insert into pelanggan

values('119','Armenda','Delitua','+6216653');
Query OK, 1 row affected (0.015 sec)

MariaDB [penjualan_sepatu]> insert into pelanggan values('120','Astra','Kaban

jahe','+6216764');
Query OK, 1 row affected (0.023 sec)

MariaDB [penjualan_sepatu]> insert into produk value('511','Sepatu Running

A','70000','80');
Query OK, 1 row affected (0.013 sec)

MariaDB [penjualan_sepatu]> insert into produk value('512','Sepatu Running

B','75000','85');
Query OK, 1 row affected (0.013 sec)
MariaDB [penjualan_sepatu]> insert into produk value('513','Sandal Casual
A','30000','60');
Query OK, 1 row affected (0.076 sec)

MariaDB [penjualan_sepatu]> insert into produk value('514','Sandal Casual

B','30000','50');
Query OK, 1 row affected (0.013 sec)

MariaDB [penjualan_sepatu]> insert into produk value('515','Sepatu Jordan

A','700000','30');
Query OK, 1 row affected (0.013 sec)

MariaDB [penjualan_sepatu]> insert into produk value('516','Sepatu Jordan

B','700000','30');
Query OK, 1 row affected (0.013 sec)

MariaDB [penjualan_sepatu]> insert into produk value('517','Sepatu Sneakers

A','130000','70');
Query OK, 1 row affected (0.024 sec)

MariaDB [penjualan_sepatu]> insert into produk value('518','Sepatu Sneakers

B','135000','70');
Query OK, 1 row affected (0.005 sec)

MariaDB [penjualan_sepatu]> insert into produk value('519','High

Heels','120000','65');
Query OK, 1 row affected (0.013 sec)

MariaDB [penjualan_sepatu]> insert into produk value('520','Boots

A','100000','60');
Query OK, 1 row affected (0.013 sec)

MariaDB [penjualan_sepatu]> insert into produk value('521','Boots

B','110000','65');
Query OK, 1 row affected (0.015 sec)
MariaDB [penjualan_sepatu]> insert into produk value('522','Sports New
A','300000','50');
Query OK, 1 row affected (0.013 sec)

MariaDB [penjualan_sepatu]> insert into produk value('523','Sports New

B','350000','50');
Query OK, 1 row affected (0.012 sec)

MariaDB [penjualan_sepatu]> insert into produk value('524','Sports New

C','320000','50');
Query OK, 1 row affected (0.013 sec)

MariaDB [penjualan_sepatu]> insert into produk value('525','All Stars

','300000','20');
Query OK, 1 row affected (0.021 sec)

15 rows in set (0.001 sec) insert into penjualan value('411','2024/01/26','111');

Query OK, 1 row affected (0.013 sec)

MariaDB [penjualan_sepatu]> insert into penjualan value('412','2024/01/26','112');

Query OK, 1 row affected (0.073 sec)

MariaDB [penjualan_sepatu]> insert into penjualan value('413','2024/01/25','113');

Query OK, 1 row affected (0.013 sec)

MariaDB [penjualan_sepatu]> insert into penjualan value('414','2024/01/25','114');

Query OK, 1 row affected (0.013 sec)

MariaDB [penjualan_sepatu]> insert into penjualan value('415','2024/01/20','115');

Query OK, 1 row affected (0.014 sec)

MariaDB [penjualan_sepatu]> insert into penjualan value('416','2024/01/20','116');

Query OK, 1 row affected (0.013 sec)

MariaDB [penjualan_sepatu]> insert into penjualan value('417','2024/01/21','117');

Query OK, 1 row affected (0.012 sec)
MariaDB [penjualan_sepatu]> insert into penjualan value('418','2024/01/21','118');
Query OK, 1 row affected (0.013 sec)

MariaDB [penjualan_sepatu]> insert into penjualan value('419','2024/01/22','119');

Query OK, 1 row affected (0.003 sec)

MariaDB [penjualan_sepatu]> insert into penjualan value('420','2024/01/22','120');

Query OK, 1 row affected (0.013 sec)

MENAMPILKAN ISI TABLE

 Select From Where

10 rows in set (0.002 sec)se near 'from where alamat_pelanggan ='Medan'' at line 1
MariaDB [penjualan_sepatu]> select *from pelanggan where alamat_pelanggan
='Medan';

MariaDB [penjualan_sepatu]> select sum(harga_produk) as Total_harga from

produk;

MariaDB [penjualan_sepatu]> select max(harga_produk) as Harga_Tertinggi from

produk; MariaDB [penjualan_sepatu]> select max(harga_produk) as
Harga_Tertinggi from produk;

MariaDB [penjualan_sepatu]> select min(harga_produk) as Harga_Terendah from

produk;

MariaDB [penjualan_sepatu]> select avg(harga_produk) as Jumlah_Rata from

produk;

MariaDB [penjualan_sepatu]> select id_produk,sum(harga_produk) as

Jumlah_Harga from produk group by id_produk;
INNER JOIN
 UNION

SELECT CASE
CREATE VIEW
Pertemuan Kesepuluh
Latihan Join
MariaDB [latihan_join]> create table mahasiswa(NIM char(14) primary key not null, Nama
char(25) not null, Alamat char(30), Sex set('P','W'), Agama
enum('Islam','Katolik','Kristen','Hindu','Buddha'), Tgl_Lahir date);
Query OK, 0 rows affected (0.010 sec)
MariaDB [latihan_join]> create table Mata_Kuliah(Kode_Kuliah char(7) primary key not null,
Nama_Kuliah char(20) not null, SKS decimal(2) not null, Smt_Tawar char(1), Klp_Kul
enum('MPK','MKK','MKB'), Progdi enum('MI-03','SI-S1'));
Query OK, 0 rows affected (0.017 sec)
MariaDB [latihan_join]> create table KRS(Kode_Kuliah char(7) not null, NIM char(14) not null,
Nilai char(1), CONSTRAINT FK_Kuliah FOREIGN KEY (Kode_Kuliah) REFERENCES
Mata_Kuliah (Kode_Kuliah), CONSTRAINT FK_NIM FOREIGN KEY (NIM) REFERENCES
Mahasiswa (NIM));
Query OK, 0 rows affected (0.016 sec)
MariaDB [latihan_join]> insert into mahasiswa values('A21.2001.00234','Dedy
Sutanta','Jl.A.Yani 20 Solo','P','1','1979-10-20');
Query OK, 1 row affected (0.004 sec)
MariaDB [latihan_join]> insert into mahasiswa values('A21.2001.00214','Budi Satria','Jl.Dago
10 Yogya','P','1','1978-11-22');
Query OK, 1 row affected (0.004 sec)
MariaDB [latihan_join]> insert into mahasiswa values('A21.2001.00224','Sulis','Jl.Nakula I 25
smg','W','2','1975-08-16');
Query OK, 1 row affected (0.002 sec)

MariaDB [latihan_join]> insert into mata_kuliah values('A21-101','Pendidikan

Pancasila','2','1','1','1');
Query OK, 1 row affected (0.004 sec)
MariaDB [latihan_join]> insert into mata_kuliah values('A21-102','Logika
Algoritma','4','1','2','1');
Query OK, 1 row affected (0.002 sec)
MariaDB [latihan_join]> insert into mata_kuliah values('A21-103','Pemrograman
(Basic)','2','1','2','1');
Query OK, 1 row affected (0.003 sec)
MariaDB [latihan_join]> insert into mata_kuliah values('A21-201','Sistem Basis Data
I','2','2','2','1');
Query OK, 1 row affected (0.003 sec)
MariaDB [latihan_join]> insert into mata_kuliah values('A21-202','Pemrograman
Pascal','4','2','2','1');
Query OK, 1 row affected (0.003 sec)
MariaDB [latihan_join]> insert into mata_kuliah values('A21-203','Ilmu Sosial
Dasar','2','2','1','1');
Query OK, 1 row affected (0.003 sec)
MariaDB [latihan_join]> insert into mata_kuliah values('A21-501','Bimbingan
Karier','2','5','3','1');
Query OK, 1 row affected (0.003 sec)
MariaDB [latihan_join]> insert into mata_kuliah values('A21-601','Proyek Akhir','4','6','3','1');
Query OK, 1 row affected (0.003 sec)

MariaDB [latihan_join]> insert into krs values('A21-101','A21.2001.00234','B');

Query OK, 1 row affected (0.003 sec)
MariaDB [latihan_join]> insert into krs values('A21-201','A21.2001.00214','A');
Query OK, 1 row affected (0.003 sec)
MariaDB [latihan_join]> insert into krs values('A21-201','A21.2001.00234','C');
Query OK, 1 row affected (0.003 sec)
MariaDB [latihan_join]> insert into krs values('A21-601','A21.2001.00214','B');
Query OK, 1 row affected (0.003 sec)
MariaDB [latihan_join]> insert into krs values('A21-501','A21.2001.00214','A');
Query OK, 1 row affected (0.002 sec)
MariaDB [latihan_join]> insert into krs values('A21-501','A21.2001.00234','A');
Query OK, 1 row affected (0.004 sec)
MariaDB [latihan_join]> insert into krs values('A21-101','A21.2001.00224','B');
Query OK, 1 row affected (0.004 sec)
MariaDB [latihan_join]> select mahasiswa.nama,mata_kuliah.nama_kuliah,krs.nilai from
mahasiswa inner join krs on mahasiswa.nim=krs.nim inner join mata_kuliah on
mata_kuliah.kode_kuliah=krs.kode_kuliah;

select mhs.Nama from mhs inner join krs on mhs.Nim=krs.Nim inner join mkuliah on
mkuliah.kode_kul=krs.kode_kul where Nama_kul='Bimbingan Carrier';

select mhs.Nama from mhs inner join krs on mhs.Nim=krs.Nim inner join mkuliah on
mkuliah.kode_kul=krs.kode_kul where nilai='A';

MariaDB [latihanjoin]> select mhs.Nama from mhs inner join krs on mhs.Nim=krs.Nim inner
join mkuliah on mkuliah.kode_kul=krs.kode_kul where Nama_kul='Proyek Akhir';
MariaDB [latihanjoin]> select mhs.Nama,sum(mkuliah.sks) as Jumlah_SKS_Kumulatif from mhs inner join
krs on mhs.Nim=krs.Nim inner join mkuliah on mkuliah.kode_kul=krs.kode_kul where
mhs.Nim='A21.2001.00214';

MariaDB [latihanjoin]> select mhs.Nama,count(mkuliah.Nama_kul) as Jumlah_Mata_Kuliah from mhs

inner join krs on mhs.Nim=krs.Nim inner join mkuliah on mkuliah.kode_kul=krs.kode_kul where
mhs.Nim='A21.2001.00214';

MariaDB [latihanjoin]> select mhs.Nama from mhs inner join krs on mhs.Nim=krs.Nim inner join mkuliah
on mkuliah.kode_kul=krs.kode_kul where Nama_kul='Proyek Akhir' and nilai='A';

select Nama as 'Nama Mahasiswa' FROM mhs JOIN krs on mhs.Nim = krs.Nim JOIN mkuliah ON
krs.kode_kul = mkuliah.Kode_kul WHERE Nama_kul = 'Bimbingan Carrier' UNION SELECT Nama AS
'Nama Mahasiswa' FROM mhs JOIN krs ON mhs.Nim = krs.Nim JOIN mkuliah ON krs.kode_kul =
mkuliah.Kode_kul WHERE Nama_kul = 'Proyek Akhir';

select mhs.nama as Nama_Mahasiswa,mkuliah.nama_kul as Mata_Kuliah from mhs inner join krs on

mhs.Nim=krs.Nim inner join mkuliah on krs.kode_kul=mkuliah.kode_kul where Nama_kul='Bimbingan
Carrier' EXCEPT select mhs.nama as Nama_Mahasiswa,mkuliah.nama_kul as Mata_Kuliah from mhs
inner join krs on mhs.Nim=krs.Nim inner join mkuliah on krs.kode_kul=mkuliah.kode_kul where
Nama_kul='Proyek Akhir';