LATIHAN MEMBUAT TABEL MENGGUNAKAN ORACLE

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