Tugas Perancanaan Basis Data 1

a. Tabel : koran

Create table koran (kdkoran text (6) primary key, nmkoran text (15), harga currency, jnsterbit text (10))

Mengisi tabel_koran

· Insert into koran values (‘KM01’,’Kompas’,’2500’,’Harian’)

· Insert into koran values (‘BI01’,”Bisnis_Indonesia’,’3000’,’Mingguan’)

· Insert into koran values (‘PK01’,’Poskota’,’2000’,’Harian’)

b. Tabel : loper

Create table loper (idloper text (4) primary key, nmloper text (20), alamat text (50), jnskel text (10))

Mengisi tabel_loper

· Insert into loper values(‘LP01’,’Raihan’,’Jl. Melati No. 5’,’Pria’)

· Insert into loper values (‘LP02’,’Windi’,’Jl. Mawar No. 1’,’Wanita’)

· Insert into loper values (‘LP03’,’Ricky’,’Jl. Kenanga No. 13’,’Pria’)

a. Tabel : transaksi

Create table transaksi (nobukti text (4) primary key,tgl date,kdkoran text (6),idloper text (4),jml number);

Mengisi tabel_transaksi

· Insert into transaksi values (‘B001’,’4/20/2010’,’BI001’,’LP03’,’100’)

· Insert into transaksi values (‘B002’,’4/20/2010’,’KM01’,’LP01’,’150’)

· Insert into transaksi values (‘(‘B003’,’4/21/2010’,’PK01’,’LP02’,’200’)

Relasionship

4. Query : queryagen

select transaksi.nobukti, transaksi.kdkoran, transaksi.idloper, koran.harga, transaksi.jml, (koran.harga*transaksi.jml) as total, iif(transaksi.jml>150,total*0.1,0) as bonus, (total-bonus) as subtotal

from koran inner join transaksi

on koran.kdkoran=transaksi.kdkoran