Nama Database: dbakademik
Tabel: kelas,guru,mapel,siswa,nilai.
Field: Asumsikan Sendiri
Query :
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `rangking` AS
select
`kelas`.`kelas` AS `kelas`,
`mapel`.`mapel` AS `mapel`,
`mapel`.`semester` AS `semester`,
`nilai`.`nis` AS `nis`,
`siswa`.`nama` AS `nama`,
`nilai`.`lokal` AS `lokal`,
avg(`nilai`.`nr`) AS `rata-rata`,
sum(`nilai`.`nuas`) AS `penjumlahan`
from
(((`nilai` join `siswa`) join `mapel`) join `kelas`)
where
((`nilai`.`kdmapel` = `mapel`.`kd_mapel`) and (`nilai`.`nis` = `siswa`.`nis`) and (`siswa`.`lokal` = `nilai`.`lokal`))
group by
`nilai`.`nis`;
Query diatas akan membenntuk sebuah tabel query baru. Intinya jika ingin melakukan penjumlahan dari sebuah field, maka anda bisa menggunakan "SUM" dan "AVG" untuk menghitung rata-rata...
Read More..
Tabel: kelas,guru,mapel,siswa,nilai.
Field: Asumsikan Sendiri
Query :
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `rangking` AS
select
`kelas`.`kelas` AS `kelas`,
`mapel`.`mapel` AS `mapel`,
`mapel`.`semester` AS `semester`,
`nilai`.`nis` AS `nis`,
`siswa`.`nama` AS `nama`,
`nilai`.`lokal` AS `lokal`,
avg(`nilai`.`nr`) AS `rata-rata`,
sum(`nilai`.`nuas`) AS `penjumlahan`
from
(((`nilai` join `siswa`) join `mapel`) join `kelas`)
where
((`nilai`.`kdmapel` = `mapel`.`kd_mapel`) and (`nilai`.`nis` = `siswa`.`nis`) and (`siswa`.`lokal` = `nilai`.`lokal`))
group by
`nilai`.`nis`;