Kueri Apotek OracleDb

kueriapotekoracle.sql

create table pelanggan (
  IdPel varchar2(5) not null primary key,
  NamaPel varchar2(50) not null,
  Alamat varchar2(100) not null,
  JenKel varchar2(1) not null,
  Umur varchar(3),
  NoHp varchar(15)
);

select * from pelanggan;

create table jenis (
  IdJenis varchar2(5) not null primary key,
  NamaJenis varchar2(20) not null
);

create table penyakit (
  IdPenyakit varchar2(3) not null primary key,
  NamaPenyakit varchar2(50) not null,
  Simptom varchar2(255) not null
);

create table Obat(
	IdObat varchar2(5) not null Primary Key,
  IdJenis varchar2(5) not null,
	NamaObat varchar2(50) not null,
	Harga int,
  Stok int not null,
	TanggalMasuk timestamp default current_timestamp not null,
	TanggalExpired Date not null,
  IdPenyakit varchar2(3) not null
);

select * from obat;

create table kasir (
  IdKasir varchar2(3) not null primary key,
  NamaKasir varchar2(50) not null,
  JK varchar2(5),
  Shift varchar2(5)
);

create table transaksi (
  IdTransaksi int not null primary key,
  IdObat varchar2(5) not null,
  IdKasir varchar2(3) not null,
  IdPel varchar2(5) not null,
  TanggalTransaksi timestamp default current_timestamp not null,
  Qty int,
  Constraint FK_Obat foreign key (IdObat) references obat(IdObat),
  constraint FK_kasir foreign key (IdKasir) references kasir(IdKasir),
  constraint FK_Pel foreign key (IdPel) references pelanggan(IdPel)
  );
  
alter table obat
add IdPenyakit varchar2(3);

alter table obat
add constraint FK_Jenis FOREIGN KEY (IdJenis) REFERENCES jenis(IdJenis);

alter table obat
add constraint FK_PenyDiObat FOREIGN KEY (IdPenyakit) REFERENCES penyakit(IdPenyakit);

alter table obat
add constraint FK_Jenis FOREIGN KEY (IdJenis) REFERENCES jenis(IdJenis);