hiyahiyahiya

uts.sql

--Soal 1
create table Dokter (
  IdDokter varchar2(3) not null,
  IdSpesialis varchar2(3) not null,
  Nama varchar2(150) not null,
  JenisKelamin varchar2(15) not null,
  Telepon varchar2(15) not null
);

create table Pasien (
  IdPasien varchar2(3) not null,
  NamaPasien varchar2(150) not null,
  JenisKelamin varchar2(15) not null,
  Telepon varchar2(15) not null
);

create table Spesialis (
  IdSpesialis varchar2(3) not null,
  NamaSpesialis varchar2(150) not null
);

create table Rawat (
  IdRawat varchar2(3) not null,
  IdDokter varchar2(3) not null,
  IdPasien varchar2(3) not null,
  Penyakit varchar2(150) not null,
  TanggalRawat varchar2(80) not null
);

--Soal 2
--(Menambahkan Primary Key)
alter table Dokter
add primary key (IdDokter);

alter table Pasien
add primary key (IdPasien);

alter table Spesialis
add primary key (IdSpesialis);

alter table Rawat
add primary key (IdRawat);

--Menambahkan Foreign Key
alter table Dokter
add constraint FK_SpesialisDiDokter FOREIGN KEY (IdSpesialis) REFERENCES Spesialis(IdSpesialis);

alter table Rawat
add constraint FK_IdDokterDiRawat FOREIGN KEY (IdDokter) REFERENCES Dokter(IdDokter);

alter table Rawat
add constraint FK_IdDPasienDiRawat FOREIGN KEY (IdPasien) REFERENCES Pasien(IdPasien);

--Soal 3
--Input data

--Pasien
INSERT INTO Pasien VALUES('P1','REZA','LAKI-LAKI','0812345431');
INSERT INTO Pasien VALUES('P2','SARAH','PEREMPUAN','0812345432');
INSERT INTO Pasien VALUES('P3','LISA','PEREMPUAN','0812345433');
INSERT INTO Pasien VALUES('P4','ABDUL','LAKI-LAKI','0812345434');
INSERT INTO Pasien VALUES('P5','AMAT','LAKI-LAKI','0812345435');
INSERT INTO Pasien VALUES('P6','LINDA','PEREMPUAN','0812345436');
INSERT INTO Pasien VALUES('P7','AHMAD','LAKI-LAKI','0812345437');
INSERT INTO Pasien VALUES('P8','DINDA','PEREMPUAN','0812345438');
INSERT INTO Pasien VALUES('P9','LINA','PEREMPUAN','0812345439');
INSERT INTO Pasien VALUES('P10','SITI','PEREMPUAN','0812345440');

--Spesialis
INSERT INTO Spesialis VALUES('SJ','SPESIALIS JANTUNG');
INSERT INTO Spesialis VALUES('SM','SPESIALIS MATA');
INSERT INTO Spesialis VALUES('SK','SPESIALIS KANDUNGAN');
INSERT INTO Spesialis VALUES('SU','SPESIALIS UMUM');

--Dokter
INSERT INTO Dokter VALUES('D1','SJ','DR. RAHMAT','LAKI-LAKI','81234567890');
INSERT INTO Dokter VALUES('D2','SM','DR. KEIZA','PEREMPUAN','81234567891');
INSERT INTO Dokter VALUES('D3','SK','DR. NUGROHO','LAKI-LAKI','81234567892');
INSERT INTO Dokter VALUES('D4','SU','DR. ANDI','PEREMPUAN','81234567893');

--Rawat
INSERT INTO Rawat VALUES('R1','D1','P1','JANTUNG','01-APR-2018');
INSERT INTO Rawat VALUES('R2','D2','P2','MATA','10-APR-2018');
INSERT INTO Rawat VALUES('R3','D3','P3','KANDUNGAN','10-APR-2018');
INSERT INTO Rawat VALUES('R4','D4','P4','DEMAM','15-APR-2018');
INSERT INTO Rawat VALUES('R5','D1','P5','JANTUNG','16-APR-2018');
INSERT INTO Rawat VALUES('R6','D2','P6','MATA','16-APR-2018');
INSERT INTO Rawat VALUES('R7','D3','P7','MATA','18-APR-2018');
INSERT INTO Rawat VALUES('R8','D4','P8','KANDUNGAN','20-APR-2018');
INSERT INTO Rawat VALUES('R9','D1','P9','KANDUNGAN','25-APR-2018');
INSERT INTO Rawat VALUES('R10','D2','P10','KANDUNGAN','25-APR-2018');

--Soal 3
--Menampilkan semua data yang ada pada masing-masing tabel
select * from dokter;
select * from pasien;
select * from spesialis;
select * from rawat;

--Soal 4
update pasien set namapasien = 'Rizal' where idpasien = 'P1';

--Soal 5
select penyakit, count(penyakit) as JumlahPenyakit from rawat where penyakit in ('JANTUNG','MATA') group by penyakit;

--Soal 6
select IdDokter, count(IdDokter) as JumlahPasien from rawat where IdDokter = 'D1' group by IdDokter;

--Soal 7
select JenisKelamin, count(JenisKelamin) as JumlahPasien from pasien group by JenisKelamin;