Breaking News

Pra Responsi SMBD


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)



No comments