Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE `test`;
- DROP TABLE IF EXISTS `tbl_anggota`;
- CREATE TABLE IF NOT EXISTS `tbl_anggota` (
- `id_anggota` int(11) unsigned NOT NULL AUTO_INCREMENT,
- `nama` VARCHAR(30) NOT NULL,
- PRIMARY KEY (`id_anggota`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
- INSERT INTO `tbl_anggota`
- (`nama`)
- VALUES
- ('Agus Pambudi'),
- ('Budiati Wijayanti'),
- ('Carla Hapsari'),
- ('Dedi Suhendra');
- DROP TABLE IF EXISTS `tbl_buku`;
- CREATE TABLE IF NOT EXISTS `tbl_buku` (
- `id_buku` int(11) unsigned NOT NULL AUTO_INCREMENT,
- `judul` VARCHAR(50) NOT NULL,
- PRIMARY KEY (`id_buku`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
- INSERT INTO `tbl_buku`
- (`judul`)
- VALUES
- ('Pemrograman PHP dan MySQL'),
- ('Administrasi Database MySQL'),
- ('Pemrograman PHP untuk Pemula'),
- ('PHP dan AJaX');
- DROP TABLE IF EXISTS `tbl_pinjam`;
- CREATE TABLE IF NOT EXISTS `tbl_pinjam` (
- `id_pinjam` int(11) unsigned NOT NULL AUTO_INCREMENT,
- `tgl_pinjam` DATE NOT NULL,
- `tgl_kembali` DATE NOT NULL DEFAULT 0,
- `id_buku` smallint(5) unsigned NOT NULL,
- `id_anggota` smallint(5) unsigned NOT NULL,
- PRIMARY KEY (`id_pinjam`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
- INSERT INTO `tbl_pinjam`
- (`tgl_pinjam`,`id_buku`,`id_anggota`)
- VALUES
- ('2013-11-22',2,4),
- ('2013-11-24',2,1),
- ('2013-11-24',3,1),
- ('2013-11-25',1,2),
- ('2013-11-28',2,3),
- ('2013-11-30',3,2),
- ('2013-12-01',2,4),
- ('2013-12-02',4,4),
- ('2013-12-05',4,1);
- // menampilkan jumlah peminjaman buku berdasarkan judul buku
- SELECT b.judul,COUNT(a.id_buku) AS jml, MONTH(a.tgl_pinjam) AS bln
- FROM
- tbl_pinjam a
- JOIN tbl_buku b USING(id_buku)
- GROUP BY a.id_buku,bln
- ORDER BY bln;
- // menampilkan jumlah peminjaman buku terbanyak tiap bulan berdasarkan judul buku
- SELECT c.judul,MAX(c.jml) AS jml,c.bln
- FROM
- (
- SELECT b.judul,COUNT(a.id_buku) AS jml, MONTH(a.tgl_pinjam) AS bln
- FROM
- tbl_pinjam a
- JOIN tbl_buku b USING(id_buku)
- GROUP BY a.id_buku,bln
- ORDER BY bln,jml DESC
- )c
- GROUP BY c.bln;
- // menampilkan jumlah peminjaman buku berdasarkan anggota
- SELECT b.nama,COUNT(a.id_anggota) AS jml, MONTH(a.tgl_pinjam) AS bln
- FROM
- tbl_pinjam a
- JOIN tbl_anggota b USING(id_anggota)
- GROUP BY a.id_anggota,bln
- ORDER BY bln,b.nama;
- // menampilkan jumlah peminjaman buku terbanyak tiap bulan berdasarkan anggota
- SELECT c.nama,MAX(c.jml) AS jml,c.bln
- FROM
- (
- SELECT b.nama,COUNT(a.id_anggota) AS jml, MONTH(a.tgl_pinjam) AS bln
- FROM
- tbl_pinjam a
- JOIN tbl_anggota b USING(id_anggota)
- GROUP BY a.id_anggota,bln
- ORDER BY bln,jml DESC
- )c
- GROUP BY c.bln;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement