NAMA : AHMAD BAGUS HENDRAWAN
NIM : 1119101755
KELAS: SP1.3
Tabel pelanggan
no
|
Nama Field
|
Type data
|
Keterangan
|
1.
|
idPelanggan
|
Int
|
Pk, auto_increment
|
2.
|
namaPelanggan
|
Varchar(25)
|
idPelanggan
|
namaPelanggan
|
1.
|
Cristiano Ronaldo
|
2.
|
Lionel Messi
|
3.
|
Frank Lampard
|
4.
|
Rumelu Lukaku
|
5.
|
Zlatan Ibrahimovic
|
6.
|
Neymar Jr
|
7.
|
Paul Pogba
|
8.
|
Muhammad Salah
|
9.
|
Mesut Ozil
|
10.
|
Luiz Suares
|
Tabel perangkat
no
|
Nama Field
|
Type data
|
Keterangan
|
1.
|
idPerangkat
|
Int
|
Pk, auto_increment
|
2.
|
namaPerangkat
|
Varchar(20)
|
|
3.
|
Keterangan
|
Text
|
|
4.
|
idBilik
|
Int
|
FK
|
idPerangkat
|
namaPerangkat
|
Keterangan
|
idbilik
|
1.
|
Perangkat 1
|
PS4, TV 21 Inc, 2 Stick
|
1
|
2.
|
Perangkat 2
|
PS4, TV 32 Inc, 2 Stick
|
2
|
3.
|
Perangkat 3
|
PS4, TV 21 Inc, 2 Stick
|
3
|
4.
|
Perangkat 4
|
PS4, TV 21 Inc, 2 Stick
|
4
|
5.
|
Perangkat 5
|
PS4, TV 32 Inc, 2 Stick
|
5
|
Tabel paket
No
|
Nama Field
|
Type data
|
Keterangan
|
1.
|
idPaket
|
Int
|
Pk, auto_increment
|
2.
|
namaPaket
|
Varchar(20)
|
|
3.
|
Durasi
|
Int
|
|
4.
|
Harga
|
Int
|
idPaket
|
namaPaket
|
Durasi
|
harga
|
1.
|
Paket 1
|
1
|
10000
|
2.
|
Paket 2
|
2
|
15000
|
3.
|
MidNight
|
6
|
25000
|
4.
|
Daylight
|
4
|
20000
|
5.
|
Begadang
|
8
|
30000
|
Tabel bilik
No
|
Nama Field
|
Type data
|
Keterangan
|
1.
|
idBilik
|
Int
|
Pk, auto_increment
|
2.
|
namaBilik
|
Varchar(20)
|
idBilik
|
namaBilik
|
1.
|
Bilik 1
|
2.
|
Bilik 2
|
3.
|
Bilik 3
|
4.
|
Bilik 4
|
5.
|
Bilik 5
|
Tabel transaksi
No
|
Nama Field
|
Type data
|
Keterangan
|
1.
|
idTransaksi
|
Int
|
Pk, auto_increment
|
2.
|
idPelanggan
|
Int
|
Fk
|
3.
|
idPerangkat
|
Int
|
Fk
|
4.
|
idPaket
|
Int
|
Fk
|
5.
|
Tanggal
|
date
|
idTransaksi
|
idPelanggan
|
idPerangkat
|
IdPaket
|
Tanggal
|
1.
|
1
|
1
|
5
|
2019-10-01
|
2.
|
1
|
3
|
3
|
2019-10-05
|
3.
|
5
|
4
|
1
|
2019-10-05
|
4.
|
2
|
2
|
1
|
2019-10-05
|
5.
|
4
|
5
|
1
|
2019-10-05
|
6.
|
7
|
1
|
4
|
2019-10-06
|
7.
|
8
|
4
|
2
|
2019-10-06
|
8.
|
6
|
3
|
4
|
2019-10-06
|
9.
|
4
|
2
|
3
|
2019-10-06
|
10.
|
5
|
1
|
3
|
2019-10-06
|
11.
|
3
|
3
|
1
|
2019-10-08
|
12.
|
1
|
4
|
2
|
2019-10-08
|
13.
|
9
|
5
|
3
|
2019-10-09
|
14.
|
10
|
2
|
4
|
2019-10-10
|
15.
|
5
|
3
|
3
|
2019-10-10
|
16.
|
4
|
2
|
1
|
2019-10-11
|
17.
|
2
|
1
|
1
|
2019-10-11
|
18.
|
1
|
4
|
3
|
2019-10-12
|
19.
|
3
|
5
|
4
|
2019-10-12
|
20.
|
4
|
4
|
5
|
2019-10-12
|
Perintah:
1 1. Buatlah query untuk membuat seluruh tabel
Query 1 : CREATE TABLE
pelanggan(id_pelanggan int,nama_pelanggan varchar(25));
Query 2 : CREATE TABLE
perangkat(id_perangkat int,nama_perangkat varchar(20),keterangan text,id_bilik
int);
Query 3 : CREATE TABLE
paket(id_paket int,nama_paket varchar(20),durasi int,harga int);
Query 4 : CREATE TABLE
bilik(id_bilik int,nama_bilik varchar(20));
Query 5 : CREATE TABLE
transaksi(id_transaksi int,id_pelanggan int,id_perangkat int,id_paket
int,tanggal date);
2.
Isikan data sesuai tabel yang ada:
Query 1 : INSERT
INTO pelanggan VALUES
("1.","Cristiano
Ronaldo"),
("2.","Lionel
Messi"),
("3.","Frank
Lampard"),
("4.","Rumelu
Lukaku"),
("5.","Zlatan
Ibrahimovic"),
("6.","Neymar
Jr"),
("7.","Paul
Pogba"),
("8.","Muhammad
Salah"),
("9.","Mesut
Ozil"),
("10.","Luiz
Suares");
Query 2 : INSERT
INTO perangkat VALUES
("1.","Perangkat
1","PS4,TV 21 inc,2 Stick","1"),
("2.","Perangkat
2","PS4,TV 32 inc,2 Stick","2"),
("3.","Perangkat
3","PS4,TV 21 inc,2 Stick","3"),
("4.","Perangkat
4","PS4,TV 21 inc,2 Stick","4");
Query 3 : INSERT
INTO paket VALUES
("1.","Paket
1","1","10000"),
("2.","Paket
2","2","15000"),
("3.","MidNight","6","25000"),
("4.","DayLight","4","20000"),
("5.","Begadang","8","30000");
Query 4 : INSERT
INTO bilik VALUES
("1.","Bilik
1"),
("2.","Bilik
2"),
("3.","Bilik
3"),
("4.","Bilik
4"),
("5.","Bilik
5");
Query 5 : INSERT
INTO transaksi VALUES
("1.","1","1","5","2019-10-01"),
("2.","1","3","3","2019-10-05"),
("3.","5","4","1","2019-10-05"),
("4.","2","2","1","2019-10-05"),
("5.","4","5","1","2019-10-05"),
("6.","7","1","4","2019-10-06"),
("7.","8","4","2","2019-10-06"),
("8.","6","3","4","2019-10-06"),
("9.","4","2","3","2019-10-06"),
("10.","5","1","3","2019-10-06"),
("11.","3","3","1","2019-10-08"),
("12.","1","4","2","2019-10-08"),
("13.","9","5","3","2019-10-09"),
("14.","10","2","4","2019-10-10"),
("15.","5","3",",3","2019-10-10"),
("16.","4","2","1","2019-10-11"),
("17.","2","1","1","2019-10-11"),
("18.","1","4","3","2019-10-12"),
("19.","3","5","4","2019-10-12"),
("20.","4","4","5","2019-10-12");
3 3.Tampilkan
idPelanggan,Namapelanggan,namaPerangkat,NamaPaket,Harga
Query: SELECT
pelanggan.id_pelanggan,pelanggan.nama_pelanggan,perangkat.nama_perangkat,paket.nama_paket,paket.harga
FROM pelanggan JOIN perangkat JOIN paket
4. 4.Tampilkan
penghasilan perhari(group by tanggal)
Query:
SELECT tanggal,SUM(harga) FROM
paket JOIN transaksi ON paket.id_paket=transaksi.id_paket GROUP BY tanggal
5. 5.Tampilkan
berapa penghasilan rental perorang pelanggan(group by pelanggan)
Query: SELECT
pelanggan.nama_pelanggan,SUM(paket.harga)FROM pelanggan JOIN paket JOIN
transaksi ON paket.id_paket=transaksi.id_paket AND
pelanggan.id_pelanggan=transaksi.id_pelanggan GROUP BY pelanggan.nama_pelanggan
