*) Update table add foreign key / change table structure
mysql> alter table NamaTabel
add constraint
foreign key (FOREIGNKEYFIELD) references TABLENAME(PRIMARYKEYFIELD);
*) Change value table
mysql> update NamaTabel
set FieldName='NewValue'
where FieldName='OldValue';
1)Tampilkan pegawai dan jabatan
mysql> select p.nama, j.jabatan
-> from pegawai p, jabatan j
-> where p.kj=j.kj;
+------------+------------------+
| nama | jabatan |
+------------+------------------+
| Amir Yahya | System Analyst |
| Bayu | Marketing Leader |
| Dahlan | Programmer |
| Rizal | Programmer |
| Iwan | Network Engineer |
| Dewi | Sekretaris |
| Hakim | Programmer |
+------------+------------------+
7 rows in set (0.00 sec)
2)Tampilkan pegawai dan anaknya
mysql> select p.nama, a.nama_anak
-> from pegawai p, anak a
-> where p.nip=a.nip;
+------+-----------+
| nama | nama_anak |
+------+-----------+
| Bayu | Haris |
| Bayu | Hany |
| Iwan | Sitti |
| Iwan | Sultan |
+------+-----------+
4 rows in set (0.00 sec)
3)Tampilkan pegawai dan gajinya
mysql> select p.nama, j.gaji
-> from pegawai p, jabatan j
-> where p.kj=j.kj;
+------------+---------+
| nama | gaji |
+------------+---------+
| Amir Yahya | 6000000 |
| Bayu | 7000000 |
| Dahlan | 3000000 |
| Rizal | 3000000 |
| Iwan | 4500000 |
| Dewi | 3000000 |
| Hakim | 3000000 |
+------------+---------+
7 rows in set (0.00 sec)
4) Tampilkan pegawai dg gaji tertinggi
mysql> select p.nama, j.gaji
-> from pegawai p, jabatan j
-> where j.kj=p.kj order by j.gaji desc
-> limit 0,1;
+------+---------+
| nama | gaji |
+------+---------+
| Bayu | 7000000 |
+------+---------+
1 row in set (0.00 sec)
5)Tampilkan pegawai dg gaji > 5000000
mysql> select p.nama, j.gaji
-> from pegawai p, jabatan j
-> where p.kj=j.kj and j.gaji<5000000;
+--------+---------+
| nama | gaji |
+--------+---------+
| Dahlan | 3000000 |
| Rizal | 3000000 |
| Iwan | 4500000 |
| Dewi | 3000000 |
| Hakim | 3000000 |
+--------+---------+
5 rows in set (0.00 sec)
6)Tampilkan pegawai dan gaji >=1500000 dan <=4000000
mysql> select p.nama, j.gaji
-> from pegawai p, jabatan j
-> where p.kj=j.kj and j.gaji>=1500000 and j.gaji<=4000000;
+--------+---------+
| nama | gaji |
+--------+---------+
| Dahlan | 3000000 |
| Rizal | 3000000 |
| Dewi | 3000000 |
| Hakim | 3000000 |
+--------+---------+
4 rows in set (0.05 sec)
7)Tampilkan pegawai dan anaknya yg berumur < 5
mysql> select p.nama, a.nama_anak, a.umur
-> from pegawai p, anak a
-> where p.nip=a.nip and a.umur<5;
+------+-----------+------+
| nama | nama_anak | umur |
+------+-----------+------+
| Iwan | Sultan | 1 |
+------+-----------+------+
1 row in set (0.00 sec)
8)Tampilkan View dari 3 Tabel
mysql> select p.nama, a.nama_anak, j.jabatan
-> from pegawai p, anak a, jabatan j
-> where p.nip=a.nip and p.kj=j.kj;
+------+-----------+------------------+
| nama | nama_anak | jabatan |
+------+-----------+------------------+
| Bayu | Haris | Marketing Leader |
| Bayu | Hany | Marketing Leader |
| Iwan | Sitti | Network Engineer |
| Iwan | Sultan | Network Engineer |
+------+-----------+------------------+
4 rows in set (0.00 sec)
No Comment.
Add Your Comment