Jawaban uas praktik PBD

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)