💾 Command MySQL Labsheet 4
Lembar latihan ini mencakup perintah pembuatan database, tabel, relasi, hingga query manipulasi data dan laporan hasil.
1. Membuat Database
CREATE DATABASE alif_farma;
Membuat Tabel Obat
CREATE TABLE obat(
kodeobat VARCHAR(6),
namaobat VARCHAR(30),
harga INTEGER,
PRIMARY KEY(kodeobat)
);
Membuat Tabel Jual
CREATE TABLE jual(
kodejual VARCHAR(7),
tgljual DATE,
PRIMARY KEY(kodejual)
);
Membuat Tabel Detailjual
CREATE TABLE detailjual(
kodejual VARCHAR(7),
kodeobat VARCHAR(6),
jmljual INTEGER,
PRIMARY KEY(kodejual,kodeobat),
FOREIGN KEY (kodejual) REFERENCES jual(kodejual)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (kodeobat) REFERENCES obat(kodeobat)
ON DELETE CASCADE ON UPDATE CASCADE
);
2. Insert Data
Tabel Obat
INSERT INTO obat VALUES
('AF0001', 'Asam Mefenamat', 7000),
('AF0002', 'Amoxilin', 6000),
('AF0003', 'Bisolvon', 12000),
('AF0004', 'Betametason', 15000),
('AF0005', 'Combantrin', 17000),
('AF0006', 'Caladine cair', 22000),
('AF0007', 'Paracetamol', 9000),
('AF0008', 'Gentamycyn', 3500),
('AF0009', 'Nystatin', 23500),
('AF0010', 'Rivanol', 7500),
('AF0011', 'Vicks', 12500);
Tabel Jual
INSERT INTO jual VALUES
('PA00001', '2018-08-23'),
('PA00002', '2018-08-25'),
('PA00003', '2018-09-01'),
('PA00004', '2019-01-02'),
('PA00005', '2019-02-27');
Tabel Detailjual
INSERT INTO detailjual VALUES
('PA00001', 'AF0006', 1),
('PA00001', 'AF0008', 3),
('PA00002', 'AF0001', 2),
('PA00002', 'AF0011', 1),
('PA00002', 'AF0009', 2),
('PA00003', 'AF0001', 1),
('PA00004', 'AF0007', 1),
('PA00004', 'AF0003', 2),
('PA00005', 'AF0001', 3),
('PA00005', 'AF0002', 4),
('PA00005', 'AF0009', 1),
('PA00005', 'AF0010', 2),
('PA00005', 'AF0004', 4);
3. Manipulasi Data
Menambah Transaksi Baru
INSERT INTO jual VALUES('PA00006', '2019-06-09');
INSERT INTO detailjual VALUES('PA00006', 'AF0005',5);
INSERT INTO detailjual VALUES('PA00006', 'AF0008',3);
Menghapus Data Referensi
DELETE FROM obat WHERE kodeobat='AF0011';
Update Data Relasi
UPDATE detailjual SET kodejual='PA00005' WHERE kodejual='PA00006';
4. Menampilkan Data
a) Kodejual, Namaobat, Jumlah Jual
SELECT dj.kodejual, b.namaobat, dj.jmljual
FROM detailjual dj
JOIN obat b ON dj.kodeobat = b.kodeobat;
b) Laporan Penjualan Lengkap
SELECT
dj.kodejual,
j.tgljual,
b.namaobat,
b.harga AS hrgjual,
dj.jmljual,
(b.harga * dj.jmljual) AS total
FROM detailjual dj
JOIN jual j ON dj.kodejual = j.kodejual
JOIN obat b ON dj.kodeobat = b.kodeobat;
c) Total Penjualan per Tanggal
SELECT
j.tgljual,
SUM(b.harga * dj.jmljual) AS total
FROM detailjual dj
JOIN jual j ON dj.kodejual = j.kodejual
JOIN obat b ON dj.kodeobat = b.kodeobat
GROUP BY j.tgljual;
5. Membuat Tabel & Relasi Baru
Tabel Suplier
CREATE TABLE suplier (
kodesup VARCHAR(6) PRIMARY KEY,
namasup VARCHAR(50)
);
Tabel Beli
CREATE TABLE beli (
kodebeli VARCHAR(7) PRIMARY KEY,
kodesup VARCHAR(6),
tglbeli DATE,
FOREIGN KEY (kodesup) REFERENCES suplier(kodesup)
ON DELETE CASCADE ON UPDATE CASCADE
);
Tabel Detailbeli
CREATE TABLE detailbeli (
kodebeli VARCHAR(7),
kodeobat VARCHAR(6),
jmlbeli INT,
PRIMARY KEY (kodebeli, kodeobat),
FOREIGN KEY (kodebeli) REFERENCES beli(kodebeli)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (kodeobat) REFERENCES obat(kodeobat)
ON DELETE CASCADE ON UPDATE CASCADE
);