Aplikasi php mysql

1

Program Studi Sistem Informasi dan Teknik IndustriProgram Studi Sistem Informasi dan Teknik Industri

2

FAKULTASKode_FNm_Fakultas

MATAKULIAHKode_mtkNama_MtkSks Kode_f

MAHASISWANobpNamaKode_fPin

NILAINPMKode_MtkNILAi_KuisNilai_PrNilai_UTSNilai_UAS

3

Create Table Tfakultas( Kode_f Char(16) NOT NULL PRIMARY Key, Nama_f Varchar2(100), Nama_PS char(20), Status char(45), NoSK char(35));

Create Table Tmhs( Nobp Char(16) NOT NULL PRIMARY Key, Nama Varchar2(100), Kelas char(15), Kode_f char(3) References Tfakultas(Kode_f));

4

Create Table Tmtk( Kode_MK Char(10) NOT NULL PRIMARY Key, Nama_MK Varchar2(100), Sks number(2), Prasyarat1 char(10), Prasyarat2 char(10), Prasyarat3 char(10));

Create Table tNilai( Kode_MK Char(10) References tmtk(kode_mk), Nobp Char(16) References tmhs(nobp), Nilai_KUIS number(5,2), Nilai_PR number(5,2), Nilai_UTS number(5,2), Nilai_UAS number(5,2), constraint pk_nilai PRIMARY KEY (kode_mk, Nobp));

5

Insert into Tmtk values ('KKKI53001','Apl. DBServer',2,NULL,NULL,NULL);Insert into Tmtk values ('KKKI52002','Perbankan',2,NULL,NULL,NULL);Insert into Tmtk values ('KKKI53011','Multiuser',2,NULL,NULL,NULL);Insert into Tmtk values ('KKKI53012','Mnj. Sains',2,NULL,NULL,NULL);Insert into Tmtk values ('KKKI52005','Peng. Sistem Informasi',2,NULL,NULL,NULL);Insert into Tmtk values ('KKKI52006','Prak. Sistem Operasi Jaringan',2,NULL,NULL,NULL);Insert into Tmtk values ('KKKI53007','Teknik Kompilasi',2,NULL,NULL,NULL);

Insert into tfakultas values ('261','Ilmu Komputer','Sistem Informasi','Terakreditasi','001/BAN-PT/S1/SK/2009');Insert into tfakultas values ('262','Ilmu Komputer','Sistem Komputer','Terakreditasi','056/BAN-PT/S1/SK/2006');Insert into tfakultas values ('260','Ilmu Komputer','Mnj. Informatika','Terakreditasi','089/BAN-PT/D3/SK/2006');Insert into tfakultas values ('263','Ilmu Komputer','Teknik Informatika','Terdaftar','-');Insert into tfakultas values ('511','Ekonomi','S1-Akuntansi', 'Terakreditasi','056/BAN-PT/S1/SK/2006');Insert into tfakultas values ('531','Ekonomi','S1-Manajemen‘ ,'Terakreditasi','089/BAN-PT/D3/SK/2006');Insert into tfakultas values ('751','Psikologi','Psikologi' ,'Terakreditasi','089/BAN-PT/S1/SK/2009');Insert into tfakultas values ('425','Teknologi Industri','Teknik Industri' ,'Terdaftar','-');Insert into tfakultas values ('433','Sipil Dan Perencanaan','Teknik Sipil,'Terdaftar','-');Insert into tfakultas values ('911','Desain Komunikasi Visual','Desain Komunikasi Visual' ,'Terdaftar','-');Insert into tfakultas values ('811','Pascasarjana','S2-Magister Manajemen' ,'Terakreditasi','089/BAN-PT/S2/SK/2008');Insert into tfakultas values ('812','Pascasarjana','S2-Magister Komputer' ,'Terakreditasi','089/BAN-PT/S2/SK/2008');

6

Insert into tmhs values ('07101152610254','Widya','4SI-6','261');Insert into tmhs values ('07101152610255','Atika','4SI-6','261');Insert into tmhs values ('07101152610256','Danny','4SI-6','261');Insert into tmhs values ('07101152610257','Debie','4SI-6','261');Insert into tmhs values ('07101152610258','Thia','4SI-6','261');Insert into tmhs values ('07101152610259','Susy','4SI-6','261');Insert into tmhs values ('07101152610260','Egil','4SI-6','261');

