UAS Basis Data

Tugas Basis Data

Nama      : Ayu Inaya Messti

Prodi       : SIG2 (101080403019)

Semester : IV (empat)

Jawaban Uas

 

1. CREATE database akademik

 

2. dan 3.

 

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

   b. mahasiswa
      CREATE TABLE `akademik`.`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 `akademik`.`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 `akademik`.`krs` (
     `id_krs` INT,
     `th_ak` CHAR (10),
     `nim` VARCHAR( 12 ),
     `kode_mk` VARCHAR( 12 ),
     `nilai_uts` INT,
     `nilai_uas`INT,
     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,nilai_uas=95 WHERE nim=2011101001 AND kode_mk='TI002'

 

6.

A.DELETE FROM mahasiswa WHERE nim='2011101004'
B.DELETE FROM krs WHERE nim='2011101003' or 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.nama,a.nama,b.th_ak,b.kode_mk FROM mahasiswa As a JOIN krs 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