msql

Cara membuat database penerbangan di mysql
1. Buka msql enter
2. create database penerbangan; enter
3. use penerbangan; enter(untuk masuk ke database penerbangan)
4. untuk membuat tabel pesawat
create table pesawat
(kode_pesawat varchar(5) primary key,
nama_pesawat varchar(15),
 kapasitas varchar(7));
5. untuk melihat tabel kita desc pesawat;
mysql> desc pesawat;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| kode_pesawat | varchar(5)  | NO   | PRI | NULL    |       |
| nama_pesawat | varchar(15) | YES  |     | NULL    |       |
| kapasitas    | varchar(7)  | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
4.selanjutnya kita akan mengisi tabel
insert into pesawat values
('p01','garuda01','500'),
('p02','garuda02','200'),
('p03','garuda03','140');
7.Untuk melihat isi tabel
select * from pesawat;
mysql> select * from pesawat;
+--------------+--------------+-----------+
| kode_pesawat | nama_pesawat | kapasitas |
+--------------+--------------+-----------+
| p01          | garuda01     | 500       |
| p02          | garuda02     | 200       |
| p03          | garuda03     | 140       |
+--------------+--------------+-----------+
3 rows in set (0.00 sec)

8. membuat tabel jadwal
create table jadwal
(kode_jadwal varchar(5) primary key,
tanggal Date,
waktu Time,
kode_pesawat varchar(5));
9. melihat tabel jadwal desc jadwal;
mysql> desc jadwal;
+--------------+------------+------+-----+---------+-------+
| Field        | Type       | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| kode_jadwal  | varchar(5) | NO   | PRI | NULL    |       |
| tanggal      | date       | YES  |     | NULL    |       |
| waktu        | time       | YES  |     | NULL    |       |
| kode_pesawat | varchar(5) | YES  |     | NULL    |       |
+--------------+------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

10. selanjutnya kita akan mengisi tabel jadwal
insert into jadwal values
('Jp','2010-06-15','09:00:00','P01'),
('JKp','2010-07-17','10:00:00','P02'),
('Jkj','2010-08-18','11:00:00','P03');
11.select * from jadwal;(melihat isi tabel)
mysql> select * from jadwal;
+-------------+------------+----------+--------------+
| kode_jadwal | tanggal    | waktu    | kode_pesawat |
+-------------+------------+----------+--------------+
| JJ          | 2010-06-15 | 09:00:00 | P01          |
| JK          | 2010-07-17 | 10:00:00 | P02          |
| JL          | 2010-08-18 | 11:00:00 | P03          |
+-------------+------------+----------+--------------+

3 rows in set (0.02 sec)

12.membuat tabel rute
create table rute
(Kode_Rute varchar(5) primary key,
Tujuan varchar(25),
Kedatangan varchar(25));

13.desc rute;(u/melihat tabel rute)
mysql> desc rute;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| Kode_Rute  | varchar(5)  | NO   | PRI | NULL    |       |
| Tujuan     | varchar(25) | YES  |     | NULL    |       |
| Kedatangan | varchar(25) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


14.mengisi tabel rute

insert into rute values
('R01','Samarinda-Bandung','Bandung-Samarinda'),
('R02','Samarinda–Jakarta','Jakarta-Samarinda'),
('R03','Samarinda-Batam','Batam-Samarinda');
15. select * from rute; (untuk melihat isi tabel)
mysql> select * from rute;
+-----------+-------------------+-------------------+
| Kode_Rute | Tujuan            | Kedatangan        |
+-----------+-------------------+-------------------+
| R01       | Samarinda-Bandung | Bandung-Samarinda |
| R02       | Samarinda-Jakarta | Jakarta-Samarinda |
| R03       | Samarinda-Batam   | Batam-Samarinda   |
+-----------+-------------------+-------------------+
3 rows in set (0.00 sec)

16.membuat tabel rate
create table rate
(Kode_Rate varchar(5) primary key,
Harga varchar(10),
Kode_Rute varchar(5),
Kode_jadwal varchar(5));
18.mysql> desc rate;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| Kode_Rate   | varchar(5)  | NO   | PRI | NULL    |       |
| Harga       | varchar(10) | YES  |     | NULL    |       |
| Kode_Rute   | varchar(5)  | YES  |     | NULL    |       |
| Kode_jadwal | varchar(5)  | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

19.mengisi tabel rate
insert into rate values
('T1','Rp.1.000.000','R01','JJ'),
('T2','Rp.850.000','R02','JK'),
('T3','Rp.800.000','R03','JL');


mysql> select * from rate;
+-----------+------------+-----------+-------------+
| Kode_Rate | Harga      | Kode_Rute | Kode_jadwal |
+-----------+------------+-----------+-------------+
| T1        | Rp.1.000.0 | R01       | JJ          |
| T2        | Rp.850.000 | R02       | JK          |
| T3        | Rp.800.000 | R03       | JL          |
+-----------+------------+-----------+-------------+
3 rows in set (0.00 sec)

21.membuat tabel penerbangan
create table penerbangan
(Kode_Penerbangan varchar(5) primary key,
Kode_Rute varchar(5),
Kode_jadwal varchar(5),
Kode_Pilot varchar(5),
Kode_pewasat varchar(5));

