Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create table mahasiswa(
- NIM_MHS char(8) PRIMARY KEY,
- NAMA_MHS varchar(25) NOT NULL,
- ALMT_MHS varchar(30),
- TGLLAHIR_MHS date,
- JK_MHS char(1),
- STATUS_MHS varchar(10),
- EMAIL_MHS varchar(20) UNIQUE NOT NULL
- );
- insert into mahasiswa values('11011001','Adinta','Jl.Kelapa 2/2',to_date('12-01-1994','dd-mm-yyyy'),'P','Aktif','adin@yahoo.com');
- insert into mahasiswa values('11011002','Budiawan','Jl.Nanas X/20',to_date('10-01-1993','dd-mm-yyyy'),'L','Aktif','budi@gmail.com');
- insert into mahasiswa values('11011003','Charlie','Jl.Pintu Air 20',to_date('12-01-1994','dd-mm-yyyy'),'L','Aktif','charlie@gmail.com');
- insert into mahasiswa values('11011004','Denok','Jl.Asia Afrika',to_date('12-04-1995','dd-mm-yyyy'),'P','Aktif','denok@yahoo.com');
- insert into mahasiswa values('11011005','Nathalie','Jl.Kerinci III/4',to_date('09-07-1994','dd-mm-yyyy'),'P','Aktif','nath@hotmail.com');
- insert into mahasiswa values('11011006','Indianto','Jl.Arjuna 1/10',to_date('14-09-1993','dd-mm-yyyy'),'L','Aktif','indi@yahoo.com');
- create table dosen(
- NIP_DSN char(6) PRIMARY KEY,
- NAMA_DSN varchar(25) NOT NULL,
- STATUS varchar(17)
- );
- insert into dosen values('107001','Prof.Aminarto,M.Sc.','Dosen Tetap');
- insert into dosen values('107002','Dr.Melania,M.Kom','Dosen Tetap');
- insert into dosen values('007003','Dr.Setyo,S.Kom,M.T','Dosen Tidak Tetap');
- insert into dosen values('007004','Prof.Merita,M.Kom','Dosen Tidak Tetap');
- insert into dosen values('107005','Dr.Danuarto,M.Sc.','Dosen Tetap');
- insert into dosen values('007006','Prof.Winda,S.Kom,M.T','Dosen Tidak Tetap');
- create table matakuliah(
- KD_MK char(6) PRIMARY KEY,
- NAMA_MK varchar(25) NOT NULL,
- SKS_MK int NOT NULL,
- SMT_MK char(2) NOT NULL
- );
- insert into matakuliah values('TI506','Konsep Bahasa Pemograman',3,'5');
- insert into matakuliah values('TI532','Pemograman Web',3,'5');
- insert into matakuliah values('SI541','Database Lanjutan',3,'5');
- insert into matakuliah values('UM121','Bahasa Inggris',2,'1');
- insert into matakuliah values('UM140','Bahasa Indonesia',3,'3');
- insert into matakuliah values('SK101','Kalkulus',3,'1');
- create table jadwal(
- KD_JDWL char(6) PRIMARY KEY,
- HARI varchar(10),
- WAKTU timestamp
- );
- insert into jadwal values('JSE01','Senin',to_timestamp('08.00','HH.MI'));
- insert into jadwal values('JSE02','Senin',to_timestamp('10.00','HH.MI'));
- insert into jadwal values('JSL01','Selasa',to_timestamp('08.00','HH.MI'));
- insert into jadwal values('JSL02','Selasa',to_timestamp('10.00','HH.MI'));
- insert into jadwal values('JRA01','Rabu',to_timestamp('08.00','HH.MI'));
- insert into jadwal values('JRA02','Rabu',to_timestamp('10.00','HH.MI'));
- create table DIAMPU(
- KD_MK char(6) CONSTRAINT diampu_kd_mk references matakuliah(KD_MK),
- KD_JDWL char(6) CONSTRAINT diampu_kd_jdwl references jadwal(KD_JDWL),
- NIP_DSN char(6) CONSTRAINT diampu_kd_dsn references dosen(NIP_DSN)
- );
- insert into DIAMPU values('TI506','JSE01','107001');
- insert into DIAMPU values('TI532','JSL01','107002');
- insert into DIAMPU values('SI541','JSE02','007003');
- insert into DIAMPU values('UM121','JRA02','007004');
- insert into DIAMPU values('UM140','JRA01','107005');
- insert into DIAMPU values('SK101','JRA02','007006');
- create table rencanastudi(
- NIM_MHS char(8) CONSTRAINT studi_nim_mhs references mahasiswa(NIM_MHS),
- KD_MK char(6) CONSTRAINT studi_mk_char references matakuliah(KD_MK),
- KD_JDWL char(6) CONSTRAINT studi_kd_jdwl references jadwal(KD_JDWL),
- NIP_DSN char(6) CONSTRAINT studi_kd_dsn references dosen(NIP_DSN)
- );
- insert into rencanastudi values('11011001','TI506','JSE01','107001');
- insert into rencanastudi values('11011002','TI506','JSE01','107001');
- insert into rencanastudi values('11011002','TI532','JSL01','107005');
- insert into rencanastudi values('11011003','TI506','JSE01','107001');
- insert into rencanastudi values('11011003','TI532','JSL02','007006');
- insert into rencanastudi values('11011003','SK101','JRA01','107005');
- insert into rencanastudi values('11011005','TI506','JSE01','107002');
- insert into rencanastudi values('11011005','TI532','JSL02','007006');
- insert into rencanastudi values('11011005','SK101','JRA02','007006');
- insert into rencanastudi values('11011005','UM140','JRA01','007004');
- insert into rencanastudi values('11011006','TI506','JSE01','107002');
- insert into rencanastudi values('11011006','SK101','JRA02','107005');
- insert into rencanastudi values('11011004','TI532','JSL02','007006');
- insert into rencanastudi values('11011004','SK101','JRA02','007006');
- 2.
- A. create view umur_mhs as
- select NIM_Mhs, NAMA_MHS from mahasiswa where Months_between (sysdate,tgllahir_mhs)/12 > 17;
- NIM_MHS NAMA_MHS
- -------- -------------------------
- 11011001 Adinta
- 11011002 Budiawan
- 11011003 Charlie
- 11011004 Denok
- 11011005 Nathalie
- 11011006 Indianto
- 6 rows selected.
- B.select r.NIM_mhs, m.Nama_MHS, sum(k.sks_mk) from rencanastudi r,mahasiswa m, matakuliah k
- where r.NIM_MHS = m.NIM_MHS
- and
- r.kd_mk = k.kd_mk
- and sum(k.sks_mk) >= 9
- and sum(k.sks_mk) <= 12
- ;
- C. select kd_mk,nama_mk from matakuliah where kd_mk like '%TI%';
- KD_MK NAMA_MK
- ------ -------------------------
- TI506 Konsep Bahasa Pemograman
- TI532 Pemograman Web
- D. select distinct m.nama_mhs,k.nama_mk,d.nama_dsn,j.hari,j.waktu from mahasiswa m,matakuliah k, dosen d, jadwal j,rencanastudi r
- where m.NIM_MHS = r.NIM_MHS
- AND
- k.kd_mk = r.kd_mk
- AND
- d.NIP_DSN = r.NIP_dsn
- AND
- j.kd_jdwl = r.kd_jdwl;
- NAMA_MHS NAMA_MK NAMA_DSN HARI WAKTU
- ------------------------- ------------------------- ------------------------- ---------- ---------------------------------------------------------------------------
- Indianto Kalkulus Dr.Danuarto,M.Sc. Rabu 01-JAN-13 10.00.00.000000 AM
- Nathalie Pemograman Web Prof.Winda,S.Kom,M.T Selasa 01-JAN-13 10.00.00.000000 AM
- Nathalie Bahasa Indonesia Prof.Merita,M.Kom Rabu 01-JAN-13 08.00.00.000000 AM
- Indianto Konsep Bahasa Pemograman Dr.Melania,M.Kom Senin 01-JAN-13 08.00.00.000000 AM
- Charlie Kalkulus Dr.Danuarto,M.Sc. Rabu 01-JAN-13 08.00.00.000000 AM
- Nathalie Kalkulus Prof.Winda,S.Kom,M.T Rabu 01-JAN-13 10.00.00.000000 AM
- Nathalie Konsep Bahasa Pemograman Dr.Melania,M.Kom Senin 01-JAN-13 08.00.00.000000 AM
- Charlie Pemograman Web Prof.Winda,S.Kom,M.T Selasa 01-JAN-13 10.00.00.000000 AM
- Budiawan Konsep Bahasa Pemograman Prof.Aminarto,M.Sc. Senin 01-JAN-13 08.00.00.000000 AM
- Denok Pemograman Web Prof.Winda,S.Kom,M.T Selasa 01-JAN-13 10.00.00.000000 AM
- Denok Kalkulus Prof.Winda,S.Kom,M.T Rabu 01-JAN-13 10.00.00.000000 AM
- NAMA_MHS NAMA_MK NAMA_DSN HARI WAKTU
- ------------------------- ------------------------- ------------------------- ---------- ---------------------------------------------------------------------------
- Charlie Konsep Bahasa Pemograman Prof.Aminarto,M.Sc. Senin 01-JAN-13 08.00.00.000000 AM
- Adinta Konsep Bahasa Pemograman Prof.Aminarto,M.Sc. Senin 01-JAN-13 08.00.00.000000 AM
- Budiawan Pemograman Web Dr.Danuarto,M.Sc. Selasa 01-JAN-13 08.00.00.000000 AM
- 14 rows selected.
- E.
- select 'Mata Kuliah : ' || '&&matakuliah1' || '
- Nilai Tugas : ' || &&nilai_tugas1 || '
- Nilai UTS : '|| &&nilai_uts1 || '
- Nilai UAS : '|| &&nilai_uas1 || '
- Bobot SKS : '|| &&bobot_sks1 || '
- Nilai Total : ' ||((&nilai_tugas1 * (30/100) + &nilai_uts1 * (30/100) + &nilai_uas1 * (40/100)) AS NT1) ||'
- Mata Kuliah : ' || '&&matakuliah2' || '
- Nilai Tugas : ' || &&nilai_tugas2 || '
- Nilai UTS : '|| &&nilai_uts2 || '
- Nilai UAS : '|| &&nilai_uas2 || '
- Bobot SKS : '|| &&bobot_sks2 || '
- Nilai Total : ' || (&nilai_tugas2 * (30/100) + &nilai_uts2 * (30/100) + &nilai_uas2 * (40/100)) AS NT2 || '
- Mata Kuliah : ' || '&&matakuliah3' || '
- Nilai Tugas : ' || &&nilai_tugas3 || '
- Nilai UTS : '|| &&nilai_uts3 || '
- Nilai UAS : '|| &&nilai_uas3 || '
- Bobot SKS : '|| &&bobot_sks3 || '
- Nilai Total : ' || (&nilai_tugas3 * (30/100) + &nilai_uts3 * (30/100) + &nilai_uas3 * (40/100)) AS NT3
- from dual;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
