Read 539 times | Created 2014-04-22 02:06:29 | Updated 2014-04-22 02:06:29 | | |

-- -
USE `test`;

DROP TABLE IF EXISTS `mapel`;
CREATE TABLE IF NOT EXISTS `mapel` (
 `id_mp` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `nama_mp` varchar(30) NOT NULL,
 PRIMARY KEY(`id_mp`)
) ENGINE=MyISAM;

INSERT INTO `mapel`
VALUES(1,'Ujian Masuk');

DROP TABLE IF EXISTS `ujian`;
CREATE TABLE IF NOT EXISTS `ujian` (
 `id_ujian` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `id_mp` int(11) unsigned NOT NULL,
 `nama_ujian` varchar(30) NOT NULL,
 PRIMARY KEY(`id_ujian`)
) ENGINE=MyISAM;

INSERT INTO `ujian`
VALUES
(1,1,'Bahasa Inggris'),
(2,1,'Bahasa Indonesia'),
(3,1,'Matematika'),
(4,1,'IPA Terpadu');

DROP TABLE IF EXISTS `siswa`;
CREATE TABLE IF NOT EXISTS `siswa` (
 `nis` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `nama` varchar(30) NOT NULL,
 PRIMARY KEY(`nis`)
) ENGINE=MyISAM;

INSERT INTO `siswa`
VALUES
(1,'Agus'),
(2,'Budi'),
(3,'Carlie'),
(4,'Dewi'),
(5,'Endah'),
(6,'Fitri'),
(7,'Gita');

DROP TABLE IF EXISTS `nilai`;
CREATE TABLE IF NOT EXISTS `nilai` (
 `id_nilai` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `id_ujian` int(11) unsigned NOT NULL,
 `id_user` int(11) unsigned NOT NULL,
 `nilai` int(11) unsigned  NOT NULL,
 PRIMARY KEY(`id_nilai`)
) ENGINE=MyISAM;

INSERT INTO `nilai`
VALUES
(1,1,1,40),
(2,1,2,100),
(3,1,3,80),
(4,1,4,60);

SELECT 
  a.nis,
  a.nama,
  SUM(IF(d.id_ujian=1,d.nilai,0)) AS ujian_1,
  SUM(IF(d.id_ujian=2,d.nilai,0)) AS ujian_2,
  SUM(IF(d.id_ujian=3,d.nilai,0)) AS ujian_3,
  SUM(IF(d.id_ujian=4,d.nilai,0)) AS ujian_4,
  SUM(IF(ISNULL(d.nilai),0,d.nilai))/4 as rerata
FROM siswa a
  LEFT JOIN ( 
    SELECT b.*
    FROM nilai b
    LEFT JOIN ujian c ON b.id_ujian=c.id_ujian
    WHERE c.id_mp='1'
  ) d  ON a.nis=d.id_user
GROUP BY a.nis
ORDER BY rerata DESC;