7

Insert into tnilai values ('KKKI53001','07101152610260',80,75,60,60);Insert into tnilai values ('KKKI52002','07101152610260',80,85,65,75);Insert into tnilai values ('KKKI53011','07101152610260',70,55,70,80);Insert into tnilai values ('KKKI53012','07101152610260',80,65,80,90);Insert into tnilai values ('KKKI52005','07101152610260',90,75,90,60);Insert into tnilai values ('KKKI52006','07101152610260',60,65,80,70);Insert into tnilai values ('KKKI53001','07101152610254',80,75,60,60);Insert into tnilai values ('KKKI52002','07101152610254',80,85,65,75);Insert into tnilai values ('KKKI53011','07101152610254',70,55,70,80);Insert into tnilai values ('KKKI53012','07101152610254',80,65,80,90);Insert into tnilai values ('KKKI52005','07101152610254',90,75,90,60);Insert into tnilai values ('KKKI52006','07101152610254',60,65,80,70);Insert into tnilai values ('KKKI53007','07101152610254',70,55,90,80);Insert into tnilai values ('KKKI53001','07101152610255',80,75,60,60);Insert into tnilai values ('KKKI52002','07101152610255',80,85,65,75);Insert into tnilai values ('KKKI53011','07101152610255',70,55,70,80);Insert into tnilai values ('KKKI53012','07101152610255',80,65,80,90);Insert into tnilai values ('KKKI52005','07101152610255',90,75,90,60);Insert into tnilai values ('KKKI52006','07101152610255',60,65,80,70);Insert into tnilai values ('KKKI53007','07101152610255',70,55,90,80);Insert into tnilai values ('KKKI53001','07101152610256',80,75,60,60);

8

Insert into tnilai values ('KKKI52002','07101152610256',80,85,65,75);Insert into tnilai values ('KKKI53011','07101152610256',70,55,70,80);Insert into tnilai values ('KKKI53012','07101152610256',80,65,80,90);Insert into tnilai values ('KKKI52005','07101152610256',90,75,90,60);Insert into tnilai values ('KKKI52006','07101152610256',60,65,80,70);Insert into tnilai values ('KKKI53007','07101152610256',70,55,90,80);Insert into tnilai values ('KKKI53001','07101152610257',80,75,60,60);Insert into tnilai values ('KKKI52002','07101152610257',80,85,65,75);Insert into tnilai values ('KKKI53011','07101152610257',70,55,70,80);Insert into tnilai values ('KKKI53012','07101152610257',80,65,80,90);Insert into tnilai values ('KKKI52005','07101152610257',90,75,90,60);Insert into tnilai values ('KKKI52006','07101152610257',60,65,80,70);Insert into tnilai values ('KKKI53007','07101152610257',70,55,90,80);Insert into tnilai values ('KKKI53001','07101152610258',80,75,60,60);Insert into tnilai values ('KKKI52002','07101152610258',80,85,65,75);Insert into tnilai values ('KKKI53011','07101152610258',70,55,70,80);Insert into tnilai values ('KKKI53012','07101152610258',80,65,80,90);Insert into tnilai values ('KKKI52005','07101152610258',90,75,90,60);Insert into tnilai values ('KKKI52006','07101152610258',60,65,80,70);Insert into tnilai values ('KKKI53007','07101152610258',70,55,90,80);commit;

9

select t1.nobp, nama, t2.kode_mk,nama_mk,sks,nilai_kuis, nilai_pr, nilai_uts, nilai_uas, (nilai_kuis+nilai_pr+nilai_uts+nilai_uas)/4 as Ratarata from tmhs t1, tmtk t2, tnilai t3 where t1.nobp=t3.nobp and t2.kode_mk=t3.kode_mk and t1.nobp in ('07101152610254','07101152610260');

