*) 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

Your Comment