Praktikum 04 Basis Data
-->1. cara membuat tabelcreate database bakery_cake
Query OK, 1 row affected (0.01 sec)
use bakery_cake
Database changed
create table Menu(
Id_menu varchar (7) primary key not null,
Menu varchar (120) not null,
Harga int (20) not null
);
Query OK, 0 rows affected (0.77 sec)
mysql> desc Menu;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| Id_menu | varchar(7) | NO | PRI | NULL | |
| Menu | varchar(120) | NO | | NULL | |
| Harga | int(20) | NO | | NULL | |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
create table Pelanggan(
Id_pelanggan varchar (5) primary key not null,
Nama_pelanggan varchar (30) not null,
Alamat_pelanggan varchar (20) not null,
Kontak_pelanggan varchar (15) not null
);
Query OK, 0 rows affected (0.59 sec)
mysql> desc pelanggan;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| Id_pelanggan | varchar(5) | NO | PRI | NULL | |
| Nama_pelanggan | varchar(30) | NO | | NULL | |
| Alamat_pelanggan | varchar(20) | NO | | NULL | |
| Kontak_pelanggan | varchar(15) | NO | | NULL | |
+------------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
create table Pelayan(
Id_pelayan varchar (5)primary key not null,
Nama_pelayan varchar (30) not null,
Kontak_pelayan varchar(20) not null
);
Query OK, 0 rows affected (0.44 sec)
mysql> desc pelayan;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| Id_pelayan | varchar(5) | NO | PRI | NULL | |
| Nama_pelayan | varchar(30) | NO | | NULL | |
| Kontak_pelayan | varchar(20) | NO | | NULL | |
+----------------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
create table Pemesanan(
Id_pesanan varchar (7) primary key,
Id_pelayan varchar (5) not null,
Id_Menu varchar (5) not null,
Id_pelanggan varchar (5) not null,
foreign key (Id_pelayan)references pelayan (Id_pelayan),
foreign key (Id_menu)references Menu (Id_menu),
foreign key (Id_pelanggan)references pelanggan (Id_pelanggan),
Order_date date null ,
Banyak_pesanan int (11) not null,
Jenis_pesanan varchar (20) not null
);
Query OK, 0 rows affected (1.18 sec)
mysql> desc pemesanan;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| Id_pesanan | varchar(7) | NO | PRI | NULL | |
| Id_pelayan | varchar(5) | NO | MUL | NULL | |
| Id_Menu | varchar(5) | NO | MUL | NULL | |
| Id_pelanggan | varchar(5) | NO | MUL | NULL | |
| Order_date | date | YES | | NULL | |
| Banyak_pesanan | int(11) | NO | | NULL | |
| Jenis_pesanan | varchar(20) | NO | | NULL | |
+----------------+-------------+------+-----+---------+-------+
7 rows in set (0.13 sec)
-->2. Masukkan data-data berikut pada tabel yang telah ditentukan:
a) tabel Menu
insert into menu
(Id_menu, Menu, Harga) values
('MN1','Blackforest','60000'),
('MN2','Rainbow cake','180000'),
('MN3','Jar cake','27500'),
('MN4','Mochilok','7500'),
('MN5','Red Velvet cake','250000'),
('MN6','Lapis Legit','300000');
Query OK, 6 rows affected (0.85 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select *from menu;
+---------+-----------------+--------+
| Id_menu | Menu | Harga |
+---------+-----------------+--------+
| MN1 | Blackforest | 60000 |
| MN2 | Rainbow cake | 180000 |
| MN3 | Jar cake | 27500 |
| MN4 | Mochilok | 7500 |
| MN5 | Red Velvet cake | 250000 |
| MN6 | Lapis Legit | 300000 |
+---------+-----------------+--------+
6 rows in set (0.00 sec)
b) Tabel Pelanggan
insert into pelanggan
(Id_pelanggan, Nama_pelanggan, Alamat_pelanggan, Kontak_pelanggan) values
('PN1','Willdan','Jalan Tongkeng','0899242420'),
('PN2','Rabihi','Jalan Holis','980019020'),
('PN3','Intan','Jalan Gerlong','129304800'),
('PN4','Asep','Jalan Kemakmuran','097364788'),
('PN5','Dendi','Jalan Kemerdekaan','08000192839');
Query OK, 5 rows affected (0.07 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select *from pelanggan;
+--------------+----------------+-------------------+------------------+
| Id_pelanggan | Nama_pelanggan | Alamat_pelanggan | Kontak_pelanggan |
+--------------+----------------+-------------------+------------------+
| PN1 | Willdan | Jalan Tongkeng | 0899242420 |
| PN2 | Rabihi | Jalan Holis | 980019020 |
| PN3 | Intan | Jalan Gerlong | 129304800 |
| PN4 | Asep | Jalan Kemakmuran | 097364788 |
| PN5 | Dendi | Jalan Kemerdekaan | 08000192839 |
+--------------+----------------+-------------------+------------------+
5 rows in set (0.00 sec)
c) Tabel Pelayan
insert into Pelayan
(Id_pelayan, Nama_pelayan, Kontak_pelayan) values
('PL1','Aksan','080914845'),
('PL2','Fernaldy','078928394'),
('PL3','Ani','7849583030'),
('PL4','Ahmad','0011111687'),
('PL5','Abul','0226783984');
Query OK, 5 rows affected (0.91 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select *from pelayan;
+------------+--------------+----------------+
| Id_pelayan | Nama_pelayan | Kontak_pelayan |
+------------+--------------+----------------+
| PL1 | Aksan | 080914845 |
| PL2 | Fernaldy | 078928394 |
| PL3 | Ani | 7849583030 |
| PL4 | Ahmad | 0011111687 |
| PL5 | Abul | 0226783984 |
+------------+--------------+----------------+
5 rows in set (0.00 sec)
insert into pemesanan
(id_pesanan,id_pelayan,id_menu,id_pelanggan,order_date,banyak_pesanan,jenis_pesanan) values
('PS1','PL1','MN1','PN2','2014-02-01','1','Delivery order'),
('PS10','PL3','MN2','PN3','2014-02-06','2','Delivery order'),
('PS11','PL2','MN1','PN4','2014-02-07','2','Ambil Lanngsung'),
('PS12','PL5','MN5','PN4','2014-02-08','2','Delivery order'),
('PS13','PL1','MN3','PN4','2014-02-08','2','Ambil Langsung'),
('PS14','PL3','MN5','PN5','2014-02-02','3','Delivery order'),
('PS15','PL2','MN1','PN4','2014-02-11','4','Ambil Langsung'),
('PS16','PL5','MN3','PN2','2014-02-12','2','Delivery order'),
('PS17','PL1','MN3','PN3','2014-02-13','2','Ambil Langsung'),
('PS18','PL3','MN2','PN3','2014-02-14','2','Delivery order'),
('PS19','PL2','MN1','PN5','2014-02-14','2','Ambil Langsung'),
('PS2','PL4','MN4','PN1','2014-02-01','2','Ambil Langsung'),
('PS20','PL4','MN2','PN2','2014-02-15','2','Ambil Langsung'),
('PS21','PL1','MN1','PN3','2014-02-20','3','Ambil Lanngsung'),
('PS22','PL5','MN5','PN3','2014-02-21','3','Delivery order'),
('PS23','PL3','MN3','PN1','2014-02-22','3','Ambil Langsung'),
('PS24','PL1','MN4','PN2','2014-02-23','3','Delivery order'),
('PS25','PL4','MN2','PN5','2014-02-22','3','Ambil Langsung'),
('PS26','PL2','MN1','PN3','2014-02-25','3','Delivery order'),
('PS27','PL2','MN1','PN1','2014-02-26','4','Ambil Langsung'),
('PS3','PL2','MN5','PN3','2014-02-01','3','Delivery order'),
('PS4','PL2','MN5','PN3','2014-02-01','1','Delivery order'),
('PS5','PL1','MN1','PN3','2014-02-02','2','Ambil Langsung'),
('PS6','PL2','MN3','PN4','2014-02-02','2','Ambil Langsung'),
('PS7','PL2','MN1','PN2','2014-02-03','2','Delivery order'),
('PS8','PL3','MN4','PN1','2014-02-04','3','Ambil Langsung'),
('PS9','PL1','MN2','PN5','2014-02-05','4','Delivery order');
mysql> select *from pemesanan;
+------------+------------+---------+--------------+------------+----------------+-----------------+
| Id_pesanan | Id_pelayan | Id_Menu | Id_pelanggan | Order_date | Banyak_pesanan | Jenis_pesanan |
+------------+------------+---------+--------------+------------+----------------+-----------------+
| PS1 | PL1 | MN1 | PN2 | 2014-02-01 | 1 | Delivery order |
| PS10 | PL3 | MN2 | PN3 | 2014-02-06 | 2 | Delivery order |
| PS11 | PL2 | MN1 | PN4 | 2014-02-07 | 2 | Ambil Lanngsung |
| PS12 | PL5 | MN5 | PN4 | 2014-02-08 | 2 | Delivery order |
| PS13 | PL1 | MN3 | PN4 | 2014-02-08 | 2 | Ambil Langsung |
| PS14 | PL3 | MN5 | PN5 | 2014-02-02 | 3 | Delivery order |
| PS15 | PL2 | MN1 | PN4 | 2014-02-11 | 4 | Ambil Langsung |
| PS16 | PL5 | MN3 | PN2 | 2014-02-12 | 2 | Delivery order |
| PS17 | PL1 | MN3 | PN3 | 2014-02-13 | 2 | Ambil Langsung |
| PS18 | PL3 | MN2 | PN3 | 2014-02-14 | 2 | Delivery order |
| PS19 | PL2 | MN1 | PN5 | 2014-02-14 | 2 | Ambil Langsung |
| PS2 | PL4 | MN4 | PN1 | 2014-02-01 | 2 | Ambil Langsung |
| PS20 | PL4 | MN2 | PN2 | 2014-02-15 | 2 | Ambil Langsung |
| PS21 | PL1 | MN1 | PN3 | 2014-02-20 | 3 | Ambil Lanngsung |
| PS22 | PL5 | MN5 | PN3 | 2014-02-21 | 3 | Delivery order |
| PS23 | PL3 | MN3 | PN1 | 2014-02-22 | 3 | Ambil Langsung |
| PS24 | PL1 | MN4 | PN2 | 2014-02-23 | 3 | Delivery order |
| PS25 | PL4 | MN2 | PN5 | 2014-02-22 | 3 | Ambil Langsung |
| PS26 | PL2 | MN1 | PN3 | 2014-02-25 | 3 | Delivery order |
| PS27 | PL2 | MN1 | PN1 | 2014-02-26 | 4 | Ambil Langsung |
| PS3 | PL2 | MN5 | PN3 | 2014-02-01 | 3 | Delivery order |
| PS4 | PL2 | MN5 | PN3 | 2014-02-01 | 1 | Delivery order |
| PS5 | PL1 | MN1 | PN3 | 2014-02-02 | 2 | Ambil Langsung |
| PS6 | PL2 | MN3 | PN4 | 2014-02-02 | 2 | Ambil Langsung |
| PS7 | PL2 | MN1 | PN2 | 2014-02-03 | 2 | Delivery order |
| PS8 | PL3 | MN4 | PN1 | 2014-02-04 | 3 | Ambil Langsung |
| PS9 | PL1 | MN2 | PN5 | 2014-02-05 | 4 | Delivery order |
+------------+------------+---------+--------------+------------+----------------+-----------------+
27 rows in set (0.00 sec)
--> 3. Tampilkan jumlah pemesanan dalam tabel pemesanan
select sum (banyak_pesanan) from pemesanan;
mysql> select sum(Banyak_pesanan)from pemesanan;
+---------------------+
| sum(Banyak_pesanan) |
+---------------------+
| 67 |
+---------------------+
1 row in set (0.10 sec)
--> 4.Tampilkan tabe pemesanan dikelompokkan berdasarkan Id_pelayan
select *from pemesanan group by Id_pelayan;
+------------+------------+---------+--------------+------------+----------------+-----------------+
| Id_pesanan | Id_pelayan | Id_Menu | Id_pelanggan | Order_date | Banyak_pesanan | Jenis_pesanan |
+------------+------------+---------+--------------+------------+----------------+-----------------+
| PS1 | PL1 | MN1 | PN2 | 2014-02-01 | 1 | Delivery order |
| PS11 | PL2 | MN1 | PN4 | 2014-02-07 | 2 | Ambil Lanngsung |
| PS10 | PL3 | MN2 | PN3 | 2014-02-06 | 2 | Delivery order |
| PS2 | PL4 | MN4 | PN1 | 2014-02-01 | 2 | Ambil Langsung |
| PS12 | PL5 | MN5 | PN4 | 2014-02-08 | 2 | Delivery order |
+------------+------------+---------+--------------+------------+----------------+-----------------+
5 rows in set (0.07 sec)
-->5. Tampilkan tabel pelayan terurut berdasarkan nama_pelayan secara descending
select *from pelayan Order by nama_pelayan desc;
mysql> select *from pelayan Order by nama_pelayan desc;
+------------+--------------+----------------+
| Id_pelayan | Nama_pelayan | Kontak_pelayan |
+------------+--------------+----------------+
| PL2 | Fernaldy | 078928394 |
| PL3 | Ani | 7849583030 |
| PL1 | Aksan | 080914845 |
| PL4 | Ahmad | 0011111687 |
| PL5 | Abul | 0226783984 |
+------------+--------------+----------------+
5 rows in set (0.08 sec)
--> 6. Tampilkan nama makanan yang mempunyai harga lebih dari "20000"
select menu from Menu where harga>20000;
mysql> select menu from Menu where harga>20000;
+-----------------+
| menu |
+-----------------+
| Blackforest |
| Rainbow cake |
| Jar cake |
| Red Velvet cake |
| Lapis Legit |
+-----------------+
5 rows in set (0.09 sec)
-->7.Buat tabel semu "v_pemesanan" dengan field , "id_pelanggan",
"nama_pelanggan", "nama_pelayan", "menu", dan "banyak_pesanan",
create view v_pemesanan as select a.Id_pelanggan, a.nama_pelanggan, b.nama_pelayan, c.menu, d.banyak_pesanan
from pelanggan as a,Pelayan as b,menu as c,Pemesanan as d
where a.Id_pelanggan=d.Id_pelanggan and b.Id_pelayan=d.Id_pelayan and c.Id_menu=d.Id_menu;
mysql> select *from v_pemesanan;
+--------------+----------------+--------------+-----------------+----------------+
| id_pelanggan | nama_pelanggan | nama_pelayan | menu | banyak_pesanan |
+--------------+----------------+--------------+-----------------+----------------+
| PN2 | Rabihi | Aksan | Blackforest | 1 |
| PN2 | Rabihi | Aksan | Mochilok | 3 |
| PN3 | Intan | Aksan | Jar cake | 2 |
| PN3 | Intan | Aksan | Blackforest | 3 |
| PN3 | Intan | Aksan | Blackforest | 2 |
| PN4 | Asep | Aksan | Jar cake | 2 |
| PN5 | Dendi | Aksan | Rainbow cake | 4 |
| PN1 | Willdan | Fernaldy | Blackforest | 4 |
| PN2 | Rabihi | Fernaldy | Blackforest | 2 |
| PN3 | Intan | Fernaldy | Blackforest | 3 |
| PN3 | Intan | Fernaldy | Red Velvet cake | 3 |
| PN3 | Intan | Fernaldy | Red Velvet cake | 1 |
| PN4 | Asep | Fernaldy | Blackforest | 2 |
| PN4 | Asep | Fernaldy | Blackforest | 4 |
| PN4 | Asep | Fernaldy | Jar cake | 2 |
| PN5 | Dendi | Fernaldy | Blackforest | 2 |
| PN1 | Willdan | Ani | Jar cake | 3 |
| PN1 | Willdan | Ani | Mochilok | 3 |
| PN3 | Intan | Ani | Rainbow cake | 2 |
| PN3 | Intan | Ani | Rainbow cake | 2 |
| PN5 | Dendi | Ani | Red Velvet cake | 3 |
| PN1 | Willdan | Ahmad | Mochilok | 2 |
| PN2 | Rabihi | Ahmad | Rainbow cake | 2 |
| PN5 | Dendi | Ahmad | Rainbow cake | 3 |
| PN2 | Rabihi | Abul | Jar cake | 2 |
| PN3 | Intan | Abul | Red Velvet cake | 3 |
| PN4 | Asep | Abul | Red Velvet cake | 2 |
+--------------+----------------+--------------+-----------------+----------------+
27 rows in set (0.04 sec)
-->8. Tampilkan table semu v_pemesanan dikelompokan berdasarkan “menu” terurut
berdasarkan “nama_pelanggan” secara descending
select *from v_pemesanan group by menu order by nama_pelanggan desc;
mysql> select *from v_pemesanan group by menu order by nama_pelanggan desc;
+--------------+----------------+--------------+-----------------+----------------+
| id_pelanggan | nama_pelanggan | nama_pelayan | menu | banyak_pesanan |
+--------------+----------------+--------------+-----------------+----------------+
| PN2 | Rabihi | Aksan | Mochilok | 3 |
| PN2 | Rabihi | Aksan | Blackforest | 1 |
| PN3 | Intan | Fernaldy | Red Velvet cake | 3 |
| PN3 | Intan | Aksan | Jar cake | 2 |
| PN5 | Dendi | Aksan | Rainbow cake | 4 |
+--------------+----------------+--------------+------- -----+--------------------+
5 rows in set (0.02 sec)
-->9. Tampilkan pemesanan terbanyak dari banyaknya pembelian menu makanan "Rainbow cake"
select max("Rainbow cake") from v_pemesanan where menu='Rainbow cake';
mysql> select max("Rainbow cake") from v_pemesanan where menu='Rainbow cake';
+---------------------+
| max("Rainbow cake") |
+---------------------+
| Rainbow cake |
+---------------------+
1 row in set (0.04 sec)
-->10. Tampilkan banyak transaksi yang dilakukan oleh pelayan yang bernama "Aksan"
select count(nama_pelanggan) from v_pemesanan where nama_pelayan='Aksan';
mysql> select count(nama_pelanggan) from v_pemesanan where nama_pelayan='Aksan';
+-----------------------+
| count(nama_pelanggan) |
+-----------------------+
| 7 |
+-----------------------+
1 row in set (0.00 sec)
-->11. Tampilkan nama_pelanggan, menu, banyak_pesanan dikelompokan berdasarkan
nama_pelanggan terurut secara ascending berdasarkan banyak_pesanan.
select nama_pelanggan,menu,banyak_pesanan from v_pemesanan group by nama_pelanggan order by banyak_pesanan;
+----------------+--------------+----------------+
| nama_pelanggan | menu | banyak_pesanan |
+----------------+--------------+----------------+
| Rabihi | Blackforest | 1 |
| Intan | Jar cake | 2 |
| Asep | Jar cake | 2 |
| Dendi | Rainbow cake | 4 |
| Willdan | Blackforest | 4 |
+----------------+--------------+----------------+
5 rows in set (0.00 sec)
Tidak ada komentar:
Posting Komentar