Rabu, 19 Maret 2014

Tugas Praktikum 03 Basis Data

Praktikum 04 Basis Data 
-->1. cara membuat tabel

 create 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