Jawaban UAS Semester IV

PROFIL SAYA

NIM                     : 10201045101

Nama                   : Oman Rohman

Jenis Kelamin        : Laki-Laki

Jurusan                 : Sistem Informasi

Kelas                    : SIKF (Malam)

Semester               : IV (Empat)

1. CREATE DATABASE `akademik1` ;
2  a. jurusan
      CREATE TABLE `akademik1`.`jurusan` (
     `kode_jurusan` VARCHAR( 10 ),
     `nama_jurusan` CHAR( 40 ),
     `jenjang` VARCHAR( 5 ),
      PRIMARY KEY ( `kode_jurusan` )
      )

   b. mahasiswa
      CREATE TABLE `akademik1`.`mahasiswa` (
     `nim` VARCHAR( 12 ),
     `nama` CHAR( 40 ),
     `jenis_kelamin` CHAR( 1 ),
     `alamat` VARCHAR( 35 ),
     `kota` CHAR( 30 ),
     `kode_jurusan` VARCHAR( 12 ),
      PRIMARY KEY ( `nim` , `kode_jurusan` )
      )

   c. mata_kuliah
      CREATE TABLE `akademik1`.`mata_kuliah` (
     `kode_MK` VARCHAR( 7 ),
     `nama_MK` CHAR( 25 ),
     `SKS` INT NOT NULL ,
     `semester` CHAR( 5 ),
      PRIMARY KEY ( `kode_MK` )
      )

   d. krs
      CREATE TABLE `akademik1`.`KRS` (
     `ID_KRS` INT,
     `TH_AK` DATE,
     `nim` VARCHAR( 12 ),
     `kode_MK` VARCHAR( 12 ),
     `nilai_UTS`,
     `nilai_UAS`,
     PRIMARY KEY ( `ID_KRS` , `nim` , `kode_MK` )
     )

3. a. jurusan
      CREATE TABLE `akademik1`.`jurusan` (
     `kode_jurusan` VARCHAR( 10 ),
     `nama_jurusan` CHAR( 40 ),
     `jenjang` VARCHAR( 5 ),
      PRIMARY KEY ( `kode_jurusan` )
      )

   b. mahasiswa
      CREATE TABLE `akademik1`.`mahasiswa` (
     `nim` VARCHAR( 12 ),
     `nama` CHAR( 40 ),
     `jenis_kelamin` CHAR( 1 ),
     `alamat` VARCHAR( 35 ),
     `kota` CHAR( 30 ),
     `kode_jurusan` VARCHAR( 12 ),
      PRIMARY KEY ( `nim` , `kode_jurusan` )
      )

   c. mata_kuliah
      CREATE TABLE `akademik1`.`mata_kuliah` (
     `kode_MK` VARCHAR( 7 ),
     `nama_MK` CHAR( 25 ),
     `SKS` INT NOT NULL ,
     `semester` CHAR( 5 ),
      PRIMARY KEY ( `kode_MK` )
      )

   d. krs
      CREATE TABLE `akademik1`.`KRS` (
     `ID_KRS` INT,
     `TH_AK` DATE,
     `nim` VARCHAR( 12 ),
     `kode_MK` VARCHAR( 12 ),
     `nilai_UTS`,
     `nilai_UAS`,
     PRIMARY KEY ( `ID_KRS` , `nim` , `kode_MK` )
     )

4. INSERT INTO 
mahasiswa (`nim`,`nama`,`jenis_kelamin`,`alamat`,`kota`,`kode_jurusan`)
   VALUES ('2011101001','Udin','L','Ciwaru Raya','Serang','102'),
          ('2011101002','Nina','P','Cimuncang','Serang','102'),
          ('2011101003','Saprudin','L','Cinanggung','Serang','101'),
          ('2011101004','Dina','P','Jl. Jend. Sudirman','Serang','101')

5. a. UPDATE  
        mahasiswa SET alamat='Jl. A. Yani No. 33',kota='Cilegon' WHERE nim='2011101002'
    b. UPDATE  krs SET nilai_UTS=90 WHERE nim='2011101001'
      UPDATE  krs SET nilai_UAS=95 WHERE kode_MK='TI002'

6. a. DELETE FROM mahasiswa WHERE nim='2011101004'
   b. DELETE FROM krs WHERE nim='2011101003'
      DELETE FROM krs WHERE kode_MK='SI022'

7. SELECT a.nim, a.nama, a.jenis_kelamin, a.kode_jurusan, b.nama_jurusan
   FROM mahasiswa AS a
   JOIN jurusan AS b ON a.kode_jurusan= b.kode_jurusan

8. SELECT a.TH_AK,b.nim,b.nama,c.kode_MK,c.nama_MK,a.nilai_UTS,a.nilai_UAS,(a.nilai_UTS+nilai_UAS)/2 As Rata2
   FROM krs As a
   JOIN mahasiswa As b ON a.nim=b.nim JOIN mata_kuliah As c ON a.kode_MK=c.kode_MK

9. SELECT a.nim,a.nama,b.TH_AK,b.kode_MK
   FROM mahasiswa As a
   JOIN kra As b ON a.nim=b.nim

10. SELECT a.nim,a.nama,b.TH_AK, Count(b.kode_MK ) As Jumlah_MK
 FROM mahasiswa as a
 JOIN krs as b
 ON a.nim=b.nim