Read 137 times | Created 2013-09-09 04:21:08 | Updated 2013-09-09 04:26:50 | | |

 

-- tabel2:
DROP TABLE IF EXISTS `kuliah`;  
CREATE TABLE IF NOT EXISTS `kuliah` (  
  `id_jadwal` int(10) unsigned NOT NULL AUTO_INCREMENT,  
  `nama_hari` CHAR(5)NOT NULL,  
  `id_jam` CHAR(10) NOT NULL, 
  `id_ruangan` CHAR(5) NOT NULL,
  `id_kelas` CHAR(5) NOT NULL,
  `kd_matkul` CHAR(25) NOT NULL,
  PRIMARY KEY (`id_jadwal`)  
) ENGINE=MyISAM; 

INSERT INTO kuliah
VALUES
(1,'Sabtu','PG01','L27','01','KBTI114309'),
(2,'Senin','PG03','R16','01','KBTI114206'),
(3,'Senin','PG02','R17','01','KBTI114308'),
(4,'Senin','PG01','R16','04','KKTI114323'),
(5,'Senin','PG02','L43','04','KBTI114105');
 
DROP TABLE IF EXISTS `jam`;  
CREATE TABLE IF NOT EXISTS `jam` (  
  `id_jam` CHAR(10) NOT NULL,  
  `jam_masuk` TIME NOT NULL,  
  `jam_keluar` TIME NOT NULL,
  PRIMARY KEY (`id_jam`)  
) ENGINE=MyISAM; 

INSERT INTO jam
VALUES
('MLM01','17:00:00','18:30:00'),
('MLM02','18:30:00','19:15:00'),
('MLM03','19:15:00','20:45:00'),
('MLM04','20:45:00','21:30:00'),
('PG01','08:00:00','09:30:00'),
('PG02','09:45:00','11:15:00'),
('PG03','11:15:00','12:00:00'),
('PG04','12:15:00','13:00:00'),
('PG05','13:00:00','14:30:00'),
('PG06','14:45:00','16:15:00'),
('PG07','16:15:00','17:00:00');


DROP TABLE IF EXISTS `ruangan`;  
CREATE TABLE IF NOT EXISTS `ruangan` (  
  `id_ruangan` CHAR(5) NOT NULL,  
  `nama_ruangan` VARCHAR(50)NOT NULL,  
  `lantai` VARCHAR(4) NOT NULL,
  PRIMARY KEY (`id_ruangan`)  
) ENGINE=MyISAM; 

INSERT INTO ruangan
VALUES
('L14','Labor 1.4','1'),
('L15','Labor 1.5','1'),
('L27','Labor 2.7','2'),
('L32','Labor 3.2','3'),
('L41','Labor 4.1','4'),
('L43','Labor 4.3','4'),
('R16','Ruangan 1.6','1'),
('R17','Ruangan 1.7','1'),
('R210','Ruangan 2.10','2'),
('R310','Ruangan 3.10','3'),
('R34','Ruangan 3.4','3'),
('R35','Ruangan 3.5','3'),
('R36','Ruangan 3.6','3'),
('R37','Ruangan 3.7','3'),
('R38','Ruangan 3.8','3'),
('R39','Ruangan 3.9','3'),
('R42','Ruangan 4.2','4'),
('R44','Ruangan 4.4','4'),
('R45','Ruangan 4.5','4'),
('R46','Ruangan 4.6','4'),
('R47','Ruangan 4.7','4'),
('R48','Ruangan 4.8','4'),
('R49','Ruangan 4.9','4');

DROP TABLE IF EXISTS `kelas`;  
CREATE TABLE IF NOT EXISTS `kelas` (  
  `id_kelas` CHAR(10) NOT NULL,  
  `nama_kelas` VARCHAR(50)NOT NULL,  
  `jml_mhs` INT(4) NOT NULL,
  `jurusan` CHAR(3) NOT NULL,
  PRIMARY KEY (`id_kelas`)  
) ENGINE=MyISAM;

INSERT INTO kelas
VALUES
('01','01PT5',30,'TI'),
('02','02PT5',30,'TI'),
('04','04PT4',40,'TI');

-- jam yg belum dipakai
SELECT b.*
FROM kuliah a
RIGHT JOIN jam b USING(id_jam ) 
WHERE a.id_jam IS NULL;

-- ruangan yg belum dipakai
SELECT c.*
FROM kuliah a
RIGHT JOIN ruangan c USING(id_ruangan) 
WHERE a.id_jam IS NULL;

-- kelas yg belum dipakai
SELECT d.*
FROM kuliah a
RIGHT JOIN kelas d USING(id_kelas) 
WHERE a.id_jam IS NULL;

-- jam,ruangan,kelas yg belum terpakai
SELECT b.*,c.*,d.*
FROM (jam b,ruangan c,kelas d)
LEFT JOIN kuliah a ON (a.id_jam=b.id_jam AND c.id_ruangan=a.id_ruangan AND d.id_kelas=a.id_kelas)
WHERE a.id_jadwal IS NULL;

-- jam,ruangan,kelas yg sudah terpakai
SELECT b.*,c.*,d.*
FROM (jam b,ruangan c,kelas d)
LEFT JOIN kuliah a ON (a.id_jam=b.id_jam AND c.id_ruangan=a.id_ruangan AND d.id_kelas=a.id_kelas)
WHERE a.id_jadwal IS NOT NULL;