Myanimeanalysis

CREATE DATABASE ngampus
on primary
(
Name= 'ngampus_data',
filename= 'D:\Hani IK201\ngampus.mdf',
size = 5MB,
maxsize = Unlimited,
Filegrowth = 10MB
)
log on
(
Name= 'ngampus_log',
filename= 'D:\Hani IK201\ngampus.log',
size = 5MB,
maxsize = Unlimited,
Filegrowth = 10MB
)
use ngampus;

create table matakuliah(
kd_nik varchar (5) primary key not null,
nm_mtkl varchar (30),
sks int
);


--------

create table kuliah(
kd_kl varchar (5) primary key not null,
kd_nik varchar (5),
nip int,
thnakademik varchar (20),
ruang varchar (15),
jam time,
semester varchar (10)
foreign key (kd_nik) references matakuliah(kd_nik),
foreign key (nip) references dosen(nip)
);


-----

create table peserta(
kd_kl varchar (5),
nim int,
nilai int
foreign key (kd_kl) references kuliah(kd_kl),
foreign key (nim) references mahasiswa (nim)
);


-----
create table dosen(
nip int primary key not null,
nama varchar (25)
);


create table mahasiswa(
nim int primary key not null,
nama varchar (25),
ds_pmb int
foreign key (ds_pmb)references dosen (nip)
);


select * from kuliah
select * from matakuliah
select * from peserta
select * from mahasiswa
select * from dosen

create proc add_matakuliah
@kd_Nik varchar(5),
@nm_mtkl varchar(30),
@sks int
as insert into matakuliah
values(@kd_Nik,@nm_mtkl,@sks);

exec add_matakuliah 'M01','Database',2;
exec add_matakuliah 'M02','Website Desain',4;
exec add_matakuliah 'M03','Agama',2;

select * from matakuliah;

create proc add_dosen
@nip int,
@nama varchar(25)
as insert into dosen
values(@nip,@nama);

exec add_dosen '121','Aris';
exec add_dosen '122','Firdaus';
exec add_dosen '123','Fauzan';

select * from dosen;

create proc add_kuliah
@kd_kl varchar(5),
@kd_nik varchar(5),
@nip int,
@thnakademik varchar(20),
@ruang varchar(15),
@jam time(7),
@semester varchar(10)
as insert into kuliah
values(@kd_kl,@kd_nik,@nip,@thnakademik,@ruang,@jam,@semester);

exec add_kuliah 'K0111','M01',121,'2016/2017','INTEL','08:00:01','2';
exec add_kuliah 'K0112','M02',122,'2016/2017','AMD','13:00:01','2';
exec add_kuliah 'K0113','M03',123,'2017/2018','AULA','15:00:01','2';

select * from kuliah;

create proc add_mahasiswa
@nim int,
@nama varchar(25),
@ds_pmb int
as insert into mahasiswa
values(@nim,@nama,@ds_pmb);

exec add_mahasiswa '160442','Aziz',121;
exec add_mahasiswa '160443','Hani',122;
exec add_mahasiswa '160444','Aris',123;

select * from mahasiswa;

create proc add_peserta
@kd_kl varchar(5),
@nim int,
@nilai int
as insert into peserta
values(@kd_kl,@nim,@nilai);

exec add_peserta 'K0111',160442,90;
exec add_peserta 'K0112',160443,80;
exec add_peserta 'K0113',160444,75;


-----------------------

/*soal no1 (buat procedure lihat_mahasiswa, menampilkan nama berdasarkan NIM)*/
create procedure lihat_mahasiswa
@nim int
as select * from mahasiswa where nim = @nim;

exec lihat_mahasiswa '160442';
-----------------

/*soal no2 (buat procedure lihat_dosen ,berdasarkan nip dosen)*/
create procedure lihat_dosen
@nip int
as select * from dosen where nip = @nip;

exec lihat_dosen '121';
--------------


/*soal no3 (buat procedure lihat_matkul, berdasarkan sks)*/
create procedure lihat_matkul
@sks int
as select * from matakuliah where sks = @sks;

exec lihat_matkul '4'
--------------

/*soal no4 (buat procedure lihat_ruang, berdasarkan ruang)*/
create procedure lihat_ruang
@ruang varchar(15)
as select * from kuliah where ruang = @ruang;

exec lihat_ruang 'AMD'
--------------