Jawaban uas semester 4
Nama : Andriansyah
Nim : 10201045001
jurusan : sistem Informasi
Semester : 4
Jawaban UTS (Semester 4)
1. database= penjualan
tabel= sepatu, konsumen, suplier, pembelian, detail_penjualan
2. a) CREATE TABLE `penjualan`.`sepatu` (
`kode_sepatu` VARCHAR( 5 ),
`nama_sepatu` CHAR( 30 ),
`ukuran` CHAR( 10 ),
`harga` INT,
`jumlah` INT,
`stok` INT,
`kode_suplier` VARCHAR( 5 ),
PRIMARY KEY ( `kode_sepatu` , `kode_suplier` ))
b) CREATE TABLE `penjualan`.`konsumen` (
`kode_konsumen` VARCHAR( 5 ),
`nama_konsumen` CHAR( 40 ),
`alamat_konsumen` VARCHAR( 35 ),
`jenis_kelamin` CHAR( 1 ),
`telepon` CHAR( 12 ),
`tempat_lahir` CHAR( 15 ),
`tanggal_lahir` DATE,
`agama` CHAR( 12 ),
`kode_sepatu` VARCHAR( 5 ),
PRIMARY KEY ( `kode_konsumen` , `kode_sepatu` ))
c) CREATE TABLE `penjualan`.`suplier` (
`kode_suplier` VARCHAR( 5 ),
`nama_suplier` CHAR( 40 ),
`alamat_suplier` CHAR( 35 ),
`telp` CHAR( 12 ),
PRIMARY KEY ( `kode_suplier` ))
d) CREATE TABLE `penjualan`.`pembelian` (
`no_faktur_pembelian` VARCHAR( 7 ),
`tanggal_faktur` DATE,
`kode_sepatu` VARCHAR( 5 ),
`kode_suplier` VARCHAR( 5 ),
`retur` CHAR( 20 ),
PRIMARY KEY ( `no_faktur_pembelian` ))
e) CREATE TABLE `penjualan`.`detail_penjualan` (
`no_faktur_penjualan` VARCHAR( 7 ),
`kode_sepatu` VARCHAR( 5 ),
`kode_konsumen` VARCHAR( 5 ),
`retur` CHAR( 20 ),
`jumlah` INT,
`harga` INT ,
`total` INT ,
PRIMARY KEY ( `no_faktur_penjualan` , `kode_sepatu` , `kode_konsumen`))
3.
 |
| Relasi Antar Table |
4. INSERT INTO sepatu (`kode_sepatu`,`nama_sepatu`,`ukuran`,`harga`,`jumlah`,`stok`,`kode_suplier`)
VALUES ('S0001','Spiser','40',30000,3,30,'SP001'),
('S0002','Spiser','40',40000,3,30,'SP002'),
('S0003','Radial','41',35000,5,21,'SP003'),
('S0004','Dallas','40',50000,3,22,'SP004')
INSERT INTO pembelian (`no_faktur_pembelian`,`tanggal_faktur`,`kode_sepatu`,`kode_suplier`,`retur`)
VALUES ('FP0001','2012-06-24','S0001','SP001','Tidak Ada'),
('FP0002','2012-06-24','S0002','SP002','Tidak Ada'),
('FP0003','2012-06-24','S0003','SP003','Cacat'),
('FP0004','2012-06-24','S0004','SP004','Cacat')
Jawaban UAS (Semester 4)
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
