TUGAS UTS BASIS DATA

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