create database S_Pakar
use S_Pakar
--1
create table Pasien
(
KD_PASIEN varchar (6)not null primary key,
NM_PASIEN varchar (45)not null,
ALAMAT varchar (20),
TELP varchar (13)
)
create table Dokter
(
KD_DOKTER varchar (6) not null primary key,
NM_DOKTER varchar (45) not null,
JABATAN varchar (20),
TELP varchar (13)
)
create table Konsultasi
(
NO_KONSUL varchar (8) not null primary key,
KD_PASIEN varchar (6)not null,
KD_DOKTER varchar (6) not null,
KD_RULE varchar (6) not null,
TGL_CHECK datetime,
BIAYA INT
)
insert into Pasien values ('PSN001','Hendra Divayana', 'Tabanan', '081916622460')
insert into Pasien values ('PSN002','Dessy Sugiharni', 'Gianyar', '081999786892')
insert into Pasien values ('PSN003','Budidana Susila', 'Tabanan', '085367900005')
insert into Pasien values ('PSN004','Ferry Hermawati', 'Denpasar', '087689234671')
insert into Pasien values ('PSN005','Kharisma Jandinhi', 'Denpasar', '081338760928')
SELECT * FROM Pasien
insert into Dokter values ('DKT001','dr.Gede Suyasa','Dokter Umum','081916611122')
insert into Dokter values ('DKT002','dr.Imanuel Rizki, Sp.PD','Spesialis','081999098563')
insert into Dokter values ('DKT003','Prof. Dr. dr.Riska, Sp.PD','Pakar','085361672890')
insert into Dokter values ('DKT004','dr.Surya Witantra Giri','Dokter Umum','087689239864')
insert into Dokter values ('DKT005','dr.Cok Istri Sembari','Dokter Umum','081337658903')
SELECT * FROM Dokter
insert into Konsultasi values ('URT00001','PSN001','DKT003','RL0006','3/2/2011','250000')
insert into Konsultasi values ('URT00002','PSN003','DKT004','RL0004','3/4/2011','300000')
insert into Konsultasi values ('URT00003','PSN001','DKT001','RL0002','3/3/2011','450000')
insert into Konsultasi values ('URT00004','PSN005','DKT002','RL0005' ,'3/6/2011','200000')
insert into Konsultasi values ('URT00005','PSN002','DKT005','RL0001','3/4/2011','250000')
insert into Konsultasi values ('URT00006','PSN004','DKT003','RL0003','3/5/2011','300000')
SELECT * FROM Konsultasi
create table kepala
(kode_kepala varchar (6) not null primary key,
Gejala_kepala varchar (50))
insert into kepala values ('KPL001','senut-senut')
insert into kepala values ('KPL002','pening' )
insert into kepala values ('KPL003','Sakit sebelah')
select * from kepala
create table badan
(kode_badan varchar (6) not null primary key,
Gejala_badan varchar (50))
insert into badan values ('BDN001','meriang')
insert into badan values ('BDN002','demam' )
insert into badan values ('BDN003','pegal-pegal')
create table tangan
(kode_tangan varchar (6) not null primary key,
Gejala_tangan varchar (50))
insert into tangan values ('TGN001','kesemutan')
insert into tangan values ('TGN002','bengkak' )
insert into tangan values ('TGN003','pegal-pegal')
create table kaki
(kode_kaki varchar (6) not null primary key,
Gejala_kaki varchar (50))
insert into kaki values ('KKI001','kesemutan')
insert into kaki values ('KKI002','bengkak' )
insert into kaki values ('KKI003','pegal-pegal')
create table penyakit
(kode_penyakit varchar (6) not null primary key,
Nama_Penyakit varchar (50), obat varchar(20), dosis varchar (3))
insert into Penyakit values ('PYK001','Infectious Coryza','Tripsin','3x1')
insert into Penyakit values ('PYK002','Newcastle Disease','Diksinetan','4x1')
insert into Penyakit values ('PYK003','Salmonella Typhoza','Salnesson','2x1')
insert into Penyakit values ('PYK004','Migrain','merisslon','3x1')
insert into Penyakit values ('PYK005','Bibliorosis Gunectiosin','bibnecton','2x1')
create table tbRule
(kd_rule varchar (6) not null primary key,
kode_kepala varchar (6) not null,
kode_badan varchar (6) not null,
kode_tangan varchar (6) not null,
kode_kaki varchar (6) not null,
kode_penyakit varchar (6) not null )
insert into tbRule values ('RL0001','KPL001','BDN003','TGN003','KKI002','PYK002')
insert into tbRule values ('RL0002','KPL003','BDN002','TGN001','KKI001','PYK001')
insert into tbRule values ('RL0003','KPL002','BDN001','TGN002','KKI001','PYK003')
insert into tbRule values ('RL0004','KPL001','BDN002','TGN003','KKI003','PYK002')
insert into tbRule values ('RL0005','KPL002','BDN002','TGN003','KKI001','PYK005')
insert into tbRule values ('RL0006','KPL003','BDN003','TGN002','KKI002','PYK004')
-- SOAL NO 2--
--2.A
select A.NM_PASIEN FROM Pasien a, Konsultasi b where b.TGL_CHECK ='03/04/2011' and b.KD_PASIEN=a.KD_PASIEN
--2.B
SELECT NM_DOKTER FROM Dokter WHERE JABATAN ='Pakar'
--2.C
SELECT A.Nama_Penyakit FROM PENYAKIT A, TBRULE B,KONSULTASI C WHERE C.NO_KONSUL='URT00005' AND C.KD_RULE =B.kd_rule AND A.kode_penyakit =B.kode_penyakit
--2.D
SELECT A.NM_PASIEN,A.ALAMAT FROM Pasien A,Konsultasi B WHERE B.BIAYA='300000' AND A.KD_PASIEN=B.KD_PASIEN
--2.E
SELECT A.obat,A.dosis FROM penyakit A,tbRule B WHERE B.kd_rule='RL0003' AND A.kode_penyakit=B.kode_penyakit
--2.F
SELECT A.Gejala_kepala,B.Gejala_kaki,C.Gejala_tangan,D.Gejala_badan,E.Nama_Penyakit
FROM
kepala A,kaki B,tangan C,badan D, penyakit E, tbRule F
WHERE F.kd_rule='RL0005' AND F.kode_kepala=A.kode_kepala AND F.kode_kaki=B.kode_kaki
AND F.kode_tangan=C.kode_tangan AND F.kode_badan=D.kode_badan AND F.kode_penyakit=E.kode_penyakit
--2.G
SELECT C.NM_DOKTER,C.TELP
FROM Pasien A, Konsultasi B,Dokter C
WHERE A.NM_PASIEN='Budidana Susila' AND A.KD_PASIEN = B.KD_PASIEN AND C.KD_DOKTER=B.KD_DOKTER
--3
CREATE VIEW a3A(kepala,kaki,tangan,badan,penyakit)
AS
SELECT A.Gejala_kepala,B.Gejala_kaki,C.Gejala_tangan,D.Gejala_badan,E.Nama_Penyakit
FROM kepala A,kaki B,tangan C,badan D, penyakit E, tbRule F
WHERE F.kode_kepala=A.kode_kepala AND F.kode_kaki=B.kode_kaki
AND F.kode_tangan=C.kode_tangan AND F.kode_badan=D.kode_badan AND F.kode_penyakit=E.kode_penyakit
SELECT * FROM a3A
CREATE VIEW Vpasien(Pasien,dokter,penyakit)AS
SELECT aa.NM_PASIEN,cc.NM_DOKTER,ee.Nama_Penyakit
FROM Pasien aa join Konsultasi bb on aa.KD_PASIEN=bb.KD_PASIEN
join Dokter cc on cc.KD_DOKTER = bb.KD_DOKTER join tbRule dd on dd.kd_rule=bb.KD_RULE
join penyakit ee on dd.kode_penyakit=ee.kode_penyakit
SELECT * FROM Vpasien
CREATE VIEW Vpasien1(penyakit,konsultasi)AS
SELECT aa.Nama_Penyakit,cc.BIAYA
FROM penyakit aa join tbRule bb on aa.kode_penyakit=bb.kode_penyakit join Konsultasi cc on bb.kd_rule=cc.KD_RULE
SELECT * FROM Vpasien1
--4
create procedure tambahPasien
@kode varchar (6),
@NAMA varchar (45),
@alamat varchar (20),
@TELP varchar (13)
as insert into Pasien values (@kode,@NAMA,@alamat,@TELP)
exec tambahPasien 'PSN006','Harry Budiarto','Kuta','081916908765'
exec tambahPasien 'PSN007','Rina Januhari','Jimbaran','081999666789'
SELECT * FROM Pasien
Create procedure mengubahPasien
@kode varchar (6),
@NAMA varchar (45),
@alamat varchar (20),
@TELP varchar (13)
as
Update Pasien set KD_PASIEN=@kode, NM_PASIEN=@NAMA,ALAMAT=@alamat,TELP =@TELP Where KD_PASIEN=@kode
exec mengubahPasien 'PSN001','Hendra Divayana, M.Kom.','Tabanan','081916622461'
exec mengubahPasien 'PSN002','Dessy Sugiharni, S.Pd.','Gianyar','081999786893'
Create procedure hapusPasien
@NAMA varchar (45)
as
Delete Pasien where NM_PASIEN=@NAMA
exec hapusPasien 'Harry Budiarto'