mysql> desc penerbangan;
+------------------+------------+------+-----+---------+-------+
| Field            | Type       | Null | Key | Default | Extra |
+------------------+------------+------+-----+---------+-------+
| Kode_Penerbangan | varchar(5) | NO   | PRI | NULL    |       |
| Kode_Rute        | varchar(5) | YES  |     | NULL    |       |
| Kode_jadwal      | varchar(5) | YES  |     | NULL    |       |
| Kode_Pilot       | varchar(5) | YES  |     | NULL    |       |
| Kode_pewasat     | varchar(5) | YES  |     | NULL    |       |
+------------------+------------+------+-----+---------+-------+
5 rows in set (0.08 sec)

22.mengisi tabel penerbangan
  insert into penerbangan values
('K1','R01','JJ','B1','P01'),
('K2','R02','JK','B2','P02'),
('K3','R03','JL','B3','P03');
mysql> select * from penerbangan;
+------------------+-----------+-------------+------------+--------------+
| Kode_Penerbangan | Kode_Rute | Kode_jadwal | Kode_Pilot | Kode_pesawat |
+------------------+-----------+-------------+------------+--------------+
| K1               | R01       | JJ          | B1         | P01          |
| K2               | R02       | JK          | B2         | P02          |
| K3               | R03       | JL          | B3         | P03          |
+------------------+-----------+-------------+------------+--------------+
3 rows in set (0.00 sec)

23.membuat tabel pilot
create table pilot
(Kode_Pilot varchar(5) primary key,
Nama varchar(15),
Alamat varchar(15));
mysql> desc pilot;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| Kode_Pilot | varchar(5)  | NO   | PRI | NULL    |       |
| Nama       | varchar(15) | YES  |     | NULL    |       |
| Alamat     | varchar(15) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

24.mengisi tabel pilot
insert into pilot values
('B1','ADI','JAKARTA'),
('B2','ADE','Samarinda'),
('B3','FENDI','BATAM');

mysql> select * from pilot;
+------------+-------+-----------+
| Kode_Pilot | Nama  | Alamat    |
+------------+-------+-----------+
| B1         | ADI   | JAKARTA   |
| B2         | ADE   | Samarinda |
| B3         | FENDI | BATAM     |
+------------+-------+-----------+
3 rows in set (0.00 sec)

25.membuat tabel penumpang
create table penumpang
(Nama varchar(10),
Alamat varchar(10),
Kode_Rute varchar(5),
Kode_Penerbangan varchar(5));

mysql> desc penumpang;
+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| Nama             | varchar(10) | YES  |     | NULL    |       |
| Alamat           | varchar(10) | YES  |     | NULL    |       |
| Kode_Rute        | varchar(5)  | YES  |     | NULL    |       |
| Kode_Penerbangan | varchar(5)  | YES  |     | NULL    |       |
+------------------+-------------+------+-----+---------+-------+
4 rows in set (0.06 sec)

26. mengisi tabel penumpang
insert into penumpang values
('Cici','Bandung','K1'),
('Yati','Batam','K2'),
('Elvi','samarinda','R03','K3');

mysql> select * from penumpang;
+------+-----------+------------------+
| Nama | Alamat    | Kode_Penerbangan |
+------+-----------+------------------+
| Cici | Bandung   | K1               |
| Yati | Batam     | K2               |
| Elvi | samarinda | K3               |
+------+-----------+------------------+
3 rows in set (0.00 sec)


6.manggabungkan
a)penerbangan dan penumpang

select * from penerbangan natural join penumpang;
mysql> select * from penerbangan natural join penumpang;
+------------------+-----------+-------------+------------+--------------+------+-----------+
| Kode_Penerbangan | Kode_Rute | Kode_jadwal | Kode_Pilot | Kode_pesawat | Nama | Alamat    |
+------------------+-----------+-------------+------------+--------------+------+-----------+
| K1               | R01       | JJ          | B1         | P01          | Cici | Bandung   |
| K2               | R02       | JK          | B2         | P02          | Yati | Batam     |
| K3               | R03       | JL          | B3         | P03          | Elvi | samarinda |
+------------------+-----------+-------------+------------+--------------+------+-----------+
3 rows in set (0.01 sec)

b)select * from penerbangan natural join pilot;

mysql> select * from penerbangan natural join pilot;
+------------+------------------+-----------+-------------+--------------+-------+-----------+
| Kode_Pilot | Kode_Penerbangan | Kode_Rute | Kode_jadwal | Kode_pesawat | Nama  | Alamat    |
+------------+------------------+-----------+-------------+--------------+-------+-----------+
| B1         | K1               | R01       | JJ          | P01          | ADI   | JAKARTA   |
| B2         | K2               | R02       | JK          | P02          | ADE   | Samarinda |
| B3         | K3               | R03       | JL          | P03          | FENDI | BATAM     |
+------------+------------------+-----------+-------------+--------------+-------+-----------+
3 rows in set (0.02 sec)

c)select * from jadwal natural join pesawat;

mysql> select * from jadwal natural join pesawat;
+--------------+-------------+------------+----------+--------------+-----------+
| kode_pesawat | kode_jadwal | tanggal    | waktu    | nama_pesawat | kapasitas |
+--------------+-------------+------------+----------+--------------+-----------+
| P01          | JJ          | 2010-06-15 | 09:00:00 | garuda01     | 500       |
| P02          | JK          | 2010-07-17 | 10:00:00 | garuda02    | 200       |
| P03          | JL          | 2010-08-18 | 11:00:00 | garuda03     | 140       |
+--------------+-------------+------------+----------+--------------+-----------+
3 rows in set (0.00 sec)