Tugas Prak BDL 1

--TUGAS PRAK BDL 1
--Senu Dwi Aussie, 0974016

--membuat tabel nilai

create table tbnilai(
NIM char(8),
semester varchar(10),
kodemk char(5),
uts float default 0,
uas float default 0,
kat float default 0,
primary key (NIM, semester, kodemk)
)

--membuat tabel mata kuliah

create table tbMataKuliah(
kodemk char(5),
MataKuliah varchar(30),
Semester int,
SKS int
primary key(kodemk)

)

-- membuat foreign key pada tbNilai(NIM) dengan references tbMhs(NIM)

alter table tbNilai add constraint fk_tbNilai_ref_tbMhs
foreign key(NIM) references tbMhs(NIM);

-- membuat foreign key pada tbNilai(KodeMK) dengan references tbMataKuliah(KodeMK)

alter table tbNilai add constraint fk_tbNilai_ref_tbMataKuliah
foreign key(KodeMK) references tbMataKuliah(KodeMK);

-- memasukan data pada tabel mata kuliah

insert into tbMataKuliah values('IF001','Data Base Lanjut',1,4);
insert into tbMataKuliah values('IF002','OOP',1,4);
insert into tbMataKuliah values('IF003','Artificial Intelligence',1,4);
insert into tbMataKuliah values('IF004','Web',1,4);
insert into tbMataKuliah values('IF005','Multimedia',1,4);

-- memasukan data pada tabel nilai

insert into tbnilai values('10113025','GENAP','IF001',80,70,75);
insert into tbnilai values('10113026','GENAP','IF002',40,60,75);
insert into tbnilai values('10113025','GANJIL','IF002',30,70,80);
insert into tbnilai values('10113027','GENAP','IF003',20,20,90);
insert into tbnilai values('10113029','GANJIL','IF004',50,70,75);
insert into tbnilai values('10113030','GENAP','IF003',60,70,75);
insert into tbnilai values('10113030','GANJIL','IF001',50,60,75);
insert into tbnilai values('10113029','GENAP','IF004',20,50,90);
insert into tbnilai values('10113031','GANJIL','IF005',45,75,45);
insert into tbnilai values('10113026','GANJIL','IF005',80,55,65);

-- menampilkan NIM, semester, kodemk, uts, uas, kat, NA
-- dengan ketentuan NA = 30% UTS + 30%KAT + 40%UAS

select * , ((30*uts)/100+(30*kat)/100+(40*uas)/100)as'NA' from tbNilai;