1. Tampilkan nobp, nama, kode_mk,nama_mk,sks,nilai_kuis, nilai_pr, nilai_uts, nilai_uas, dan rata-rata =nilai_kuis+nilai_pr+nilai_uts+nilai_uas)/4 Ratarata dari tabel tmhs t1, tmtk t2, tnilai t3 untuk Nonp 07101152610254 dan 07101152610260

10

Create view contohvw as select t1.nobp, nama, t2.kode_mk,nama_mk,sks,nilai_kuis, nilai_pr, nilai_uts, nilai_uas, (0.10*nilai_kuis)+(0.20*nilai_pr)+(0.30*nilai_uts)+(0.40*nilai_uas) as Ratarata from tmhs t1, tmtk t2, tnilai t3 where t1.nobp=t3.nobp and t2.kode_mk=t3.kode_mk and t1.nobp in ('07101152610254','07101152610260');

2. Tampilkan nobp, nama, t2.kode_mk,nama_mk,sks,nilai_kuis, nilai_pr, nilai_uts, nilai_uas, ratarata= (10%*nilai_kuis) + (20%*nilai_pr) + (30%*nilai_uts) + (40%*nilai_uas) dari table tmhs, tmtk dan tnilai untuk Nobp 07101152610254 dan 07101152610260

11

Create view contohvw select t1.nobp, nama, t2.kode_mk,nama_mk,sks,nilai_kuis, nilai_pr, nilai_uts, nilai_uas, (0.10*nilai_kuis)+(0.20*nilai_pr)+(0.30*nilai_uts)+(0.40*nilai_uas) as Ratarata from tmhs t1, tmtk t2, tnilai t3 where t1.nobp=t3.nobp and t2.kode_mk=t3.kode_mk and t2.nama_mk like '%an%'

3. Tampilkan nobp, nama, t2.kode_mk,nama_mk,sks,nilai_kuis, nilai_pr, nilai_uts, nilai_uas, ratarata= (10%*nilai_kuis) + (20%*nilai_pr) + (30%*nilai_uts) + (40%*nilai_uas) dari table tmhs, tmtk dan tnilai Untuk nama_mk yang mengandung karakter ‘an’

12

13

Create view contohvw select t2.kd_cust,nama, t1.kd_brg, nama_brg,tglj,t1.harga,jml, t1.harga*jml as TotalHarga, (0.05*t1.harga*jml) as Discount, (t1.harga*jml)-(0.05*t1.harga*jml) as TotalBersih from barang t1, customer t2, transaksi t3 where t1.kd_brg=t3.kd_brg and t2.kd_cust=t3.kd_cust;

1. Tampilkan kd_k,nama, kd_m, jenis,tglj, jml, harga, TotalHarga= jml*t1.harga, discount=10%*jml*harga, TotalBersih=TotalHarga-Discount dari table Mobil, konsumen, dan Jual

14

Create view contohvw select t2.kd_cust,nama, t1.kd_brg, nama_brg,tglj,t1.harga,jml, t1.harga*jml as TotalHarga, (0.05*t1.harga*jml) as Discount, (t1.harga*jml)-(0.05*t1.harga*jml) as TotalBersih from barang t1, customer t2, transaksi t3 where t1.kd_brg=t3.kd_brg and t2.kd_cust=t3.kd_cust and t2.kd_cust in ('K0002','K0005');

2. Tampilkan kd_k,nama, kd_m, jenis,tglj, jml, harga, TotalHarga= jml*t1.harga, discount=10%*jml*harga, TotalBersih=TotalHarga-Discount dari table Mobil, konsumen, dan Jual untuk Konsumen 'K0001‘ dan 'K0005'

15

Create view contohvw select t2.kd_cust,nama, t1.kd_brg, nama_brg,tglj,t1.harga,jml, t1.harga*jml as TotalHarga, (0.05*t1.harga*jml) as Discount, (t1.harga*jml)-(0.05*t1.harga*jml) as TotalBersih from barang t1, customer t2, transaksi t3 where t1.kd_brg=t3.kd_brg and t2.kd_cust=t3.kd_cust and tglj between '02-mar-05' and '05-dec-05' order by tglj;

