mysql> create table mahasiswa ( -> nim char(10),
-> nama_mhs char(14),
-> tmpt_lhr char(15),
-> tgl_lhr date,
-> alamat char(25));
Query OK, 0 rows affected (0.11 sec)
mysql> insert into mahasiswa values
-> ('M001','sahrun','praya','1989/12/21','Jl.pejanggik No.2'),
-> ('M002','yanti','gerung','1987/04/14','Jl.KH.ismail No.21'),
-> ('M003','raeba','tanjung','1988/07/07','Jl.Raden Rya No.34'),
-> ('M004','azhar','mataram','1989/03/24','Jl.pahlawan No.56');
Query OK, 4 rows affected (0.08 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select *from mahasiswa;
+------+----------+----------+------------+--------------------+
| nim | nama_mhs | tmpt_lhr | tgl_lhr | alamat |
+------+----------+----------+------------+--------------------+
| M001 | sahrun | praya | 1989-12-21 | Jl.pejanggik No.2 |
| M002 | yanti | gerung | 1987-04-14 | Jl.KH.ismail No.21 |
| M003 | raeba | tanjung | 1988-07-07 | Jl.Raden Rya No.34 |
| M004 | azhar | mataram | 1989-03-24 | Jl.pahlawan No.56 |
+------+----------+----------+------------+--------------------+
4 rows in set (0.03 sec)
mysql> create table jurusan (
-> kd_jur char(6),
-> nama_jur char(25));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into jurusan values
-> ('J001','manajemen informatika'),
-> ('J002','komputer akuntansi'),
-> ('J003','teknik komputer');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select *from jurusan;
+--------+-----------------------+
| kd_jur | nama_jur |
+--------+-----------------------+
| J001 | manajemen informatika |
| J002 | komputer akuntansi |
| J003 | teknik komputer |
+--------+-----------------------+
3 rows in set (0.00 sec)
mysql> create table spp (
-> no_nota char(6),
-> angkatan char(5),
-> tgl_byr date,
-> nim char(6),
-> kd_jur char(6),
-> spp int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into spp values
-> ('N002','2006','2007/12/23','M003','J002','500000'),
-> ('N003','2005','2006/07/12','M004','J001','250000'),
-> ('N005','2003','2005/01/12','M002','J003','350000'),
-> ('N001','2002','2004/06/25','M004','J001','250000'),
-> ('N006','2004','2006/11/27','M002','J003','350000'),
-> ('N004','2007','2009/12/09','M001','J001','750000');
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select *from spp;
+---------+----------+------------+------+--------+--------+
| no_nota | angkatan | tgl_byr | nim | kd_jur | spp |
+---------+----------+------------+------+--------+--------+
| N002 | 2006 | 2007-12-23 | M003 | J002 | 500000 |
| N003 | 2005 | 2006-07-12 | M004 | J001 | 250000 |
| N005 | 2003 | 2005-01-12 | M002 | J003 | 350000 |
| N001 | 2002 | 2004-06-25 | M004 | J001 | 250000 |
| N006 | 2004 | 2006-11-27 | M002 | J003 | 350000 |
| N004 | 2007 | 2009-12-09 | M001 | J001 | 750000 |
+---------+----------+------------+------+--------+--------+
6 rows in set (0.00 sec)
1.mysql> select nama_mhs,nama_jur from
-> mahasiswa natural join spp natural join jurusan
-> where spp.kd_jur=jurusan.kd_jur and spp.nim=mahasiswa.nim
-> and spp>=300000 and nama_jur in('manajemen informatika','teknik komputer')
-> order by nama_mhs asc;
+----------+-----------------------+
| nama_mhs | nama_jur |
+----------+-----------------------+
| sahrun | manajemen informatika |
| yanti | teknik komputer |
| yanti | teknik komputer |
+----------+-----------------------+
3 rows in set (0.02 sec)
2.mysql> select nama_mhs,if(nama_jur='manajemen informatika','MI',if(nama_jur='komputer akuntansi','KA','TK')) as jur,
-> if(angkatan=2004,spp*0.12,spp*0.15)as potongan_spp from
-> mahasiswa , spp , jurusan
-> where spp.kd_jur=jurusan.kd_jur and spp.nim=mahasiswa.nim
-> and nama_mhs like ('%N%');
+----------+-----+--------------+
| nama_mhs | jur | potongan_spp |
+----------+-----+--------------+
| yanti | TK | 52500.00 |
| yanti | TK | 42000.00 |
| sahrun | MI | 112500.00 |
+----------+-----+--------------+
3 rows in set (0.02 sec)
3. mysql> select nama_mhs,nama_jur,angkatan,avg(spp) as Rata_SPP,sum(spp) as Total_SPP,count(spp) as jumlah from
-> mahasiswa , spp , jurusan
-> where spp.kd_jur=jurusan.kd_jur and spp.nim=mahasiswa.nim
-> and spp between 300000 and 600000 group by angkatan;
+----------+--------------------+----------+-------------+-----------+--------+
| nama_mhs | nama_jur | angkatan | Rata_SPP | Total_SPP | jumlah |
+----------+--------------------+----------+-------------+-----------+--------+
| yanti | teknik komputer | 2003 | 350000.0000 | 350000 | 1 |
| yanti | teknik komputer | 2004 | 350000.0000 | 350000 | 1 |
| raeba | komputer akuntansi | 2006 | 500000.0000 | 500000 | 1 |
+----------+--------------------+----------+-------------+-----------+--------+
3 rows in set (0.03 sec)
4. mysql> select nama_mhs,tgl_byr,nama_jur,sum(spp) as Tot_SPP,max(spp) as SPP_Tertinggi,min(spp) as SPP_Terendah from
-> mahasiswa , spp , jurusan
-> where spp.kd_jur=jurusan.kd_jur and spp.nim=mahasiswa.nim
-> and tgl_byr between '2006/01/01' and '2009/12/30'
-> group by no_nota order by nama_mhs asc;
+----------+------------+-----------------------+---------+---------------+--------------+
| nama_mhs | tgl_byr | nama_jur | Tot_SPP | SPP_Tertinggi | SPP_Terendah |
+----------+------------+-----------------------+---------+---------------+--------------+
| azhar | 2006-07-12 | manajemen informatika | 250000 | 250000 | 250000 |
| raeba | 2007-12-23 | komputer akuntansi | 500000 | 500000 | 500000 |
| sahrun | 2009-12-09 | manajemen informatika | 750000 | 750000 | 750000 |
| yanti | 2006-11-27 | teknik komputer | 350000 | 350000 | 350000 |
+----------+------------+-----------------------+---------+---------------+--------------+
4 rows in set (0.02 sec)
5. mysql> select nama_mhs,nama_jur,if(angkatan>='2004','Angkatan Tua','Angkatan Muda') as keterangan,
-> if(tmpt_lhr='Praya','Lombok Tengah',if(tmpt_lhr='gerung','Lombok Barat',if(tmpt_lhr='tanjung','KLU','Lombok Timur'))) as Tmpt_lhr from
-> mahasiswa , spp , jurusan
-> where spp.kd_jur=jurusan.kd_jur and spp.nim=mahasiswa.nim
-> and spp>=300000 order by nama_jur asc;
+----------+-----------------------+---------------+---------------+
| nama_mhs | nama_jur | keterangan | Tmpt_lhr |
+----------+-----------------------+---------------+---------------+
| raeba | komputer akuntansi | Angkatan Tua | KLU |
| sahrun | manajemen informatika | Angkatan Tua | Lombok Tengah |
| yanti | teknik komputer | Angkatan Muda | Lombok Barat |
| yanti | teknik komputer | Angkatan Tua | Lombok Barat |
+----------+-----------------------+---------------+---------------+
4 rows in set (0.02 sec)
6. mysql> select nama_mhs,angkatan,if(nama_jur='manajemen informatika','MI',if(nama_jur='komputer akuntansi','KA','TK')) as jur,
-> if(angkatan>=2004,spp*0.1,'') as SPP_Bayar from
-> mahasiswa natural join jurusan natural join spp where
-> spp.kd_jur=jurusan.kd_jur and spp.nim=mahasiswa.nim
-> and tgl_byr between '2006/01/01' and '2009/12/31'
-> and nama_mhs like '%R%';
+----------+----------+-----+-----------+
| nama_mhs | angkatan | jur | SPP_Bayar |
+----------+----------+-----+-----------+
| raeba | 2006 | KA | 50000.0 |
| azhar | 2005 | MI | 25000.0 |
| sahrun | 2007 | MI | 75000.0 |
+----------+----------+-----+-----------+
3 rows in set (0.00 sec)
7. mysql> select nama_mhs,angkatan, if(angkatan<=2004,'Angkatan Tua','Angkatan Muda') as Angkatan,
-> if(nama_jur='manajemen informatika','MI',if(nama_jur='komputer akuntansi','KA','TK')) as Jur,
-> if(spp<=400000,'Murah','Mahal') as Keterangan from
-> mahasiswa natural join jurusan natural join spp where
-> spp.kd_jur=jurusan.kd_jur and spp.nim=mahasiswa.nim
-> group by nama_mhs;
+----------+----------+---------------+-----+------------+
| nama_mhs | angkatan | Angkatan | Jur | Keterangan |
+----------+----------+---------------+-----+------------+
| azhar | 2005 | Angkatan Muda | MI | Murah |
| raeba | 2006 | Angkatan Muda | KA | Mahal |
| sahrun | 2007 | Angkatan Muda | MI | Mahal |
| yanti | 2003 | Angkatan Tua | TK | Murah |
+----------+----------+---------------+-----+------------+
4 rows in set (0.00 sec)
8. mysql> select mahasiswa.nim,mahasiswa.nama_mhs,if(nama_jur='manajemen informatika','MI',
-> if(nama_jur='komputer akuntansi','KA','TK')) as jur,
-> if(tmpt_lhr='praya','Lombok Tengah',if(tmpt_lhr='gerung','Lombok Barat',if(tmpt_lhr='tanjung','KLU','Lombok Timur'))) as
-> Alamat_Asal from spp,mahasiswa,jurusan where
-> spp.kd_jur=jurusan.kd_jur and spp.nim=mahasiswa.nim
-> or no_nota between 'N003' and 'N005' group by nim ;
+------+----------+-----+---------------+
| nim | nama_mhs | jur | Alamat_Asal |
+------+----------+-----+---------------+
| M001 | sahrun | MI | Lombok Tengah |
| M002 | yanti | MI | Lombok Barat |
| M003 | raeba | KA | KLU |
| M004 | azhar | MI | Lombok Timur |
+------+----------+-----+---------------+
4 rows in set (0.00 sec)
Tidak ada komentar:
Posting Komentar