contoh data base

drop database bd_a5;
create database bd_a5;
use bd_a5;

create table mahasiswa(
nim char(10)not null primary key,
nama char(20),
jk enum('L','P')
)Engine=InnoDB;

create table matakuliah(
kode char(10)not null primary key,
nama char(20),
sks int
)Engine=InnoDB;

create table krs(
id int not null primary key auto_increment,
semester enum('gasal','genap'),
mahasiswa_nim char(10)not null,
matakuliah_kode char(10)not null,
foreign key(matakuliah_kode)references matakuliah(kode),
foreign key(mahasiswa_nim) references mahasiswa(nim),
unique(semester,mahasiswa_nim,matakuliah_kode)
);

/*inPut data mahasiswa*/
insert into mahasiswa(nim,nama,jk)values
('14001','Adi','L'),
('14002','Ida','P'),
('14003','Edi','L'),
('14004','Susi','P'),
('14005','Joko','L');

/*inPut data matakuliah*/
insert into matakuliah(kode,nama,sks)values
('40001','Pemograman I','4'),
('40002','Pemograman II','4'),
('40003','Basis data','3'),
('40004','PTI','3');

select * from mahasiswa;
select * from matakuliah;

/*inPut data krs(hub mahasiswa & matakuliah)*/
insert into krs(semester,mahasiswa_nim,matakuliah_kode)values
('Gasal','14001','40001'),
('Gasal','14001','40003'),
('Gasal','14003','40001'),
('Genap','14001','40002'),
('Genap','14002','40002'),
('Genap','14005','40002'),
('Gasal','14003','40004'),
('Gasal','14004','40004'),
('Gasal','14005','40004');



select * from krs;
/*cartesian prodak*/
select * from mahasiswa,matakuliah,krs;

/*join*/
select *
from mahasiswa mh,matakuliah mt,krs
where mh.nim=krs.mahasiswa_nim AND mt.kode=krs.matakuliah_kode;

/*tampilkan matakuliah dan nama mahasiswa*/
select mh.nama,mt.nama
from mahasiswa mh,matakuliah mt,krs
where mh.nim=krs.mahasiswa_nim AND mt.kode=krs.matakuliah_kode;

/*tampilkan semua data mahasiswa nama, dan sks matakuliah dan semester krs(sudah join)dari mahasiswa jk laki-laki dan matakuliah=PTI*/
select mh.*,mt.nama 'nama matakuliah',mt.sks,krs.semester
from mahasiswa mh,matakuliah mt,krs
where mh.nim=krs.mahasiswa_nim AND mt.kode=krs.matakuliah_kode AND mh.jk='L' AND mt.nama='PTI';