3. Tampilkan kd_k,nama, kd_m, jenis,tglj, jml, harga, TotalHarga= jml*t1.harga, discount=10%*jml*harga, TotalBersih=TotalHarga-Discount dari table Mobil, konsumen, dan Jual untuk transaksi penjualan tanggal '02-mar-05' sampai dengan '05-dec-05' dan urutkan berdasarkan tanggal jual

16

17

select t2.kd_k,nama, t1.kd_m, jenis,tglj, jml, t1.harga, jml*t1.harga as TotalHarga, (0.10*jml*t1.harga) as Discount, (jml*t1.harga)-(0.10*jml*t1.harga) as TotalBersihfrom Mobil t1, konsumen t2, Jual t3 where t1.kd_m=t3.kd_m and t2.kd_k=t3.kd_k;

1. Tampilkan kd_k,nama, kd_m, jenis,tglj, jml, harga, TotalHarga= jml*t1.harga, discount=10%*jml*harga, TotalBersih=TotalHarga-Discount dari table Mobil, konsumen, dan Jual

18

select t2.kd_k,nama, t1.kd_m, jenis,tglj, jml, t1.harga, jml*t1.harga as TotalHarga, (0.10*jml*t1.harga) as Discount, (jml*t1.harga)-(0.10*jml*t1.harga) as TotalBersihfrom Mobil t1, konsumen t2, Jual t3 where t1.kd_m=t3.kd_m and t2.kd_k=t3.kd_k and t3.kd_k in ('K0001','K0005');

2. Tampilkan kd_k,nama, kd_m, jenis,tglj, jml, harga, TotalHarga= jml*t1.harga, discount=10%*jml*harga, TotalBersih=TotalHarga-Discount dari table Mobil, konsumen, dan Jual untuk Konsumen 'K0001‘ dan 'K0005'

19

select t2.kd_k,nama, t1.kd_m, jenis,tglj, jml, t1.harga, jml*t1.harga as TotalHarga, (0.10*jml*t1.harga) as Discount, (jml*t1.harga)-(0.10*jml*t1.harga) as TotalBersihfrom Mobil t1, konsumen t2, Jual t3 where t1.kd_m=t3.kd_m and t2.kd_k=t3.kd_k and tglj between '02-mar-05' and '05-dec-05' order by tglj;

3. Tampilkan kd_k,nama, kd_m, jenis,tglj, jml, harga, TotalHarga= jml*t1.harga, discount=10%*jml*harga, TotalBersih=TotalHarga-Discount dari table Mobil, konsumen, dan Jual untuk transaksi penjualan tanggal '02-mar-05' sampai dengan '05-dec-05' dan urutkan berdasarkan tanggal jual

20

21

select t2.kodevcd, judul,t1.kodepenyewa, nama, hargasewa, tglsewa, tglkembali from penyewa t1, vcd t2, peminjaman t3 where t1.kodepenyewa=t3.kodepenyewa and t2.kodevcd=t3.kodevcd and t1.kodepenyewa in ('P-001','P-005') order by t2.kodevcd;

1. Tampilkan kodevcd, judul,t1.kodepenyewa, nama, hargasewa, tglsewa, tglkembali dari table penyewa , vcd , dan peminjaman untuk kdpenyewa 'P-001‘ dan 'P-005') urutkan berdasarkan kodevcd;

22

select t2.kodevcd, judul,t1.kodepenyewa, nama, hargasewa, tglsewa from penyewa t1, vcd t2, peminjaman t3 where t1.kodepenyewa=t3.kodepenyewa and t2.kodevcd=t3.kodevcd and tglsewa between '01-Mar-2006' and '01-dec-2006' order by t2.kodevcd;

2. Tampilkan kodevcd, judul,t1.kodepenyewa, nama, hargasewa, tglsewa, tglkembali dari table penyewa , vcd , dan peminjaman untuk tanggal sewa mulai '01-Mar-2006' sampai dengan '01-dec-2006' diurutkan berdasarkan kodevcd;

23

select t1.kodepenyewa, nama, sum(hargasewa) as TotalBayar from penyewa t1, vcd t2, peminjaman t3 where t1.kodepenyewa = t3.kodepenyewa and t2.kodevcd=t3.kodevcd and tglsewa between '01-jan-2006' and '01-dec-2006' group by t1.kodepenyewa, nama

