LATIHAN 1:
Program codingnya sebagai berikut:
CREATE TABLE mahasiswa (
nim char(10),
nama varchar(15),
alamat varchar(20),
kota varchar(15),
constraint pk_nim primary key (nim)
);
ALTER TABLE mahasiswa
ADD kodepos char(8);
ALTER TABLE mahasiswa
MODIFY kodepos varchar2(5);
ALTER TABLE mahasiswa
MODIFY alamat varchar2(30);
INSERT INTO mahasiswa (nim, nama, alamat, kota, kodepos)
VALUES (201291001,'Alfa','JL. Duri Kosambi
No.10','Jakarta',12345);
INSERT INTO mahasiswa (nim, nama, alamat, kota, kodepos)
VALUES (201291002,'Betta','JL. Malioboro
No.10','Yogyakarta',23456);
INSERT INTO mahasiswa
VALUES (201291003,'Charly',NULL,NULL,NULL);
INSERT INTO mahasiswa (nim, nama, alamat, kota, kodepos)
VALUES (201291004,'Delta','JL. Riau No.10','Bandung',34567);
INSERT INTO mahasiswa
VALUES (201291005,'Echo',NULL,NULL,NULL);
UPDATE mahasiswa
SET kota = 'Semarang'
WHERE nim = 201291004;
DELETE FROM mahasiswa
WHERE nim = 201291001;
select * from mahasiswa
desc mahasiswa;
Output dari program diatas adalah:
===============================================
LATIHAN 2
Program codingnya sebagai berikut:
CREATE TABLE matakuliah (
kode_mk char(6),
nama_mk varchar(30),
semester char(1),
sks number(2),
constraint pk_kode primary key (kode_mk)
);
INSERT INTO matakuliah(kode_mk, nama_mk, semester, sks)
VALUES('MKI001','SQL',4,2);
INSERT INTO matakuliah(kode_mk, nama_mk, semester, sks)
VALUES('MKI002','SISTEM BASIS DATA',5,2);
INSERT INTO matakuliah(kode_mk, nama_mk, semester, sks)
VALUES('MKI003','BAHASA PEMROGRAMAN',6,3);
INSERT INTO matakuliah(kode_mk, nama_mk, semester, sks)
VALUES('MKI004','PERANCANGAN BASIS DATA',4,2);
select * from matakuliah
desc matakuliah
Output dari program diatas adalah:
==========================================================
LATIHAN 3
Codingnya:
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 matakuliah(kode_mk)
);
INSERT INTO nilai1(nim,kode_mk,nilai)
VALUES
(201291001,'MKI001',60);
INSERT INTO nilai1(nim,kode_mk,nilai)
VALUES (201291002,'MKI002',80);
INSERT INTO nilai1(nim,kode_mk,nilai)
VALUES
(201291003,'MKI003',65);
INSERT INTO nilai1(nim,kode_mk,nilai)
VALUES
(201291004,'MKI004',80);
INSERT INTO nilai1(nim,kode_mk,nilai)
VALUES
(201491005,'MKI001',75);
INSERT INTO nilai1(nim,kode_mk,nilai)
VALUES
(201291001,'MKI002',50);
INSERT INTO nilai1(nim,kode_mk,nilai)
VALUES
(201291002,'MKI003',45);
INSERT INTO nilai1(nim,kode_mk,nilai)
VALUES
(201291003,'MKI004',90);
INSERT INTO nilai1(nim,kode_mk,nilai)
VALUES
(201291004,'MKI001',70);
==================================================================
LATIHAN 4
CODINGNYA:
SELECT nim, nama, alamat, kota, kodepos
FROM mahasiswa;
SELECT mahasiswa.nim, mahasiswa.nama, matakuliah.nama_mk
FROM mahasiswa, matakuliah, nilai1
WHERE nilai1.nim=mahasiswa.nim and nilai>65 and nilai1.kode_MK=matakuliah.kode_mk;
UPDATE nilai1
SET nilai1.nilai=100
WHERE nilai1.kode_MK ='MKI001' and nilai1.nim=201291001 and
nilai1.nilai=60;
UPDATE matakuliah
SET nama_mk = ‘ALGORITMA PEMROGRAMAN’
WHERE nama_mk = ‘BAHASA PEMROGRAMAN’;
DELETE FROM mahasiswa.nim, nilai1.nim
WHERE nim = '201291005';
desc nilai1;
Sekian latihan membuat tabel dengan Oracle..
Terimakasih...
Semoga Bermanfaat
