Sistem Informasi Apotik

Langsung saja pada tugas berikutnya kali ini penulis akan menampilkan Sistem Informasi Apotik setelah sebelumnya resumean Materi SI menyinggung mengenai DBMS (Database Management System)..
Software yang dipersiapkan : Xampp dan SQL yog Enterprise

 

Kita membutuhkan 10 tabel dengan atribut yang mengikutinya :

Sebelumnya :
Membuat database apotik :
CREATE DATABASE apotik

Menghapus database :
DROP DATABASE apotik



Untuk database yang akan kita gunakan :
CREATE DATABASE apotik_aulia


CREATE TABLE golobat (
kdGol CHAR(3) NOT NULL PRIMARY KEY,
golongan VARCHAR (15))

INSERT INTO golobat VALUES
("G01","Dewasa"),
("G02","Anak-anak"),
("G03","Remaja"),
("G04","Semua umur")



CREATE TABLE jabatan (
kdJab CHAR(2) NOT NULL PRIMARY KEY,
jabatan VARCHAR (15))

INSERT INTO jabatan VALUES
("AP","Apoteker"),
("KS","Kasir"),
("OB","Office Boy"),
("OG","Office Girl"),
("PG","Pegawai")

CREATE TABLE JK (
kdJK CHAR(1) NOT NULL PRIMARY KEY,
jenisKelamin VARCHAR (10))

INSERT INTO JK VALUES
("P","Pria"),
("W","Wanita")

CREATE TABLE obat (
kdObat CHAR(5) NOT NULL PRIMARY KEY,
kdGol char(5) NOT NULL,
nmObat VARCHAR (30),
kdStock CHAR(5) NOT NULL,
harga DOUBLE,
Kadaluarsa DATE)

INSERT INTO obat VALUES
("01","G01","Panadol","S001",10000,"2014-02-08"),
("02","G02","Hufagrip","S003",13000,"2013-02-05"),
("03","G01","Neutropin","S003",15000,"2013-04-19"),
("04","G01","Polysilane","S002",14000,"2014-03-06"),
("05","G02","Biogesic","S001",22200,"2014-01-23")


CREATE TABLE pegawai (
idPeg CHAR(5) NOT NULL PRIMARY KEY,
nmPeg VARCHAR(30),
alamat VARCHAR(30),
kdJK CHAR(1),
kdJab CHAR(2))

INSERT INTO pegawai VALUES
("01","Susi","Jalan Melon 45","W","OG"),
("02","Mike","Jalan Palmerah","P","MG"),
("03","Rana","Jalan Bumi","P","PG")

CREATE TABLE konsumen(
idKon CHAR(5) NOT NULL PRIMARY KEY,
nmKon VARCHAR(30),
alamat VARCHAR(30))

INSERT INTO konsumen
VALUES
("K001","Winda","Jl. Kuswari"),
("K002","Sarip","Jl. Merdeka"),
("K003","Santi","Jl. Bebas"),
("K004","Opi","Jl. Onta"),
("K005","Luki","Jl. Bogor Barat")

CREATE TABLE pembelian(
kdPemb CHAR(2) NOT NULL PRIMARY KEY,
idSup CHAR(2) NOT NULL,
idKon CHAR(5) NOT NULL,
kdobat CHAR(5) NOT NULL,
jmlPemb INT)

INSERT INTO pembelian
VALUES
("P1","S1","K003","01",4),
("P2","S3","K001","02",6)



CREATE TABLE penjualan(
kdPenj CHAR(2) NOT NULL PRIMARY KEY,
idPeg CHAR(2) NOT NULL,
kdObat CHAR(5) NOT NULL,
tglJual DATE,
obatTerjual CHAR(4))

INSERT INTO penjualan
VALUES
("J1","5","03","2012/09/08","2"),
("J2","3","02","2013/02/02","4"),
("J3","1","01","2010/09/08","1")

CREATE TABLE stockObat(
kdStock CHAR(5) NOT NULL PRIMARY KEY,
jmlStock CHAR (6))

INSERT INTO stockObat
VALUES
("S001","100"),
("S002","200"),
("S003","300"),
("S004","400")

CREATE TABLE suplier(
idSup CHAR(2) NOT NULL PRIMARY KEY,
nama VARCHAR(10),
alamat VARCHAR(20))

INSERT INTO suplier
VALUES
("S0","Jery","Jl Malang"),
("S1","Mita","Jl Jakarta"),
("S2","Dela","Jl Kelimutu")

ALTER TABLE pegawai
ADD  ket VARCHAR (30)

ALTER TABLE pegawai
DROP COLUMN ket

ALTER TABLE obat CHANGE harga price DOUBLE

UPDATE pegawai SET nmPeg='Aulia' WHERE
idPeg='02'






SELECT nmKon AS Nama_Konsumen FROM
konsumen

 

SELECT * FROM pegawai WHERE kdJk='P'

SELECT nama,alamat FROM suplier WHERE nama
LIKE "J%"

SELECT obat.nmObat,golobat.golongan FROM obat,
golobat
WHERE golobat.kdGol=obat.kdGol

SELECT pegawai.nmPeg,jk.jenisKelamin FROM
pegawai, jk
WHERE
jk.kdJK=pegawai.kdJK


SELECT  obat.nmObat,stockobat.jmlStock FROM
obat,stockobat
WHERE
obat.kdStock=stockobat.kdStock