3. Tampilkan kodepenyewa, nama, sum(hargasewa) dari table penyewa , vcd , dan peminjaman yang disewa tanggal sewa mulai '01-jan-2006' sampai dengan '01-dec-2006'

24

25

Select b.npm, b.nama_mhs, a.kd_b, c.judul, a.tgl_p, a.tgl_k, a.tgl_hrs_k, (a.tgl_k)-(a.tgl_hrs_k) as Lewat, TO_NUMBER((a.tgl_k)-(a.tgl_hrs_k))*1000 as DendaFrom peminjaman1 a, mahasiswa1 b, buku c Where a.nobp = b.npm AND a.kd_b=c.kd_b and b.npm between '07101152610003' and '07101152610005' order by b.npm

1. Tampilkan npm, b.nama_mhs, a.kd_b, c.judul, a.tgl_p, a.tgl_k, a.tgl_hrs_k, lewat=(a.tgl_k)-(a.tgl_hrs_k), denda setiap hari Rp.1000,- dari tabel peminjaman1, mahasiswa1, dan buku Untuk Npm mulai '07101152610003' sampai dengan '07101152610005' dan urutkan berdasarkan npm

26

Select b.npm, b.nama_mhs, a.kd_b, c.judul, a.tgl_p, a.tgl_k, a.tgl_hrs_k, (a.tgl_k)-(a.tgl_hrs_k) as Lewat, TO_NUMBER((a.tgl_k)-(a.tgl_hrs_k))*1000 as Denda From peminjaman1 a, mahasiswa1 b, buku c Where a.nobp = b.npm AND a.kd_b=c.kd_b and tgl_p between '11-may-09' and '13-may-09' and (a.tgl_k)-(a.tgl_hrs_k)>0 order by b.npm

2. Tampilkan npm, b.nama_mhs, a.kd_b, c.judul, a.tgl_p, a.tgl_k, a.tgl_hrs_k, lewat=(a.tgl_k)-(a.tgl_hrs_k), denda setiap hari Rp.1000,- dari tabel peminjaman1, mahasiswa1, dan buku Untuk Tanggal peminjaman mulai '11-may-09' and '13-may-09' sampai dengan yang terlambat mengembalikan buku dan urutkan berdasarkan npm

27

Select b.npm, b.nama_mhs, a.kd_b, c.judul, a.tgl_p, a.tgl_k, a.tgl_hrs_k, (a.tgl_k)-(a.tgl_hrs_k) as Lewat, TO_NUMBER((a.tgl_k)-(a.tgl_hrs_k))*1000 as DendaFrom peminjaman1 a, mahasiswa1 b, buku c Where a.nobp = b.npm AND a.kd_b=c.kd_b and tgl_p between '14-may-09' and '17-may-09' and (a.tgl_k)-(a.tgl_hrs_k)>=0 order by b.npm

3. Tampilkan npm, b.nama_mhs, a.kd_b, c.judul, a.tgl_p, a.tgl_k, a.tgl_hrs_k, lewat=(a.tgl_k)-(a.tgl_hrs_k), denda setiap hari Rp.1000,- dari tabel peminjaman1, mahasiswa1, dan buku Untuk Tanggal peminjaman mulai '14-may-09' and '17-may-09' sampai dengan yang terlambat mengembalikan buku dan urutkan berdasarkan npm

28

29

select rute.kd_rute, rute, pesawat.kd_pesawat, pelanggan.kd_plg, nm_plg, hargafrom rute, pesawat, pelanggan, tiket where pelanggan.kd_plg = tiket.kd_plg and rute.kd_rute=tiket.kd_rute and pesawat.kd_rute=rute.kd_rute and rute.kd_rute between '02' and '03' order by pesawat.kd_pesawat

1. Tampilkan kd_rute, rute, kd_pesawat, kd_plg, nm_plg, harga dari tabel rute, pesawat,pelanggan, dan tiket untuk kd_rute '02' dan '03' urutkan berdasarkan kd_pesawat

30

