Jawaban UTS

Pemrograman Basis Data(390103033)

Pemrograman Basis Data(390103033)

Genap 2011 - 2012

Soal 1

Tuliskan query untuk menampilkan semua nama Mahasiswa Pria yang tinggal di kota “JEMBER” dari STUDENT angkatan 2010 dan 2011 jurusan D3-MI yang lahir pada tanggal ’23 Maret’ dan sudah menikah (STS_MARITAL=M)

Jawaban Soal 1 SELECT stu_nama

FROM student WHERE stu_sex = 'L' and

stu_address='JEMBER' and substr(stu_nim,1,2) in ('10','11') and substr(stu_nim,3,5) = '39010‘ and to_char(stu_birth,'DD-MON') = '23-

MAR' and sts_marital = 'M';

Soal 2

Tuliskan query untuk menghitung jumlah mahasiswa dari jurusan S1-SI dan D3-MI (41010 dan 39010) angkatan 2010 dan 2011, yang memiliki prosentase kehadiran kurang dari 60%

Jawaban Soal 2

SELECT count(*) FROM student a, his_student bWHERE a.stu_nim = b.nim and substr(a.stu_nim,3,5) in

('41010','39010') and substr(a.stu_nim,1,2) in ('01','02') and b.persen_hdr <60;

Soal 3

Tuliskan query untuk menampilkan data NIM, NAMA, N_UTS mahasiswa yang mengambil HIS_STUDENT dari angkatan ‘10’ secara UNIQUE, urut berdasarkan Nilai UTS dari yang tertingi.

Jawaban Soal 3

SELECT distinct b.nim, a.stu_nama, b.n_uts

FROM student a, his_student bWHERE a.stu_nim = b.nim and substr(b.nim,1,2) = '10‘ ORDER BY b.nim;

Soal 4

Tampilkan juga mata kuliah yang diambil, sesuai pada soal No. 3 diatas.

Jawaban Soal 4

SELECT b.nim, a.stu_nama, b.kode_mk

FROM student a,his_student bWHERE a.stu_nim = b.nim and substr(b.nim,1,2) = '10‘ORDER BY b.nim;

Soal 5

Buatlah program sederhana untuk meng-update kelas = P2 pada Histori_Mhs, data mahasiswanya sesuai dengan hasil query no.3 .

Jawaban Soal 5Declare begin update his_student set kelas = ‘Q1’ where nim in (SELECT distinct(nim)

FROM his_student

WHERE substr(nim,1,2) = '10’); end;

Program Utama

Declare cNoRek rek_mf.No_Rek%type; i number := 0; Cursor Select_MASTER select NIM, SISA_BAYAR from MASTER Where (SUBSTR(NIM,1,2)>= '00' and

SUBSTR(NIM,1,2)<= '02') and KD_JENIS = '10' and SEMESTER = '051' and ANGSURAN = '1' and (YG_DIBAYAR < HRS_BAYAR or YG_DIBAYAR = 0) and STATUS is null and SEMESTER = '051' order by NIM; rec_Master Select_Master%rowType;

Soal 6

Begin For rec_Master in Select_Master Select_rekening(Nim,cBank, cNoREk); If (cNoRek <>' ' or cNorek is not null) then i := i + 1; else next_record; End If;End ;

Lanjutan Soal 5

Jawaban Soal 6Declare cNoRek rek_mf.No_Rek%type; cBank rek_mf.bank_id%type; i number := 0; Cursor Select_MASTER is select NIM, SISA_BAYAR from MASTER Where (SUBSTR(NIM,1,2)>= '00' and SUBSTR(NIM,1,2)<= '02') and KD_JENIS = '10' and SEMESTER = '051' and ANGSURAN = '1' and (YG_DIBAYAR < HRS_BAYAR or YG_DIBAYAR = 0) and STATUS is null and SEMESTER = '051' order by NIM; rec_Master Select_Master%rowType;

Lanjutan Jawaban Soal 6Begin For rec_Master in Select_Master loop Select_rekening(rec_master.Nim,cBank,

cNoREk); If (cNoRek <>' ' or cNorek is not null) then i := i + 1; else next_record; End If; end loop; End ;

Procedure

Create or replace PROCEDURE Select_Rekening (cNim in VARCHAR2,cBank in VARCHAR2,cNoREk out

varchar2) isBEGIN Select No_Rek into cNoRek From REK_MF where Mhs_Nim = cNim And Bank_id = cBank;Exception when no_data_found then NULL;END;

Lanjutan Jawaban Soal 6