Praktikum SQL (4)

create table Mahasiswa

(

NIM char(10),

NAMA varchar(15),

ALAMAT varchar(20),

KOTA varchar(15)

);

sp_columns Mahasiswa;

create table Matakuliah

(

KODE_MK char(6),

NAMA_MK varchar(30),

SEMESTER char(1),

SKS numeric(2)

);

alter table Matakuliah add constraint pk_kodeMK unique (KODE_MK);

alter table Mahasiswa add constraint pk_NIM unique (NIM);

alter table Mahasiswa alter column kodepos char(5);

alter table mahasiswa alter column alamat char(50);

sp_columns Matakuliah;

create table nilai

(

NIM char(10),

KODE_MK char(6),

NILAI numeric,

constraint fk_NIM foreign key (NIM) references Mahasiswa (NIM),

constraint fk_KODEMK foreign key (KODE_MK) references Matakuliah (KODE_MK)

);

sp_columns nilai;

select*from Mahasiswa;

insert into Mahasiswa values ('201391001','Alfa','Jl.Duri Kosambi No.10','Jakarta','12345');

insert into Mahasiswa values ('201391002','Betta','Jl.Malioboro No.10','Yogyakarta','23456');

insert into Mahasiswa values ('201391003','Charly',' ',' ',' ');

insert into Mahasiswa values ('201391004','Delta','Jl.Riau No.10','Bandung','34567');

insert into Mahasiswa values ('201391005','Echo',' ',' ',' ');

update nilai set NILAI  = '100'

where NIM = '201391001' and KODE_MK = 'MKI001';

select*from Matakuliah;

insert into Matakuliah values ('MKI001','SQL','4','2');

insert into Matakuliah values ('MKI002','Sistem Basis Data','5','2');

insert into Matakuliah values ('MKI003','Bahasa Pemograman','6','3');

insert into Matakuliah values ('MKI004','Perangcangan Basis Data','4','2');

insert into Matakuliah values ('MKI005','Perangkat Lunak Jaringan','3','2');

select*from nilai;

insert into nilai values ('201391001','MKI001','60');

insert into nilai values ('201391002','MKI002','80');

insert into nilai values ('201391003','MKI003','65');

insert into nilai values ('201391004','MKI004','80');

insert into nilai values ('201391005','MKI001','75');

insert into nilai values ('201391001','MKI002','50');

insert into nilai values ('201391002','MKI003','45');

insert into nilai values ('201391003','MKI004','90');

insert into nilai values ('201391004','MKI001','70');

select Mahasiswa.NAMA, Matakuliah.NAMA_MK from Mahasiswa,Matakuliah where NAMA_MK = 'SQL' and Mahasiswa.NIM = nilai.NIM and Matakuliah.KODE_MK = nilai.KODE_MK;

select nilai.NILAI from nilai where NILAI > '65';