Jumat, 26 April 2019
















create database INNER_1701030016

on
(

name= INNER_1701030016_data, filename="D:\agungadipratama\INNER_1701030016_data.mdf",

size=10, maxsize=100, filegrowth=2
)

log on

(

name= INNER_1701030016_log, filename="D:\agungadipratama\INNER_1701030016_data.ldf",

size=4, maxsize=100, filegrowth=2
)




create table jabatan

(kodejabatan char(2) primary key not null,

jabatan varchar(20)not null)

create table guru

(nip varchar(19) primary key not null,

nama varchar(25)not null,

kodejabatan char(2) foreign key references jabatan(kodejabatan)not null)






insert into jabatan(kodejabatan, jabatan) values ('01','Kepala Sekolah');

insert into jabatan(kodejabatan, jabatan) values ('02','Wakil Kepala Sekolah');

insert into jabatan(kodejabatan, jabatan) values ('03','Guru');

insert into jabatan(kodejabatan, jabatan) values ('04','Guru BP');

insert into jabatan(kodejabatan, jabatan) values ('05','Konseling');

select * from jabatan



insert into guru(nip, nama, kodejabatan) values ('1234567891011121314','Hendra Kurniawan','01');

insert into guru(nip, nama, kodejabatan) values ('1234567891110121314','Kirana','02');

insert into guru(nip, nama, kodejabatan) values ('1234567891012111314','Anggorowati','03');

insert into guru(nip, nama, kodejabatan) values ('1234567891410111213','Kenzi','03');

select * from guru






--b

select Guru.Nama,jabatan.jabatan from Guru, jabatan

where Guru.kodejabatan=jabatan.kodejabatan

and Guru.kodejabatan='01'




--c

select nama,kodejabatan from Guru

where Nama='kirana'




--d

select * from guru

order by kodejabatan desc



select Guru.kodejabatan,jabatan.jabatan from Guru, jabatan

where Guru.kodejabatan=jabatan.kodejabatan

group by Guru.kodejabatan,jabatan.jabatan




--e

select Guru.*,jabatan.jabatan from Guru inner join jabatan

on Guru.kodejabatan=jabatan.kodejabatan



select jabatan.jabatan,Guru.* from Guru left join jabatan

on Guru.kodejabatan=jabatan.kodejabatan

select jabatan.jabatan,Guru.* from Guru RIGHT join jabatan

on Guru.kodejabatan=jabatan.kodejabatan



select jabatan.jabatan,Guru.* from Guru FULL join jabatan

on Guru.kodejabatan=jabatan.kodejabatan




--f

select distinct Guru.kodejabatan,jabatan.jabatan from Guru, jabatan

where Guru.kodejabatan=jabatan.kodejabatan




--g

select Guru.*,jabatan.jabatan from Guru inner join jabatan

on Guru.kodejabatan=jabatan.kodejabatan

where (guru.kodejabatan='01' and guru.kodejabatan='02');




--h

select Guru.*,jabatan.jabatan from Guru inner join jabatan

on Guru.kodejabatan=jabatan.kodejabatan

where (guru.kodejabatan='01' or guru.kodejabatan='02');



--i

select Guru.*,jabatan.jabatan from Guru inner join jabatan

on Guru.kodejabatan=jabatan.kodejabatan

where not(guru.kodejabatan='01' or guru.kodejabatan='02');





















Kamis, 11 April 2019







CREATE DATABASE DB_AGUNG1
ON
(name=DB_AGUNG1, filename="D:\agung db\DB_AGUNG1.mdf",size=10,
maxsize=100,filegrowth=2)
log on
(name=DB_AGUNG1_log, filename="D:\agung db\DB_AGUNG1.ldf",size=4,
maxsize=100,filegrowth=2)


CREATE TABLE pasien
(
kd_pasien char(5) primary key not null,
nama_pasien varchar(50) not null,
alamat varchar(50)not null
)
select * from pasien


CREATE TABLE obat
(
kd_obat char(10) primary key not null,
nama_obat varchar(50) not null,
satuan varchar(10) not null,
harga varchar(30) not null
)
select * from obat


CREATE TABLE ditail_obat
(
id_obat char(10) primary key not null,
kd_obat char(5) not null,
jumlah int not null,
harga varchar(30) not null
)

CREATE TABLE ruang
(
kd_ruang char(10) primary key not null,
nama_ruang varchar(20) not null,
harga_ruang float not null
)
CREATE TABLE dokter
(
id_dokter char(10) primary key not null,
nama_dokter varchar(20) not null,
biaya_dokter float not null
)

CREATE TABLE petugas
(
kd_petugas char(10) primary key not null,
nama_petugas varchar(20) not null
)

CREATE TABLE rawat_inap
(kd_rawat_inap char(10) primary key not null,
tgl_masuk datetime not null,
kd_pasien char(5) foreign key references pasien(kd_pasien)on delete cascade on update cascade,
kd_ruang char(10)foreign key references ruang(kd_ruang)on delete cascade on update cascade,
kd_petugas char(10)foreign key references petugas(kd_petugas)on delete cascade on update cascade
)



CREATE TABLE transaksi_obat
(id_transaksi_obat char(10) primary key not null,
tanggal datetime not null,
kd_pasien char(5) not null
)

CREATE TABLE kunjungan_dokter
(id_kunjungan char(10) primary key not null,
kd_pasien char(5) foreign key references pasien(kd_pasien)on delete cascade on update cascade,
kd_ruang char(10)foreign key references ruang(kd_ruang)on delete cascade on update cascade,
id_doker char(10)foreign key references dokter(id_dokter)on delete cascade on update cascade,
tanggal_kunjungan datetime not null,
hasil_kujungan varchar (50) not null
)

CREATE TABLE pembayaran
(id_pembayaran char(10) primary key not null,
tgl_pembayaran datetime not null,
kd_pasien char(5) foreign key references pasien(kd_pasien)on delete cascade on update cascade,
lama_inap smalldatetime not null,
biaya_kamar float not null,
biaya_kunjungan_dokter float not null,
biaya_obat float not null
)
select * from pembayaran

)