TUGAS 5 MANAJEMEN DAN ADMINISTRASI BASIS DATA
Dosen Pengampu: Bp. Eko Purwanto
Dosen Pengampu: Bp. Eko Purwanto
NAMA : Eko Pranoto Saputro
NIM : 160101237
Smt : 4 (empat)
STMIK DUTA BANGSA SURAKARTA
2018
========================================================================
SOAL NO 2:
SOAL NO 1:
mysql> use ekopranotosaputro;
Database changed
mysql> create table pegawai(id_peg varchar(4) primary key,
nama varchar(50),
alamat varchar(50),
id_dept varchar(2));
Query OK, 0 rows affected
mysql> create table departemen(id_dept varchar(2) primary key,
nama_dept varchar(40),
lokasi varchar(40));
Query OK, 0 rows affected
mysql> insert into pegawai(id_peg,nama,alamat,id_dept) values
('0001','Yuni','Surabaya','01'),
('0002','Mariyatun','Malang','02'),
('0003','Joni','Jimbaran','01'),
('0004','Andi','Jakarta','03'),
('0005','Yanti','Surabaya','01'),
('0006','Indri','Malang','02'),
('0007','Indra','Surabaya','01'),
('0008','Toni','Jakarta','03');
Query OK, 8 rows affected
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into departemen(id_dept,nama_dept,lokasi) values
('01','Keuangan','Surabaya'),
('02','Marketing','Malang'),
('03','Produksi','Jakarta'),
('04','SDM','Bandung');
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
mysql> select a.id_peg,a.nama,a.alamat,b.nama_dept,b.lokasi from pegawai a
left join departemen b on a.id_dept=b.id_dept;
+--------+-----------+----------+-----------+----------+
| id_peg | nama | alamat | nama_dept | lokasi |
+--------+-----------+----------+-----------+----------+
| 0001 | Yuni | Surabaya | Keuangan | Surabaya |
| 0002 | Mariyatun | Malang | Marketing | Malang |
| 0003 | Joni | Jimbaran | Keuangan | Surabaya |
| 0004 | Andi | Jakarta | Produksi | Jakarta |
| 0005 | Yanti | Surabaya | Keuangan | Surabaya |
| 0006 | Indri | Malang | Marketing | Malang |
| 0007 | Indra | Surabaya | Keuangan | Surabaya |
| 0008 | Toni | Jakarta | Produksi | Jakarta |
+--------+-----------+----------+-----------+----------+
8 rows in set
mysql>
SOAL NO 2:
mysql> use ekopranotosaputro;
Database changed
mysql> create table Mahasiswa(nrp varchar(9) primary key,
nama varchar(50),
alamat varchar(50),
hoby varchar(50));
Query OK, 0 rows affected
mysql> create table nilaimhs(nrp varchar(9),
kode_mk varchar(5),
nilai int(3));
Query OK, 0 rows affected
mysql> create table mtkuliah(kode_mk varchar(5) primary key,
nama_mk varchar(50),sks int(1));
Query OK, 0 rows affected
mysql> insert into mahasiswa(nrp,nama,alamat,hoby) values
('120101001','Anto Budi','Surakarta','Mancing'),
('120101002','Sugiyarto','Klaten','Sepakbola'),
('120101003','Sumini','Sragen','Membaca'),
('120101004','yuni','Sukoharjo','makan');
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into mtkuliah(kode_mk,nama_mk,sks) values
('VT001','Pemograman 1','3'),
('VT002','Basis Data 1','3'),
('VT003','Manajemen','2'),
('VT004','Algoritma','3'),
('VT005','Agama','2');
Query OK, 5 rows affected
Records: 5 Duplicates: 0 Warnings: 0
mysql> insert into nilaimhs(nrp,kode_mk,nilai) values
('120101001','VT001','70'),('120101001','VT002','80'),
('120101001','VT003','85'),('120101001','VT004','78'),
('120101001','VT005','80'),('120101002','VT001','70'),
('120101002','VT002','70'),('120101002','VT003','75'),
('120101002','VT004','80'),('120101002','VT005','80'),
('120101003','VT001','80'),('120101003','VT002','70'),
('120101003','VT003','65'),('120101003','VT004','70'),
('120101003','VT005','70'),('120101004','VT001','90'),
('120101004','VT002','75'),('120101004','VT003','75'),
('120101004','VT004','80'),('120101004','VT005','80');
Query OK, 20 rows affected
Records: 20 Duplicates: 0 Warnings: 0
mysql> select mhs.nrp,mhs.nama,mk.nama_mk,nilai.nilai from nilaimhs nilai
inner join mahasiswa mhs on nilai.nrp = mhs.nrp inner join mtkuliah mk on nilai.kode_mk = mk.kode_mk;
+-----------+-----------+--------------+-------+
| nrp | nama | nama_mk | nilai |
+-----------+-----------+--------------+-------+
| 120101001 | Anto Budi | Pemograman 1 | 70 |
| 120101001 | Anto Budi | Basis Data 1 | 80 |
| 120101001 | Anto Budi | Manajemen | 85 |
| 120101001 | Anto Budi | Algoritma | 78 |
| 120101001 | Anto Budi | Agama | 80 |
| 120101002 | Sugiyarto | Pemograman 1 | 70 |
| 120101002 | Sugiyarto | Basis Data 1 | 70 |
| 120101002 | Sugiyarto | Manajemen | 75 |
| 120101002 | Sugiyarto | Algoritma | 80 |
| 120101002 | Sugiyarto | Agama | 80 |
| 120101003 | Sumini | Pemograman 1 | 80 |
| 120101003 | Sumini | Basis Data 1 | 70 |
| 120101003 | Sumini | Manajemen | 65 |
| 120101003 | Sumini | Algoritma | 70 |
| 120101003 | Sumini | Agama | 70 |
| 120101004 | yuni | Pemograman 1 | 90 |
| 120101004 | yuni | Basis Data 1 | 75 |
| 120101004 | yuni | Manajemen | 75 |
| 120101004 | yuni | Algoritma | 80 |
| 120101004 | yuni | Agama | 80 |
+-----------+-----------+--------------+-------+
20 rows in set
mysql> select mhs.nrp,mhs.nama,mk.nama_mk,nilai.nilai from nilaimhs nilai left join mahasiswa mhs on
nilai.nrp = mhs.nrp left join mtkuliah mk on nilai.kode_mk = mk.kode_mk;
+-----------+-----------+--------------+-------+
| nrp | nama | nama_mk | nilai |
+-----------+-----------+--------------+-------+
| 120101001 | Anto Budi | Pemograman 1 | 70 |
| 120101001 | Anto Budi | Basis Data 1 | 80 |
| 120101001 | Anto Budi | Manajemen | 85 |
| 120101001 | Anto Budi | Algoritma | 78 |
| 120101001 | Anto Budi | Agama | 80 |
| 120101002 | Sugiyarto | Pemograman 1 | 70 |
| 120101002 | Sugiyarto | Basis Data 1 | 70 |
| 120101002 | Sugiyarto | Manajemen | 75 |
| 120101002 | Sugiyarto | Algoritma | 80 |
| 120101002 | Sugiyarto | Agama | 80 |
| 120101003 | Sumini | Pemograman 1 | 80 |
| 120101003 | Sumini | Basis Data 1 | 70 |
| 120101003 | Sumini | Manajemen | 65 |
| 120101003 | Sumini | Algoritma | 70 |
| 120101003 | Sumini | Agama | 70 |
| 120101004 | yuni | Pemograman 1 | 90 |
| 120101004 | yuni | Basis Data 1 | 75 |
| 120101004 | yuni | Manajemen | 75 |
| 120101004 | yuni | Algoritma | 80 |
| 120101004 | yuni | Agama | 80 |
+-----------+-----------+--------------+-------+
20 rows in set
mysql>
Tidak ada komentar:
Posting Komentar