LATIHAN MEMBUAT DAN MENGISI TABEL DI DATABASE DENGAN ORACLE DATABASE 10g EXPRESS EDITION

Latihan 1

Sintaks :

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);

Sintaks :

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

Sintaks :

DELETE FROM mahasiswa
WHERE nim=201291001;

select * from mahasiswa

desc mahasiswa;

Output :

Latihan 2

Sintaks :

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 :

Latihan 3

Sintaks :

CREATE TABLE nilai(
nim char(10),
kode_MK char(6),
Nilai number,
constraint pk_nilai primary key(nim,kode_MK),
constraint fk_nilai_mahasiswa foreign key(nim)
references mahasiswa (nim),
constraint fk_nilai_matakuliah foreign key(kode_MK)
references matakuliah (kode_MK)
);

INSERT INTO nilai(nim, kode_MK, Nilai)
VALUES ('201291001', 'MKI001', 60);

INSERT INTO nilai(nim, kode_MK, Nilai)
VALUES ('201291002', 'MKI002', 80);

INSERT INTO nilai(nim, kode_MK, Nilai)
VALUES ('201291003', 'MKI003', 65);

INSERT INTO nilai(nim, kode_MK, Nilai)
VALUES ('201291004', 'MKI004', 80);

INSERT INTO nilai(nim, kode_MK, Nilai)
VALUES ('201291005', 'MKI001', 75);

INSERT INTO nilai(nim, kode_MK, Nilai)
VALUES ('201291001', 'MKI002', 50);

INSERT INTO nilai(nim, kode_MK, Nilai)
VALUES ('201291002', 'MKI003', 45);

INSERT INTO nilai(nim, kode_MK, Nilai)
VALUES ('201291003', 'MKI004', 90);

INSERT INTO nilai(nim, kode_MK, Nilai)
VALUES ('201291004', 'MKI001', 70);

select * from nilai;

desc nilai;

Output :

Latihan 4

Sintaks :

SELECT * FROM mahasiswa;

Output :

Sintaks :

SELECT mahasiswa.nim, mahasiswa.nama, matakuliah.nama_mk
FROM mahasiswa, matakuliah, nilai
WHERE nilai.nim=mahasiswa.nim
   AND nilai>65
   AND nilai.kode_MK=matakuliah.kode_MK

Output :

Sintaks :

UPDATE nilai
SET nilai.nilai=100
WHERE nilai.kode_MK='MKI001'
   AND nilai.nim=201291001
   AND nilai.nilai=60;

SELECT nama, nama_mk, nilai
FROM mahasiswa, matakuliah, nilai
WHERE nilai=100
   AND mahasiswa.nim=nilai.nim
   AND nilai.kode_MK=matakuliah.kode_MK

Output :

Sintaks :

UPDATE matakuliah
SET nama_MK = 'ALGORITMA PEMROGRAMAN'
WHERE nama_MK = 'BAHASA PEMROGRAMAN';

SELECT * FROM matakuliah;

Output :

Sintaks :

DELETE FROM mahasiswa

WHERE nim = 201291005;

SELECT * FROM mahasiswa;

Output :