1 / 17

Jawaban UTS

Jawaban UTS. Genap 2011 - 2012. Pemrograman Basis Data (390103033). 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).

brand
Download Presentation

Jawaban UTS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Jawaban UTS Genap 2011 - 2012 Pemrograman Basis Data (390103033)

  2. 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)

  3. 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';

  4. 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%

  5. Jawaban Soal 2 SELECT count(*) FROM student WHERE substr(stu_nim,3,5) in ('41010','39010') and substr(stu_nim,1,2) in ('01','02') GROUP BY stu_nim;

  6. 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.

  7. Jawaban Soal 3 SELECT distinct b.nim, a.stu_nama, b.n_uts FROM student a, his_student b WHERE a.stu_nim = b.nim and substr(b.nim,1,2) = '10‘ ORDER BY b.nim;

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

  9. Jawaban Soal 4 SELECT b.nim, a.stu_nama, b.kode_mk FROM student a,his_student b WHERE a.stu_nim = b.nim and substr(b.nim,1,2) = '10‘ ORDER BY b.nim;

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

  11. Jawaban Soal 5 Declare begin update his_student set kelas = 'P2' where nim in (SELECT distinct NIM FROM his_student WHERE substr(b.nim,1,2) = ‘10'); end;

  12. Soal 6 Program Utama Declare cNoRekrek_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_MasterSelect_Master%rowType;

  13. Lanjutan Soal 5 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 ;

  14. Jawaban Soal 6 Declare 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;

  15. Lanjutan Jawaban Soal 6 Begin 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 ;

  16. Lanjutan Jawaban Soal 6 Procedure Create or replace PROCEDURE Select_Rekening (cNim in VARCHAR2,cBank in VARCHAR2,cNoREk out varchar2) is BEGIN Select No_Rek into cNoRek From REK_MF where Mhs_Nim = cNim And Bank_id = cBank; Exception when no_data_found then NULL; END;

More Related