Blogger Anak Belajar: PEMROGRAMAN SQL PART IV

create table mahasiswa (
nim char(10),
nama varchar(15),
alamat varchar(50),
kota varchar(15),
constraint pk_mahasiswa primary key (nim)
);

create table matakuliah1 (
kode_MK char(6),
nama_MK varchar(30),
semester char(1),
sks number(2),
constraint pk_matakuliah1 primary key (kode_MK)
);

create table nilai1 (
nim char(10),
kode_MK char(6),
nilai number(2),
constraint fk_nim_mk foreign key (nim)
references mahasiswa(nim),
constraint fk_nilai_mk foreign key (kode_MK)
references matakuliah1(kode_MK)
);

alter table mahasiswa
add kodepos char (8);

alter table mahasiswa
modify kodepos varchar2(5);

alter table mahasiswa
modify alamat varchar2(50);

alter table nilai1
modify nilai number;

INSERT INTO mahasiswa
VALUES (201291001,'alfa','Jl.Duri Kosambi No.10','Jakarta',12345);
INSERT INTO mahasiswa
VALUES (201291002,'betta','Jl.malioboro No.10','Yogyakarta',23456);
INSERT INTO mahasiswa
VALUES (201291003,'charly',NULL,NULL,NULL);
INSERT INTO mahasiswa
VALUES (201291004,'delta','Jl.Riau No.10','Bandung',34567);
INSERT INTO mahasiswa
VALUES (201291005,'echo',NULL,NULL,NULL);

INSERT INTO matakuliah1
VALUES ('MKI001','SQL',4,2);
INSERT INTO matakuliah1
VALUES ('MKI002','Sistem Basis Data',5,2);
INSERT INTO matakuliah1
VALUES ('MKI003','Bahasa Pemrograman',6,3);
INSERT INTO matakuliah1
VALUES ('MKI004','Perancangan Basis Data',4,2);

INSERT INTO nilai1
VALUES (201291001,'MKI001',60);
INSERT INTO nilai1
VALUES (201291002,'MKI002',80);
INSERT INTO nilai1
VALUES (201291003,'MKI001',65);
INSERT INTO nilai1
VALUES (201291004,'MKI004',80);
INSERT INTO nilai1
VALUES (201491001,'MKI001',75);
INSERT INTO nilai1
VALUES (201291001,'MKI002',50);
INSERT INTO nilai1
VALUES (201291002,'MKI003',45);
INSERT INTO nilai1
VALUES (201291003,'MKI004',90);
INSERT INTO nilai1
VALUES (201291004,'MKI001',70);
INSERT INTO nilai1
VALUES (201291005,'MKI001',75);

UPDATE mahasiswa
SET kota = 'semarang'
WHERE nim = 201291004;

UPDATE mahasiswa
SET alamat = NULL
WHERE nim = 201291005;

UPDATE nilai1
SET kode_MK = 'MKI003'
WHERE nim = 201291003;

DELETE FROM mahasiswa
WHERE nim=201291001;

SELECT NIM, nama, alamat, kota, kodepos
FROM mahasiswa;

SELECT *
FROM matakuliah1;

SELECT *
FROM nilai1;

select mahasiswa.nim, mahasiswa.nama, matakuliah1.nama_MK
from mahasiswa, matakuliah1, nilai1
where nilai1.nim=mahasiswa.nim and nilai>65 and nilai1.kode_MK=matakuliah1.kode_MK

update nilai1
set nilai1.nilai=100
where nilai1.kode_MK ='MKI001' and nilai1.nim=201291001 and nilai1.nilai=60;

UPDATE matakuliah1
SET nama_MK = 'Algoritma Pemrograman'
WHERE nama_MK = 'Bahasa Pemrograman';

DELETE from nilai1
WHERE nim = 201291005