1.
mysql> create database pra_sn4;
Query OK, 1 row affected (0.21 sec)
2.
- TABEL KOMIK
mysql> create table komik(kode_komik char(5) primary key, judul_komik varchar(20), harga_jual int);
Query OK, 0 rows affected (0.42 sec)
mysql> insert into komik values('K001', 'Legenda Naga', 12000);
Query OK, 1 row affected (0.06 sec)
mysql> insert into komik values('K002', 'Fight Ippo', 11000);
Query OK, 1 row affected (0.04 sec)
mysql> insert into komik values('K003', 'Shincan', 11000);
Query OK, 1 row affected (0.05 sec)
mysql> select*from komik;
+------------+--------------+------------+
| kode_komik | judul_komik | harga_jual |
+------------+--------------+------------+
| K001 | Legenda Naga | 12000 |
| K002 | Fight Ippo | 11000 |
| K003 | Shincan | 11000 |
+------------+--------------+------------+
3 rows in set (0.05 sec)
- TABEL PELANGGAN
mysql> create table pelanggan(kode_pelanggan char(5) primary key, nama_pelanggan varchar(30), alamat varchar(50));
Query OK, 0 rows affected (0.09 sec)
mysql> insert into pelanggan values('P001','Achmad Budi', 'Jl. Nuri 123');
Query OK, 1 row affected (0.03 sec)
mysql> insert into pelanggan values('P002','Rini Sucipta', 'Jl. Mawar 70');
Query OK, 1 row affected (0.04 sec)
mysql> insert into pelanggan values('P003','Agustina', 'Jl. Kemuning Raya 20');
Query OK, 1 row affected (0.05 sec)
mysql> select*from pelanggan;
+----------------+----------------+----------------------+
| kode_pelanggan | nama_pelanggan | alamat |
+----------------+----------------+----------------------+
| P001 | Achmad Budi | Jl. Nuri 123 |
| P002 | Rini Sucipta | Jl. Mawar 70 |
| P003 | Agustina | Jl. Kemuning Raya 20 |
+----------------+----------------+----------------------+
3 rows in set (0.00 sec)
- TABEL TRANSAKSI
mysql> create table transaksi(kode_pelanggan char(5), kode_komik char(5), jumlah_pembelian int, tanggal date, foreign key(kode_pelanggan) references pelanggan (kode_pelanggan),foreign key(kode_komik) references komik (kode_komik));
Query OK, 0 rows affected (0.14 sec)
mysql> insert into transaksi values('P001','K002',3,'2008/05/10');
Query OK, 1 row affected (0.03 sec)
mysql> insert into transaksi values('P001','K003',2,'2008/05/10');
Query OK, 1 row affected (0.03 sec)
mysql> insert into transaksi values('P003','K003',1,'2008/05/11');
Query OK, 1 row affected (0.02 sec)
mysql> insert into transaksi values('P002','K001',4,'2008/05/11');
Query OK, 1 row affected (0.02 sec)
mysql> select*from transaksi;
+----------------+------------+------------------+------------+
| kode_pelanggan | kode_komik | jumlah_pembelian | tanggal |
+----------------+------------+------------------+------------+
| P001 | K002 | 3 | 2008-05-10 |
| P001 | K003 | 2 | 2008-05-10 |
| P003 | K003 | 1 | 2008-05-11 |
| P002 | K001 | 4 | 2008-05-11 |
+----------------+------------+------------------+------------+
4 rows in set (0.00 sec)
3.
mysql> update pelanggan set nama_pelanggan='Budi Susanto',alamat='Jl. Melati 30' where kode_pelanggan='P002';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select*from pelanggan;
+----------------+----------------+----------------------+
| kode_pelanggan | nama_pelanggan | alamat |
+----------------+----------------+----------------------+
| P001 | Achmad Budi | Jl. Nuri 123 |
| P002 | Budi Susanto | Jl. Melati 30 |
| P003 | Agustina | Jl. Kemuning Raya 20 |
+----------------+----------------+----------------------+
3 rows in set (0.00 sec)
4.
mysql> select kode_pelanggan, nama_pelanggan from pelanggan where nama_pelanggan like 'A%';
+----------------+----------------+
| kode_pelanggan | nama_pelanggan |
+----------------+----------------+
| P001 | Achmad Budi |
| P003 | Agustina |
+----------------+----------------+
2 rows in set (0.00 sec)
5.
mysql> select nama_pelanggan from pelanggan, transaksi, komik where transaksi.kode_komik=komik.kode_komik and pelanggan.kode_pelanggan=transaksi.kode_pelanggan and komik.judul_komik='Shincan';
+----------------+
| nama_pelanggan |
+----------------+
| Achmad Budi |
| Agustina |
+----------------+
2 rows in set (0.03 sec)
6.
mysql> select nama_pelanggan,alamat,judul_komik,harga_jual, tanggal from komik, pelanggan, transaksi where transaksi.kode_pelanggan=pelanggan.kode_pelanggan and transaksi.kode_komik=komik.kode_k
omik and tanggal='2008-05-10';
+----------------+--------------+-------------+------------+------------+
| nama_pelanggan | alamat | judul_komik | harga_jual | tanggal |
+----------------+--------------+-------------+------------+------------+
| Achmad Budi | Jl. Nuri 123 | Fight Ippo | 11000 | 2008-05-10 |
| Achmad Budi | Jl. Nuri 123 | Shincan | 11000 | 2008-05-10 |
+----------------+--------------+-------------+------------+------------+
2 rows in set (0.00 sec)
7.
mysql> select count(kode_pelanggan) as 'Jumlah Pelanggan' from transaksi group by kode_pelanggan;
+------------------+
| Jumlah Pelanggan |
+------------------+
| 2 |
| 1 |
| 1 |
+------------------+
3 rows in set (0.00 sec)
8.
mysql> select judul_komik, harga_jual from komik, pelanggan, transaksi where komik.kode_komik=transaksi.kode_komik and pelanggan.kode_pelanggan=transaksi.kode_pelanggan;
+--------------+------------+
| judul_komik | harga_jual |
+--------------+------------+
| Fight Ippo | 11000 |
| Shincan | 11000 |
| Shincan | 11000 |
| Legenda Naga | 12000 |
+--------------+------------+
4 rows in set (0.00 sec)
9.
mysql> select kode_komik, judul_komik, max(harga_jual) from komik;
+------------+--------------+-----------------+
| kode_komik | judul_komik | max(harga_jual) |
+------------+--------------+-----------------+
| K001 | Legenda Naga | 12000 |
+------------+--------------+-----------------+
1 row in set (0.00 sec)
10.
mysql> select p.kode_pelanggan, p.nama_pelanggan, k.kode_komik, k.judul_komik, k.harga_jual, t.tanggal, t.jumlah_pembelian, k.harga_jual*t.jumlah_pembelian as "Total Pembelian" from pelanggan p,
komik k, transaksi t where t.kode_pelanggan=p.kode_pelanggan and t.kode_komik=k.kode_komik;
+----------------+----------------+------------+--------------+------------+------------+------------------+-----------------+
| kode_pelanggan | nama_pelanggan | kode_komik | judul_komik | harga_jual | tanggal | jumlah_pembelian | Total Pembelian |
+----------------+----------------+------------+--------------+------------+------------+------------------+-----------------+
| P001 | Achmad Budi | K002 | Fight Ippo | 11000 | 2008-05-10 | 3 | 33000 |
| P001 | Achmad Budi | K003 | Shincan | 11000 | 2008-05-10 | 2 | 22000 |
| P003 | Agustina | K003 | Shincan | 11000 | 2008-05-11 | 1 | 11000 |
| P002 | Budi Susanto | K001 | Legenda Naga | 12000 | 2008-05-11 | 4 | 48000 |
+----------------+----------------+------------+--------------+------------+------------+------------------+-----------------+
4 rows in set (0.01 sec)
mysql> select t.kode_pelanggan, p.nama_pelanggan, t.kode_komik, k.judul_komik, k.harga_jual, t.tanggal, t.jumlah_pembelian from komik k, pelanggan p, transaksi t where t.kode_pelanggan=p.kode_pel
anggan and t.kode_komik=k.kode_komik;
+----------------+----------------+------------+--------------+------------+------------+------------------+
| kode_pelanggan | nama_pelanggan | kode_komik | judul_komik | harga_jual | tanggal | jumlah_pembelian |
+----------------+----------------+------------+--------------+------------+------------+------------------+
| P001 | Achmad Budi | K002 | Fight Ippo | 11000 | 2008-05-10 | 3 |
| P001 | Achmad Budi | K003 | Shincan | 11000 | 2008-05-10 | 2 |
| P003 | Agustina | K003 | Shincan | 11000 | 2008-05-11 | 1 |
| P002 | Budi Susanto | K001 | Legenda Naga | 12000 | 2008-05-11 | 4 |
+----------------+----------------+------------+--------------+------------+------------+------------------+
4 rows in set (0.00 sec)