select rute.kd_rute, rute, pesawat.kd_pesawat, pelanggan.kd_plg, nm_plg, hargafrom rute, pesawat, pelanggan, tiket where pelanggan.kd_plg = tiket.kd_plg and rute.kd_rute=tiket.kd_rute and pesawat.kd_rute=rute.kd_rute and pesawat.kd_pesawat='M-3' order by pesawat.kd_pesawat

2. Tampilkan kd_rute, rute, kd_pesawat, kd_plg, nm_plg, harga dari tabel rute, pesawat,pelanggan, dan tiket untuk kd_Pesawat ='M-3' urutkan berdasarkan kd_pesawat

31

select rute.kd_rute, rute, pesawat.kd_pesawat, sum(harga) as TotalBiayafrom rute, pesawat, pelanggan, tiket where pelanggan.kd_plg = tiket.kd_plg and rute.kd_rute=tiket.kd_rute and pesawat.kd_rute=rute.kd_rute group by rute.kd_rute, rute, pesawat.kd_pesawat

3. Tampilkan kd_rute, rute, kd_pesawat, sum(harga) dari tabel rute, pesawat, pelanggan, dan tiket urutkan berdasarkan kd_pesawat

32

33

select kabupaten.kd_Kab, Nm_Kab, pangan.Kd_Pangan, Nm_Pangan, satuan, klmpk_pangan, stock, bulan, produksi, jumlah_sedia, kebutuhan, jumlah_minta, (stock+produksi)-(kebutuhan+jumlah_minta) as Sisa from pangan, kabupaten, ketersediaan where pangan.kd_pangan=ketersediaan.kd_pangan and kabupaten.kd_kab=ketersediaan.kd_kab and kabupaten.kd_Kab in ('K001','K004','K002') order by kabupaten.kd_Kab

1. Tampilkan kd_Kab, Nm_Kab,Kd_Pangan, Nm_Pangan, satuan, klmpk_pangan, stock, bulan, produksi, jumlah_sedia, kebutuhan, jumlah_minta, dan sisa (stock+produksi)-(kebutuhan+jumlah_minta) dari tabel pangan, kabupaten, dan ketersediaan untuk kd_Kab 'K001','K004', dan 'K002‘ urutkan berdasarkan kd_Kab

34

select kabupaten.kd_Kab, Nm_Kab, pangan.Kd_Pangan, Nm_Pangan, satuan, klmpk_pangan, stock, bulan, produksi, jumlah_sedia, kebutuhan, jumlah_minta, (stock+produksi)-(kebutuhan+jumlah_minta) as Sisa from pangan, kabupaten, ketersediaan where pangan.kd_pangan=ketersediaan.kd_pangan and kabupaten.kd_kab=ketersediaan.kd_kab and bulan between '01-feb-09' and '01-aug-09' order by kabupaten.kd_Kab

2. Tampilkan kd_Kab, Nm_Kab,Kd_Pangan, Nm_Pangan, satuan, klmpk_pangan, stock, bulan, produksi, jumlah_sedia, kebutuhan, jumlah_minta, dan sisa (stock+produksi)-(kebutuhan+jumlah_minta) dari tabel pangan, kabupaten, dan ketersediaan untuk bulan Februari s/d agustus 2009

35

select kabupaten.kd_Kab, Nm_Kab, sum(stock), sum(produksi), sum(jumlah_sedia), sum(kebutuhan), sum(jumlah_minta), sum((stock+produksi)-(kebutuhan+jumlah_minta)) as Sisa from pangan, kabupaten, ketersediaanwhere pangan.kd_pangan=ketersediaan.kd_pangan and kabupaten.kd_kab = ketersediaan.kd_kab group by kabupaten.kd_Kab, Nm_Kab order by kabupaten.kd_Kab

3. Tampilkan kabupaten.kd_Kab, Nm_Kab, sum(stock), sum(produksi), sum(jumlah_sedia), sum(kebutuhan), sum(jumlah_minta), sum((stock+produksi)-(kebutuhan+jumlah_minta)) dari tabel pangan, kabupaten, dan ketersediaan urutkan berdasarkan kd_kab

36