BDD terakhir smester 2

create database puskesdb
use puskesdb
create table pasien(kodepas char(10)primary key not null,namapas varchar(20),janiskel char(5),umur varchar(5))
create table dokter(kodedok char(5) primary key not null,namadokter varchar(20))
create table poli(kodepoli char(5)primary key not null,jenispoli varchar(20))
create table periksa(kodeperiksa char(10),tglperiksa date)
insert into pasien value("P-1001","agnes monika","P","15")
insert into pasien value("L-2001","hengky tornado","L","45")
insert into pasien value("P-1002","nunung","P","30")
insert into pasien value("L-2002","sule","L","10")
insert into pasien value("P-1003","monika","P","5")
insert into dokter value("A1","dr.solehatin")
insert into dokter value("A2","dr.djuniharto")
insert into dokter value("A3","dr.rudi")
insert into dokter value("A4","dr.heri")
insert into dokter value("A5","dr.mia")
insert into poli value("01","poli umum")
insert into poli value("02","poli anak")
insert into poli value("03","poli mata")
insert into poli value("04","poli gigi")
insert into poli value("05","poli jantung")
insert into periksa value("A1-01-1002","2010-12-27")
insert into periksa value("A3-04-2002","2010-12-10")
insert into periksa value("A4-02-1003","2010-12-05")
insert into periksa value("A2-05-2001","2010-12-10")
insert into periksa value("A1-01-1001","2010-12-30")
1
select namapas,umur from pasien where janiskel="P"
2
select periksa.kodeperiksa,pasien.namapas,poli.jenispoli from periksa join pasien on right(kodepas,4)=right(kodeperiksa,4) join poli on substring(kodeperiksa,4,2)=kodepoli where janiskel="L"
3
select periksa.kodeperiksa,pasien.namapas,pasien.umur,dokter.namadokter,poli.jenispoli
from periksa join pasien on right(kodeperiksa,4)=right(kodepas,4) join dokter on left(kodeperiksa,2)=kodedok join poli on substring(kodeperiksa,4,2)=kodepoli
where kodepoli='01'
4
select kodepoli,jenispoli from poli where kodepoli="03"
5
select periksa.kodeperiksa,pasien.namapas,poli.jenispoli,dokter.namadokter,pasien.umur
from periksa join pasien on right(kodepas,4)=right(kodeperiksa,4) join poli on substring(kodeperiksa,4,2)=kodepoli join dokter on left(kodeperiksa,2)=kodedok
where umur>='20'

6
select dokter.kodedok,dokter.namadokter from dokter where kodedok="A5"
7
select periksa.kodeperiksa,pasien.namapas,dokter.namadokter,poli.jenispoli
from periksa join pasien on right(kodepas,4)=right(kodeperiksa,4) join poli on substring(kodeperiksa,4,2)=kodepoli join dokter on left(kodeperiksa,2)=kodedok
where tglperiksa='2010-12-05'
8
select periksa.kodeperiksa,pasien.namapas,dokter.namadokter
from periksa join pasien on right(kodepas,4)=right(kodeperiksa,4) join poli on substring(kodeperiksa,4,2)=kodepoli join dokter on left(kodeperiksa,2)=kodedok
where namadokter='dr. solehatin'